Data quality diagnosis

General diagnosis of all variables with diagnose()

diagnose() allows the diagnosis of variables in a data frame. Like the function of dplyr, the first argument is the tibble (or data frame). The second and subsequent arguments refer to variables within that data frame.

The variables of the tbl_df object returned by diagnose () are as follows.

• variables: variable names
• types: the data type of the variables
• missing_count: number of missing values
• missing_percent: percentage of missing value
• unique_count: number of unique value
• unique_rate: rate of unique value. unique_count / number of observation

For example, we can diagnose all variables in flights:

diagnose(flights)
# A tibble: 19 × 6
  variables types   missing_count missing_percent unique_count unique_rate
  <chr>     <chr>           <int>           <dbl>        <int>       <dbl>
1 year      integer             0            0               1    0.000333
2 month     integer             0            0              12    0.004   
3 day       integer             0            0              31    0.0103  
4 dep_time  integer            82            2.73          982    0.327   
# ℹ 15 more rows

• Missing Value(NA): Variables with many missing values, i.e., those with a missing_percent close to 100, should be excluded from the analysis.
• Unique value: If the data type is not numeric (integer, numeric) and the number of unique values equals the number of observations (unique_rate = 1), the variable will likely be an identifier. Therefore, this variable is also not suitable for the analysis mode

year can be considered not to be used in the analysis model since unique_count is 1. However, you do not have to remove it if you configure date as a combination of year, month, and day.

For example, we can diagnose only a few selected variables:

# Select columns by name
diagnose(flights, year, month, day)
# A tibble: 3 × 6
  variables types   missing_count missing_percent unique_count unique_rate
  <chr>     <chr>           <int>           <dbl>        <int>       <dbl>
1 year      integer             0               0            1    0.000333
2 month     integer             0               0           12    0.004   
3 day       integer             0               0           31    0.0103  
# Select all columns between year and day (include)
diagnose(flights, year:day)
# A tibble: 3 × 6
  variables types   missing_count missing_percent unique_count unique_rate
  <chr>     <chr>           <int>           <dbl>        <int>       <dbl>
1 year      integer             0               0            1    0.000333
2 month     integer             0               0           12    0.004   
3 day       integer             0               0           31    0.0103  
# Select all columns except those from year to day (exclude)
diagnose(flights, -(year:day))
# A tibble: 16 × 6
  variables      types   missing_count missing_percent unique_count unique_rate
  <chr>          <chr>           <int>           <dbl>        <int>       <dbl>
1 dep_time       integer            82            2.73          982       0.327
2 sched_dep_time integer             0            0             588       0.196
3 dep_delay      numeric            82            2.73          204       0.068
4 arr_time       integer            87            2.9          1010       0.337
# ℹ 12 more rows

Using dplyr, variables, including missing values, can be sorted by the weight of missing values.:

flights %>%
  diagnose() %>%
  select(-unique_count, -unique_rate) %>% 
  filter(missing_count > 0) %>% 
  arrange(desc(missing_count))
# A tibble: 6 × 4
  variables types   missing_count missing_percent
  <chr>     <chr>           <int>           <dbl>
1 arr_delay numeric            89            2.97
2 air_time  numeric            89            2.97
3 arr_time  integer            87            2.9 
4 dep_time  integer            82            2.73
# ℹ 2 more rows

Diagnosis of numeric variables with diagnose_numeric()

diagnose_numeric() diagnoses numeric(continuous and discrete) variables in a data frame. Usage is the same as diagnose() but returns more diagnostic information. However, if you specify a non-numeric variable in the second and subsequent argument list, the variable is automatically ignored.

The variables of the tbl_df object returned by diagnose_numeric() are as follows.

• min: minimum value
• Q1: 1/4 quartile, 25th percentile
• mean: arithmetic mean
• median: median, 50th percentile
• Q3: 3/4 quartile, 75th percentile
• max: maximum value
• zero: number of observations with a value of 0
• minus: number of observations with negative numbers
• outlier: number of outliers

The summary() function summarizes the distribution of individual variables in the data frame and outputs it to the console. The summary values of numeric variables are min, Q1, mean, median, Q3 and max, which help to understand the data distribution.

However, the result displayed on the console has the disadvantage that the analyst has to look at it with the eyes. However, when the summary information is returned in a data frame structure such as tbl_df, the scope of utilization is expanded. diagnose_numeric() supports this.

zero, minus, and outlier are helpful measures to diagnose data integrity. For example, in some cases, numerical data cannot have zero or negative numbers. A numeric variable, employee salary, cannot have negative numbers or zeros. Therefore, this variable should be checked for the inclusion of zero or negative numbers in the data diagnosis process.

diagnose_numeric() can diagnose all numeric variables of flights as follows.:

diagnose_numeric(flights)
# A tibble: 14 × 10
  variables   min    Q1    mean median      Q3   max  zero minus outlier
  <chr>     <dbl> <dbl>   <dbl>  <dbl>   <dbl> <dbl> <int> <int>   <int>
1 year       2013 2013  2013      2013 2013     2013     0     0       0
2 month         1    4     6.54      7    9.25    12     0     0       0
3 day           1    8    15.8      16   23       31     0     0       0
4 dep_time      1  905. 1354.     1417 1755     2359     0     0       0
# ℹ 10 more rows

If a numeric variable can not logically have a negative or zero value, it can be used with filter() to easily find a variable that does not logically match:

diagnose_numeric(flights) %>% 
  filter(minus > 0 | zero > 0) 
# A tibble: 3 × 10
  variables   min     Q1  mean median    Q3   max  zero minus outlier
  <chr>     <dbl>  <dbl> <dbl>  <dbl> <dbl> <dbl> <int> <int>   <int>
1 dep_delay   -22  -5    13.6      -1    12  1126   143  1618     401
2 arr_delay   -70 -17     7.13     -5    15  1109    43  1686     250
3 minute        0   9.75 26.4      29    44    59   527     0       0

Diagnosis of categorical variables with diagnose_category()

diagnose_category() diagnoses the categorical(factor, ordered, character) variables of a data frame. The usage is similar to diagnose() but returns more diagnostic information. The variable is automatically ignored if you specify a non-categorical variable in the second and subsequent argument list.

The top argument specifies the number of levels to return for each variable. The default is 10, which returns the top 10 levels. Of course, if the number of levels is less than 10, all levels are returned.

The variables of the tbl_df object returned by diagnose_category() are as follows.

• variables: variable names
• levels: level names
• N: number of observation
• freq: number of observation at the levels
• ratio: percentage of observation at the levels
• rank: rank of occupancy ratio of levels

`diagnose_category() can diagnose all categorical variables of flights as follows.:

diagnose_category(flights)
# A tibble: 43 × 6
  variables levels     N  freq ratio  rank
  <chr>     <chr>  <int> <int> <dbl> <int>
1 carrier   UA      3000   551  18.4     1
2 carrier   EV      3000   493  16.4     2
3 carrier   B6      3000   490  16.3     3
4 carrier   DL      3000   423  14.1     4
# ℹ 39 more rows

In collaboration with filter() in the dplyr package, we can see that the tailnum variable is ranked in top 1 with 2,512 missing values in the case where the missing value is included in the top 10:

diagnose_category(flights) %>% 
  filter(is.na(levels))
# A tibble: 1 × 6
  variables levels     N  freq ratio  rank
  <chr>     <chr>  <int> <int> <dbl> <int>
1 tailnum   <NA>    3000    23 0.767     1

The following example returns a list where the level’s relative percentage is 0.01% or less. Note that the value of the top argument is set to a large value, such as 500. If the default value of 10 were used, values below 0.01% would not be included in the list:

flights %>%
  diagnose_category(top = 500)  %>%
  filter(ratio <= 0.01)
# A tibble: 0 × 6
# ℹ 6 variables: variables <chr>, levels <chr>, N <int>, freq <int>,
#   ratio <dbl>, rank <int>

In the analytics model, you can also consider removing levels where the relative frequency is minimal in the observations or, if possible, combining them together.

Diagnosing outliers with diagnose_outlier()

diagnose_outlier() diagnoses the outliers of the data frame’s numeric (continuous and discrete) variables. The usage is the same as diagnose().

The variables of the tbl_df object returned by diagnose_outlier() are as follows.

• 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

diagnose_outlier() can diagnose outliers of all numerical variables on flights as follows:

diagnose_outlier(flights)
# A tibble: 14 × 6
  variables outliers_cnt outliers_ratio outliers_mean with_mean without_mean
  <chr>            <int>          <dbl>         <dbl>     <dbl>        <dbl>
1 year                 0              0           NaN   2013         2013   
2 month                0              0           NaN      6.54         6.54
3 day                  0              0           NaN     15.8         15.8 
4 dep_time             0              0           NaN   1354.        1354.  
# ℹ 10 more rows

Numeric variables that contained outliers are easily found with filter().:

diagnose_outlier(flights) %>% 
  filter(outliers_cnt > 0) 
# A tibble: 4 × 6
  variables outliers_cnt outliers_ratio outliers_mean with_mean without_mean
  <chr>            <int>          <dbl>         <dbl>     <dbl>        <dbl>
1 dep_delay          401          13.4           94.4     13.6         0.722
2 arr_delay          250           8.33         121.       7.13       -3.52 
3 air_time            38           1.27         389.     149.        146.   
4 distance             3           0.1         4970.    1029.       1025.   

The following example finds a numeric variable with an outlier ratio of 5% or more and then returns the result of dividing the mean of outliers by the overall mean in descending order:

diagnose_outlier(flights) %>% 
  filter(outliers_ratio > 5) %>% 
  mutate(rate = outliers_mean / with_mean) %>% 
  arrange(desc(rate)) %>% 
  select(-outliers_cnt)
# A tibble: 2 × 6
  variables outliers_ratio outliers_mean with_mean without_mean  rate
  <chr>              <dbl>         <dbl>     <dbl>        <dbl> <dbl>
1 arr_delay           8.33         121.       7.13       -3.52  16.9 
2 dep_delay          13.4           94.4     13.6         0.722  6.94

In cases where the mean of the outliers is large relative to the overall average, it may be desirable to impute or remove the outliers.

Visualization of outliers using plot_outlier()

plot_outlier() visualizes outliers of numerical variables(continuous and discrete) of data.frame. Usage is the same as diagnose().

The plot derived from the numerical data diagnosis is as follows.

• With outliers box plot
• Without outliers box plot
• With outliers histogram
• Without outliers histogram

plot_outlier() can visualize an outliers in the arr_delay variable of flights as follows:

flights %>%
  plot_outlier(arr_delay) 

The following example uses diagnose_outlier(), plot_outlier(), and dplyr packages to visualize all numerical variables with an outlier ratio of 5% or higher.

flights %>%
  plot_outlier(diagnose_outlier(flights) %>% 
                 filter(outliers_ratio >= 5) %>% 
                 select(variables) %>% 
                 unlist())

Analysts should look at the visualization results to decide whether to remove or replace outliers. Sometimes, you should consider removing variables with outliers from the data analysis model.

Looking at the visualization results, arr_delay shows that the observed values without outliers are similar to the normal distribution. In the case of a linear model, we might consider removing or imputing outliers.

Visualization for missing values

It is essential to look at the missing values of individual variables, but it is also important to look at the relationship between the variables, including the missing values.

dlookr provides a visualization tool that looks at the relationship of variables, including missing values.

mice::boys %>% 
  plot_na_pareto(col = "blue")

mice::boys %>% 
  plot_na_pareto(only_na = TRUE, main = "Pareto Chart for mice::boys")

mice::boys %>% 
  plot_na_pareto(grade = list(High = 0.1, Middle = 0.6, Low = 1), relative = TRUE)

plot_na_pareto(mice::boys, only_na = TRUE, plot = FALSE)

mice::boys %>% 
  plot_na_hclust(main = "Distribution of missing value")

mice::boys %>% 
  plot_na_intersect()

mice::boys %>%
  plot_na_intersect(n_vars = 5)

mice::boys %>%
  plot_na_intersect(only_na = FALSE, n_intersacts = 7)

Create a diagnostic report using diagnose_web_report()

diagnose_web_report() creates a dynamic report for objects inherited from data.frame(tbl_df, tbl, etc) or data.frame.

flights %>%
  diagnose_web_report(subtitle = "flights", output_dir = "./", 
                      output_file = "Diagn.html", theme = "blue")

Screenshot of dynamic report

• The part of the report is shown in the following figure.:

The part of the report

• The dynamic contents of the report are shown in the following figure.:

The dynamic contents of the report

Create a diagnostic report using diagnose_paged_report()

diagnose_paged_report() create static report for object inherited from data.frame(tbl_df, tbl, etc) or data.frame.

flights %>%
  diagnose_paged_report(subtitle = "flights", output_dir = "./",
                        output_file = "Diagn.pdf", theme = "blue")

Screenshot of static report

• The cover of the report is shown in the following figure.:

The part of the report

• The contents of the report are shown in the following figure.:

The dynamic contents of the report

Preparing table data

Copy the carseats data frame to the SQLite DBMS and create it as a table named TB_CARSEATS. Mysql/MariaDB, PostgreSQL, Oracle DBMS, and other DBMS are also available for your environment.

library(dplyr)

carseats <- Carseats
carseats[sample(seq(NROW(carseats)), 20), "Income"] <- NA
carseats[sample(seq(NROW(carseats)), 5), "Urban"] <- NA

# connect DBMS
con_sqlite <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

# copy carseats to the DBMS with a table named TB_CARSEATS
copy_to(con_sqlite, carseats, name = "TB_CARSEATS", overwrite = TRUE)

Diagnose data quality of variables in the DBMS

Use dplyr::tbl() to create a tbl_dbi object, then use it as a data frame object. The data argument of all diagnose functions is specified as a tbl_dbi object instead of a data frame object.

# Diagnosis of all columns
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose()

# Positions values select columns, and In-memory mode
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose(1, 3, 8, in_database = FALSE)
  
# Positions values select columns, and In-memory mode and collect size is 200
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose(-8, -9, -10, in_database = FALSE, collect_size = 200)

Diagnose data quality of categorical variables in the DBMS

# Positions values select variables, and In-memory mode and collect size is 200
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose_category(7, in_database = FALSE, collect_size = 200) 
  
# Positions values select variables
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose_category(-7)

Diagnose data quality of numerical variables in the DBMS

# Diagnosis of all numerical variables
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose_numeric()
  
# Positive values select variables, and In-memory mode and collect size is 200
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose_numeric(Sales, Income, collect_size = 200)

Diagnose outlier of numerical variables in the DBMS

con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose_outlier()  %>%
  filter(outliers_ratio > 1)

Plot outlier information of numerical data diagnosis in the DBMS

# Visualization of numerical variables with a ratio of
# outliers greater than 1%
# the result is same as a data.frame, but not display here. reference above in document.
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  plot_outlier(con_sqlite %>% 
                 tbl("TB_CARSEATS") %>% 
                 diagnose_outlier() %>%
                 filter(outliers_ratio > 1) %>%
                 select(variables) %>%
                 pull())

Reporting the information of data diagnosis for table of thr DBMS

The following shows several examples of creating a data diagnosis report for a DBMS table.

Using the collect_size argument, you can perform data diagnosis with the corresponding number of sample data. If the number of data is huge, use collect_size.

# create web report file. 
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose_web_report()
  
# create pdf file. file name is Diagn.pdf, and collect size is 350
con_sqlite %>% 
  tbl("TB_CARSEATS") %>% 
  diagnose_paged_report(collect_size = 350, output_file = "Diagn.pdf")