Skip to main content

[Infographic] Data Cleaning Checklist

Data cleaning takes up 80% of the data science workflow. Use this checklist to identify and resolve any quality issues with your data
Jun 2022  · 5 min read

Data cleaning takes up 80% of the data science workflow. This is why we created this checklist to help you identify and resolve any quality issues with your data. If you want to learn more about cleaning data, check out our course on Cleaning Data in Python and R respectively. 

data cleaning checklist

To download this infographic, press on the image above

Data Constraints Problems

1. Data Type Constraints

  • What is it: Ensuring that different columns have the correct data type before beginning analysis. 
  • Example in Action: A revenue_usd column that is a string, and not a numeric data type.
  • Potential Solutions: Convert to the correct data type

2. Data Range Constraints

  • What is it: Ensuring that different columns have the correct range. This is especially the case for columns that have limits.
  • Example in Action: A gpa column should be constrained to [0.0, 4.0]
  • Potential Solutions:
    • Check for typos, like a decimal point in the wrong place.
    • Drop rows where data points break range constraints
    • Set the data point that breaks range constraints to the maximum, or minimum
    • Treat the data point that breaks range constraints to missing, and impute it

3. Uniqueness Constraints

  • What is it: Ensuring that there are no exact or almost exact duplicates within your rows. 
  • Example in Action: A duplicate row where the name and phone_number columns are identical, but not the height_cm column
  • Potential Solutions:
    • Keep only one of the exact duplicate rows
    • Merge rows that have non-exact duplicate rows 

Text and Categorical Data Problems

1. Membership Constraints for Categorical Data

  • What is it: Ensuring that categorical columns have correct and consistent categories
  • Example in Action: Two different entries for “New York” in the city column
  • Potential Solutions:
    • Drop rows that are affected by inconsistent categories
    • Remap inconsistent categories to the correct category name
    • Infer categories based on other data points if it’s not clear how it should be remapped

2. Length Violation for Text Data

  • What is it: Ensuring that text columns that follow a specific standard have the same string length 
  • Example in Action: A US phone_number column that is 9 characters instead of 14
  • Potential Solutions:
    • Drop rows that are affected by length violation
    • Set affected observations to missing

3. Text Data with Inconsistent Formatting

  • What is it: Ensuring that text columns that follow a specific standard have the same string length 
  • Example in Action: A US phone_number column that is 9 characters instead of 14
  • Potential Solutions:
    • Drop rows that are affected by length violation
    • Set affected observations to missing

Data Uniformity Problems

1. Unit Uniformity for Numeric Columns

  • What is it: Ensuring that numeric columns have the same units (Temperature being Celsius or Fahrenheit across all observations. This is especially relevant when joining datasets from different countries or sources.) 
  • Example in Action: A temperature column in celsius that has absurdly high or low-temperature values 
  • Potential Solutions:
    • Dropping rows where no context on units appears and don’t pass a sanity check
    • Standardize the units where possible

2. Unit Uniformity for Date Columns

  • What is it: Ensuring that date columns have the same datetime format
  • Example in Action: birthday column where there are dates in dd-mm-yyyy and mm-dd-yyyy
  • Potential Solutions:
    • Standardizing datetime formats where possible
    • Dropping rows where no context on datetime format appears and don’t pass a sanity check

3. Crossfield Validation for Numeric Columns

  • What is it: Crossfield validation is when we use multiple fields in a dataset to ensure the validity of another. For example, ensuring that part to whole columns add to a relevant total.
  • Example in Action: Flight bookings per class add up to the total recorded bookings
  • Potential Solutions:
    • Dropping rows where sanity checks fail
    • Apply rules from domain knowledge based on knowing the data 

4. Crossfield Validation for Date Columns

  • What is it: Ensuring that date and temporal columns pass sanity checks (for example, ensuring that webinar registration dates always precede webinar attendance dates) 
  • Example in Action: A date_of_birth column that doesn't correspond with the age column
  • Potential Solutions:
    • Dropping rows where sanity checks fail
    • Apply rules from domain knowledge based on knowing the data 

Missing Data Problems

1. Missing Completely at Random

  • What is it: When there is no systematic relationship between missing values and other values within the dataset
  • Example in Action: There is no observed relationship between missing data and other values within the dataset
  • Potential Solutions:
    • Drop missing rows
    • Impute missing rows with measures of centrality such as median or mean
    • Impute missing rows with algorithmic, machine-learning-based approaches
    • Collect new data points and features 

2. Missing at Random Data

  • What is it: When there is a systematic relationship between missing data and other observed values
  • Example in Action: Missing census data from a specific region, because the postal service doesn’t have full coverage in that region
  • Potential Solutions:
    • Drop missing rows
    • Impute missing rows with measures of centrality such as median or mean
    • Impute missing rows with algorithmic, machine-learning-based approaches
    • Collect new data points and features 

3. Missing Not at Random Data

  • What is it: When there is a systematic relationship between missing data and other unobserved values 
  • Example in Action: Temperature readings from a sensor missing because temperate was too low, or high
  • Potential Solutions:
    • Drop missing rows
    • Impute missing rows with measures of centrality such as median or mean
    • Impute missing rows with algorithmic, machine-learning-based approaches
    • Collect new data points and features 

Cleaning Data with PySpark

Beginner
4 hours
18,357
Learn how to clean data with Apache Spark in Python.
See DetailsRight Arrow
Start Course

Cleaning Data in SQL Server Databases

Beginner
4 hours
7,148
Develop the skills you need to clean raw data and transform it into accurate insights.

Cleaning Data in R

Beginner
4 hours
36,075
Learn to clean data as quickly and accurately as possible to help your business move from raw data to awesome insights.
See all coursesRight Arrow
Related
Data Science Concept Vector Image

How to Become a Data Scientist in 8 Steps

Find out everything you need to know about becoming a data scientist, and find out whether it’s the right career for you!
Jose Jorge Rodriguez Salgado's photo

Jose Jorge Rodriguez Salgado

12 min

How to Become a Data Analyst in 2023: 5 Steps to Start Your Career

Learn how to become a data analyst and discover everything you need to know about launching your career, including the skills you need and how to learn them.
Elena Kosourova 's photo

Elena Kosourova

18 min

How to Analyze Data For Your Business in 5 Steps

Discover the different steps to analyze data and extract value from it, as well as the methods and techniques involved in the process.
Javier Canales Luna 's photo

Javier Canales Luna

14 min

DC Data in Soccer Infographic.png

How Data Science is Changing Soccer

With the Fifa 2022 World Cup upon us, learn about the most widely used data science use-cases in soccer.
Richie Cotton's photo

Richie Cotton

Sports Analytics: How Different Sports Use Data Analytics

Discover how sports analytics works and how different sports use data to provide meaningful insights. Plus, discover what it takes to become a sports data analyst.
Kurtis Pykes 's photo

Kurtis Pykes

13 min

How to Write a Bash Script: A Simple Bash Scripting Tutorial

Discover the basics of bash scripting and learn how to write a bash script.
Kurtis Pykes 's photo

Kurtis Pykes

5 min

See MoreSee More