| Title: | Simplifies Plotting Data Inside Databases |
|---|---|
| Description: | Leverages 'dplyr' to process the calculations of a plot inside a database. This package provides helper functions that abstract the work at three levels: outputs a 'ggplot', outputs the calculations, outputs the formula needed to calculate bins. |
| Authors: | Edgar Ruiz [aut, cre] |
| Maintainer: | Edgar Ruiz <[email protected]> |
| License: | GPL-3 |
| Version: | 0.3.3 |
| Built: | 2024-11-07 03:11:20 UTC |
| Source: | https://github.com/edgararuiz/dbplot |
Uses the rlang package to build the formula needed to create the bins of a numeric variable in an unevaluated fashion. This way, the formula can be then passed inside a dplyr verb.
db_bin(var, bins = 30, binwidth = NULL)db_bin(var, bins = 30, binwidth = NULL)
var |
Variable name or formula |
bins |
Number of bins. Defaults to 30. |
binwidth |
Single value that sets the side of the bins, it overrides bins |
library(dplyr) # Important: Always name the field and # prefix the function with `!!`` (See Details) # Uses the default 30 bins mtcars %>% group_by(x = !!db_bin(mpg)) %>% tally() # Uses binwidth which overrides bins mtcars %>% group_by(x = !!db_bin(mpg, binwidth = 10)) %>% tally()library(dplyr) # Important: Always name the field and # prefix the function with `!!`` (See Details) # Uses the default 30 bins mtcars %>% group_by(x = !!db_bin(mpg)) %>% tally() # Uses binwidth which overrides bins mtcars %>% group_by(x = !!db_bin(mpg, binwidth = 10)) %>% tally()
Uses very generic dplyr code to create histogram bins. Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_spark
db_compute_bins(data, x, bins = 30, binwidth = NULL)db_compute_bins(data, x, bins = 30, binwidth = NULL)
data |
A table (tbl) |
x |
A continuous variable |
bins |
Number of bins. Defaults to 30. |
binwidth |
Single value that sets the side of the bins, it overrides bins |
# Returns record count for 30 bins in mpg mtcars %>% db_compute_bins(mpg) # Returns record count for bins of size 10 mtcars %>% db_compute_bins(mpg, binwidth = 10)# Returns record count for 30 bins in mpg mtcars %>% db_compute_bins(mpg) # Returns record count for bins of size 10 mtcars %>% db_compute_bins(mpg, binwidth = 10)
Uses very generic dplyr code to create boxplot calculations. Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_spark
It currently only works with Spark, Hive, and SQL Server connections.
Note that this function supports input tbl that already contains grouping variables. This can be useful when creating faceted boxplots.
db_compute_boxplot(data, x, var, coef = 1.5)db_compute_boxplot(data, x, var, coef = 1.5)
data |
A table (tbl), can already contain additional grouping vars specified |
x |
A discrete variable in which to group the boxplots |
var |
A continuous variable |
coef |
Length of the whiskers as multiple of IQR. Defaults to 1.5 |
mtcars %>% db_compute_boxplot(am, mpg)mtcars %>% db_compute_boxplot(am, mpg)
Uses very generic dplyr code to aggregate data. Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_sql
db_compute_count(data, x, ..., y = n())db_compute_count(data, x, ..., y = n())
data |
A table (tbl) |
x |
A discrete variable |
... |
A set of named or unamed aggregations |
y |
The aggregation formula. Defaults to count (n) |
# Returns the row count per am mtcars %>% db_compute_count(am) # Returns the average mpg per am mtcars %>% db_compute_count(am, mean(mpg)) # Returns the average and sum of mpg per am mtcars %>% db_compute_count(am, mean(mpg), sum(mpg))# Returns the row count per am mtcars %>% db_compute_count(am) # Returns the average mpg per am mtcars %>% db_compute_count(am, mean(mpg)) # Returns the average and sum of mpg per am mtcars %>% db_compute_count(am, mean(mpg), sum(mpg))
To visualize two continuous variables, we typically resort to a Scatter plot. However, this may not be practical when visualizing millions or billions of dots representing the intersections of the two variables. A Raster plot may be a better option, because it concentrates the intersections into squares that are easier to parse visually.
Uses very generic dplyr code to aggregate data. Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_sql
db_compute_raster(data, x, y, fill = n(), resolution = 100, complete = FALSE) db_compute_raster2(data, x, y, fill = n(), resolution = 100, complete = FALSE)db_compute_raster(data, x, y, fill = n(), resolution = 100, complete = FALSE) db_compute_raster2(data, x, y, fill = n(), resolution = 100, complete = FALSE)
data |
A table (tbl) |
x |
A continuous variable |
y |
A continuous variable |
fill |
The aggregation formula. Defaults to count (n) |
resolution |
The number of bins created by variable. The highest the number, the more records can be potentially imported from the source |
complete |
Uses tidyr::complete to include empty bins. Inserts value of 0. |
There are two considerations when using a Raster plot with a database. Both considerations are related to the size of the results downloaded from the database:
- The number of bins requested: The higher the bins value is, the more data is downloaded from the database.
- How concentrated the data is: This refers to how many intersections return a value. The more intersections without a value, the less data is downloaded from the database.
# Returns a 100x100 grid of record count of intersections of eruptions and waiting faithful %>% db_compute_raster(eruptions, waiting) # Returns a 50x50 grid of eruption averages of intersections of eruptions and waiting faithful %>% db_compute_raster(eruptions, waiting, fill = mean(eruptions), resolution = 50)# Returns a 100x100 grid of record count of intersections of eruptions and waiting faithful %>% db_compute_raster(eruptions, waiting) # Returns a 50x50 grid of eruption averages of intersections of eruptions and waiting faithful %>% db_compute_raster(eruptions, waiting, fill = mean(eruptions), resolution = 50)
Uses very generic dplyr code to aggregate data and then 'ggplot2' to create the plot. Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_spark
dbplot_bar(data, x, ..., y = n())dbplot_bar(data, x, ..., y = n())
data |
A table (tbl) |
x |
A discrete variable |
... |
A set of named or unamed aggregations |
y |
The aggregation formula. Defaults to count (n) |
dbplot_line ,
dbplot_histogram, dbplot_raster
library(ggplot2) library(dplyr) # Returns a plot of the row count per am mtcars %>% dbplot_bar(am) # Returns a plot of the average mpg per am mtcars %>% dbplot_bar(am, mean(mpg)) # Returns the average and sum of mpg per am mtcars %>% dbplot_bar(am, avg_mpg = mean(mpg), sum_mpg = sum(mpg))library(ggplot2) library(dplyr) # Returns a plot of the row count per am mtcars %>% dbplot_bar(am) # Returns a plot of the average mpg per am mtcars %>% dbplot_bar(am, mean(mpg)) # Returns the average and sum of mpg per am mtcars %>% dbplot_bar(am, avg_mpg = mean(mpg), sum_mpg = sum(mpg))
Uses very generic dplyr code to aggregate data and then 'ggplot2' to create the boxplot Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_spark
It currently only works with Spark and Hive connections.
dbplot_boxplot(data, x, var, coef = 1.5)dbplot_boxplot(data, x, var, coef = 1.5)
data |
A table (tbl) |
x |
A discrete variable in which to group the boxplots |
var |
A continuous variable |
coef |
Length of the whiskers as multiple of IQR. Defaults to 1.5 |
dbplot_bar, dbplot_line ,
dbplot_raster, dbplot_histogram
Uses very generic dplyr code to aggregate data and then 'ggplot2' to create the histogram. Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_spark
dbplot_histogram(data, x, bins = 30, binwidth = NULL)dbplot_histogram(data, x, bins = 30, binwidth = NULL)
data |
A table (tbl) |
x |
A continuous variable |
bins |
Number of bins. Defaults to 30. |
binwidth |
Single value that sets the side of the bins, it overrides bins |
dbplot_bar, dbplot_line ,
dbplot_raster
library(ggplot2) library(dplyr) # A ggplot histogram with 30 bins mtcars %>% dbplot_histogram(mpg) # A ggplot histogram with bins of size 10 mtcars %>% dbplot_histogram(mpg, binwidth = 10)library(ggplot2) library(dplyr) # A ggplot histogram with 30 bins mtcars %>% dbplot_histogram(mpg) # A ggplot histogram with bins of size 10 mtcars %>% dbplot_histogram(mpg, binwidth = 10)
Uses very generic dplyr code to aggregate data and then 'ggplot2' to create a line plot. Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_spark
If multiple named aggregations are passed, 'dbplot' will only use one SQL query to perform all of the operations. The purpose is to increase efficiency, and only make one "trip" to the database in order to obtains multiple, related, plots.
dbplot_line(data, x, ..., y = n())dbplot_line(data, x, ..., y = n())
data |
A table (tbl) |
x |
A discrete variable |
... |
A set of named or unamed aggregations |
y |
The aggregation formula. Defaults to count (n) |
dbplot_bar,
dbplot_histogram, dbplot_raster
library(ggplot2) library(dplyr) # Returns a plot of the row count per cyl mtcars %>% dbplot_line(cyl) # Returns a plot of the average mpg per cyl mtcars %>% dbplot_line(cyl, mean(mpg)) # Returns the average and sum of mpg per am mtcars %>% dbplot_line(am, avg_mpg = mean(mpg), sum_mpg = sum(mpg))library(ggplot2) library(dplyr) # Returns a plot of the row count per cyl mtcars %>% dbplot_line(cyl) # Returns a plot of the average mpg per cyl mtcars %>% dbplot_line(cyl, mean(mpg)) # Returns the average and sum of mpg per am mtcars %>% dbplot_line(am, avg_mpg = mean(mpg), sum_mpg = sum(mpg))
To visualize two continuous variables, we typically resort to a Scatter plot. However, this may not be practical when visualizing millions or billions of dots representing the intersections of the two variables. A Raster plot may be a better option, because it concentrates the intersections into squares that are easier to parse visually.
Uses very generic dplyr code to aggregate data and ggplot2 to create a raster plot. Because of this approach, the calculations automatically run inside the database if 'data' has a database or sparklyr connection. The 'class()' of such tables in R are: tbl_sql, tbl_dbi, tbl_spark
dbplot_raster(data, x, y, fill = n(), resolution = 100, complete = FALSE)dbplot_raster(data, x, y, fill = n(), resolution = 100, complete = FALSE)
data |
A table (tbl) |
x |
A continuous variable |
y |
A continuous variable |
fill |
The aggregation formula. Defaults to count (n) |
resolution |
The number of bins created by variable. The highest the number, the more records can be potentially imported from the sourd |
complete |
Uses tidyr::complete to include empty bins. Inserts value of 0. |
There are two considerations when using a Raster plot with a database. Both considerations are related to the size of the results downloaded from the database:
- The number of bins requested: The higher the bins value is, the more data is downloaded from the database.
- How concentrated the data is: This refers to how many intersections return a value. The more intersections without a value, the less data is downloaded from the database.
dbplot_bar, dbplot_line ,
dbplot_histogram
library(ggplot2) library(dplyr) # Returns a 100x100 raster plot of record count of intersections of eruptions and waiting faithful %>% dbplot_raster(eruptions, waiting) # Returns a 50x50 raster plot of eruption averages of intersections of eruptions and waiting faithful %>% dbplot_raster(eruptions, waiting, fill = mean(eruptions), resolution = 50)library(ggplot2) library(dplyr) # Returns a 100x100 raster plot of record count of intersections of eruptions and waiting faithful %>% dbplot_raster(eruptions, waiting) # Returns a 50x50 raster plot of eruption averages of intersections of eruptions and waiting faithful %>% dbplot_raster(eruptions, waiting, fill = mean(eruptions), resolution = 50)