The diagnose() produces information for diagnosing the quality of the column of the DBMS table through tbl_dbi.
# S3 method for tbl_dbi
diagnose(.data, ..., in_database = TRUE, collect_size = Inf)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() 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.
a logical. 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.
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 data quality diagnosis is information on missing values and unique value information. Data quality diagnosis can determine variables that require missing value processing. Also, the unique value information can determine the variable to be removed from the data analysis. You can use grouped_df as the group_by() function.
The information derived from the data diagnosis is as follows.:
variables : column names
types : data type of the variable or to select a variable to be corrected or removed through data diagnosis.
integer, numeric, factor, ordered, character, etc.
missing_count : number of missing values
missing_percent : percentage of missing values
unique_count : number of unique values
unique_rate : ratio of unique values. unique_count / number of observation
See vignette("diagonosis") for an introduction to these concepts.
# \donttest{
library(dplyr)
if (requireNamespace("DBI", quietly = TRUE) & requireNamespace("RSQLite", quietly = TRUE)) {
# connect DBMS
con_sqlite <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
# copy jobchange to the DBMS with a table named TB_JOBCHANGE
copy_to(con_sqlite, jobchange, name = "TB_JOBCHANGE", overwrite = TRUE)
# Using pipes ---------------------------------
# Diagnosis of all columns
con_sqlite %>%
tbl("TB_JOBCHANGE") %>%
diagnose() %>%
print()
# Positions values select columns, and In-memory mode and collect size is 200
con_sqlite %>%
tbl("TB_JOBCHANGE") %>%
diagnose(gender, education_level, company_size, in_database = FALSE, collect_size = 200) %>%
print()
# Using pipes & dplyr -------------------------
# Diagnosis of missing variables
con_sqlite %>%
tbl("TB_JOBCHANGE") %>%
diagnose() %>%
filter(missing_count > 0) %>%
print()
# Using pipes & dplyr -------------------------
# Diagnosis of missing variables
con_sqlite %>%
tbl("TB_JOBCHANGE") %>%
group_by(job_chnge) %>%
diagnose() %>%
print()
# Disconnect DBMS
DBI::dbDisconnect(con_sqlite)
} else {
cat("If you want to use this feature, you need to install the 'DBI' and 'RSQLite' package.\n")
}
#> # A tibble: 14 × 6
#> variables types missing_count missing_percent unique_count unique_rate
#> <chr> <chr> <dbl> <dbl> <int> <dbl>
#> 1 enrollee_id char… 0 0 19158 1
#> 2 city char… 0 0 123 0.00642
#> 3 city_dev_index doub… 0 0 93 0.00485
#> 4 gender char… 4508 23.5 3 0.000157
#> 5 relevent_experi… char… 0 0 2 0.000104
#> 6 enrolled_univer… char… 386 2.01 3 0.000157
#> 7 education_level char… 460 2.40 5 0.000261
#> 8 major_discipline char… 2813 14.7 6 0.000313
#> 9 experience char… 65 0.339 22 0.00115
#> 10 company_size char… 5938 31.0 8 0.000418
#> 11 company_type char… 6140 32.0 6 0.000313
#> 12 last_new_job char… 423 2.21 6 0.000313
#> 13 training_hours inte… 0 0 241 0.0126
#> 14 job_chnge char… 0 0 2 0.000104
#> # A tibble: 3 × 6
#> variables types missing_count missing_percent unique_count unique_rate
#> <chr> <chr> <int> <dbl> <int> <dbl>
#> 1 gender charac… 48 24 4 0.02
#> 2 education_level charac… 6 3 5 0.025
#> 3 company_size charac… 57 28.5 9 0.045
#> # A tibble: 8 × 6
#> variables types missing_count missing_percent unique_count unique_rate
#> <chr> <chr> <dbl> <dbl> <int> <dbl>
#> 1 gender char… 4508 23.5 3 0.000157
#> 2 enrolled_univers… char… 386 2.01 3 0.000157
#> 3 education_level char… 460 2.40 5 0.000261
#> 4 major_discipline char… 2813 14.7 6 0.000313
#> 5 experience char… 65 0.339 22 0.00115
#> 6 company_size char… 5938 31.0 8 0.000418
#> 7 company_type char… 6140 32.0 6 0.000313
#> 8 last_new_job char… 423 2.21 6 0.000313
#> # A tibble: 28 × 8
#> variables types job_chnge data_count missing_count missing_percent
#> <chr> <chr> <chr> <int> <dbl> <dbl>
#> 1 enrollee_id chara… No 14381 0 0
#> 2 enrollee_id chara… Yes 4777 0 0
#> 3 city chara… No 14381 0 0
#> 4 city chara… Yes 4777 0 0
#> 5 city_dev_index double No 14381 0 0
#> 6 city_dev_index double Yes 4777 0 0
#> 7 gender chara… No 14381 3119 21.7
#> 8 gender chara… Yes 4777 1389 29.1
#> 9 relevent_experience chara… No 14381 0 0
#> 10 relevent_experience chara… Yes 4777 0 0
#> # ℹ 18 more rows
#> # ℹ 2 more variables: unique_count <int>, unique_rate <dbl>
# }