Skip to main content
HomeBlogData Science

[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 

Learn more about Data Cleaning

Cleaning Data in Python

BeginnerSkill Level
4 hr
86.9K
Learn to diagnose and treat dirty data and develop the skills needed to transform your raw data into accurate insights!
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

An Introduction to DuckDB: What is It and Why Should You Use It?

Explore DuckDB, the fast, easy-to-use analytics database for Python and R. Learn its key features, use cases, and how it optimizes data analysis tasks.
Kurtis Pykes 's photo

Kurtis Pykes

7 min

What is Data Analysis? An Expert Guide With Examples

Explore the world of data analysis with our comprehensive guide. Learn about its importance, process, types, techniques, tools, and top careers in 2023
Matt Crabtree's photo

Matt Crabtree

15 min

What is Data Science? Definition, Examples, Tools & More

Data science is an interdisciplinary field that uses scientific methods, processes, algorithms, and systems to extract knowledge and insights from structured and unstructured data.
Matt Crabtree's photo

Matt Crabtree

15 min

How Data Science is Transforming the NBA

Richie and Seth Partnow look into the intricate dynamics of elite basketball.
Richie Cotton's photo

Richie Cotton

49 min

Fighting the Climate Crisis with Data

Jean-Pierre Pélicier shares his unique perspective on how data is not just transforming the renewable energy industry, but also redefining the way we approach the climate crisis.
Adel Nehme's photo

Adel Nehme

1 min

How to Pass the PL-300 Power BI Certification

Learn how to pass the PL-300 certification exam for Power BI—one of the world’s most popular business intelligence tools—in this convenient cheat sheet!
Richie Cotton's photo

Richie Cotton

7 min

See MoreSee More