Find duplicate customers when names differ by M/s, Shri or Pvt Ltd

What the duplicates actually look like

Before writing any matching logic, sort the customer master by name and read two hundred rows. In most Indian billing systems, you will find these patterns again and again:

• the same firm with and without "M/s" or "Messrs";
• "Pvt. Ltd.", "Pvt Ltd", "Private Limited" and nothing at all, for one company;
• "Shri", "Sri", "Smt", "Kumari" or "Dr" in front of an individual's name;
• initials in one record and full given names in another: "R. K. Sharma" and "Rajesh Kumar Sharma";
• punctuation noise: full stops, commas, "&" versus "and".

The first three are safe to remove mechanically. The fourth is not; initials are a hint, never a proof.

Clean into a new column, never over the original

Keep the name as entered for invoices and correspondence. Build a norm column that exists only for comparison: lower case, honorifics removed, legal forms collapsed and then dropped, punctuation replaced by spaces, and runs of spaces squeezed to one.

Order matters for the legal forms. Map "private" to "pvt" and "limited" to "ltd" first, then remove "pvt", "ltd" and "llp". Removing words before mapping them leaves stray fragments behind when the same firm is spelt two ways.

Compare only where a match is plausible

Comparing every customer with every other is slow and produces silly pairs. Two "Sunita Devi" records 1,500 km apart are almost certainly different people. Restrict comparisons to a block: same PIN is a good default, same city plus same first letter works when PINs are missing. This is called blocking, and it is what keeps false matches down.

Normalising and scoring within each PIN

All names, PINs and IDs below are synthetic.

import re
from difflib import SequenceMatcher
from itertools import combinations
import pandas as pd

# Synthetic customer master with the usual duplicates.
cust = pd.DataFrame({
    "id": [1, 2, 3, 4, 5, 6, 7, 8],
    "name": ["M/s. Agarwal Traders Pvt. Ltd.", "Agarwal Traders Private Limited",
             "Shri R. K. Sharma", "Rajesh Kumar Sharma", "Smt. Sunita Devi",
             "Sunita Devi", "Dr. Ramesh Sharma", "R K Enterprises"],
    "pin": ["201301", "201301", "110092", "110092", "226010", "226010", "110092", "110092"],
})

HONORIFICS = r"\b(?:m/s|messrs|shri|sri|smt|kumari|km|mr|mrs|ms|dr|prof)\b\.?"
LEGAL = [(r"\bprivate\b", "pvt"), (r"\blimited\b", "ltd"), (r"\bpvt\b|\bltd\b|\bllp\b", "")]

def normalise(name: str) -> str:
    s = name.lower()
    s = re.sub(HONORIFICS, " ", s)
    for pat, rep in LEGAL:
        s = re.sub(pat, rep, s)
    s = re.sub(r"[^a-z\s]", " ", s)          # dots, commas, ampersands
    return " ".join(s.split())

def initials_compatible(a: str, b: str) -> bool:
    """'r k sharma' vs 'rajesh kumar sharma': same surname, initials agree."""
    ta, tb = a.split(), b.split()
    if len(ta) != len(tb) or ta[-1] != tb[-1]:
        return False
    return all(x[0] == y[0] and (len(x) == 1 or len(y) == 1 or x == y)
               for x, y in zip(ta[:-1], tb[:-1]))

cust["norm"] = cust["name"].map(normalise)

pairs = []
for pin, block in cust.groupby("pin"):              # only compare within a PIN
    for (_, a), (_, b) in combinations(block.iterrows(), 2):
        score = SequenceMatcher(None, a["norm"], b["norm"]).ratio()
        init = initials_compatible(a["norm"], b["norm"])
        if a["norm"] == b["norm"]:
            verdict = "same after cleaning"
        elif init:
            verdict = "initials match: review"
        elif score >= 0.85:
            verdict = "similar: review"
        else:
            continue
        pairs.append((a["id"], b["id"], a["name"], b["name"], round(score, 2), verdict))

out = pd.DataFrame(pairs, columns=["id_a", "id_b", "name_a", "name_b", "score", "verdict"])
print(cust[["id", "norm"]].to_string(index=False))
print(out.to_string(index=False))

Three pairs come out. Agarwal Traders (IDs 1 and 2) and Sunita Devi (5 and 6) are identical after cleaning. R. K. Sharma and Rajesh Kumar Sharma (3 and 4) have a similarity score of only 0.69, which a plain threshold would miss, but the initials check catches them and routes them to review. Dr. Ramesh Sharma, in the same PIN, is not paired with R. K. Sharma, because R and K do not line up with a single given name. R K Enterprises is left alone: it shares initials with a person, which says nothing about whether it is the same party.

Why the similarity score is not enough on its own

SequenceMatcher.ratio() measures how many characters two strings share in order, scaled from 0 to 1. It is excellent at catching typos ("Agarwal" and "Agrawal") and poor at abbreviations, since "r k" has very few characters in common with "rajesh kumar". A single threshold therefore fails both ways: set it low and unrelated names pair up, set it high and initials are missed. Combining a strict threshold with a rule written for the specific Indian pattern of initials is more reliable than tuning one number.

Common surnames are the other weakness. Within a large PIN, "Sharma", "Singh" and "Kumar" produce many high-scoring pairs of different people. If your review list is swamped, add a second agreeing field before a pair is listed at all: the same mobile number, the same GSTIN, or the same street line.

What the review sheet should contain

Give the reviewer both original names, both IDs, the score, the verdict, and the fields that did or did not agree: PIN, phone, GSTIN, last invoice date. Add a decision column with three allowed values: merge, keep both, unsure. Record which ID survives a merge, and never delete the other record; mark it inactive and point it at the survivor, so old invoices still resolve.

Re-run the check monthly on new customers only, compared against the full master. Once the backlog is cleared, the list becomes short enough to finish in a few minutes.

Related clean-ups

Phone numbers are often the best second key, but they are just as messy as names; see normalise Indian mobile numbers before de-duplicating leads. Location blocking works better once PINs are cleaned, as in map customer PIN codes to district and state. Customer-master cleaning with honest review steps, rather than blind auto-merging, is part of the data quality work in our Data Analyst course.