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

**Diagnose data quality.****If there is a problem with data quality,****The data must be corrected or re-acquired.**

- Explore data to understand the data and find scenarios for performing the analysis.
- Derive new variables or perform variable transformations.

The dlookr package makes these steps fast and easy:

**Performs a data diagnosis or automatically generates a data diagnosis report.**- Discover data in various ways and automatically generate EDA(exploratory data analysis) reports.
- Impute missing values and outliers, resolve skewed data, and categorize continuous variables into categorical variables. And generates an automated report to support it.

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 with `dplyr`

. Particularly in
data exploration and data wrangling, it increases the efficiency of the
`tidyverse`

package group.

Data diagnosis supports the following data structures.

- data frame: data.frame class.
- data table: tbl_df class.
**table of DBMS**: table of the DBMS through tbl_dbi.**Use dplyr as the back-end interface for any DBI-compatible database.**

To illustrate the primary use of the dlookr package, use the
`flights`

data from the `nycflights13`

package.
The `flights`

data frame is data about departure and arrival
on all flights departing from NYC in 2013.

```
dim(flights)
[1] 3000 19
flights
# A tibble: 3,000 × 19
year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
<int> <int> <int> <int> <int> <dbl> <int> <int>
1 2013 6 17 1033 1040 -7 1246 1309
2 2013 12 26 1343 1329 14 1658 1624
3 2013 8 26 1258 1218 40 1510 1516
4 2013 8 17 1558 1600 -2 1835 1849
# ℹ 2,996 more rows
# ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
# tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
# hour <dbl>, minute <dbl>, time_hour <dttm>
```

dlookr aims to diagnose the data and select variables that can not be used for data analysis or to find the variables that need calibration.:

`diagnose()`

provides basic diagnostic information for variables.`diagnose_category()`

provides detailed diagnostic information for categorical variables.`diagnose_numeric()`

provides detailed diagnostic information for numerical variables.`diagnose_outlier()`

and`plot_outlier()`

provide information and visualization of outliers.

`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
```

`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
```

`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.

`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.

`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:

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.

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.

`plot_na_pareto()`

`plot_na_pareto()`

draws a Pareto chart by collecting
variables, including missing values.

The default value of the `only_na`

argument is FALSE,
which includes variables that do not contain missing values. Still, only
variables containing missing values are visualized if this value is set
to TRUE. The variable `age`

was excluded from this plot.

The rating of the variable is expressed as a proportion of missing
values. It is calculated as the ratio of missing values. If it is [0,
0.05), it is `Good`

, if it is [0.05, 0.4) it is
`OK`

, if it is [0.4, 0.8) it is `Bad`

, and if it
is [0.8, 1.0] it is `Remove`

. You can override this grade
using the `grade`

argument as follows:

If the `plot`

argument is set to FALSE, information about
missing values is returned instead of plotting.

`plot_na_hclust()`

It is essential to look at the relationship between variables,
including missing values. `plot_na_hclust()`

visualizes the
relationship of variables that contain missing values. This function
rearranges the positions of variables using hierarchical clustering.
Then, the expression of the missing value is visualized by grouping
similar variables.

`plot_na_intersect()`

`plot_na_intersect()`

visualizes the combinations of
missing values across cases.

The visualization consists of four parts. The bottom left, which is the most basic, visualizes the case of cross(intersection)-combination. The x-axis is the variable including the missing value, and the y-axis represents the case of a combination of variables. And on the marginal of the two axes, the frequency of the case is expressed as a bar graph. Finally, the visualization at the top right expresses the number of variables, including missing values in the data set, and the number of observations, including missing values and complete cases.

This example visualizes the combination of variables that include missing values.

If the `n_vars`

argument is used, only the top
`n`

variables containing many missing values are
visualized.

If you use the `n_intersacts`

argument, only the top n
numbers of variable combinations(intersection), including missing
values, are visualized. Suppose you want to visualize the combination
variables, that includes missing values and complete cases. You just add
only_na = FALSE.

dlookr provides two automated data diagnostic reports:

- Web page-based dynamic reports can perform in-depth analysis through visualization and statistical tables.
- Static reports generated as pdf files or html files can be archived as output of data analysis.

`diagnose_web_report()`

`diagnose_web_report()`

creates a dynamic report for
objects inherited from data.frame(`tbl_df`

, `tbl`

,
etc) or data.frame.

The contents of the report are as follows.:

- Overview
- Data Structures
- Data Structures
- Data Types
- Job Informations

- Warnings
- Variables

- Data Structures
- Missing Values
- List of Missing Values
- Visualization

- Unique Values
- Categorical Variables
- Numerical Variables

- Outliers
- Samples
- Duplicated
- Heads
- Tails

diagnose_web_report() generates various reports with the following arguments.

- output_file
- name of the generated file.

- output_dir
- name of the directory to generate report file.

- title
- title of the report.

- subtitle
- subtitle of the report.

- author
- author of the report.

- title_color
- color of title.

- thres_uniq_cat
- threshold to use for “Unique Values - Categorical Variables”.

- thres_uniq_num
- threshold to use for “Unique Values - Numerical Variables”.

- logo_img
- name of the logo image file on the top left.

- create_date
- The date on which the report is generated.

- theme
- name of theme for report. Support “orange” and “blue”.

- sample_percent
- Sample percent of data for performing Diagnosis.

The following script creates a quality diagnosis report for the
`tbl_df`

class object, `flights`

.

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

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

`diagnose_paged_report()`

`diagnose_paged_report()`

create static report for object
inherited from data.frame(`tbl_df`

, `tbl`

, etc) or
data.frame.

The contents of the report are as follows.:

- Overview
- Data Structures
- Job Informations
- Warnings
- Variables

- Missing Values
- List of Missing Values
- Visualization

- Unique Values
- Categorical Variables
- Numerical Variables

- Categorical Variable Diagnosis
- Top Ranks

- Numerical Variable Diagnosis
- Distribution
- Zero Values
- Minus Values

- Outliers
- List of Outliers
- Individual Outliers

- Distribution

diagnose_paged_report() generates various reports with the following arguments.

- output_format
- report output type. Choose either “pdf” or “html”.

- output_file
- name of the generated file.

- output_dir
- name of the directory to generate report file.

- title
- title of the report.

- subtitle
- subtitle of the report.

- abstract_title
- abstract of the report

- author
- author of the report.

- title_color
- color of title.

- subtitle_color
- color of subtitle.

- thres_uniq_cat
- threshold to use for “Unique Values - Categorical Variables”.

- thres_uniq_num
- threshold to use for “Unique Values - Numerical Variables”.

- flag_content_zero
- whether to output “Zero Values” information.

- flag_content_minus
- whether to output “Minus Values” information.

- flag_content_missing
- whether to output “Missing Value” information.

- whether to output “Missing Value” information.
- logo_img
- name of the logo image file on the top left.

- cover_img
- name of cover image file on center.

- create_date
- The date on which the report is generated.

- theme
- name of the theme for the report. Support “orange” and “blue”.

- sample_percent
- Sample percent of data for performing Diagnosis.

The following script creates a quality diagnosis report for the
`tbl_df`

class object, `flights`

.

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

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

The DBMS table diagnostic function supports In-database mode that performs SQL operations on the DBMS side. If the data size is large, using In-database mode is faster.

It isn’t easy to obtain anomalies or to implement the sampling-based algorithm in SQL of DBMS. So, some functions do not yet support In-database mode. In this case, it is performed in In-memory mode in which table data is brought to the R side and calculated. In this case, if the data size is large, the execution speed may be slow. It supports the collect_size argument, allowing you to import the specified number of data samples into R.

- In-database support functions
`diagonse()`

`diagnose_category()`

- In-database not support functions
`diagnose_numeric()`

`diagnose_outlier()`

`plot_outlier()`

`diagnose_web_report()`

`diagnose_paged_report()`

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)
```

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)
```

```
# 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())
```

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`

.