Detecting and Handling Duplicates
One entity, one row — deliberately
Exact, keyed, and fuzzy duplicates corrupt counts and inflate totals. Detection is a deliberate key decision; dedupe policy decides keep-first, keep-last, or aggregate.
What you will learn
- Detect exact and keyed duplicates
- Choose keep-first/last or aggregate policies
- Scope fuzzy duplicates without collapsing legitimate records
What is this?
A duplicate is two rows representing the same real-world entity. drop_duplicates(subset=['id']) removes rows sharing a key, with keep='first'|'last'|False. Exact duplicates share every field; fuzzy duplicates (name misspellings, address variants) are the hard case. The dedupe key is an analytical decision: customer_id, invoice_id, order+line.
Why do you need it?
- Counts, totals, and funnel steps all inflate on unseen duplicates.
- A join against a non-unique key fans out and quietly triples revenue.
- Dedupe policy determines report truth and affects every downstream number.
- Fuzzy dedupe, done badly, merges two real customers into one.
Mental model
A doorman keeping one entry per guest
Deduplication
The doorman checks each guest against the guest list by ID (the key), keeps the first arrival (keep-first), and if the same person appears under a slightly altered name, must decide 'same guest?' without losing two genuinely different people with similar names — that's the fuzzy-duplicate judgment call.
How it works
- df.duplicated(subset=None) finds rows; df.drop_duplicates() removes them.
- Key choice is the real decision: a business key (invoice_id) vs a signature (all columns).
- keep='first' prefers the earliest row; keep='last' the latest; keep=False keeps no dupes at all.
- Within groups, dedupe can aggregate — e.g., sum line-level rows per order-first.
- Fuzzy matches: normalized keys (lowercase, stripped, soundex-ish) catch close-but-not-exact pairs.
- Always count before and after so dedupe decisions are visible.
Dedupe decision flow.
Examples
From dupes to decision
import pandas as pddf = pd.DataFrame({"invoice_id": [101, 101, 102, 102, 102], "line": [1, 1, 1, 2, 2], "amount": [50, 50, 30, 40, 40]})print(df.duplicated(subset=["invoice_id", "line"]).sum())keep = df.drop_duplicates(subset=["invoice_id", "line"], keep="first")order_total = keep.groupby("invoice_id")["amount"].sum()print(keep)print(order_total)The key is invoice+line; duplicates collapse to one row per line, and per-order totals become correct.
In production
- A billing reconciliation dedupes invoice lines before summing revenue by month.
- An ETL marks the latest daily snapshot with keep='last' against a versioned key.
- A CRM cleans customer_sync exports by normalized email key to avoid double-counting accounts.
Best practices
- Decide the dedupe key out loud — that's the real spec.
- Count duplicates before removal and re-report the count after.
- For event data, dedupe on the natural key (session+event_id), not all columns.
- Prefer keep='first' when earlier is timestamp-authoritative, 'last' when updates overwrite.
Common mistakes
- Deduping by all columns and missing the 'same order, new field' dupes.
- Using a non-unique key and silently dropping distinct records.
- Ignoring case/whitespace so 'ACME' vs 'acme' duplicate rows survive.
- Deduping money data by rounded values and merging distinct charges.
Debugging tips
- If dedupe removes 'too much', the key was too coarse — inspect unique counts on the key.
- Dupes still present after dedupe → a new field differs; diff the row pair to see which.
- For fuzzy work, start lowercase/strip, then inspect the leaked pairs manually.
- Duplicated counts vary by key — print distinct per key candidate before choosing.
Interview checkpoint
Hands-on lab
- Find exact and keyed duplicates
- Apply keep-first/last policy on a business key
- Sum line-level rows per order without double counting
Dedupe an Order Export Correctly
25 min · beginner
Quick quiz
drop_duplicates(subset=['order_id'], keep='last') keeps:
drop_duplicates() with no subset considers all columns together.