Vendor Dedupe: one row per real company from a messy supplier master

Project at a glance

• Track: Data Engineering
• Difficulty: Intermediate · 6/10
• Time: ~1.25 h for Part A · ~3.5 h more for the optional Part B
• You’ll use: pandas, rapidfuzz, pytest
• 💻 Local setup needed (no in-browser ▶ Run yet) — a one-time, free Python install, then you just run files. Start Here walks you through it.
• New here? This is an intermediate project — it assumes you can read and run a little Python. New to the setup? Start Here walks you through it.

The data in this project is synthetic — generated locally by data/make_data.py, with no external source. That is deliberate: you can only score a de-duplication if you know which records are truly the same company, and only synthetic data carries that ground truth. No signup, no downloads, no GPU.

Before you start

New to entity resolution? Start here — it’s the idea the whole project rests on.

The same company gets typed into different systems a dozen different ways: Acme Corp, ACME Corporation, Acme Corp., Acme Crop (a typo), Acme & Sons vs Acme and Sons. To a computer these are five different strings, so a naive GROUP BY name counts five vendors where there is one. Entity resolution (also called record linkage or de-duplication) is the craft of deciding which records refer to the same real-world thing and collapsing them to one.

A tiny example — four supplier rows:

supplier_id   name
S001          Acme Corporation
S002          ACME Corp.
S003          Acme Crop         <- typo of "Corp"
S004          Globex LLC

The right answer is two companies: {S001, S002, S003} = Acme, and {S004} = Globex. Getting there means looking past case, punctuation, legal suffixes (Corp/LLC/…), and typos.

What you’ll learn: the standard four-stage resolution pipeline — normalize the names, block to avoid comparing every pair, fuzzy-match the candidates within a block, and cluster the matches into one entity each — and how to measure whether it worked with pairwise F1. Section 3 lists exactly what you’ll be able to do.

New to Python too? See Start Here — it starts from zero.

1. The Brief

You are handed a supplier master export where the same company appears under many spellings, and asked the question every procurement and finance team eventually asks: how many vendors do we actually have, and which records are the same one? You will build a pipeline that turns the messy list into one row per real company, with a chosen canonical name — and prove it worked by scoring it against known truth.

This is the unglamorous bedrock of spend analytics. Until suppliers are de-duplicated, every downstream number is wrong: total spend per vendor is scattered across spelling variants, “tail spend” looks bigger than it is, duplicate-payment checks miss, and you can’t negotiate from a real volume. A finance team that thinks it has 8,000 vendors often has 5,000 — and the gap is entirely records like Acme Corp / ACME Corporation. There is almost no free, project-based material on this precisely because real, labelled vendor data doesn’t exist to share; synthetic data with known truth is the honest way to teach it.

2. Difficulty & time

Difficulty 6 / 10. This composes a four-stage pipeline (normalize → block → fuzzy-match → cluster) and asks you to tune a precision/recall tradeoff (the match threshold) rather than follow a fixed recipe. Calibrated against the ledger (G10): it sits above the difficulty-5 builds analytics-with-duckdb and hidden-communities — each is essentially one technique applied well, whereas this one stacks four stages and a metric you have to reason about — and below recommend-from-scratch (7), which derives and implements gradient descent from scratch. It’s near hidden-communities in spirit (both cluster records and score against ground truth) but adds the fuzzy-matching and blocking layers, nudging it to 6.

Time is separate from difficulty: Part A is about 75 minutes of reading and running; Part B is 2–4 hours of your own work. The scripts run in seconds — the time is comprehension and tuning.

3. What you’ll be able to do after

• Normalize entity names so cosmetic differences (case, punctuation, legal suffixes, &/and) stop masquerading as different companies.
• Block a dataset so you fuzzy-compare only plausible candidates instead of all N² pairs — the difference between instant and hours at real scale.
• Fuzzy-match names with an edit-distance ratio that survives typos and word-order changes, and choose a threshold by trading precision against recall.
• Cluster pairwise matches into one group per entity with a union-find, pick a canonical record, and measure the whole thing with pairwise F1 against known truth.

The finished result

By the end of Part A you’ll have watched the score climb as each stage is added — from “every record looks unique” to a real resolver:

exact match only ......... pairwise F1 0.03   (141 records look like ~139 companies)
+ normalize names ........ pairwise F1 0.41   (cosmetic variants collapse)
+ block + fuzzy-match .... pairwise F1 0.88   (typos and reworded names recovered)

141 messy records resolve toward the 60 real companies behind them. Each stage earns its jump — and the metric (pairwise F1) is how you know it worked, not just that the count looks about right.

4. Prereqs & time box

You can write a for loop and a function, and you’ve seen a pandas DataFrame. No prior entity-resolution experience is needed — the primer above and Part A cover it.

Where to write and run code

Run this project on your computer. It uses rapidfuzz (the fuzzy-matching library); the in-browser ▶ Run option isn’t confirmed for rapidfuzz yet, so this is a local-only project for now.

The one-time setup — installing Python, downloading the code from the course site (no GitHub, no git), opening a terminal, creating a virtual environment, installing the requirements, and running Part A step by step — is covered once in Start Here. Come back here once python -c "import pandas, rapidfuzz" runs without error.

Time box

• Part A — Guided Build: 60–90 minutes. Hard cap. Past 90 minutes you’re fighting your setup, not the resolution — skip ahead and come back.
• Part B — Your Turn: 2–4 hours. Hard cap.

5. Setup & data

The data is synthetic and generated locally — see the banner above and SOURCES.md for full provenance. One command creates both files:

python data/make_data.py

That writes two CSVs into data/ (git-ignored; regenerate anytime, byte-for-byte identical because the seed is fixed):

• Part A — suppliers_a.csv (141 records, 60 real companies).
• Part B — suppliers_b.csv (528 records, 220 real companies).

Each row is supplier_id, name, city, country. The name is deliberately messy: the same company appears with swapped legal suffixes, &/and, punctuation and case noise, stray whitespace, and single-character typos. The true company behind each record is not in the file — it’s recovered from the seed by the grader, so you never see the answer key.

6. Part A — Guided Build

You’ll resolve the 141-record suppliers_a.csv and watch the score climb as each technique is added. Run the whole thing, or (better) one # %% cell at a time — see Start Here (this project runs on your computer, not in the browser):

python part_a/resolve_suppliers.py

The full, commented script is in part_a/resolve_suppliers.py. The spine:

Step 1 — Exact match is hopeless. Group only byte-identical names and score with pairwise F1 (F1 over all “same-company” record pairs).

Checkpoint: 141 records, 60 true companies, but ~139 exact clusters and F1 ≈ 0.03. Case, punctuation, suffixes and typos make almost every record look unique.

Step 2 — Normalize, then group. Lower-case, drop periods and punctuation, expand &, strip legal suffixes (Inc/Corp/LLC/…). Group identical normalized names.

Checkpoint: clusters drop and F1 ≈ 0.41. Cleaning collapses the cosmetic variants — but every typo (Textiles vs Txetiles) still reads as a different company.

Step 3 — Block, so you don’t compare every pair. Bucket records by a cheap key (first 4 normalized chars) so fuzzy matching runs only within a bucket. A second key (last 4 chars) catches typos that land in the stem, via the shared industry word.

Checkpoint: all-pairs would be 141·140/2 ≈ 9,870 comparisons; blocking cuts it by ~10× here — and by far more at Part B’s scale.

Step 4 — Fuzzy-match within blocks, union into clusters. token_sort_ratio(a, b) >= 90 links a pair; a union-find turns linked pairs into connected clusters.

Checkpoint: F1 ≈ 0.88 (precision ≈ 1.00, recall ≈ 0.79). Fuzzy matching recovers the typo’d variants cleaning left behind — the whole jump from 0.41 to 0.88.

Step 5 — Pick a canonical name and save the crosswalk. Most common raw spelling per cluster (tie-break: longest). Writes part_a/resolved_a.csv.

Why this and not that

• Why block before matching, not just fuzzy-match everything. Fuzzy comparison is O(N²). At 141 records that’s fine; at a real master of 100k it’s 5 billion comparisons. Blocking makes resolution scale — and Part B is where you feel it.
• Why token_sort_ratio, not raw equality or in. It’s an edit-distance ratio that’s robust to word order and small typos, so Copperline Textiles and Copperline Txetiles score ~95 while two genuinely different companies score far lower. The threshold is a dial: raise it for precision, lower it for recall.
• Why pairwise F1, not “number of clusters.” Getting the count right (60 clusters) can still put the wrong records together. Pairwise F1 scores every same-company decision, so it catches both over-merging (precision) and under-merging (recall).

Functions you’ll meet (and where to read more)

Function	What it does here	Docs
rapidfuzz.fuzz.token_sort_ratio(a, b)	Similarity 0–100, robust to word order and typos	rapidfuzz
s.str.lower() / str methods	Normalize case and strip characters	Series.str
pd.factorize(series)	Turn group keys into integer cluster labels	factorize
pd.crosstab(pred, truth)	Contingency table for the pairwise-F1 calculation	crosstab
union-find (disjoint set)	Merge linked pairs into connected clusters	disjoint-set
df.to_csv(path, index=False)	Save the resolved crosswalk	to_csv

If something looks off

• My resolved count is 72, not the 60 true companies — is that wrong? No. Part A reaches pairwise F1 ~0.88 with near-perfect precision but ~0.79 recall, so a few typo’d variants are still split off — you get slightly more clusters than the truth. The goal is a high F1, not an exact count (over-merging to force 60 would wreck precision).
• ModuleNotFoundError: No module named 'rapidfuzz'. The install step was skipped — run pip install -r requirements.txt inside your activated virtual environment (see Start Here).
• A checkpoint number doesn’t match. The data is seeded, so the F1 values are exact. Re-run python data/make_data.py (you may have skipped or edited it), then re-run Part A.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already handles the easy version; your job is to finish the # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Same core skill, bigger and messier, and now blind. Resolve the 528-record suppliers_b.csv (220 real companies) into one cluster per company. You don’t get the truth — the grader has it and scores your clusters.csv with pairwise F1.

The starter (part_b/starter.py) runs end to end but only normalizes the names (the Step-2 baseline) and scores ~0.50 — below the floor. Your job is to make it a real resolver.

Acceptance criteria (checkable)

Your solution writes into part_b/out/:

• clusters.csv — one row per input record: supplier_id, cluster_id, canonical_name.
• report.json — with keys records, clusters_found, achieved_metric (you can leave achieved_metric null; the grader scores you).

Then:

python part_b/starter.py
pytest part_b/test_solution.py -q

If pytest reports everything as skipped, it just means the output files don’t exist yet — run python part_b/starter.py first (once your solution fills in the TODOs), then re-run.

You’re done when the tests pass: every supplier_id appears exactly once, and your clustering reaches pairwise F1 ≥ 0.82 (the reference reaches ~0.91). Normalize-only scores ~0.50 and fails — fuzzy matching is what clears the floor.

Constraints — what must be true of your solution, not how to get there

• One row per record; no supplier_id appears twice.
• Every record lands in exactly one cluster, with a chosen canonical name.
• You must block — an all-pairs comparison of 528 records (~146k pairs) is the wrong habit to take to a real master. Compare within buckets.
• You must decide a match threshold and be able to say why. Too low merges different companies (precision falls); too high strands variants (recall falls).

Hints

Hint 1 — where to start

Reuse Part A’s normalize, then don’t stop there. Print how many clusters normalize-only gives vs. the 220 true companies — the gap is the typo’d and reworded variants that only fuzzy matching recovers.

Hint 2 — the two tools

Block records into buckets by a cheap key (first 4 normalized chars; add last 4 to catch stem typos), then run rapidfuzz.fuzz.token_sort_ratio only within a bucket. Link pairs at or above your threshold.

Hint 3 — from pairs to clusters (still not the code)

Matching gives you pairs; you need groups. If A matches B and B matches C, all three are one company. A union-find (disjoint-set) merges linked pairs into connected clusters in one pass — assign each record its cluster’s root as the cluster_id.

8. Self-check

You don’t need the answer key. You’re done when all of these hold:

• Every supplier_id in clusters.csv appears exactly once.
• pytest reports pairwise F1 ≥ 0.82 (aim past the reference’s ~0.91 if you tune well).
• Your clusters_found is near 220, not ~400 (that’s normalize-only) and not ~50 (that’s over-merging different companies).
• Re-running from python data/make_data.py reproduces the same numbers — the data is seeded.

And the tell-tale: if your cluster count is much higher than ~220, you’re still leaving typo’d variants stranded; if it’s much lower, your threshold is merging companies that only look alike (e.g. Aurora Supply and Aurora Freight).

9. Stretch

• Make it reusable. Wrap the pipeline into resolve(names, threshold) -> labels and sweep the threshold from 80 to 95, plotting precision, recall, and F1 to see the tradeoff move.
• Better blocking. Replace first/last-4-char keys with a phonetic key (Soundex/Metaphone) or sorted-token key and show it catches variants the char keys miss without exploding comparisons.
• (Genuinely hard) Add a second signal. Real masters carry a city or tax-id. Combine name similarity with an agreement bonus when city matches, and show it rescues records whose names alone are too far apart — the start of multi-field entity resolution.

10. Ship it

Put this in a portfolio repo and it counts. In that repo’s README:

• State the problem in one sentence a non-engineer gets: “the same vendor was entered 4 different ways; here’s how I collapsed 528 records to 220 real companies.”
• Show the before/after: exact-match count vs. resolved count, and the pairwise-F1 climb from 0.03 → 0.41 → 0.91 as you add normalization then fuzzy matching.
• Include your threshold sweep and one paragraph on the precision/recall tradeoff. That paragraph is the part that signals you understand resolution, not just called a library.

Keep make_data.py and the tests in the repo so anyone can reproduce your numbers from zero.

11. Sources

Rendered from SOURCES.md.

Field	Value
Dataset	Synthetic supplier master — messy vendor names (Part A 141 / Part B 528 records)
Type	Synthetic
Generated by	data/make_data.py (this repo), seeds 20260718 (A) and 20260719 (B)
Source URL	Not applicable — generated, not fetched
License	Not applicable — synthetic, no third-party rights
Access / generation date	2026-07-18
Redistribution	Permitted — synthetic; output regenerated from the seeded script, never committed
robots.txt checked	Not applicable — no scraping
ToS URL	Not applicable — no external source

Synthetic data teaches the mechanic; it does not prove a finding about the real world. The duplicate patterns were injected on purpose — recovering them demonstrates the technique, not a claim about any real supplier base.

Next up

That’s the top of the Data Engineering track. Browse all courses → to pick your next build.