Find Near-Duplicate Names Within One Excel Column

drop_duplicates removes rows that are identical. It does nothing about "ACME Ltd", "Acme Limited" and "A.C.M.E. Ltd." — three spellings of one customer, typed by three people over five years. Those near-duplicates split revenue across three rows in every report, send three statements to the same company, and make the customer count wrong. Finding them is a two-part job: first remove the differences that never matter (case, punctuation, "Ltd" versus "Limited"), then measure how similar the remaining strings are and group the close ones. The rapidfuzz library does the measuring quickly. Because fuzzy matching always produces some false positives, the output should be a list for a person to confirm, not an automatic merge. This page belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl rapidfuzz

customers.xlsx lists twenty customer names with the variants that real lists accumulate: legal suffixes added or dropped, punctuation, spaces inside words, accents, "&" versus "and", plurals — and two names that look alike but are different companies (Brightwater and Brightwell).

Step 1 — Normalise away the differences that never matter

import re
import unicodedata
import pandas as pd

LEGAL = r"\b(ltd|limited|gmbh|inc|llc|plc|co|company|corp|corporation)\b"

def normalise(name):
    s = unicodedata.normalize("NFKD", str(name)).encode("ascii", "ignore").decode()
    s = s.lower().replace("&", " and ")
    s = re.sub(r"[^\w\s]", "", s)            # A.C.M.E. -> acme
    s = re.sub(LEGAL, " ", s)
    return " ".join(s.split())

cust = pd.read_excel("customers.xlsx")
cust["Key"] = cust["Customer"].map(normalise)
print(cust[["Customer", "Key"]].to_string(index=False))
print("exact duplicates after normalising:", cust["Key"].duplicated(keep=False).sum())

Decomposing accents and dropping the marks turns "Café Señor" into "cafe senor"; removing punctuation turns "A.C.M.E." into "acme"; dropping legal suffixes makes "ACME Ltd" and "Acme Limited" both "acme". Normalisation alone already makes several groups identical, which is cheap and certain. What remains — "bright water supplies" against "brightwater supplies", "oak ridge farm" against "oakridge farms" — needs a similarity measure. Keep the normalised key in its own column; the original spelling stays untouched for the report.

Step 2 — Score every pair with rapidfuzz

import numpy as np
from rapidfuzz import fuzz, process

keys = cust["Key"].tolist()
squashed = [k.replace(" ", "") for k in keys]
by_words = process.cdist(keys, keys, scorer=fuzz.token_sort_ratio, workers=1)
by_letters = process.cdist(squashed, squashed, scorer=fuzz.ratio, workers=1)
scores = np.maximum(by_words, by_letters)
pairs = [(i, j, scores[i][j]) for i in range(len(keys)) for j in range(i + 1, len(keys)) if scores[i][j] >= 85]
for i, j, s in sorted(pairs, key=lambda p: -p[2]):
    print(f"{s:5.1f}  words {by_words[i][j]:5.1f}  letters {by_letters[i][j]:5.1f}  "
          f"{cust.at[i, 'Customer']:26} ~ {cust.at[j, 'Customer']}")

process.cdist computes the full similarity matrix in compiled code; scores run from 0 (nothing in common) to 100 (identical). Two scorers are combined, because names differ in two ways. token_sort_ratio sorts the words before comparing, so word order does not matter — but it treats "bright water" and "brightwater" as different words. Comparing the keys with all spaces removed using plain fuzz.ratio catches exactly those cases: "Bright Water Supplies Ltd" and "Pine Crest Ltd" now match their run-together spellings at 100. The pair score is the better of the two.

The list at 85 or above contains the true variants and one deliberate trap: "brightwater supplies" against "brightwell supplies" scores about 87, because only a few characters differ, yet they are different businesses. "North Wind Trading" against "Northwind Traders" scores about 81 and misses the cut — a near miss of the other kind. No threshold separates every true match from every false one, which is why the next steps end in a review rather than a merge. Matching between two lists instead of within one is covered in Fuzzy Match Names Between Two Excel Files with Python.

Step 3 — Group matches into clusters

Pairs chain: if A matches B and B matches C, all three belong together even if A and C score lower. A small union-find structure turns the pairs into clusters:

parent = list(range(len(keys)))

def find(i):
    while parent[i] != i:
        parent[i] = parent[parent[i]]
        i = parent[i]
    return i

for i, j, _ in pairs:
    parent[find(i)] = find(j)

cust["Cluster"] = [find(i) for i in range(len(keys))]
sizes = cust["Cluster"].map(cust["Cluster"].value_counts())
groups = cust[sizes > 1].sort_values(["Cluster", "Customer"])
for _, g in groups.groupby("Cluster"):
    print(" | ".join(g["Customer"]))

Each printed line is one suspected entity. Chaining is powerful and slightly dangerous: one borderline pair can join two otherwise separate clusters. Keeping the threshold fairly high and reviewing the clusters is the usual compromise.

Step 4 — Pick a canonical name and write a review sheet

For each cluster, propose one spelling — the most frequent, or the longest original as a stand-in for the most complete — and write everything to a sheet where a person approves or rejects each row:

def canonical(group):
    return group.loc[group.str.len().idxmax()]

cust["Proposed"] = cust.groupby("Cluster")["Customer"].transform(canonical)
review = cust[sizes > 1][["CustomerID", "Customer", "Proposed", "Cluster"]].sort_values(["Cluster", "Customer"])
review["Approve (Y/N)"] = ""
with pd.ExcelWriter("near_duplicates_review.xlsx", engine="xlsxwriter") as xw:
    review.to_excel(xw, sheet_name="Review", index=False)
    ws = xw.sheets["Review"]
    ws.set_column("A:A", 12)
    ws.set_column("B:C", 30)
    ws.data_validation(1, 4, len(review), 4, {"validate": "list", "source": ["Y", "N"]})
    ws.freeze_panes(1, 0)
print(review.to_string(index=False))

The reviewer sees each original name next to the proposed canonical spelling and picks Y or N from a dropdown. Cluster 5 shows why this matters: Brightwell Supplies has been pulled into the Brightwater cluster through its high score, and the reviewer — who knows these are two different suppliers — marks that row N while approving the two Brightwater spellings. Once the sheet comes back, read it, apply only the approved mappings to the source data, and keep the review file as the record of what was merged and why — the audit trail that an automatic merge would lose. Dropdowns are covered in Add Dropdown Data Validation to Excel with openpyxl.

Step 5 — Scale with blocking

cdist compares every name with every other: 20 names make 400 comparisons, 50,000 names make 2.5 billion — too many even for rapidfuzz. Blocking only compares names that share something cheap to compute, such as the first letter of the key:

def fuzzy_pairs(keys, threshold=85, block=lambda k: k[:1]):
    out = []
    frame = pd.DataFrame({"key": keys})
    for _, idx in frame.groupby(frame["key"].map(block)).groups.items():
        idx = list(idx)
        if len(idx) < 2:
            continue
        sub = [keys[i] for i in idx]
        m = np.maximum(process.cdist(sub, sub, scorer=fuzz.token_sort_ratio, workers=1),
                       process.cdist([k.replace(" ", "") for k in sub], [k.replace(" ", "") for k in sub],
                                     scorer=fuzz.ratio, workers=1))
        out += [(idx[a], idx[b], m[a][b]) for a in range(len(sub)) for b in range(a + 1, len(sub)) if m[a][b] >= threshold]
    return out

blocked = fuzzy_pairs(keys)
print(len(pairs), "pairs without blocking,", len(blocked), "with first-letter blocking")

With first-letter blocking the number of comparisons drops roughly by the number of blocks, and here no pair is lost because every matching pair starts with the same letter. The trade-off is that names differing in their first character — "The Acme Company" against "Acme" — can no longer match; normalisation that strips leading words such as "the" reduces that risk. For lists of tens of thousands, also pass workers=-1 to use every CPU core.

Common pitfalls

• Merging automatically. Fuzzy scores produce false positives (Brightwater versus Brightwell); always review.
• Skipping normalisation. Without it, legal suffixes and punctuation dominate the scores.
• Over-normalising. Stripping too many words ("supplies", "farm") makes different businesses identical.
• Chaining clusters. One weak pair can glue two clusters together; check large clusters closely.
• Comparing everything with everything. Quadratic growth; block for large lists.
• Picking the canonical name blindly. The longest spelling is a heuristic; let the reviewer overwrite the proposed name, or choose the spelling used on the most recent invoice.

Conclusion

Near-duplicates in one column need two passes: normalise away case, accents, punctuation and legal suffixes, which turns many variants into exact matches; then score the remaining pairs with rapidfuzz and group matches into clusters with union-find. Propose a canonical name per cluster, write a review sheet with an approval dropdown, apply only what a person approves, and block by a cheap key so the comparison scales beyond a few thousand names.

Frequently asked questions

How do I find similar but not identical names in an Excel column? Normalise the names (lower case, remove punctuation, accents and legal suffixes), then compare every pair with rapidfuzz, for example process.cdist with fuzz.token_sort_ratio, plus fuzz.ratio on the names with spaces removed, and keep pairs above a threshold such as 85.

What similarity threshold should I use? Start around 85 for token_sort_ratio on normalised company names and adjust after looking at the results. Lower thresholds find more variants but more false matches; there is no value that avoids review entirely.

How do I group fuzzy matches into one entity? Treat each matching pair as an edge and find connected components, for example with a small union-find structure. Every name in a component is a suspected duplicate of the others.

How do I de-duplicate a list of 50,000 names efficiently? Avoid comparing every pair: block the names by a cheap key such as the first letter of the normalised name and compare only within blocks, and use rapidfuzz with workers=-1 to use all CPU cores.

Related

• Up one level: Cleaning Excel Data with pandas — cleaning messy spreadsheet data.
• pandas drop_duplicates on an Excel Column — exact duplicates first.
• Standardise Category Values with a Mapping Table — applying approved mappings.
• Find Duplicate Rows in Excel with Python — duplicate detection across several columns.