Cleaning messy data with pandas
Real tables have missing values, numbers stored as text, spelling variations and duplicate rows. Fix them in a clear order, and keep a record of every change.
In 60 seconds
Cleaning messy data with pandas
Real tables have missing values, numbers stored as text, spelling variations and duplicate rows. Fix them in a clear order, and keep a record of every change.
| Problem | Example | Typical fix |
|---|---|---|
| Missing values | Empty cells, "N/A", "-" | Turn them into a proper missing value (NaN), then drop or fill |
| Wrong type | Numbers stored as text: "1,200" | Remove commas, convert with pd.to_numeric |
| Inconsistent text | "Wheat", " wheat", "WHEAT" | Strip spaces and use one case |
| Duplicates | The same row entered twice | drop_duplicates(), after fixing text |
| Impossible values | A negative weight, a date in the future | Check with rules; fix or mark as missing |
First, look
import pandas as pd
# A made-up table with typical problems.
df = pd.DataFrame({
"village": ["Rampur", "rampur ", "Sonpur", "Sonpur", "Kalyani"],
"wells": ["4", "4", "N/A", "N/A", "7"],
"people": ["1,250", "1,250", "980", "980", ""],
})
print(df)
print()
print(df.dtypes) # "object" or "str" means text: the numbers are stored as text
print()
df["village"] = df["village"].str.strip().str.title()
df["wells"] = pd.to_numeric(df["wells"], errors="coerce") # "N/A" becomes NaN
df["people"] = pd.to_numeric(df["people"].str.replace(",", ""), errors="coerce")
print(df)
print()
print("Missing per column:")
print(df.isna().sum())
print("Duplicate rows:", df.duplicated().sum())
print(df.drop_duplicates())errors="coerce" turns anything that is not a number into NaN, so you can count and handle it. Notice that "Rampur" and "rampur " only become duplicates after the text is fixed. Order matters.A sensible order
- 1
Look
Print the first rows,dtypes,isna().sum()and a few counts. - 2
Fix text
Strip spaces and make the case the same in category columns. - 3
Fix types
Convert numbers and dates; turn junk into NaN. - 4
Remove duplicates
Now that equal rows look equal. - 5
Handle missing values
Drop or fill, with a reason you write down. - 6
Check again
Counts, ranges and a few rows by eye.
Missing values: drop or fill?
- Filling every gap with 0, when 0 is a real value
- Dropping every row with any gap, losing most of the data
- Filling using the whole dataset before the train/test split
- Not saying how many values were filled
- Drop the row if the label itself is missing
- Fill a number with the median of similar rows, and note it
- Fit any filling on the training data only (scikit-learn's
SimpleImputerin a pipeline) - Keep a count of changes, or a column marking filled values
Watch and read more
Lab
A clean harvest table: tidy crop names, real numbers, no duplicate rows and no gaps, built with a clear sequence of pandas steps.
The problem
The starter has a small made-up table of harvest records. Clean it into a new DataFrame called clean, in this order:
- Crop names: strip spaces and make them lower case (so " Wheat" becomes "wheat").
harvest_kg: remove commas and convert to numbers. "N/A" and empty cells must become NaN.- Drop exact duplicate rows.
- Fill each missing
harvest_kgwith the median of the same crop.
Then print clean, how many rows were removed, how many values were filled, and the total harvest per crop.
Practice lab
Edit the code and run it. Python runs in your browser — nothing is sent to our server.
No output. Add print(...) to see values.
You are done when
Hard questions
Try to answer before you reveal. If you can answer these, you understood the lesson.
Q1Farm 107 appears twice with the same harvest. How could you tell whether it is a typing error or two real harvests?Reveal
From the data alone you cannot be sure. Look for a date, season or plot column: two different seasons would be two real harvests. If there is none, ask the people who collected the data. Record your decision, because it changes the totals.
Q2Why would filling the missing values using the median of <em>all</em> crops be a poor choice here?Reveal
Rice harvests in this made-up table are much larger than maize harvests. A median across all crops would give the missing rice farm too little and the missing maize farm too much. Filling within the same crop respects the obvious difference between groups.
Questions people ask
Should I fix data by hand in a spreadsheet?
For a quick look, maybe. For anything you will reuse, do it in code. Code is repeatable, can be reviewed, and does not silently change the original.
When are duplicates not really duplicates?
Two identical sales rows might be two real sales of the same item. Before removing, check whether your data has an id or time that tells real repeats apart from copying errors.
What is the difference between NaN and None in pandas?
Both mean missing. In number columns pandas uses NaN. isna() finds both.
How do I fill with the median of each group?
Use df.groupby("crop")["kg"].transform("median") to get each row's group median, then fillna with it. The lab uses this.
Lesson test
5 questions. Get 3 right (60%) to pass and complete this lesson.