Data Cleaning: a checklist for messy datasets

What does "clean" actually mean for a dataset?

A clean dataset is one where every value is what it claims to be. That sounds obvious until you look at a real export: a name with a leading space (" ali "), the same city spelled three ways ("Lahore", "lahore", "LAHORE"), an age column containing the word "unknown", a customer listed twice, a blank where someone forgot to type.

None of those rows is "wrong" in a way a spreadsheet complains about. Excel will happily average an age column containing "unknown" by silently ignoring it, and a pivot table will happily count " ali " and "ali" as two customers. A machine learning model will simply refuse to train, or worse, train on the mess and learn the wrong pattern.

So "clean" is a checklist, not a vibe. A dataset is clean when every row is a real, distinct record (no duplicates, hidden or exact); every column has one type and every value fits it; there are no missing values you have not deliberately decided about; and text that means the same thing is spelled the same way.

Notice what is *not* on that list: the data being correct. Cleaning cannot tell you whether Ali really is 25. Only whether the value is usable. Cleaning makes data usable; your judgement makes it trustworthy.

The checklist: six steps, in this order

1. INSPECT. How dirty is it? Count missing values per column, count exact duplicates, look at the types.
2. STANDARDISE TEXT. Strip spaces, fix casing, unify spellings. Do this *before* deduplication.
3. DROP DUPLICATES, now that hidden near-duplicates have been exposed, remove them.
4. FIX TYPES. Turn text that should be numbers into numbers, safely.
5. FILL MISSING VALUES, replace each gap with a deliberate, defensible value.
6. VALIDATE. Re-run the step-1 checks. Zero missing, zero duplicates, sensible types, or the job is not done.

The order is not a suggestion. Step 2 before step 3 is the whole point of this tutorial: if you drop duplicates first, " ali " and "ali" look like different people and both survive. Standardise first and the duplicate reveals itself. The same logic applies throughout. Each step makes the next step's job visible.

How do I inspect a dataset before cleaning it?

Inspection is two questions: *where are the gaps?* and *what repeats?*

df.isna().sum() counts the missing values in each column. df.duplicated().sum() counts rows that are exact copies of an earlier row. Run both before you change a single value, and write the numbers down. They are your "before" photograph, and the validation step at the end only means something if you can compare it with the start.

Also look at the data itself with print(df). A column you expect to be numeric that shows up as object (pandas' word for "text") is a warning: something in that column is not a number, and step 4 will find what it is.

One habit from the start: work on a copy. clean = df.copy() means every change below happens to clean, and the raw df stays exactly as it arrived. If a step goes wrong, you re-run from the copy. You never have to re-download the data. Professional analysts keep raw data untouched as a rule, not a preference.

Why standardise text before dropping duplicates?

Text is where mess hides. The same person can appear as " ali ", "Ali" and "ALI"; the same city as "Lahore", "lahore" and "LAHORE". To a human these are obviously the same. To pandas, they are three different strings, and duplicated() will not flag any of them.

Standardising means making one decision per column and applying it everywhere:

clean["name"] = clean["name"].str.strip().str.title()
clean["city"] = clean["city"].str.strip().str.title()

.str.strip() removes leading and trailing spaces. .str.title() capitalises the first letter of each word and lowercases the rest. After these two lines, " ali ", "ALI" and "ali" are all "Ali", and "karachi" and "KARACHI" are both "Karachi".

Only now is drop_duplicates() safe to run. Because the duplicates hiding behind spelling differences have been exposed. In the worked example below, the exact-duplicate count is 1 before standardising and 2 after it. That second duplicate is the entire lesson: it was there all along, invisible until the text was standardised.

How do I fix values with the wrong type?

A column should have one type. When a number column contains a word, an age of "unknown", a price of "TBC", pandas reads the whole column as text, and every calculation on it either fails or silently misbehaves.

The safe fix is pd.to_numeric(..., errors="coerce"):

clean["age"] = pd.to_numeric(clean["age"], errors="coerce")

errors="coerce" is the important part: values that cannot become numbers turn into NaN (pandas' marker for "missing") instead of crashing your script. The alternative beginners reach for, astype(int), raises an error on the first non-numeric value and stops everything. Coercion keeps you moving and turns a type problem into a missing-value problem, which step 5 already knows how to handle. After coercion, check the type: the column should now be numeric (float64 once NaN is present). If it is still text, something else in the column is not a number. Look at the values, don't guess.

How do I handle missing values?

By now every gap is a deliberate, visible decision. Not an accident you have not noticed. There are three honest options, and the right one depends on the column: drop the row when it is useless without the value; fill with a sensible value, the median for a number, the most common value for a category, a clear label like "Unknown" for text; or leave it and say so, reporting "age unknown for 3% of customers" rather than inventing a number.

For this tutorial's small dataset, filling is the right call, because dropping rows would throw away a quarter of the data:

clean["name"] = clean["name"].fillna("Unknown")
clean["city"] = clean["city"].fillna("Unknown")
clean["age"] = clean["age"].fillna(clean["age"].median())

A missing name becomes the label "Unknown", visible, honest, and easy to filter later. A missing age becomes the median of the ages that remain, which is less distorted by an extreme value than the average would be. What you must never do is fill a gap silently and forget it happened: note the fill in your analysis, so anyone reading your results knows which values are real and which are filled.

Two technical notes. Fill the median *after* fixing types: the median of a text column is meaningless, and it only exists once "unknown" has become NaN. And the .copy() after drop_duplicates() in the worked example is not decoration: without it, pandas may warn you (the SettingWithCopyWarning) that you are editing a slice of the original rather than a standalone table.

Worked example: the full checklist on one messy dataset

Six rows, four columns, every classic problem in miniature: a name with spaces, a name in capitals, a duplicated customer, a missing name and city, and the word "unknown" sitting in a numeric column.

import pandas as pd

df = pd.DataFrame({
    "name":   [" ali ", "Sara", "BILAL", "Sara", None, "ali"],
    "age":    [25, 30, "unknown", 30, 28, 25],
    "city":   ["Lahore", "karachi", "Lahore", "karachi", None, "lahore"],
    "orders": [3, 5, 2, 5, 1, 3],
})

# 1. INSPECT - how dirty is it?
print(df.isna().sum())
print("Exact duplicate rows:", df.duplicated().sum())

# 2. STANDARDISE TEXT first - " ali " and "ali" are the same person
clean = df.copy()
clean["name"] = clean["name"].str.strip().str.title()
clean["city"] = clean["city"].str.strip().str.title()
print("Duplicates after standardising text:", clean.duplicated().sum())

# 3. DROP DUPLICATES (now the hidden ones are caught too)
clean = clean.drop_duplicates().copy()

# 4. FIX TYPES - "unknown" is not a number
clean["age"] = pd.to_numeric(clean["age"], errors="coerce")

# 5. HANDLE MISSING VALUES
clean["name"] = clean["name"].fillna("Unknown")
clean["city"] = clean["city"].fillna("Unknown")
clean["age"] = clean["age"].fillna(clean["age"].median())

# 6. VALIDATE
print(clean)
print(clean.isna().sum())
print("Rows: before", len(df), "| after", len(clean))

Exact output, as run for this tutorial:

name      1
age       0
city      1
orders    0
dtype: int64
Exact duplicate rows: 1
Duplicates after standardising text: 2
      name   age     city  orders
0      Ali  25.0   Lahore       3
1     Sara  30.0  Karachi       5
2    Bilal  28.0   Lahore       2
4  Unknown  28.0  Unknown       1
name      0
age       0
city      0
orders    0
dtype: int64
Rows: before 6 | after 4

Walk through what happened. Inspection found 1 missing name, 1 missing city and 1 exact duplicate. After standardising text, the duplicate count rose to 2. Row 5 ("ali", 25, "lahore", 3) only matches row 0 once whitespace and casing are normalised. Dropping duplicates removed both. Then "unknown" became NaN, and the median of the remaining ages (25, 30, 28) is 28.0, which is what Bilal's age becomes, since his was the "unknown" one. The final table has 4 rows, zero missing values, and every column the right type.

Two rows were removed and one value was filled, a third of the dataset gone, which is exactly why the checklist exists. On a six-row example the loss is visible; on a 60,000-row export the same mistakes would be invisible, and the wrong totals would go into a report nobody questions.

Common mistakes

• Dropping duplicates before standardising text. The hidden near-duplicate survives, and your counts are wrong by exactly the rows you thought you had cleaned.
• Cleaning the original instead of a copy. One wrong drop and the raw data is gone. Always df.copy() first.
• Using astype(int) on a column with text in it. It crashes. Use pd.to_numeric(..., errors="coerce") and deal with the resulting NaN deliberately.
• Filling missing values with the average without thinking. The average is pulled by extreme values; the median is usually the safer fill for a number. And a filled value is still a guess. Note it in your analysis.
• Skipping validation. If you never re-run isna().sum() and duplicated() at the end, you are assuming the cleaning worked. Check it.
• Treating cleaning as a one-off. The next export from the same shop will have the same problems. Turn the six steps into a script you re-run, not a morning you repeat.

Frequently asked questions