The previous chapter promised that the techniques for duplicates and inconsistencies were coming. Here they are. This is a pandas chapter: less philosophy, more recipes you will reuse on every dataset.
The Order of Operations
Cleaning has a sequence, and the order is not optional. Standardize before you deduplicate (so "Acme " and "Acme" collapse together), and validate after (to prove the fixes worked).
Always keep the raw data untouched and clean into a new copy, logging how many rows you change. Cleaning you cannot audit is cleaning you cannot trust.
Removing Duplicates
Duplicates inflate counts, double-count revenue, and bias every average. In pandas,
duplicated() finds them (returns a boolean flag, removes nothing) and
drop_duplicates() removes them. Two arguments do the real work:
| Argument | What it does | Use it when |
|---|---|---|
subset= | Compare only these columns (a key) instead of all columns | "One row per customer_id" even if other fields differ |
keep="first" | Keep the first occurrence (the default) | Order does not matter, or the first is canonical |
keep="last" | Keep the last occurrence | The latest record is the most current |
keep=False | Flag/drop every member of a duplicate group | df[df.duplicated(keep=False)] to inspect all dupes |
Two different customers can share a name. Without a unique key (email, account ID), you
cannot be sure two identical-looking rows are really the same entity. And drop_duplicates()
with no subset keeps an arbitrary "first" row, you may instead want the
most complete record, so sort first or choose the key deliberately.
Near-duplicates need more than equality
Exact matching never catches "Jon Smith" vs "John Smith" or "Acme Inc" vs "Acme, Inc." These are the same entity in different clothes, the realm of record linkage. The tool is string similarity:
Levenshtein
Edit distance: how many single-character inserts, deletes, or swaps separate two strings. Often scaled to a 0 to 1 ratio.
Jaro-Winkler
A 0 to 1 score that rewards a shared prefix. Strong for short strings and names.
Jaccard (tokens)
Word-set overlap, order-independent. Good when word order varies.
A loose similarity threshold catches more true duplicates (higher recall) but risks
false merges, combining two genuinely different people (lower precision). Always
review matches above the threshold; never auto-merge blindly. Production tools:
rapidfuzz, recordlinkage, dedupe, or OpenRefine's clustering.
Standardizing Inconsistencies
Casing and whitespace are invisible bugs: "Male", "male", and "male " print almost
the same but are three different values to a computer, so they split a groupby and break a
merge. The cure is one repeatable pattern.
| Inconsistency | pandas fix | Watch out |
|---|---|---|
| Casing | s.str.lower() / .str.title() | Unify before groupby or groups split |
| Leading/trailing space | s.str.strip() | "Acme " ≠ "Acme"; invisible in print |
| Inconsistent categories | s.replace({variant: canonical}) | replace leaves unmatched as-is; map turns them to NaN |
| Dates (mixed) | pd.to_datetime(s, errors="coerce") | Bad values become NaT; target ISO 8601 |
| Numbers as text | strip $,, then pd.to_numeric(..., errors="coerce") | Count what you coerced to NaN |
| ZIP / IDs | keep as string; s.str.zfill(5) | Reading as int drops leading zeros (02134) |
errors="coerce" quietly turns anything unparseable into NaT or NaN.
That is a deliberate, useful behavior, but it creates missing data, so always check
.isna().sum() afterward. Handling those gaps is exactly what Handling Missing Data is about.
Validation Rules: a Contract
Cleaning once is good; guaranteeing the data is clean every time it loads is better. Validation rules encode what valid data must look like, so bad data fails loudly instead of slipping through.
| Rule type | Example | Check |
|---|---|---|
| Range | age between 0 and 120 | df["age"].between(0,120).all() |
| Type | qty is an integer | is_integer_dtype(df["qty"]) |
| Allowed value | gender ∈ {male, female, other} | df["gender"].isin(VALID).all() |
| Format / regex | valid email | df["email"].str.match(EMAIL_RE) |
| Uniqueness | customer_id is a primary key | df["customer_id"].is_unique |
| Cross-field | start ≤ end | (df["start"] <= df["end"]).all() |
A rule set is a contract: validate on ingest and after cleaning. Prefer raising an error
(an assertion, or a schema tool like pandera or Great Expectations) over
quietly coercing bad values, silent coercion hides the very problems you are trying to catch. A
validate() that returns every violation turns one-off cleaning into a testable pipeline.
Dedup and standardization are prerequisites, not polish. Joins and merges fail on a stray space;
groupby counts split across casing variants; duplicated rows over-weight a mean and can leak
between train and test sets. Entity resolution underpins customer-360, fraud detection, and cleaning ML
training data. Codified rules are the bridge from "I cleaned it once" to "this dataset is valid every time."
Duplicates in Machine Learning & AI
Duplicates are not just untidy, they actively distort a model. The most dangerous case is subtle: the same record landing in both the training and test sets, which quietly inflates every score you report.
| Duplicate issue | Where it shows up in ML | Why it matters |
|---|---|---|
| Train/test leakage | A duplicated row split across train and test | The model is tested on data it trained on, so reported accuracy is a lie |
| Over-weighting | Repeated rows in the training set | Duplicated examples silently count more, biasing the model toward them |
| Entity resolution | Near-duplicates (record linkage) across data sources | Matching "J. Smith" to "John Smith" is a whole modeling task of its own |
The order is not optional: remove duplicates before you split into train and test. Dedup after splitting, and copies of the same row can end up on both sides, the single most common cause of a model that scores beautifully in testing and fails in production.
Real-World Example: A Contact List with Duplicates
This contact list has 207 rows but only 180 real people. Some rows are
exact copies, and drop_duplicates() catches those, leaving 192. But a dozen more are
near-duplicates, the same person whose name got re-cased or space-padded, which slip through an
exact match. The companion notebook shows how deduplicating on a business key (email) catches them.
One row per contact (with duplicates): record_id (a row id, not a
person key), full_name (sometimes re-cased or padded), email (the reliable identity
key), city, and signup_year. It mixes exact and near-duplicates on purpose.
Bring it to life in Python
The companion notebook drops exact duplicates with subset and keep, standardizes
a six-spelling category column down to two, coerces messy dates and text-numbers, scores near-duplicates
with a dependency-free similarity ratio, and runs a validate() rule checker.
View opens the rendered notebook instantly (no setup). Open in Colab runs &
edits it live in your browser. To run locally, install numpy, pandas,
matplotlib and launch jupyter notebook.
🎓 Key Takeaways
- ✓Order matters: profile, standardize, deduplicate, validate, document, standardize before dedup, validate after.
- ✓drop_duplicates with
subsetdedupes on a key;keepchooses which row survives; identical ≠ duplicate without a key. - ✓Near-duplicates need string similarity (Levenshtein, Jaro-Winkler); fuzzy matching trades precision for recall, so review.
- ✓Standardize with discover → canonical → map → verify; casing and whitespace are invisible bugs; coercing creates missing values.
- ✓Validation rules are a contract that fails loudly; they turn cleaning into a repeatable, testable pipeline.
Practice Challenges
Five short challenges, beginner to intermediate. Try them on paper or in Python before checking the solutions.
Dedup on a key
A table has an exact duplicate row and a customer who appears twice with different spend. Drop the exact
duplicate, then keep one row per customer_id (the most recent), and report rows removed.
drop_duplicates() then drop_duplicates(subset=..., keep="last").Standardize a column
A country column has many spellings of two countries. Standardize it to two canonical values and confirm
with value_counts().
Coerce formats
A price column holds "1,250", "$90", and "oops". Convert it to
numeric and report how many values could not be parsed.
$,, then pd.to_numeric(errors="coerce"); count NaN.Score near-duplicates
Using a normalized Levenshtein ratio, decide which of "Acme Inc", "Acme, Inc.", "Acme Incorporated", "Beta LLC" are likely the same entity (similarity ≥ 0.85).
Write the rules
Write a validate() that flags a duplicate id, an age outside 0 to 120, and a
status not in {active, inactive}. Run it and print every violation.
is_unique, between, isin; return a list of issues.A fully-worked solutions notebook walks through all five challenges in the same visual style. Try them yourself first, then compare.
Quiz: Test Yourself
Eight quick questions on duplicates and inconsistencies. Answer them, hit Check Answers, and keep refining until you score 100%. Your progress is saved, so you can hop back to the chapter and return anytime.