Duplicate detection

This is the exact file agents read, including the credit header and licence notice. Save it as duplicate-detection/SKILL.md.

---
name: duplicate-detection
description: >
  Find and resolve duplicate records so that each real-world entity appears once, across pandas, SQL, Python fuzzy-matching libraries and R. Covers defining the business key that decides what counts as the same thing, exact versus near duplicates, normalising text before comparing, blocking to make fuzzy matching scale, choosing and validating a similarity threshold, the keep-first, keep-last and merge strategies, and flagging rather than deleting. Use it when someone says "remove duplicates", "dedupe", "drop_duplicates", "the same customer appears twice", "merge duplicate records", "fuzzy match these names", "record linkage", or "my totals doubled after a load". Do NOT use it for rows that doubled because of a join fan-out (see sql-joins), for missing values (see handling-missing-data), for extreme values (see outlier-detection), or for stacking two tables together.
license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/data-cleaning-preparation/duplicate-detection/.
---

# Duplicate detection

## When to use this, and when not to

Use this skill when the same real-world thing appears more than once in a dataset and you need each one to appear once. The triggers are a request to remove duplicates or dedupe, a call to `drop_duplicates`, a customer or product that shows up twice, totals that doubled after a data load, names or addresses that are the same entity spelled differently, or a record-linkage task that matches records across two sources with no shared key. The work is to define what "the same" means, find the repeats exactly or approximately, then keep, merge or flag them on purpose.

Do not use it when rows doubled because a join multiplied them (that is a grain problem for `sql-joins`), for gaps in the data (`handling-missing-data`), for values that are extreme but singular (`outlier-detection`), or for stacking two datasets on top of each other. The defining feature here is that two or more rows describe one entity and one of them should remain.

The core idea: a duplicate is defined by a business key, not by every column being identical. Decide which columns identify the entity before you remove anything, because the most common dedupe bug is deleting by the wrong definition of "same" (Christen, *Data Matching*, 2012).

## Inputs the agent must have before starting

1. The tool and version, because the pattern differs: pandas uses `drop_duplicates`, SQL uses `ROW_NUMBER()` or `GROUP BY ... HAVING`, and fuzzy matching needs a library.
2. The business key: which columns decide that two rows are the same entity. This is the single most important input. "Same email", "same order id", or "same name and date of birth" are keys; "every column identical" rarely is.
3. Whether the duplicates are exact (byte-for-byte after trimming) or near (typos, casing, spacing, formatting, abbreviations), because near duplicates need similarity matching, not equality.
4. Which record to keep when several match: the most recent, the most complete, or a merge of all of them, and the column that orders them (a timestamp, a completeness score).
5. Whether removal is allowed at all, and at what scale. Deletion and merging are irreversible, so confirm them, and for large tables plan to block (step 5) so the comparison does not blow up.

If the business key is unknown, ask for it. Deduping on the wrong key either leaves duplicates in place or merges two distinct entities into one, and the second error is worse.

## The workflow, step by step

### 1. Define the duplicate: choose the business key

Decide the subset of columns that identifies one entity. Two rows are duplicates when they share that key, even if other columns differ, and they are not duplicates when the key differs, however similar they look. Decision rule: never call a blanket "remove all identical rows" until you have confirmed the key, because a stray column such as a load timestamp or a surrogate id will make genuine duplicates look distinct.

### 2. Count before you touch anything

Count exact duplicates on the full row, then count duplicates on the business key alone (`duplicated(subset=key)`). The gap between the two numbers tells you how much of the duplication is hidden behind irrelevant differing columns, and gives you a target to reconcile against after cleaning.

### 3. Normalise before comparing

Many "near" duplicates are exact once trivial differences are removed. Trim leading and trailing whitespace, collapse repeated spaces, unify case, and standardise formats: lowercase emails, strip punctuation and spacing from phone numbers, parse dates to one format, and normalise unicode. After this, re-count on the key; a large share of apparent near duplicates usually becomes exact and can be handled without fuzzy matching.

### 4. Decide exact versus near

If records that refer to the same entity now match the key exactly, use exact deduplication (steps 6 to 7). If the same entity still appears under variant spellings (`"Jon Smith"` and `"John Smith"`, `"12 Oak St"` and `"12 Oak Street"`), you need fuzzy matching or record linkage (step 5). Decision rule: exact dedupe is cheap and safe, so exhaust normalisation and exact matching first, and only reach for fuzzy matching for the residue that genuine spelling variation leaves behind.

### 5. For near duplicates: block, compare, threshold, review

- **Block first.** Comparing every row with every other row is O(n squared) and will not finish on a large table. Group records into blocks by a cheap shared key (the first three letters of a surname, a postcode, a soundex code), and compare only within each block (Christen, 2012). The Python `recordlinkage` toolkit and `dedupe` library implement this index, compare, classify flow.
- **Compare with the right similarity.** Use an edit-distance or Jaro-Winkler score for short names, and a token-based score for multi-word fields like addresses, because a token score is not fooled by word order.
- **Set a threshold, then validate it.** Pick a similarity cut-off, then read the pairs just above and just below it by hand. The borderline band is where false matches and missed matches live, so a threshold you have not inspected is a guess (liken, "near-deduplication").
- **Review before merging.** Treat the matched pairs as candidates, not decisions, especially for identity data.

### 6. Choose the keep strategy, and order before you keep

Keep-first, keep-last or merge. The trap is that `keep="first"` on unsorted data keeps an arbitrary copy, which may be the stale or least complete one. Sort by recency and completeness first, so the row you keep is the best one. To merge, coalesce the non-null fields across the duplicate set so the survivor carries the most information, rather than discarding fields that only one copy held.

### 7. Prefer flag over delete

Add a group id and an `is_duplicate` flag rather than deleting, so the duplication is visible and reversible, and delete only after a human has confirmed. Keep a record of what was removed or merged, with before and after counts.

### 8. Verify

Re-run the key duplicate count and confirm it is zero. Confirm the kept record is the one you intended (recent and complete). Confirm that totals which were doubled now reconcile against a trusted figure. Finally, confirm you have not removed legitimate repeats: two separate orders from one customer, or two real people who share a name, are not duplicates, and an over-broad key will have swallowed them.

## Worked example, end to end

A nightly job double-inserted part of an orders table, and the finance total is doubled. A blanket dedupe removes nothing:

```python
df = df.drop_duplicates()        # removes 0 rows: each copy has a different load_ts
```

Each copy carries a different `load_ts` and surrogate `row_id`, so no two rows are fully identical. Defining the business key as `order_id` tells the true story:

```python
dupes = df.duplicated(subset="order_id").sum()      # 1,200 duplicate order ids
df = (df.sort_values("load_ts", ascending=False)    # newest first
        .drop_duplicates(subset="order_id", keep="first"))
```

Sorting by `load_ts` before `keep="first"` keeps the most recent copy of each order rather than a random one. The finance total now reconciles with the source system.

A separate customers table has the same person as `"John Smith, 12 Oak St"` and `"Jon Smith, 12 Oak Street"`. Normalising the address turns some pairs into exact matches; the rest need fuzzy matching. Blocking on postcode, then scoring names with Jaro-Winkler, flags pairs above 0.92 as matches and queues the 0.85 to 0.92 band for human review, because that band held both a true match (`"Jon"` and `"John"`) and a false one (two different people at a shared block of flats). Nothing is merged automatically.

## Variations you will meet in practice

- **Genuine repeats are not duplicates.** Two real transactions, or two real people with the same name, must survive. If a dedupe collapses them, the key is too broad; add a discriminating column.
- **Cross-source record linkage.** Matching records between two systems with no shared id is the same block, compare, threshold flow, run across the two sources rather than within one.
- **Join fan-out masquerading as duplicates.** Rows that appear duplicated only after a join are a join grain problem, not a dedupe problem. Fix the join (`sql-joins`); deduping the joined result hides the bug and can drop real rows.
- **Merge rather than keep.** When each copy holds a different fragment (one has the phone, another the email), merge by coalescing non-null fields instead of keeping one and losing the rest.
- **Unicode and hidden characters.** Non-breaking spaces, zero-width characters and smart quotes make identical strings compare as different; normalise unicode before matching.

## Templates you can use verbatim

```python
# Exact dedupe on a business key, keeping the most recent, complete record (pandas)
import pandas as pd
key = ["order_id"]
before = len(df)
df = (df.assign(email=df["email"].str.strip().str.lower())      # normalise first
        .sort_values(["load_ts"], ascending=False)
        .drop_duplicates(subset=key, keep="first"))
print(before - len(df), "duplicate rows removed")
assert df.duplicated(subset=key).sum() == 0

# Near-duplicate matching with blocking (rapidfuzz + a block key)
from rapidfuzz import fuzz
pairs = []
for _, block in df.groupby("postcode"):               # block to avoid O(n^2)
    rows = block.to_dict("records")
    for i in range(len(rows)):
        for j in range(i + 1, len(rows)):
            score = fuzz.WRatio(rows[i]["name"], rows[j]["name"])
            if score >= 85:                            # review the 85-92 band by hand
                pairs.append((rows[i]["id"], rows[j]["id"], score))
```

```sql
-- SQL: find duplicates, then keep one row per key (newest wins)
SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1;  -- find

DELETE FROM orders
WHERE row_id IN (
  SELECT row_id FROM (
    SELECT row_id,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY load_ts DESC) AS rn
    FROM orders
  ) t WHERE t.rn > 1
);   -- confirm with a human before running a DELETE
```

## Failure modes and anti-patterns

1. **Deduping on all columns.** `drop_duplicates()` removes almost nothing because a timestamp or surrogate id differs, while the key is still doubled. Spot it when the row count barely changes but totals stay inflated. Fix: subset to the business key.
2. **An over-broad key.** The key merges distinct entities, so two real people or two real orders collapse into one. Spot it when the count drops far more than expected. Fix: add a discriminating field and review.
3. **Skipping normalisation.** `"john@x.com"` and `" John@X.com"` survive as two records. Spot it when fuzzy matching flags many pairs that differ only by case or spacing. Fix: trim, lowercase and standardise before matching.
4. **Arbitrary keep.** `keep="first"` on unsorted data keeps a random, possibly stale copy. Spot it when the surviving record is missing recent fields. Fix: sort by recency and completeness first, or merge.
5. **Unblocked fuzzy matching at scale.** An all-pairs comparison never finishes on a large table. Spot it when the job hangs or runs out of memory. Fix: block on a cheap shared key, then compare within blocks.
6. **Deleting join fan-out.** Rows duplicated by a join are deleted as if they were data duplicates, removing real rows. Spot it when duplicates appear only after a join. Fix: correct the join grain, do not dedupe the result.

## Quality bar

- The business key is stated explicitly, and duplicate counts are reported on the key before and after.
- Text is normalised (trim, case, format, unicode) before any comparison.
- Exact versus near is chosen deliberately, and near-duplicate matching blocks before it compares.
- Any similarity threshold is chosen, and the borderline band is reviewed by hand, not trusted blind.
- The keep strategy sorts by recency and completeness, or merges to preserve fields; nothing is deleted or merged without confirmation.
- Verification shows zero remaining key duplicates, reconciled totals, and legitimate repeats preserved.

## Data handling and safety

Deduplication works on the most identifying data a dataset holds: names, emails, phone numbers, addresses. Do not send these to a third-party matching service without permission, and do not log raw identity values. A false match has real consequences, because merging two people's records can combine their medical, financial or account histories, so default to flagging near matches for human review and never auto-merge identity records on a similarity score alone. Deletion and merging are irreversible: require explicit human confirmation before a `DELETE` or a merge, keep an audit of what was removed, and work on a copy rather than the raw source. When the records affect health, money, legal standing or employment, a wrong merge can change a decision about a real person, so human review is mandatory and the merge is never fully automated.

## Appendix: tool-specific notes (verify before relying on these)

- **pandas:** `df.duplicated(subset=..., keep="first"|"last"|False)` returns a boolean mask (`keep=False` flags every member of a duplicate set); `df.drop_duplicates(subset=..., keep=...)` removes them. Sort before `drop_duplicates` to control which row survives. `value_counts` and `groupby(key).size()` help locate duplicates.
- **Fuzzy matching (Python):** `rapidfuzz` (fast; `ratio`, `WRatio`, `token_sort_ratio`, Jaro-Winkler) and the older `thefuzz`. For full record linkage, the `recordlinkage` toolkit and the `dedupe` library provide the index (block), compare and classify stages, and active-learning threshold selection.
- **SQL:** find duplicates with `GROUP BY key HAVING COUNT(*) > 1`; keep one row per key with `ROW_NUMBER() OVER (PARTITION BY key ORDER BY recency)` and delete where the number is above 1. Fuzzy matching varies by engine (PostgreSQL `pg_trgm` similarity and `levenshtein`, `fuzzystrmatch`).
- **R:** `duplicated` and `unique` for exact dedupe; `janitor::get_dupes` to inspect; `fuzzyjoin` and `RecordLinkage` for approximate matching.

## Sources

- Christen, *Data Matching: Concepts and Techniques for Record Linkage, Entity Resolution, and Duplicate Detection* (Springer, 2012): the index (blocking), comparison and classification stages, why blocking is required to avoid quadratic comparison, and the trade-off between false matches and missed matches when choosing a threshold. Verified 31 October 2026.
- liken SKILL.md (victoraut/liken-skills): an existing deduplication and record-linkage implementation covering exact and near deduplication, similarity dedupers (tfidf, lsh, jaccard, cosine) with a threshold, and keep strategies. Noted as a rival that this skill improves on by teaching how to choose the business key, how to select and validate the threshold, and blocking for scale, which it leaves out. Verified 31 October 2026.
- pandas documentation, `DataFrame.duplicated` and `DataFrame.drop_duplicates`: the `subset` and `keep` parameters and their `"first"`, `"last"` and `False` behaviour. Verified 31 October 2026.
- PostgreSQL documentation, `pg_trgm` and `fuzzystrmatch`: trigram similarity and Levenshtein distance for in-database fuzzy matching. Verified 31 October 2026.