Cleaning Data with Pandas
Real data is messy. Before you analyse it, you must find and fix the problems, or your answers will be wrong.
A Messy Dataset
Here is a student list typed by different people:
Example
import pandas as pd
students = pd.DataFrame({
"name": ["Ali", " ayesha ", "Bilal", "Bilal", "SANA", "Hamza"],
"city": ["Karachi", "Lahore", "Karachi", "Karachi", None, "karachi"],
"marks": [78, 91, None, None, 84, 66],
"fee": ["5000", "5000", "4500", "4500", "5000", "4,500"],
})
print(students)name city marks fee 0 Ali Karachi 78.0 5000 1 ayesha Lahore 91.0 5000 2 Bilal Karachi NaN 4500 3 Bilal Karachi NaN 4500 4 SANA NaN 84.0 5000 5 Hamza karachi 66.0 4,500
Problems we can see:
| Problem | Where |
|---|---|
| Duplicate row | Bilal appears twice |
Missing values (NaN) | Bilal's marks, Sana's city |
| Extra spaces and mixed case | " ayesha ", "SANA", "karachi" |
| Numbers stored as text | The fee column, with a comma in "4,500" |
NaN means "Not a Number", the way pandas shows a missing value.
Finding Missing Values
Example
print(students.isna().sum())name 0 city 1 marks 2 fee 0 dtype: int64
isna() marks each empty cell as True, and sum() counts them per column.
Removing Duplicates
Example
students = students.drop_duplicates()
print(len(students))5
The second Bilal row was identical to the first, so it is removed. Use drop_duplicates(subset=["name"]) to compare only certain columns.
Fixing Text
The .str accessor applies text methods to every value in a column:
Example
students["name"] = students["name"].str.strip().str.title()
students["city"] = students["city"].str.title()
print(students["name"].tolist())['Ali', 'Ayesha', 'Bilal', 'Sana', 'Hamza']
| Method | What it does |
|---|---|
.str.strip() | Removes spaces at the start and end |
.str.title() | Capitalises Each Word |
.str.lower() / .str.upper() | All small / all capital letters |
.str.replace("a", "b") | Replaces text |
.str.contains("Khan") | True where the text is found |
Handling Missing Values
You have two choices: remove the rows, or fill the gaps.
Example
students["marks"] = students["marks"].fillna(students["marks"].mean())
students["city"] = students["city"].fillna("Unknown")- Bilal's missing marks become the average of the other marks (79.75)
- Sana's missing city becomes "Unknown"
To remove rows with any missing value instead, use students.dropna().
Think before you fill. Filling a missing salary with the average might be fine for a rough summary, but wrong for a payroll. Sometimes it is better to leave the gap or ask for the real value.
Fixing Data Types
The fee column is text, so sum() would join the strings instead of adding. Remove the comma, then convert to whole numbers:
Example
students["fee"] = students["fee"].str.replace(",", "").astype(int)
print(students)
print("Total fees:", students["fee"].sum())name city marks fee 0 Ali Karachi 78.00 5000 1 Ayesha Lahore 91.00 5000 2 Bilal Karachi 79.75 4500 4 Sana Unknown 84.00 5000 5 Hamza Karachi 66.00 4500 Total fees: 24000
The data is now clean. Notice the index jumps from 2 to 4 because row 3 was removed. Use students.reset_index(drop=True) to number the rows again.
Converting messy numbers and dates
When some values cannot be converted, pd.to_numeric with errors="coerce" turns them into NaN instead of crashing:
Example
values = pd.Series(["12", "abc", "30"])
print(pd.to_numeric(values, errors="coerce"))0 12.0 1 NaN 2 30.0 dtype: float64
Dates work the same way with pd.to_datetime(). After converting, you can get parts of the date:
Example
dates = pd.to_datetime(pd.Series(["2026-10-01", "2026-10-15"]))
print(dates.dt.day_name().tolist())['Thursday', 'Thursday']
Renaming Columns
Example
students = students.rename(columns={"fee": "fee_rs"})
print(students.columns.tolist())['name', 'city', 'marks', 'fee_rs']
Exercise
Exercise
Which code counts the missing values in each column of a DataFrame df?