Data Cleansing With SQL And R

Kevin Feasel (@feaselkl)

http://CSmore.info/on/cleansing

Who Am I? What Am I Doing Here?

Catallaxy Services
@feaselkl
Curated SQL
We Speak Linux

Dirty Data

What is dirty data?

  • Inconsistent data
  • Invalid data
  • Incomplete data
  • Inaccurate data
  • Duplicate data

Dirty Data

Philosophy

The ideal solution is to clean data at the nearest possible point. In rank order:

  1. Before it gets into the OLTP system
  2. Once it is in the OLTP system
  3. ETL process to the warehouse
  4. Once it is in the warehouse
  5. During data analysis

Not all systems follow OLTP => DW => Analysis, so it is valuable to know multiple techniques for data cleansing.

Motivation

Today's talk will focus on data cleansing within SQL Server and R, with an emphasis on R. In SQL Server, we will focus on data structures. In R, we will focus on the concept of tidy data.

This will necessarily be an incomplete survey of data cleansing techniques, but should serve as a starting point for further exploration.

We will not look at Data Quality Services or other data provenance tools in this talk, but these tools are important.

Agenda

  1. High-Level Concepts
  2. SQL Server - Constraints
  3. SQL Server - Conversion Functions
  4. SQL Server - Mapping Tables
  5. R - tidyr
  6. R - dplyr
  7. R - Data and Outlier Analysis

High-Level Concepts

Data Consistency

Important questions for data consistency:

  • Are there misspellings?
  • Do we use the same values to represent the same things?
  • Do we have data stored in multiple locations which can get out of sync?
  • Are the answers to one question consistent with answers to another question? (Ex: less than 12 years of schooling but has a PhD?)

High-Level Concepts

Data Validity

Important questions for data validity:

  • Are the answers physically possible? (Ex: count of pregnant males)
  • Are the answers logically possible? (Ex: 6-year-old with a driver's license)
  • Does a test actually measure what it purports to measure?

High-Level Concepts

Data Completeness

Important questions for data completeness:

  • Are there missing values represented by NULL or some default?
  • Are any of the missing values vital for analysis?
  • Do we have a reasonable default for a missing value? (Ex: use the mean/median value or a hard-coded default)

High-Level Concepts

Data Accuracy

Important questions for data accuracy:

  • Does an answer look absurd? (Ex: $500 million/hour wage)
  • Do we have multiple sources of data with conflicting results?
  • Do we know how this data was collected? (Ex: survey, sample, direct measurement)

High-Level Concepts

Data Duplication

Important questions for data duplication:

  • Do I have a way of knowing whether data is duplicated? (Ex: natural key)
  • Are there "duplicated" results which are not actually duplicates?
  • Can I filter out duplicated results?

High-Level Concepts

Quick Determinations

These are rules of thumb.

  1. Impossible measurements (e.g., count of pregnant males) should go. Don't waste the space storing that.
  2. "Missing" data (e.g., records with some NULL values) should stay, although might not be viable for all analyses.
  3. Fixable bad data (e.g., misspellings, errors where intention is known) should be fixed and stay.
  4. Unfixable bad data is a tougher call. Could set to default, make a "best guess" change(!!), set to {NA, NULL, Unknown}, or drop from the analysis.

Agenda

  1. High-Level Concepts
  2. SQL Server - Constraints
  3. SQL Server - Conversion Functions
  4. SQL Server - Mapping Tables
  5. R - tidyr
  6. R - dplyr
  7. R - Data and Outlier Analysis

Keys and Constraints

Relational databases have several concepts to promote data quality:

  • Normalization
  • Data types
  • Primary key constraints
  • Unique key constraints
  • Foreign key constraints
  • Check constraints
  • Default constraints

Keys and Constraints

Normalization

When in doubt, go with Boyce-Codd Normal Form.

First Normal Form - consistent shape + unique entities + atomic attributes

Boyce Codd Normal Form - 1NF + all attributes fully dependent upon a candidate key + every determinant is a key.

Check the links for more details!

Keys and Constraints

Data Types

Think through your data type choices.

  • Use the best data type (int/decimal for numeric, date/datetime/datetime2/time for date data, etc.)
  • Use the smallest data type which solves the problem (Ex: date instead of datetime, varchar(10) instead of varchar(max))

Keys and Constraints

Constraints

Use constraints liberally.

  • Primary key to describe the primary set of attributes which describes an entity.
  • Unique keys to describe alternate sets of attributes which describe an entity.
  • Foreign keys to describe how entities relate.
  • Check constraints to explain valid domains for attributes and attribute combinations.
  • Default constraints when there is a reasonable alternative to NULL.

Keys and Constraints

Demo Time

Agenda

  1. High-Level Concepts
  2. SQL Server - Constraints
  3. SQL Server - Conversion Functions
  4. SQL Server - Mapping Tables
  5. R - tidyr
  6. R - dplyr
  7. R - Data and Outlier Analysis

Conversion Functions

SQL Server has a number of built-in functions which help us convert strings, deal with NULL values, and convert data between data types.

For brevity's sake, we will skip string functions (LEN, SUBSTRING, CHARINDEX, PATINDEX, etc.).

Conversion Functions

Demo Time

Agenda

  1. High-Level Concepts
  2. SQL Server - Constraints
  3. SQL Server - Conversion Functions
  4. SQL Server - Mapping Tables
  5. R - tidyr
  6. R - dplyr
  7. R - Data and Outlier Analysis

Mapping Tables

Sometimes we want to create new tables and data relationships: for example, categorizing data for easier high-level understanding. The ideal way to do this would be to modify the current schema and code to support these new relationships, keeping the data as close to the source as possible.

When that is not possible, sometimes we can do the next-best thing: create external relationships without modifying source tables.

The worst-case scenario would be to do this cleanup late in the analysis process, as then other analyses cannot take advantage of this data cleansing work.

Mapping Tables

Demo Time

Agenda

  1. High-Level Concepts
  2. SQL Server - Constraints
  3. SQL Server - Conversion Functions
  4. SQL Server - Mapping Tables
  5. R - tidyr
  6. R - dplyr
  7. R - Data and Outlier Analysis

tidyr

What Is Tidy Data?

Notes from Hadley Wickham's Structuring Datasets to Facilitate Analysis.

  • Data sets are made of variables & observations (attributes & entities)
  • Variables contain all values that measure the same underlying attribute (e.g., height, temperature, duration) across units
  • Observations contain all values measured on the same unit (a person, a day, a hospital stay) across attributes

tidyr

What Is Tidy Data?

Notes from Hadley Wickham's Structuring Datasets to Facilitate Analysis.

  • It is easier to describe relationships between variables (age is a function of birthdate and current date)
  • It is easier to make comparisons between groups of attributes (how many people are using this phone number?)
  • Tidy data IS third normal form (or, preferably, Boyce-Codd Normal Form)!

tidyr

tidyr is a library whose purpose is to use simple functions to make data frames tidy. It includes functions like gather (unpivot), separate (split apart a variable), and spread (pivot).

tidyr

Demo Time

Agenda

  1. High-Level Concepts
  2. SQL Server - Constraints
  3. SQL Server - Conversion Functions
  4. SQL Server - Mapping Tables
  5. R - tidyr
  6. R - dplyr
  7. R - Data and Outlier Analysis

dplyr

tidyr is just one part of the tidyverse. Other tidyverse packages include dplyr, lubridate, and readr.

We will take a closer look at dplyr with the next example.

dplyr

Demo Time

Agenda

  1. High-Level Concepts
  2. SQL Server - Constraints
  3. SQL Server - Conversion Functions
  4. SQL Server - Mapping Tables
  5. R - tidyr
  6. R - dplyr
  7. R - Data and Outlier Analysis

Data and Outliers

Using tidyr, dplyr, and some basic visualization techniques, we can perform univariate and multivariate analysis to determine whether the data is clean. We will focus mostly on univariate and visual analysis in the following example.

Data and Outliers

Demo Time

Wrapping Up

This has been a quick survey of data cleansing techniques. For next steps, look at:

  • SQL Server Data Quality Services
  • Integration with external data sources (APIs to look up UPCs, postal addresses, etc.)
  • Value distribution analysis (Ex: Benford's Law)

Wrapping Up

To learn more, go here: http://CSmore.info/on/cleansing

And for help, contact me: feasel@catallaxyservices.com | @feaselkl