R/tbl_dbi.R
diagnose_numeric.tbl_dbi.RdThe diagnose_numeric() produces information for diagnosing the quality of the numerical(INTEGER, NUMBER, etc.) column of the DBMS table through tbl_dbi.
# S3 method for tbl_dbi diagnose_numeric(.data, ..., in_database = FALSE, collect_size = Inf)
| .data | a tbl_dbi. |
|---|---|
| ... | one or more unquoted expressions separated by commas. You can treat variable names like they are positions. Positive values select variables; negative values to drop variables. If the first expression is negative, diagnose_numeric() will automatically start with all variables. These arguments are automatically quoted and evaluated in a context where column names represent column positions. They support unquoting and splicing. |
| in_database | Specifies whether to perform in-database operations. If TRUE, most operations are performed in the DBMS. if FALSE, table data is taken in R and operated in-memory. Not yet supported in_database = TRUE. |
| collect_size | a integer. The number of data samples from the DBMS to R. Applies only if in_database = FALSE. |
an object of tbl_df.
The scope of the diagnosis is the calculate a statistic that can be used to understand the distribution of numerical data. min, Q1, mean, median, Q3, max can be used to estimate the distribution of data. If the number of zero or minus is large, it is necessary to suspect the error of the data. If the number of outliers is large, a strategy of eliminating or replacing outliers is needed.
The information derived from the numerical data diagnosis is as follows.
variables : variable names
min : minimum
Q1 : 25 percentile
mean : arithmetic average
median : median. 50 percentile
Q3 : 75 percentile
max : maximum
zero : count of zero values
minus : count of minus values
outlier : count of outliers
See vignette("diagonosis") for an introduction to these concepts.
# \donttest{ library(dplyr) # connect DBMS con_sqlite <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") # copy heartfailure to the DBMS with a table named TB_HEARTFAILURE copy_to(con_sqlite, heartfailure, name = "TB_HEARTFAILURE", overwrite = TRUE) # Using pipes --------------------------------- # Diagnosis of all numerical variables con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_numeric()#> # A tibble: 7 x 10 #> variables min Q1 mean median Q3 max zero minus outlier #> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> #> 1 age 40 51 6.08e1 6 e1 7 e1 9.5 e1 0 0 0 #> 2 cpk_enzyme 23 116. 5.82e2 2.5 e2 5.82e2 7.86e3 0 0 29 #> 3 ejection_fr… 14 30 3.81e1 3.8 e1 4.5 e1 8 e1 0 0 2 #> 4 platelets 25100 212500 2.63e5 2.62e5 3.04e5 8.5 e5 0 0 21 #> 5 creatinine 0.5 0.9 1.39e0 1.1 e0 1.4 e0 9.4 e0 0 0 29 #> 6 sodium 113 134 1.37e2 1.37e2 1.4 e2 1.48e2 0 0 4 #> 7 time 4 73 1.30e2 1.15e2 2.03e2 2.85e2 0 0 0# Positive values select variables, and In-memory mode and collect size is 200 con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_numeric(age, sodium, collect_size = 200)#> # A tibble: 2 x 10 #> variables min Q1 mean median Q3 max zero minus outlier #> * <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> #> 1 age 40 52 62.0 60 70 95 0 0 0 #> 2 sodium 113 134 137. 137 139 146 0 0 5# Negative values to drop variables con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_numeric(-age, -sodium)#> # A tibble: 5 x 10 #> variables min Q1 mean median Q3 max zero minus outlier #> * <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> #> 1 cpk_enzyme 23 116. 5.82e2 2.5 e2 5.82e2 7.86e3 0 0 29 #> 2 ejection_fr… 14 30 3.81e1 3.8 e1 4.5 e1 8 e1 0 0 2 #> 3 platelets 25100 212500 2.63e5 2.62e5 3.04e5 8.5 e5 0 0 21 #> 4 creatinine 0.5 0.9 1.39e0 1.1 e0 1.4 e0 9.4 e0 0 0 29 #> 5 time 4 73 1.30e2 1.15e2 2.03e2 2.85e2 0 0 0#> # A tibble: 1 x 10 #> variables min Q1 mean median Q3 max zero minus outlier #> * <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> #> 1 ejection_fraction 14 30 38.1 38 45 80 0 0 2# Negative values to drop variables con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_numeric(-1, -5)#> # A tibble: 5 x 10 #> variables min Q1 mean median Q3 max zero minus outlier #> * <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> #> 1 cpk_enzyme 23 116. 5.82e2 2.5 e2 5.82e2 7.86e3 0 0 29 #> 2 platelets 25100 212500 2.63e5 2.62e5 3.04e5 8.5 e5 0 0 21 #> 3 creatinine 0.5 0.9 1.39e0 1.1 e0 1.4 e0 9.4 e0 0 0 29 #> 4 sodium 113 134 1.37e2 1.37e2 1.4 e2 1.48e2 0 0 4 #> 5 time 4 73 1.30e2 1.15e2 2.03e2 2.85e2 0 0 0# Using pipes & dplyr ------------------------- # List of variables containing outliers con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_numeric() %>% filter(outlier > 0)#> # A tibble: 5 x 10 #> variables min Q1 mean median Q3 max zero minus outlier #> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> #> 1 cpk_enzyme 23 116. 5.82e2 2.5 e2 5.82e2 7.86e3 0 0 29 #> 2 ejection_fr… 14 30 3.81e1 3.8 e1 4.5 e1 8 e1 0 0 2 #> 3 platelets 25100 212500 2.63e5 2.62e5 3.04e5 8.5 e5 0 0 21 #> 4 creatinine 0.5 0.9 1.39e0 1.1 e0 1.4 e0 9.4 e0 0 0 29 #> 5 sodium 113 134 1.37e2 1.37e2 1.4 e2 1.48e2 0 0 4