Data Cleaning Checklist: 12 Steps for Messy Real-World Data

Data Cleaning Checklist: 12 Steps for Messy Real-World Data

Data cleaning is the process of finding and fixing problems in a dataset (duplicates, missing values, inconsistent labels, wrong data types, impossible values and broken links between tables) before you analyze it. It usually takes longer than the analysis itself, and skipping it is the most common reason a correct-looking report gives wrong numbers.

Course datasets are usually clean. Real ones rarely are. This checklist goes through the problems you'll actually meet, in the order that's safest to fix them, with short pandas examples. The same logic applies in Excel, SQL or Power Query.

The running example is an e-commerce orders export with columns order_id, customer_id, order_date, city, product, quantity and amount.

Step 1: Keep the Raw Data Untouched

Never clean the original file in place. Load it, work on a copy, and save the cleaned version separately.

import pandas as pd

raw = pd.read_csv("orders_raw.csv")
df = raw.copy()

If something goes wrong three steps later, you can always start again from the raw data. This matters even more in team settings, where someone will eventually ask what the original data said.

Step 2: Understand What One Row Means

Before changing anything, answer these questions: what does one row represent (one order? one order line?), how many rows and columns are there, and what does each column mean?

df.shape
df.info()
df.head(10)
df.describe(include="all")

You can't judge whether a row is a "duplicate" or a value is "wrong" until you know the row's grain, meaning exactly what one row represents.

Step 3: Standardize Column Names

Consistent, code-friendly names prevent a whole class of small errors.

df.columns = (df.columns.str.strip()
                        .str.lower()
                        .str.replace(" ", "_"))

Order Date (with a trailing space) becomes order_date.

Step 4: Fix Data Types

Numbers stored as text won't sum, and dates stored as text won't sort correctly. df.info() from Step 2 shows the problem columns.

df["amount"] = pd.to_numeric(
    df["amount"].astype(str).str.replace(",", ""),
    errors="coerce")
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce", dayfirst=True)

errors="coerce" turns unparseable values into missing values instead of crashing. Count how many values were coerced, because a sudden jump in missing values means the format assumption was wrong. dayfirst=True matters for data from countries (including Vietnam) that write dates as day/month/year. Otherwise 03/04 turns into March 4 instead of April 3.

Step 5: Remove Duplicates, Carefully

df.duplicated().sum()                         # fully identical rows
df.duplicated(subset=["order_id"]).sum()      # same order ID appearing twice

Exact duplicate rows are usually safe to drop. Duplicate IDs with different values need investigation first. They may be legitimate (an order with two product lines) or a real error (the same order exported twice with an updated status). Decide on a rule, such as keeping the latest record, and write it down:

df = df.sort_values("order_date").drop_duplicates(subset=["order_id"], keep="last")

Step 6: Handle Missing Values Deliberately

First measure, then decide column by column:

df.isna().sum().sort_values(ascending=False)
Situation Reasonable approach
Critical field missing (order amount, ID) Drop the row, or fix it at the source
Missing means "none" (discount code) Fill with a clear value such as "none" or 0
Numeric field, few gaps, not critical Fill with the median (it's robust to outliers)
Many gaps in a column Question whether the column is usable at all

Don't fill in values automatically. Filling a missing income with the average changes the distribution and can hide a data collection problem. Always record how many values you filled and how.

Step 7: Standardize Inconsistent Categories

This is where real data gets messy. A single city column might contain:

Ho Chi Minh City, HCMC, TP.HCM, tp hcm, Saigon, Ho Chi Minh

To a computer, that's six different cities. Look at the distinct values, then map them:

df["city"].value_counts()

city_map = {"hcmc": "Ho Chi Minh City", "tp.hcm": "Ho Chi Minh City",
            "tp hcm": "Ho Chi Minh City", "saigon": "Ho Chi Minh City",
            "ho chi minh": "Ho Chi Minh City", "ho chi minh city": "Ho Chi Minh City"}
df["city"] = df["city"].str.strip().str.lower().replace(city_map).str.title()

The final .str.title() restores normal capitalization for cities that weren't in the map ("hanoi" becomes "Hanoi").

Then check value_counts() again. Keep the mapping in a file or table so it's reused next month instead of rebuilt from memory.

Step 8: Clean Text Fields

Trailing spaces, mixed case and invisible characters break joins and group-bys quietly:

df["product"] = df["product"].str.strip().str.replace(r"\s+", " ", regex=True)

"Laptop Stand" and "Laptop Stand " (with a trailing space) group as two products until you do this.

Step 9: Check Dates and Time Periods

  • Dates in the future, or before the business existed
  • Time zone mix-ups: an online order at 23:30 in one time zone may belong to the next day in another
  • Missing periods: a week with zero orders may mean an export failure, not a slow week
df["order_date"].min(), df["order_date"].max()
df.set_index("order_date").resample("W")["order_id"].count()

Step 10: Find Impossible Values and Outliers

Separate impossible values (errors) from extreme values (possibly real):

df[(df["quantity"] <= 0) | (df["amount"] < 0)]      # impossible for a normal sale
df["amount"].describe(percentiles=[.01, .5, .99])

A negative amount might be a refund that belongs in a different analysis. An order worth 50 times the median might be a genuine bulk purchase, or someone typing 5,000,000 instead of 500,000. Investigate before deleting. Removing real extreme values distorts totals just as much as keeping errors does. If extreme values are real, prefer reporting the median over the mean.

Step 11: Validate Keys and Relationships

If the data will be joined to other tables, check that the links actually work:

customers = pd.read_csv("customers.csv")
orphans = ~df["customer_id"].isin(customers["customer_id"])
orphans.sum()

Orders pointing to customers who don't exist, or products missing from the product list, will vanish in an inner join or show up as blanks in a left join. See SQL Joins Explained for why that matters.

Step 12: Document and Automate

Write down every rule you applied: what you dropped, what you filled, what you mapped, and the counts before and after. Then turn the steps into a reusable function or script, so next month's file is cleaned the same way:

def clean_orders(raw: pd.DataFrame) -> pd.DataFrame:
    df = raw.copy()
    # steps 3-11
    return df

A short cleaning log at the top of your notebook ("removed 214 duplicate order IDs, keeping the latest; mapped 6 city spellings; 38 rows with negative amounts moved to refunds") is also what makes a portfolio project look professional to a recruiter.

The Checklist at a Glance

# Step Quick check
1 Keep raw data Working on a copy?
2 Understand the grain What is one row?
3 Standardize column names Lowercase, no spaces
4 Fix data types df.info() looks right?
5 Duplicates Exact and by ID
6 Missing values Decided per column, documented
7 Inconsistent categories value_counts() clean?
8 Text cleanup Whitespace and case
9 Dates Range, time zones, gaps
10 Impossible values and outliers Investigated, not auto-deleted
11 Keys and relationships No orphans
12 Document and automate Log plus reusable script

Common Mistakes

  1. Cleaning without checking counts. Print row counts after every step. An unexpected drop from 48,000 to 31,000 rows is a bug, not cleaning.
  2. Deleting outliers by default. Extreme doesn't mean wrong.
  3. Fixing data by hand in Excel. It can't be repeated or audited. Script it, or at least use Power Query steps.
  4. Treating cleaning as a one-off. The same problems come back with every new export. Fix them at the source when you can.

What to Learn Next

If you're coming from spreadsheets, start with How to Transition from Excel to Python, and keep our Python cheat sheet for data analysts nearby. For structured practice, Data Analysis with Python (DataCamp) covers pandas cleaning hands-on, and the Applied Data Science with Python Specialization (Michigan) goes deeper with messier, realistic datasets. Browse all Data Analysis courses.

Frequently Asked Questions

How long does data cleaning take? It's commonly said to take the majority of an analyst's time on a new dataset. The exact share varies, but it's often more than the analysis itself, especially the first time you work with a source.

What's the difference between data cleaning and data transformation? Cleaning fixes errors and inconsistencies. Transformation reshapes correct data for analysis (aggregating, pivoting, joining, deriving new columns). In practice the two happen in the same pipeline.

Should I clean data in SQL, Excel or Python? Wherever it will be repeatable. SQL or dbt suits warehouse data, pandas suits files and exploratory work, and Power Query suits Power BI and Excel users. The steps are the same everywhere.

Is it OK to drop rows with missing values? Sometimes. If very few rows are affected and they're missing at random, dropping them is fine. If many rows are affected, or they're missing for a reason (such as one region's system failing), dropping them biases your results.

Bottom Line

Good data cleaning is systematic, not heroic. Keep the raw data, understand the grain, fix types, handle duplicates and missing values deliberately, standardize categories, check dates, outliers and keys, then document and automate what you did. Follow the same 12 steps every time and the numbers you report will hold up when someone checks them.

Enjoyed this article?

Share it with your network

Listings related to Data Cleaning Checklist: 12 Steps for Messy Real-World Data