Pandas · Module 9 of 22 · 4 concepts · about 5 min

Cleaning Messy Data

Real input is never clean. This module is the difference between an answer and a correct answer.

At work: Fixing what people and systems type in: duplicates, stray spaces, “N/A”, and ten spellings of the same city.

Duplicates, and what counts as one

What it is

duplicated() flags repeats and drop_duplicates() removes them, with subset to define identity and keep choosing first, last or none.

Why it matters

A duplicated fact row inflates every sum and count downstream, and duplicates in a lookup table multiply rows on a join.

In the real world: A signup form submitted twice creates two accounts for one person; counting them twice inflates the “new customers” number the CEO sees.

Visual example

Duplicates

An order logged twice is counted twice by every sum — until it is de-duplicated.

order 7 logged twice

order	total
7	50
7	50
8	20

drop_duplicates("order")

one row per order

order	total
7	50
8	20

The revenue

before: 50 + 50 + 20 = 120

after: 50 + 20 = 70

Why this matters

Nothing about 120 looks wrong. Duplicates inflate every total without an error, so de-duplicate before you aggregate.

Common misconception

Wrong idea: duplicates are harmless extra rows.

Correct idea: every sum, count and mean counts them again. Decide which columns define 'the same', then drop.

df.duplicated(subset="order_id").sum()        # how many, before removing any
df.sort_values("updated_at").drop_duplicates("order_id", keep="last")

De-duplicating after aggregating rather than before. Once a duplicate has been summed it cannot be distinguished from a genuinely larger value.

Practise it: Keep Only Each User's Latest Row, Revenue Per Customer From a Messy CSV

Column names

What it is

df.columns = [...] replaces them wholesale; df.rename(columns={...}) changes a few; a chained str.strip().str.lower().str.replace(" ", "_") normalises them all.

Why it matters

Names arriving with trailing spaces or mixed case break attribute access and make every later reference a guess.

In the real world: Tidying headers like “ Order Date ” and “Total(₹)” from an Excel export into order_date and total_inr so every script can use them.

df.columns = (
    df.columns.str.strip().str.lower().str.replace(r"[^a-z0-9]+", "_", regex=True)
)
print(repr(df.columns.tolist()))   # repr reveals the invisible

A trailing space in a header is invisible in head() output and produces a KeyError that looks impossible. Print repr(df.columns.tolist()) when a name that is obviously there cannot be found.

Practise it: Tidy Up Column Names

Whitespace and case in values

What it is

.str.strip(), .str.lower() and friends normalise the contents of string columns, not just their headers.

Why it matters

"Completed", "completed " and "COMPLETED" are three groups in a group-by and one concept in reality.

In the real world: “Mumbai”, “mumbai ” and “MUMBAI” are one city. Cleaning them makes sales per city correct.

Visual example

Strings

Three spellings of one status become one group once whitespace and case are normalised.

3 groups, by accident

status	n
completed	812
Completed	94
completed	7

.str.strip().str.lower()

1 group, correctly

status	n
completed	913

The real count

812 + 94 + 7 = 913

Why this matters

A group-by splits on exact strings. The small stray groups hide under the big one, and the report is simply wrong.

Common misconception

Wrong idea: "Completed" and "completed " are obviously the same status.

Correct idea: to Pandas they are three different strings until you strip and lower them.

before = df["status"].nunique()        # 11
df["status"] = df["status"].str.strip().str.lower()
df["status"].nunique()                 # 4 — the other 7 were spacing and case

replace, and targeted corrections

What it is

replace swaps values, accepting scalars, lists, dicts or regexes, and can be scoped to particular columns.

Why it matters

Sentinel values — -1, "N/A", "unknown", 9999 — need converting to real missing values before any numeric work.

In the real world: Mapping “N/A”, “unknown” and “-” in a country column to a proper missing value, and fixing a known product code typo.

df["age"] = df["age"].replace({-1: pd.NA, 999: pd.NA})
df["country"] = df["country"].replace({"USA": "US", "U.S.": "US"})

Using replace with a regex across the whole frame to fix one column. It touches every column, and the collateral damage is silent.