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)

Arguments

.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() 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

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.

collect_size

a integer. The number of data samples from the DBMS to R. Applies only if in_database = FALSE.

Value

An object of tbl_df.

Details

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.

Diagnostic information

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.

See also

Examples

# \donttest{ library(dplyr) # 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()
#> # A tibble: 14 x 6 #> variables types missing_count missing_percent unique_count unique_rate #> <chr> <chr> <dbl> <dbl> <int> <dbl> #> 1 enrollee_id charac… 0 0 19158 1 #> 2 city charac… 0 0 123 0.00642 #> 3 city_dev_index double 0 0 93 0.00485 #> 4 gender charac… 4508 23.5 4 0.000209 #> 5 relevent_expe… charac… 0 0 2 0.000104 #> 6 enrolled_univ… charac… 386 2.01 4 0.000209 #> 7 education_lev… charac… 460 2.40 6 0.000313 #> 8 major_discipl… charac… 2813 14.7 7 0.000365 #> 9 experience charac… 65 0.339 23 0.00120 #> 10 company_size charac… 5938 31.0 9 0.000470 #> 11 company_type charac… 6140 32.0 7 0.000365 #> 12 last_new_job charac… 423 2.21 7 0.000365 #> 13 training_hours integer 0 0 241 0.0126 #> 14 job_chnge charac… 0 0 2 0.000104
# Positive values select columns con_sqlite %>% tbl("TB_JOBCHANGE") %>% diagnose(gender, education_level, company_size)
#> # A tibble: 3 x 6 #> variables types missing_count missing_percent unique_count unique_rate #> <chr> <chr> <dbl> <dbl> <int> <dbl> #> 1 gender charact… 4508 23.5 4 0.000209 #> 2 education_lev… charact… 460 2.40 6 0.000313 #> 3 company_size charact… 5938 31.0 9 0.000470
# Negative values to drop columns con_sqlite %>% tbl("TB_JOBCHANGE") %>% diagnose(-gender, -education_level, -company_size)
#> # A tibble: 11 x 6 #> variables types missing_count missing_percent unique_count unique_rate #> <chr> <chr> <dbl> <dbl> <int> <dbl> #> 1 enrollee_id charac… 0 0 19158 1 #> 2 city charac… 0 0 123 0.00642 #> 3 city_dev_index double 0 0 93 0.00485 #> 4 relevent_expe… charac… 0 0 2 0.000104 #> 5 enrolled_univ… charac… 386 2.01 4 0.000209 #> 6 major_discipl… charac… 2813 14.7 7 0.000365 #> 7 experience charac… 65 0.339 23 0.00120 #> 8 company_type charac… 6140 32.0 7 0.000365 #> 9 last_new_job charac… 423 2.21 7 0.000365 #> 10 training_hours integer 0 0 241 0.0126 #> 11 job_chnge charac… 0 0 2 0.000104
# Positions values select columns, and In-memory mode con_sqlite %>% tbl("TB_JOBCHANGE") %>% diagnose(1, 3, 8, in_database = FALSE)
#> # A tibble: 3 x 6 #> variables types missing_count missing_percent unique_count unique_rate #> <chr> <chr> <int> <dbl> <int> <dbl> #> 1 enrollee_id charac… 0 0 19158 1 #> 2 city_dev_index numeric 0 0 93 0.00485 #> 3 major_discipli… charac… 2813 14.7 7 0.000365
# Positions values select columns, and In-memory mode and collect size is 200 con_sqlite %>% tbl("TB_JOBCHANGE") %>% diagnose(-8, -9, -10, in_database = FALSE, collect_size = 200)
#> # A tibble: 11 x 6 #> variables types missing_count missing_percent unique_count unique_rate #> <chr> <chr> <int> <dbl> <int> <dbl> #> 1 enrollee_id charac… 0 0 200 1 #> 2 city charac… 0 0 49 0.245 #> 3 city_dev_index numeric 0 0 40 0.2 #> 4 gender charac… 48 24 4 0.02 #> 5 relevent_expe… charac… 0 0 2 0.01 #> 6 enrolled_univ… charac… 5 2.5 4 0.02 #> 7 education_lev… charac… 6 3 5 0.025 #> 8 company_type charac… 62 31 7 0.035 #> 9 last_new_job charac… 5 2.5 7 0.035 #> 10 training_hours integer 0 0 101 0.505 #> 11 job_chnge charac… 0 0 2 0.01
# Using pipes & dplyr ------------------------- # Diagnosis of missing variables con_sqlite %>% tbl("TB_JOBCHANGE") %>% diagnose() %>% filter(missing_count > 0)
#> # A tibble: 8 x 6 #> variables types missing_count missing_percent unique_count unique_rate #> <chr> <chr> <dbl> <dbl> <int> <dbl> #> 1 gender charac… 4508 23.5 4 0.000209 #> 2 enrolled_unive… charac… 386 2.01 4 0.000209 #> 3 education_level charac… 460 2.40 6 0.000313 #> 4 major_discipli… charac… 2813 14.7 7 0.000365 #> 5 experience charac… 65 0.339 23 0.00120 #> 6 company_size charac… 5938 31.0 9 0.000470 #> 7 company_type charac… 6140 32.0 7 0.000365 #> 8 last_new_job charac… 423 2.21 7 0.000365
# Disconnect DBMS DBI::dbDisconnect(con_sqlite) # }