A retry in the payment system, a double-clicked submit button, two exports concatenated by mistake — and suddenly revenue is up 8% without a single new sale. Duplicates are the quietest way to be confidently wrong: every number still looks plausible, it's just counted twice.
What you'll learn
What
Deduplication is detecting rows that represent the same real-world fact more than once and keeping exactly one. In pandas that's duplicated() to flag and drop_duplicates() to remove, controlled by two decisions: the subset (which columns define 'the same') and keep (which copy survives).
Why
Aggregations are defenceless against duplicates — sum, count, and mean all happily include every copy. A dataset with duplicated orders overstates revenue, inflates user counts, and biases any model trained on it. Dedup is cheap; the errors it prevents are not.
Where it's used
Every ETL pipeline dedupes after joins and appends. CRM systems dedupe customer records, payment processors dedupe transactions from retries, and analysts dedupe before any count or sum they intend to present.
Where this runs in production
Network retries can submit the same charge twice, so payment records carry idempotency keys and pipelines dedupe on them — otherwise merchants would see doubled revenue and customers doubled charges.
The same customer signs up as 'Jon Smith' and 'Jonathan Smith' with one shared email. Dedup runs on chosen key fields (email, phone) rather than full rows, then merges the survivors' details.
Mobile clients resend play events on flaky connections. Analytics pipelines dedupe on (user, track, timestamp) before counting streams, since royalty payouts depend on the counts being right.
df.duplicated() # True for 2nd+ occurrence of a full-row repeat df.duplicated().sum() # how many repeats df.drop_duplicates() # remove them, keep first occurrence df.duplicated(keep=False) # True for EVERY member of a duplicate group df[df.duplicated(keep=False)] # inspect all copies side by side
keep='first' (default) marks later copies; keep='last' marks earlier ones; keep=False marks all copies — the inspection mode, because you should look at duplicates before deleting them.
By default every column must match. With subset=['order_id'] only the key must match. This is the central modelling decision: full-row duplicates are almost always safe to drop (they carry zero extra information), but subset-duplicates can hide conflicts — same order_id, different amounts — where dropping blindly destroys data.
# Exact copies vs key conflicts — count them separately exact = df.duplicated().sum() key_dupes = df.duplicated(subset=['order_id']).sum() conflicts = key_dupes - exact # same key but differing values print(exact, key_dupes, conflicts)
If key_dupes exceeds exact, some repeated keys carry different values. Those aren't duplicates — they're contradictions, and they need investigation or a rule (e.g. keep the latest), not a silent drop.
latest = (
df.sort_values('updated_at')
.drop_duplicates(subset=['customer_id'], keep='last')
)The sort-then-drop idiom: order the group so the row you want is last, then keep='last'. This turns 'which copy survives' from luck into a stated rule.
If the same guest RSVPs three times, the caterer who counts RSVPs cooks for three phantom guests. The host instead keeps a guest LIST — each name once — and cooks per name. Deduplication is switching from counting submissions to counting identities; subset is you deciding what counts as 'the same name'.
duplicated() finds textual sameness, not real-world sameness. Two identical rows can be two genuine events (a customer really did buy the same coffee twice at the same price) if the table has no unique event id. And near-duplicates — 'Jon Smith' vs 'Jon Smith' — pass undetected until strings are normalised. Dedup after type and string cleaning, and ask whether the table SHOULD allow repeats before deleting any.
Click each stage — deletion is the last step, not the first.
An export was accidentally concatenated with itself; remove the copies.
Row (1, 50) appears twice — an exact full-row duplicate.
Your task
A signup table has duplicate emails from double-submitted forms. Count the duplicates, keep each email's most recent signup, and report the final row count.
2 3 ['pro', 'plus', 'free']
Write your solution in the editor on the right, then hit Run.
With default parameters, df.duplicated() marks which rows True?
What does the subset parameter of drop_duplicates control?
df.duplicated().sum() is 0 but df.duplicated(subset=['user_id']).sum() is 12. What does this tell you?
What is the correct approach?
Count how many product ids are involved in CONFLICTS — i.e. the id repeats but with differing rows. Print that count. df = pd.DataFrame({'pid':['P1','P1','P2','P2','P3'], 'price':[10, 10, 20, 25, 30]})
A transactions table has no unique transaction id, and two identical rows appear: same customer, item, price, and date. Why is dropping one of them risky?
Walk me through how you'd deduplicate a messy customer table.
First I measure and inspect before touching anything: duplicated().sum() for exact copies, duplicated(subset=[key]).sum() for key-level repeats, and df[df.duplicated(keep=False)].sort_values(key) to eyeball whole groups. The gap between key-level and exact counts is my conflict count — same key, different values. Exact copies I drop freely; they carry no information. For conflicts I need a survivor rule: usually most-recent, implemented as sort_values by the update timestamp then drop_duplicates(subset=[key], keep='last'), or sometimes most-complete, keeping the row with fewest nulls. I also dedupe AFTER string normalisation and type fixes, since ' jon@x.com ' and 'jon@x.com' won't match before cleaning. Finally I verify: recount duplicates (should be zero) and reconcile an aggregate like revenue so I can state exactly what the dedup changed.
What's the difference between keep='first', keep='last', and keep=False, and when do you use each?
They control which members of a duplicate group get flagged by duplicated() or retained by drop_duplicates(). keep='first' treats the earliest occurrence as the original — the default, fine for exact copies where all rows are interchangeable. keep='last' keeps the latest occurrence, which becomes meaningful when I've sorted by a timestamp first — sort then keep='last' is the standard keep-most-recent idiom for versioned records. keep=False flags every member of the group including the first; it never makes sense for dropping (you'd lose the whole group) but it's exactly right for inspection, because df[df.duplicated(keep=False)] shows all copies side by side so I can classify them as copies versus conflicts before deciding anything.
Where do duplicates typically come from in production data, and why does the source matter?
The main sources: retries, where a client resends on timeout — fixed by deduping on an idempotency key; double-appends, where the same file is loaded twice — full-row dedup after the concat; join fan-out, where merging against a table with repeated keys multiplies rows — the fix belongs in the right-hand table, not the merged output; human entry, where the same entity is typed with variations — that needs string normalisation and often fuzzy matching, since exact dedup can't see it; and versioned records, where multiple rows per entity are history, and 'deduplication' really means a deliberate keep-latest policy. The source matters because it decides both the key and the fix — and because dedup that only treats the symptom will face the same duplicates again on the next load. Ideally the pipeline prevents them upstream rather than sweeping them downstream.
Common Mistakes to Avoid
1) Dropping before inspecting — always look at df[df.duplicated(keep=False)] first. 2) Forgetting drop_duplicates returns a new DataFrame — assign it, or the 'cleanup' silently does nothing. 3) Deduping on a key without checking for conflicts — same key with different values is a contradiction, not a copy. 4) Using keep='last' without sorting first — 'last' then means file order, which is luck. 5) Deduping before string/type cleaning — ' A' and 'A' won't match. 6) Assuming identical rows are always errors — without an event key, some repeats are genuine events. 7) Never reconciling — quantify what the dedup changed (rows removed, revenue delta) so the fix is auditable.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: subset and keep in drop_duplicates using a guest-list story.' • 'Show me the sort-then-keep-last idiom on a versioned records example.' • 'Quiz me: copies vs conflicts — I'll classify your scenarios.' • 'How would join fan-out create duplicates and where should the fix go?' • 'Interview mode: challenge my plan to dedupe a transactions table with no id column.'
Glossary
duplicated() — boolean flag for repeated rows (default: 2nd+ occurrence). drop_duplicates() — remove flagged repeats, keeping one survivor per group. subset — the columns that define row identity for the check. keep — which occurrence survives: 'first', 'last', or False (flag all). Key conflict — same key columns, different values elsewhere. Survivor rule — the stated policy for which copy is kept (first, most recent, most complete). Idempotency key — a unique id that makes retries detectable. Join fan-out — row multiplication caused by merging on non-unique keys.
Recommended Resources
• Docs: pandas API reference for DataFrame.duplicated and DataFrame.drop_duplicates — the subset/keep semantics repay a careful read. • Read: a primer on idempotency keys (Stripe's API docs have a good one) to see how systems prevent duplicates at the source. • Practice: concatenate any CSV with itself, add a few conflicting edits, and practise separating exact copies from conflicts before cleaning. • Next in DSM: with rows unique, the next lesson fixes columns whose types are wrong — Data Type Coercion.
Recap
✓ duplicated() flags repeats (2nd+ by default); drop_duplicates() removes them — assign the result. ✓ keep=False is the inspection mode: view every member of each duplicate group before deleting. ✓ subset defines identity; the gap between key-level and exact duplicate counts is your conflict count. ✓ Conflicts (same key, different values) need a survivor rule — sort by timestamp, then keep='last'. ✓ Dedup after string/type cleaning, and remember textual sameness isn't proof of real-world sameness. ✓ Verify afterwards: recount duplicates and reconcile an aggregate so the correction is auditable. Next up: Data Type Coercion. Duplicates dealt with — but numbers stored as text still can't be summed. Next you'll force every column into the type it should have been all along.
Run your code to see the output here.