Pandas Data Cleaning

Real-World Scenario

A CRM export arrives with revenue stored as "$1,200.00" strings, dates that don’t parse, the same customer listed twice, and gaps marked as N/A, -, or nothing at all. None of it can be analysed until it is cleaned.

Counting Missing Values

isna() gives a True/False mask; summing it counts the gaps per column.

import pandas as pd

df = pd.DataFrame({
    "name":    ["Alice", "Bob", None, "Dave", "Eve"],
    "age":     [28, None, 35, 42, None],
    "revenue": [1200.0, 450.0, None, 3200.0, 890.0],
})

print(df.isna().sum())
print((df.isna().mean() * 100).round(1))
print(df[df.isna().any(axis=1)])

$ python missing.py
name       1
age        2
revenue    1
dtype: int64
name       20.0
age        40.0
revenue    20.0
dtype: float64
  name   age  revenue
1  Bob   NaN    450.0
2  NaN  35.0      NaN
4  Eve   NaN    890.0

The mean of a boolean column is the fraction of True values, so isna().mean() is the missing rate directly. age holds integers, but its Nones turned it into float64 — the numbers show as NaN, not None, because a NumPy integer column has no way to store a gap.

The inplace fillna That Does Nothing

A very common line from older tutorials fills a column in place.

import warnings
import pandas as pd

df = pd.DataFrame({"dept": ["Eng", None, "Sales"], "age": [25.0, None, 32.0]})

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    df["dept"].fillna("Unknown", inplace=True)
print(df)
print([w.category.__name__ for w in caught])

df["dept"] = df["dept"].fillna("Unknown")
df["age"] = df["age"].fillna(df["age"].median())
print(df)

$ python fillna_inplace.py
    dept   age
0    Eng  25.0
1    NaN   NaN
2  Sales  32.0
['ChainedAssignmentError']
      dept   age
0      Eng  25.0
1  Unknown  28.5
2    Sales  32.0

The first print still shows the gap. df["dept"] produced a separate object, and under Pandas 3’s Copy-on-Write rules inplace=True filled that object, which was then thrown away — the ChainedAssignmentError warning is the only sign. Assigning the result back to the column is the form that always works.

Group-Wise Filling

Filling with each group’s own median usually beats one global value.

import pandas as pd

df = pd.DataFrame({
    "dept":   ["Eng", "Sales", "Eng", "Sales", "Eng", "Sales"],
    "salary": [80000, None, 90000, 50000, None, 54000],
})

df["global"] = df["salary"].fillna(df["salary"].median())
df["by_dept"] = df["salary"].fillna(df.groupby("dept")["salary"].transform("median"))
print(df)

$ python group_fill.py
    dept   salary   global  by_dept
0    Eng  80000.0  80000.0  80000.0
1  Sales      NaN  67000.0  52000.0
2    Eng  90000.0  90000.0  90000.0
3  Sales  50000.0  50000.0  50000.0
4    Eng      NaN  67000.0  85000.0
5  Sales  54000.0  54000.0  54000.0

The global median (67,000) lands between the two departments, so it overpays the missing Sales row and underpays the missing Eng row. transform("median") returns a Series the same length as df, holding each row’s department median, so fillna can pair it up row by row.

Dropping Missing Values

dropna can drop on any gap, on all-empty rows, on specific columns, or by a minimum count.

import numpy as np
import pandas as pd

df = pd.DataFrame({"a": [1, np.nan, 3, np.nan],
                   "b": [4, 5, np.nan, np.nan],
                   "c": [7, 8, 9, np.nan]})

print(len(df.dropna()), "rows with no gaps")
print(df.dropna(how="all"))
print(df.dropna(subset=["a"]))
print(df.dropna(thresh=2))

$ python dropna.py
1 rows with no gaps
     a    b    c
0  1.0  4.0  7.0
1  NaN  5.0  8.0
2  3.0  NaN  9.0
     a    b    c
0  1.0  4.0  7.0
2  3.0  NaN  9.0
     a    b    c
0  1.0  4.0  7.0
1  NaN  5.0  8.0
2  3.0  NaN  9.0

Only row 0 is complete, so a bare dropna() would keep a single row out of four. how="all" removes just the fully empty row 3, subset checks only the listed columns, and thresh=2 keeps rows with at least two real values.

Fixing String-Typed Numbers and Dates

Exported data often stores numbers and dates as text. Converting it directly fails loudly.

import pandas as pd

raw = pd.DataFrame({
    "revenue":    ["$1,200.00", "$450.50", "N/A", "$3,200.00"],
    "created_at": ["2024-01-15", "2024-02-20", "2024-03-10", "invalid"],
    "active":     ["True", "False", "True", "True"],
})

try:
    raw["revenue"].astype(float)
except ValueError as e:
    print("ValueError:", e)

raw["revenue"] = pd.to_numeric(raw["revenue"].str.replace(r"[$,]", "", regex=True),
                               errors="coerce")
raw["created_at"] = pd.to_datetime(raw["created_at"], errors="coerce")
raw["active"] = raw["active"].map({"True": True, "False": False})
print(raw)
print(raw.dtypes)

$ python fix_types.py
ValueError: could not convert string to float: '$1,200.00'
   revenue created_at  active
0   1200.0 2024-01-15    True
1    450.5 2024-02-20   False
2      NaN 2024-03-10    True
3   3200.0        NaT    True
revenue              float64
created_at    datetime64[us]
active                  bool
dtype: object

astype(float) stops at the first value it can’t read. Stripping $ and , first and then using to_numeric(errors="coerce") turns every leftover bad value — here N/A — into NaN instead of raising. to_datetime does the same for the bad date, which becomes NaT (“not a time”).

Removing Duplicates

duplicated marks repeats; subset decides which columns define “the same record”.

import pandas as pd

df = pd.DataFrame({
    "email":   ["a@x.com", "b@x.com", "a@x.com", "c@x.com", "b@x.com"],
    "name":    ["Alice", "Bob", "Alice", "Carol", "Bobby"],
    "revenue": [1200, 450, 1200, 800, 600],
})

print(df.duplicated().sum(), "exact duplicates")
print(df.duplicated(subset=["email"]).sum(), "duplicate emails")
print(df.drop_duplicates(subset=["email"], keep="last"))

$ python dupes.py
1 exact duplicates
2 duplicate emails
     email   name  revenue
2  a@x.com  Alice     1200
3  c@x.com  Carol      800
4  b@x.com  Bobby      600

Only one row is an exact copy, but two rows repeat an email — “Bobby” differs from “Bob” in name and revenue, so a full-row check doesn’t see it. keep="last" keeps the most recent version of each email, which is usually what you want when later rows are corrections.

Standardizing Text

String cleanup happens through the .str accessor, one vectorized call per step.

import pandas as pd

df = pd.DataFrame({
    "name":  ["  alice ", "BOB", "Carol  ", "  dave"],
    "city":  ["new york", "NEW YORK", "New York ", "Boston"],
    "phone": ["+1-555-1234", "555.5678", "(555) 9012", "5553456"],
})

df["name"] = df["name"].str.strip().str.title()
df["city"] = df["city"].str.strip().str.title()
df["phone"] = df["phone"].str.replace(r"\D", "", regex=True)
print(df)
print(df["city"].value_counts())

$ python strings.py
    name      city     phone
0  Alice  New York  15551234
1    Bob  New York   5555678
2  Carol  New York   5559012
3   Dave    Boston   5553456
city
New York    3
Boston      1
Name: count, dtype: int64

Before cleanup the three New York spellings would count as three different cities; after strip and title they collapse into one. \D matches any non-digit, so the phone column keeps digits only — note the +1 country code survives as a leading 1, which is a decision you have to make explicitly.

Flagging Outliers

The IQR rule and the z-score rule can disagree sharply on the same data.

import numpy as np
import pandas as pd

def count_outliers(n_extreme):
    rng = np.random.default_rng(42)
    salary = pd.Series(np.concatenate([rng.normal(75000, 15000, 1000 - n_extreme),
                                       rng.normal(500000, 50000, n_extreme)]))
    q1, q3 = salary.quantile([0.25, 0.75])
    iqr = q3 - q1
    iqr_out = ((salary < q1 - 1.5 * iqr) | (salary > q3 + 1.5 * iqr)).sum()
    z_out = ((salary - salary.mean()) / salary.std()).abs().gt(3).sum()
    print(f"{n_extreme} planted: IQR flags {iqr_out}, z-score flags {z_out}, "
          f"std {salary.std():,.0f}")

count_outliers(50)
count_outliers(150)

$ python outliers.py
50 planted: IQR flags 56, z-score flags 50, std 95,484
150 planted: IQR flags 150, z-score flags 2, std 152,900

With 50 planted outliers, both rules find them; the IQR rule also flags 6 ordinary salaries from the far tail of the normal group, because its fences are built tightly from the middle half of the data. With 150 planted outliers the z-score rule collapses to 2. Those extreme values have inflated the standard deviation so much that none of them is 3 standard deviations from the mean any more — an effect called masking. The quartiles barely move, so the IQR rule still finds all 150.

Practice

1. In missing.py, fill age with its median and name with "unknown", assigning each result back, then confirm no gaps remain.

import pandas as pd

df = pd.DataFrame({"name": ["Alice", "Bob", None], "age": [28, None, 35]})
df["age"] = df["age"].fillna(df["age"].median())
df["name"] = df["name"].fillna("unknown")
print(df)
print(df.isna().sum().sum())

$ python practice_fill.py
      name   age
0    Alice  28.0
1      Bob  31.5
2  unknown  35.0
0

2. In fix_types.py, count how many revenue values failed to convert.

import pandas as pd

revenue = pd.Series(["$1,200.00", "$450.50", "N/A", "$3,200.00", "--"])
clean = pd.to_numeric(revenue.str.replace(r"[$,]", "", regex=True), errors="coerce")
print(clean)
print("failed:", clean.isna().sum())

$ python practice_coerce.py
0    1200.0
1     450.5
2       NaN
3    3200.0
4       NaN
dtype: float64
failed: 2

3. In dupes.py, keep one row per email but choose the row with the highest revenue.

import pandas as pd

df = pd.DataFrame({
    "email":   ["a@x.com", "b@x.com", "a@x.com", "c@x.com", "b@x.com"],
    "name":    ["Alice", "Bob", "Alice", "Carol", "Bobby"],
    "revenue": [1200, 450, 1200, 800, 600],
})
best = df.sort_values("revenue", ascending=False).drop_duplicates(subset=["email"])
print(best.sort_index())

$ python practice_dedupe.py
     email   name  revenue
0  a@x.com  Alice     1200
3  c@x.com  Carol      800
4  b@x.com  Bobby      600

4. Using the 50-outlier data from outliers.py, cap the salaries at the upper IQR bound with clip instead of flagging them, and print the new maximum.

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
salary = pd.Series(np.concatenate([rng.normal(75000, 15000, 950),
                                   rng.normal(500000, 50000, 50)]))
q1, q3 = salary.quantile([0.25, 0.75])
upper = q3 + 1.5 * (q3 - q1)
capped = salary.clip(upper=upper)
print(round(salary.max()), round(capped.max()), round(upper))

$ python practice_clip.py
612146 117394 117394