Matching and deduplication

Matching is a pipeline

'Sharma Steel Traders Pvt Ltd', 'SHARMA STEEL TRADERS PRIVATE LIMITED' and 'Sharma Steels' in three source systems: exact name matching finds none, and tax ID matching only works where the ID is filled and correct. So deduplication is a pipeline, each stage shrinking noise or cost until human review is small.

Deterministic rules first, fuzzy second

Apply exact identifier rules first, remove those pairs, then spend fuzzy effort on the rest. Beware identifiers that only look strong: a shared-service bank account, a dummy tax ID like AAAAA0000A, a head-office phone used by fifty branches. Your profile tells you which identifiers to trust.

Normalise, then block

Normalisation turns many 'near duplicates' into exact ones. Keep originals; add normalised columns beside them. Then block: n records give n × (n − 1) / 2 pairs, so 1 million partners mean about 500 billion comparisons. Comparing only within a shared key (PIN, name prefix) fixes that; use two or three keys so a PIN typo does not hide a duplicate.

import re

LEGAL_FORMS = r"\b(private|pvt|limited|ltd|llp|inc|gmbh|co|company)\b"

def norm_name(s) -> str:
    if not isinstance(s, str):
        return ""
    s = s.lower().replace("&", " and ")
    s = re.sub(r"[^a-z0-9 ]", " ", s)       # drop punctuation
    s = re.sub(LEGAL_FORMS, " ", s)           # drop legal forms
    return " ".join(s.split())                # collapse spaces

df["name_n"] = df["NAME_ORG1"].map(norm_name)
# Sharma Steel Traders Pvt. Ltd.  ->  sharma steel traders
# Larsen & Toubro Limited         ->  larsen and toubro

Scoring pairs with rapidfuzz

rapidfuzz scorers return 0–100, and the scorer matters. process.extract(query, choices, scorer=..., limit=3) returns the best candidates from a list; process.cdist scores whole lists at once for large volumes.

Try it: pick the scorer

Combine fields and set thresholds

Two different 'Ganesh Traders' in different cities both score 100 on name, so weight several fields and let an exact identifier override. Set two thresholds: above the upper one auto-match, between them steward review, below ignore. Start conservative.

from rapidfuzz import fuzz

AUTO, REVIEW = 92, 80

def pair_score(r1, r2) -> float:
    if r1["TAX_NUMBER"] and r1["TAX_NUMBER"] == r2["TAX_NUMBER"]:
        return 100.0                       # exact identifier wins
    name = fuzz.token_sort_ratio(r1["name_n"], r2["name_n"])
    street = fuzz.token_set_ratio(r1["street_n"], r2["street_n"])
    city = 100.0 if r1["city_n"] == r2["city_n"] else 0.0
    return 0.6 * name + 0.3 * street + 0.1 * city

def verdict(s: float) -> str:
    return "auto-match" if s >= AUTO else "review" if s >= REVIEW else "no-match"

Try it: duplicate or not?

Steward review, survivorship and MDG

The steward compares both records, confirms or rejects, and decides survivorship field by field. On a migration you keep a cross-reference from old keys to the surviving BP. MDG's duplicate check and MDG consolidation use the same steps; Python is where you prototype and tune rules before configuring them there.

Try it: tuning the grey zone

Check yourself