Cleaning Data with pandas

Real-world data is messy: missing values, duplicate rows, numbers stored as text, inconsistent spelling, dates in odd formats, impossible outliers. Analysts often say that 80% of the work is cleaning — and conclusions drawn from dirty data are simply wrong.

This lesson works through a deliberately messy dataset and fixes it step by step: detecting and handling missing values, removing duplicates, fixing types, standardising text, parsing dates, handling outliers, and renaming and reordering columns.

Missing Values

pandas marks missing data as NaN/NaT/None (shown as NaN or <NA>). Count them with df.isna().sum(). Then decide per column: dropna() removes rows (use subset= to consider only key columns), fillna(value) fills with a constant, the mean/median or the most common value, and ffill()/bfill() carry values forward/backward in time series. Record what you did — filling changes the data.

Duplicates, Types and Text

duplicated() flags repeated rows and drop_duplicates(subset=[...], keep="first") removes them. Convert text to numbers with pd.to_numeric(col, errors="coerce") (bad values become NaN rather than crashing), dates with pd.to_datetime(col, format=..., errors="coerce"), and repeated labels with astype("category") to save memory. Clean text with .str.strip(), .str.lower()/.title() and .str.replace(), and unify spellings with replace({...}).

Outliers and Tidy Columns

Check ranges with describe() and rules you know (ages between 0 and 120, no negative quantities). The IQR rule marks values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR as possible outliers; decide whether they are errors to remove, values to cap (clip), or real extremes to keep. Finally, rename(columns=...) to consistent snake_case names, drop unused columns and reset_index(drop=True).

Examples

Inspecting a messy dataset

Python

import numpy as np
import pandas as pd

raw = pd.DataFrame({
    "Customer Name": [" Asha Rao", "ravi kumar", "Meera Iyer", "ravi kumar", "Kiran Shah", None],
    "City": ["pune", "Mumbai ", "PUNE", "Mumbai ", "Bengaluru", "Delhi"],
    "Order Date": ["2026-01-05", "2026-01-07", "not recorded", "2026-01-07", "2026-01-09", "2026-01-10"],
    "Amount": ["1,250", "480", "95000", "480", "n/a", "720"],
    "Age": [34, 29, np.nan, 29, 41, 250],
})
print(raw.dtypes)
print(raw.isna().sum())
print("duplicate rows:", raw.duplicated().sum())

Output

Customer Name        str
City                 str
Order Date           str
Amount               str
Age              float64
dtype: object
Customer Name    1
City             0
Order Date       0
Amount           0
Age              1
dtype: int64
duplicate rows: 1

Cleaning it step by step

Python

import numpy as np
import pandas as pd

raw = pd.DataFrame({
    "Customer Name": [" Asha Rao", "ravi kumar", "Meera Iyer", "ravi kumar", "Kiran Shah", None],
    "City": ["pune", "Mumbai ", "PUNE", "Mumbai ", "Bengaluru", "Delhi"],
    "Order Date": ["2026-01-05", "2026-01-07", "not recorded", "2026-01-07", "2026-01-09", "2026-01-10"],
    "Amount": ["1,250", "480", "95000", "480", "n/a", "720"],
    "Age": [34, 29, np.nan, 29, 41, 250],
})

df = raw.rename(columns=lambda c: c.strip().lower().replace(" ", "_"))   # snake_case names
df = df.drop_duplicates()                                                 # 1 exact duplicate
df = df.dropna(subset=["customer_name"])                                  # no name -> unusable

df["customer_name"] = df["customer_name"].str.strip().str.title()
df["city"] = df["city"].str.strip().str.title()
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")      # bad text -> NaT
df["amount"] = pd.to_numeric(df["amount"].str.replace(",", ""), errors="coerce")

df.loc[~df["age"].between(0, 120), "age"] = np.nan                       # impossible ages
df["age"] = df["age"].fillna(df["age"].median())
df["amount"] = df["amount"].fillna(df["amount"].median())
df = df.reset_index(drop=True)

print(df)
print(df.dtypes)

Output

  customer_name       city order_date   amount   age
0      Asha Rao       Pune 2026-01-05   1250.0  34.0
1    Ravi Kumar     Mumbai 2026-01-07    480.0  29.0
2    Meera Iyer       Pune        NaT  95000.0  34.0
3    Kiran Shah  Bengaluru 2026-01-09   1250.0  41.0
customer_name               str
city                        str
order_date       datetime64[us]
amount                  float64
age                     float64
dtype: object

Detecting outliers with the IQR rule

Python

import pandas as pd

amounts = pd.Series([420, 480, 510, 530, 560, 600, 640, 700, 95000], name="amount")
q1, q3 = amounts.quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print(f"Q1={q1}, Q3={q3}, IQR={iqr}, fences=({low}, {high})")
print("outliers:", amounts[(amounts < low) | (amounts > high)].tolist())
print("mean with / without:", round(amounts.mean(), 1), round(amounts[amounts <= high].mean(), 1))
print("median is robust:", amounts.median())
print("capped:", amounts.clip(upper=high).tolist())

Output

Q1=510.0, Q3=640.0, IQR=130.0, fences=(315.0, 835.0)
outliers: [95000]
mean with / without: 11048.9 555.0
median is robust: 560.0
capped: [420, 480, 510, 530, 560, 600, 640, 700, 835]

Common Mistakes

Dropping every row with any missing value and losing most of the data.
Filling missing values with the mean when the column has extreme outliers (the median is safer).
Using astype(float) on dirty text and crashing, instead of pd.to_numeric(..., errors="coerce").
Not standardising text, so "Pune", "pune " and "PUNE" become three different cities.
Silently deleting outliers that are real — investigate before removing.

Key Points to Remember

Start with dtypes, isna().sum(), duplicated() and describe().
Handle missing data deliberately: dropna(subset=...), fillna, ffill/bfill.
to_numeric and to_datetime with errors="coerce" convert dirty columns safely.
Standardise text with .str methods and replace.
Use the IQR rule and domain rules to find outliers; remove, cap or keep them consciously.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Use your local project environment for these examples. Codelab currently runs Python and HTML/CSS/JavaScript; framework examples may need project dependencies.