5.2 Duplicates and Entity Resolution

Missing data asks why an expected fact is absent. Duplicate analysis asks the opposite question: do repeated rows describe one event recorded twice, several valid events sharing attributes, or conflicting versions of one entity? The answer depends on the unit of observation defined in Chapter 1 and the key contract enforced in Chapter 4.

Define what “duplicate” means before deleting anything

A full-row duplicate repeats every selected field. A key duplicate repeats a field that the schema expects to be unique. They answer different questions:

full_row = orders.duplicated(keep=False)
same_order_id = orders.duplicated("order_id", keep=False)

Two rows may share order_id but disagree on status or update time. They are not full-row duplicates, yet they violate a one-row-per-order contract. Conversely, two different orders may legitimately have identical zone, minutes, and fee values.

During diagnosis, keep=False marks every member of a duplicate group:

conflicts = (
    orders.loc[same_order_id]
          .sort_values(["order_id", "updated_at"])
)

The default keep="first" marks later occurrences but leaves the first unmarked. That is useful when applying a decided rule, but risky during audit because the complete conflict is less visible.

Inspect duplicate group sizes:

group_sizes = (
    orders.groupby("order_id", dropna=False)
          .size()
          .sort_values(ascending=False)
)

A missing key requires separate handling. Several rows with missing order_id are not automatically the same order merely because the key value is absent.

A survivor needs a business rule

drop_duplicates applies a positional retention rule:

first_seen = orders.drop_duplicates("order_id", keep="first")
last_seen = orders.drop_duplicates("order_id", keep="last")

“First” and “last” refer to current row order, not earliest and latest time. If the source order changes, the survivor can change. Sort by explicit priorities first:

ranked = orders.sort_values(
    ["order_id", "success", "updated_at"],
    ascending=[True, True, True],
)

clean = ranked.drop_duplicates("order_id", keep="last")
assert clean["order_id"].is_unique

This rule keeps the latest successful version when successful rows sort after unsuccessful rows. Real systems may use source authority, completeness, approval status, or event version. Write the rule in words, encode it, and test ties.

Preserve lineage before dropping rows:

work = orders.reset_index(names="source_row_id")

An audit table should map each removed source_row_id to its survivor and record the rule, timestamp, and reason. Do not overwrite the only raw copy. Deduplication changes counts, totals, and possibly the population definition.

Near duplicates require entity resolution

Exact keys may not exist across systems. One customer could appear as:

Chen Wei | chen.wei@example.com | 200120
Wei Chen | chenwei@example.com  | 200120
陈伟     | missing              | 200120

Entity resolution links records believed to describe the same real-world entity. Unlike exact duplicate removal, it is usually an uncertain inference. A false merge combines different people; a missed match leaves one person split across IDs. Both errors matter, but their costs can differ dramatically.

A reviewable workflow has several stages.

1. Preserve and normalize matching fields

Keep raw values and create separate matching keys:

customers["name_key"] = (
    customers["name"]
        .astype("string")
        .str.normalize("NFKC")
        .str.strip()
        .str.casefold()
)

Normalization makes comparisons consistent; it does not prove identity. Two different customers can share a normalized name.

2. Generate plausible candidate pairs

Comparing every pair of $n$ records requires

comparisons. One million records would create nearly half a trillion pairs. Blocking limits comparisons to records sharing a coarse feature such as postal code, email domain, or phone prefix:

for _, block in customers.groupby("postal_code", dropna=False):
    compare_pairs_within(block)

Blocking saves work but can miss true matches placed in different blocks. Use several justified blocking rules and measure their coverage on known matches.

3. Score several pieces of evidence

Candidate features may include normalized name similarity, exact email agreement, address similarity, date proximity, or phone suffix agreement. Evidence should be interpreted in context: a shared common surname is weaker than a shared verified email.

Do not include protected or sensitive characteristics merely because they improve a score. Identity systems affect people; feature choice, access, and error review require governance.

4. Separate decisions by confidence

A practical policy may use:

• High score plus strong exact evidence: automatic match.

• Ambiguous middle band: clerical review.

• Low score or contradictory evidence: non-match.

Thresholds must be evaluated on labeled pairs. Raising the match threshold normally reduces false merges but increases missed matches. Accuracy alone can be misleading when true matches are rare; inspect both error types and their costs.

Pair decisions must become consistent entities

Pairwise matches can create a graph. If A matches B and B matches C, a connected-component rule may put all three into one entity even when A and C contradict each other. This is transitive closure, and it can amplify one incorrect link.

Before assigning a canonical ID, check entity-level constraints such as incompatible verified emails, impossible overlapping accounts, or excessive cluster size. Preserve:

• Every source record and source-system identifier.

• Candidate features, score, model or rule version, and decision.

• Human reviewer and rationale when applicable.

• Canonical ID mapping with effective dates.

• A way to reverse or supersede an incorrect merge.

Exact deduplication and entity resolution both rely on consistent representations. The next section builds those representations deliberately, separating mechanical normalization from business meaning across text, categories, dates, and units.