dlookr: Data quality diagnosis

package dlookr

Introduction to data quality diagnostics in the dlookr package

Choonghyun Ryu
05-10-2021

Preface

After you have acquired the data, you should do the following:

The dlookr package makes these steps fast and easy:

This document introduces Data Quality Diagnosis methods provided by the dlookr package. You will learn how to diagnose the quality of tbl_df data that inherits from data.frame and data.frame with functions provided by dlookr.

dlookr increases synergy when used with the dplyr package. Particularly in data exploration and data wrangle, it increases the efficiency of the tidyverse package group.

Overview

Overall Diagnose Data

Tasks Descriptions Functions Support DBI
describe overview of data Inquire basic information to understand the data in general overview()
summary overview object summary described overview of data summary.overview()
plot overview object plot described overview of data plot.overview()
diagnose data quality of variables The scope of data quality diagnosis is information on missing values and unique value information diagnose() x
diagnose data quality of categorical variables frequency, ratio, rank by levels of each variables diagnose_category() x
diagnose data quality of numerical variables descriptive statistics, number of zero, minus, outliers diagnose_numeric() x
diagnose data quality for outlier number of outliers, ratio, mean of outliers, mean with outliers, mean without outliers diagnose_outlier() x
plot outliers information of numerical data box plot and histogram whith outliers, without outliers plot_outlier.data.frame() x
plot outliers information of numerical data by target variable box plot and density plot whith outliers, without outliers plot_outlier.target_df() x
diagnose combination of categorical variables Check for sparse cases of level combinations of categorical variables diagnose_sparese()

Visualize Missing Values

Tasks Descriptions Functions Support DBI
pareto chart for missing value visualize pareto chart for variables with missing value. plot_na_pareto()
combination chart for missing value visualize distribution of missing value by combination of variables. plot_na_hclust()
plot the combination variables that is include missing value visualize the combinations of missing value across cases.. plot_na_intersect()

Reporting

Types Descriptions Functions Support DBI
reporting the information of data diagnosis into pdf file report the information for diagnosing the quality of the data. diagnose_report() x
reporting the information of data diagnosis into html file report the information for diagnosing the quality of the data. diagnose_report() x

Exercise data: nycflights13::flights

To illustrate basic use of the dlookr package, use the flights from the nycflights13 package. The flights data frame is data about departure and arrival on all flights departing from NYC in 2013.

library(dlookr)
library(dplyr)
library(ggplot2)
library(flextable)
library(nycflights13)

glimpse(flights)

Rows: 336,776
Columns: 19
$ year           <int> 2013, 2013, 2013, 2013, 2013, 2013, 2013, 201…
$ month          <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
$ day            <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
$ dep_time       <int> 517, 533, 542, 544, 554, 554, 555, 557, 557, …
$ sched_dep_time <int> 515, 529, 540, 545, 600, 558, 600, 600, 600, …
$ dep_delay      <dbl> 2, 4, 2, -1, -6, -4, -5, -3, -3, -2, -2, -2, …
$ arr_time       <int> 830, 850, 923, 1004, 812, 740, 913, 709, 838,…
$ sched_arr_time <int> 819, 830, 850, 1022, 837, 728, 854, 723, 846,…
$ arr_delay      <dbl> 11, 20, 33, -18, -25, 12, 19, -14, -8, 8, -2,…
$ carrier        <chr> "UA", "UA", "AA", "B6", "DL", "UA", "B6", "EV…
$ flight         <int> 1545, 1714, 1141, 725, 461, 1696, 507, 5708, …
$ tailnum        <chr> "N14228", "N24211", "N619AA", "N804JB", "N668…
$ origin         <chr> "EWR", "LGA", "JFK", "JFK", "LGA", "EWR", "EW…
$ dest           <chr> "IAH", "IAH", "MIA", "BQN", "ATL", "ORD", "FL…
$ air_time       <dbl> 227, 227, 160, 183, 116, 150, 158, 53, 140, 1…
$ distance       <dbl> 1400, 1416, 1089, 1576, 762, 719, 1065, 229, …
$ hour           <dbl> 5, 5, 5, 5, 6, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, …
$ minute         <dbl> 15, 29, 40, 45, 0, 58, 0, 0, 0, 0, 0, 0, 0, 0…
$ time_hour      <dttm> 2013-01-01 05:00:00, 2013-01-01 05:00:00, 20…

Overall Diagnose Data

Overview data

overview() inquire basic information to understand the data in general.

ov <- overview(flights)
ov %>% 
  flextable()

summary.overview() tries to be smart about formatting 14 information of overview.

summary(ov)

── Data Scale ──────────────────────────────────────────────
● Number of observations           :     336,776
● Number of variables              :          19
● Number of values                 :   6,398,744
● Size of located memory(bytes)    :  40,650,104

── Missing Data ────────────────────────────────────────────
● Number of completed observations :     327,346
● Number of observations with N/A  :       9,430
● Number of variables with N/A     :           6
● Number of N/A                    :      46,595

── Data Type ───────────────────────────────────────────────
● Number of numeric variables      :           6
● Number of integer variables      :           8
● Number of factors variables      :           0
● Number of character variables    :           4
● Number of other variables        :           1

── Individual variables ────────────────────────────────────
        Variables Data Type
1            year   integer
2           month   integer
3             day   integer
4        dep_time   integer
5  sched_dep_time   integer
6       dep_delay   numeric
7        arr_time   integer
8  sched_arr_time   integer
9       arr_delay   numeric
10        carrier character
11         flight   integer
12        tailnum character
13         origin character
14           dest character
15       air_time   numeric
16       distance   numeric
17           hour   numeric
18         minute   numeric
19      time_hour   POSIXct

plot.overview() plot the data type, number of observations, and number of missing values for each variable.

plot(ov)

# sort by name of variables
plot(ov, order_type = "name")

# sort by data type of variables
plot(ov, order_type = "type")

General diagnosis

diagnose() allows you to diagnosis a variables in a data frame. Like 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.

For example, we can diagnose all variables in flights:

diagnose(flights) %>% 
  flextable()

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) %>% 
  flextable()

# Select all columns between year and day (inclusive)
diagnose(flights, year:day) %>% 
  flextable()

# Select all columns except those from year to day (inclusive)
diagnose(flights, -(year:day)) %>% 
  flextable()

By 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)) %>% 
  flextable()

Diagnosis of numeric variables

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.

Applying the summary() function to a data frame can help you figure out the distribution of data by printing min, Q1, mean, median, Q3, and max give. However, the result is that analysts can only look at it with eyes. However, returning such information as a data frame structure like tbl_df widens the scope of utilization.

zero, minus, and outlier are useful for diagnosing the integrity of data. For example, numerical data in some cases may not have 0 or a negative number. Since the hypothetical numeric variable ‘employee salary’ can not have a negative or zero value, you should check for zero or negative numbers in the data diagnosis process.

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

diagnose_numeric(flights) %>% 
  flextable()

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) %>% 
  flextable()

Diagnosis of categorical variables

diagnose_category() diagnoses the categorical(factor, ordered, character) variables of a data frame. The usage is similar to diagnose () but returns more diagnostic information. If you specify a non-categorical variable in the second and subsequent argument list, the variable is automatically ignored. The top argument specifies the number of levels to return per variable. The default value is 10, which returns the top 10 level. 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.

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

diagnose_category(flights) %>% 
  flextable()

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)) %>% 
  flextable()

The following returns a list of levels less than or equal to 0.01%. It should be noted that the top argument has a generous specification of 500. If you use the default value of 10, values below 0.01% would not be included in the list:

flights %>%
  diagnose_category(top = 500)  %>%
  filter(ratio <= 0.01) %>% 
  flextable()

In the analytic model, it is also possible to consider removing the small percentage of observations in the observations or joining them together.

Diagnosing outliers

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

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

diagnose_outlier() can diagnose anomalies of all numeric variables of flights as follows:

diagnose_outlier(flights) %>% 
  flextable()

Numeric variables that contain anomalies are easily found with filter().:

diagnose_outlier(flights) %>% 
  filter(outliers_cnt > 0) %>% 
  flextable()

The following is a list of numeric variables with anomalies greater than 5%.:

diagnose_outlier(flights) %>% 
  filter(outliers_ratio > 5) %>% 
  mutate(rate = outliers_mean / with_mean) %>% 
  arrange(desc(rate)) %>% 
  select(-outliers_cnt) %>% 
  flextable()

If the outlier is larger than the average of all observations, it may be desirable to replace or remove the outlier in the data analysis process.

Visualization of outliers

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

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

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

flights %>%
  plot_outlier(arr_delay)

Use the function of the dplyr package and plot_outlier() and diagnose_outlier() to visualize anomaly values of all numeric variables with an outlier ratio of 0.5% or more.:

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

You should look at the visualization results and decide whether to remove or replace the outliers. In some cases, it is important to consider removing the variables that contain anomalies from the data analysis model.

In the visualization results, arr_delay has similar distributions to the normal distribution of the observed values. In the case of linear models, we can also consider removing or replacing anomalies. And air_time shows a roughly similar distribution before and after removing anomalies.

Check sparse cases

diagnose_sparese() checks for combinations of levels that do not appear as data among all combinations of levels of categorical variables.

glimpse(heartfailure)

Rows: 299
Columns: 13
$ age               <int> 75, 55, 65, 50, 65, 90, 75, 60, 65, 80, 75…
$ anaemia           <fct> No, No, No, Yes, Yes, Yes, Yes, Yes, No, Y…
$ cpk_enzyme        <dbl> 582, 7861, 146, 111, 160, 47, 246, 315, 15…
$ diabetes          <fct> No, No, No, No, Yes, No, No, Yes, No, No, …
$ ejection_fraction <dbl> 20, 38, 20, 20, 20, 40, 15, 60, 65, 35, 38…
$ hblood_pressure   <fct> Yes, No, No, No, No, Yes, No, No, No, Yes,…
$ platelets         <dbl> 265000, 263358, 162000, 210000, 327000, 20…
$ creatinine        <dbl> 1.9, 1.1, 1.3, 1.9, 2.7, 2.1, 1.2, 1.1, 1.…
$ sodium            <dbl> 130, 136, 129, 137, 116, 132, 137, 131, 13…
$ sex               <fct> Male, Male, Male, Male, Female, Male, Male…
$ smoking           <fct> No, No, Yes, No, No, Yes, No, Yes, No, Yes…
$ time              <int> 4, 6, 7, 7, 8, 8, 10, 10, 10, 10, 10, 10, …
$ death_event       <fct> Yes, Yes, Yes, Yes, Yes, Yes, Yes, Yes, Ye…
diagnose_sparese(heartfailure) %>% 
  flextable()

# Adjust the threshold of limt to calculate
diagnose_sparese(heartfailure, limit = 50)

NULL
# List all combinations, including sparse cases
diagnose_sparese(heartfailure, type = "all") %>% 
  flextable()  

# collaboration with dplyr
heartfailure %>% 
  diagnose_sparese(type = "all") %>% 
  arrange(desc(n_case)) %>% 
  mutate(percent = round(n_case / sum(n_case) * 100, 1)) %>% 
  filter(percent > 3) %>% 
  flextable()

Visualize missing values

Pareto chart

plot_na_pareto() visualize pareto chart for variables with missing value.

plot_na_pareto(flights)

# Visualize only variables containing missing values
plot_na_pareto(flights, only_na = TRUE)

# Change the grade
plot_na_pareto(flights, grade = list(High = 0.1, Middle = 0.6, Low = 1))

# Return the aggregate information about missing values.
plot_na_pareto(flights, only_na = TRUE, plot = FALSE)

# A tibble: 6 x 5
  variable  frequencies   ratio grade cumulative
  <fct>           <int>   <dbl> <fct>      <dbl>
1 air_time         9430 0.0280  Good        20.2
2 arr_delay        9430 0.0280  Good        40.5
3 arr_time         8713 0.0259  Good        59.2
4 dep_delay        8255 0.0245  Good        76.9
5 dep_time         8255 0.0245  Good        94.6
6 tailnum          2512 0.00746 Good       100

Combination chart using hclust

plot_na_hclust() visualize distribution of missing value by combination of variables using hclust().

# Generate data for the example
set.seed(123L)
flights2 <- flights[sample(nrow(flights), size = 1000), ]

# Visualize hcluster chart for variables with missing value.
plot_na_hclust(flights2)

Combination chart

plot_na_intersect() visualize the combinations of missing value across cases.

# Visualize the combination variables that is include missing value.
plot_na_intersect(flights2)

# Visualize variables containing missing values and complete case
plot_na_intersect(flights2, only_na = FALSE)

# Using n_vars argument
plot_na_intersect(flights2, n_vars = 5) 

# Using n_intersects argument
plot_na_intersect(flights2, only_na = FALSE, n_intersacts = 3)

Create a diagnostic report

diagnose_report() performs data diagnosis of all variables of object inherited from data.frame(tbl_df, tbl, etc) or data.frame.

`diagnose_report() writes the report in two formats:

The contents of the report are as follows.:

The following creates a quality diagnostic report for flights, a tbl_df class object. The file format is pdf and file name is DataDiagnosis_Report.pdf.

flights %>%
  diagnose_report()

The following script creates an html report named DataDiagnosis_Report.html.

flights %>%
  diagnose_report(output_format = "html")

The following generates an HTML report named Diagn.html.

flights %>%
  diagnose_report(output_format = "html", output_file = "Diagn.html")

The Data Diagnostic Report is an automated report intended to aid in the data diagnosis process. It judged whether the data is supplemented or reacquired by referring to the report results.

Contents of pdf file

Contents of html file

Reuse

Text and figures are licensed under Creative Commons Attribution CC BY 4.0. The figures that have been reused from other sources don't fall under this license and can be recognized by a note in their caption: "Figure from ...".