The diagnose_outlier() produces outlier 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_outlier(.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_outlier() 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 provide a outlier information. If the number of outliers is small and the difference between the averages including outliers and the averages not including them is large, it is necessary to eliminate or replace the outliers.
The information derived from the numerical data diagnosis is as follows.
variables : variable names
outliers_cnt : number of outliers
outliers_ratio : percent of outliers
outliers_mean : arithmetic average of outliers
with_mean : arithmetic average of with outliers
without_mean : arithmetic average of without 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_outlier()#> # A tibble: 7 x 6 #> variables outliers_cnt outliers_ratio outliers_mean with_mean without_mean #> <chr> <int> <dbl> <dbl> <dbl> <dbl> #> 1 age 0 0 NaN 60.8 60.8 #> 2 cpk_enzyme 29 9.70 2905. 582. 332. #> 3 ejection_fra… 2 0.669 75 38.1 37.8 #> 4 platelets 21 7.02 378386. 263358. 254669. #> 5 creatinine 29 9.70 3.79 1.39 1.14 #> 6 sodium 4 1.34 118. 137. 137. #> 7 time 0 0 NaN 130. 130.# Positive values select variables, and In-memory mode and collect size is 200 con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_outlier(platelets, sodium, collect_size = 200)#> # A tibble: 2 x 6 #> variables outliers_cnt outliers_ratio outliers_mean with_mean without_mean #> * <chr> <int> <dbl> <dbl> <dbl> <dbl> #> 1 platelets 11 5.5 407364. 261477. 252986. #> 2 sodium 5 2.5 120 137. 137.# Negative values to drop variables con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_outlier(-platelets, -sodium)#> # A tibble: 5 x 6 #> variables outliers_cnt outliers_ratio outliers_mean with_mean without_mean #> * <chr> <int> <dbl> <dbl> <dbl> <dbl> #> 1 age 0 0 NaN 60.8 60.8 #> 2 cpk_enzyme 29 9.70 2905. 582. 332. #> 3 ejection_fra… 2 0.669 75 38.1 37.8 #> 4 creatinine 29 9.70 3.79 1.39 1.14 #> 5 time 0 0 NaN 130. 130.#> # A tibble: 1 x 6 #> variables outliers_cnt outliers_ratio outliers_mean with_mean without_mean #> * <chr> <int> <dbl> <dbl> <dbl> <dbl> #> 1 ejection_fra… 2 0.669 75 38.1 37.8# Negative values to drop variables con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_outlier(-1, -5)#> # A tibble: 5 x 6 #> variables outliers_cnt outliers_ratio outliers_mean with_mean without_mean #> * <chr> <int> <dbl> <dbl> <dbl> <dbl> #> 1 cpk_enzyme 29 9.70 2905. 582. 332. #> 2 platelets 21 7.02 378386. 263358. 254669. #> 3 creatinine 29 9.70 3.79 1.39 1.14 #> 4 sodium 4 1.34 118. 137. 137. #> 5 time 0 0 NaN 130. 130.# Using pipes & dplyr ------------------------- # outlier_ratio is more than 1% con_sqlite %>% tbl("TB_HEARTFAILURE") %>% diagnose_outlier() %>% filter(outliers_ratio > 1)#> # A tibble: 4 x 6 #> variables outliers_cnt outliers_ratio outliers_mean with_mean without_mean #> <chr> <int> <dbl> <dbl> <dbl> <dbl> #> 1 cpk_enzyme 29 9.70 2905. 582. 332. #> 2 platelets 21 7.02 378386. 263358. 254669. #> 3 creatinine 29 9.70 3.79 1.39 1.14 #> 4 sodium 4 1.34 118. 137. 137.