String Key Cleaning: ' Apple ', 'apple', and 'APPLE' are one product, not three

Before you start

New to this? A key is a text value you group or join on — a product name, an email, a city. Computers compare text by exact equality: " Apple ", "apple", and "APPLE" are three different strings, even though a person reads them as one product. So when you ask pandas to groupby("product") or to merge two tables on product, it takes those three spellings as three separate products. Normalising a key means rewriting every spelling into one canonical form before you use it — here: strip the spaces off the ends, squeeze any run of internal spaces down to one, and lowercase (casefold) the letters, so all three become "apple". This project teaches that one habit and shows the two ways skipping it quietly corrupts a result: a groupby that splits one product’s total into fragments, and a join that drops rows without an error. (This is exact normalisation — every variant maps to one clean key. It is not fuzzy matching: no typos, no abbreviations, no “did you mean”.)

1. The Brief

You’re handed a month of an accessories store’s sales and asked one plain question: which product sold the most? One line of pandas — groupby("product").sum() — answers it, and hands back a product that earned about $82, because the real best-seller was typed four different ways at three tills and its revenue is scattered across 34 rows the groupby never joins up. This is the messy-key trap, and it shows up any time a human types the same thing twice: customer names, SKUs, city fields, email addresses. The same defect turns a join from a lookup into a silent data-loss bug — match two tables on the raw text and the rows that don’t line up character-for- character just disappear. You’ll see both failures, fix them with a three-move normalisation, and learn to reach for it reflexively before you ever group or join on hand-entered text.

2. Difficulty & time

Difficulty 2/10. One tool (pandas), and Part A’s path is fully specified — the low end of the scale. It sits at the same level as averaging-averages (2) and below silent-row-loss (3) and data-contracts (3): each of those adds moving parts (a join to validate for both drops and duplicates, a whole contract of range and cross-field rules), whereas this turns on a single idea — clean the key first — applied twice. It is deliberately the easy cousin of vendor-dedupe (6): that project handles fuzzy duplicates (typos, abbreviations, edit distance, blocking, clustering); this one handles only the exact case where every variant differs by nothing but whitespace and case. Master the exact case here before the fuzzy one there. Part A is a short read-and-run; Part B is where you do the work.

3. What you’ll be able to do after

Normalise a text key to one canonical form (strip, collapse internal whitespace, casefold) and say why .str.lower() alone is not enough.
Diagnose a groupby result whose group count is far larger than the number of real entities, and recognise it as key fragmentation rather than genuine variety.
Diagnose a join that returned far fewer rows than it should have, and trace it to un-normalised keys dropping rows silently — not to missing data.
State the rule: normalise both sides of a join on the same canonical key before merging.

4. Prereqs & time box

Part A: ~30 minutes. Part B: ~1.5 hours. Both are hard caps — if Part A runs long, you’re overthinking a three-line clean. You need to read a CSV with pandas, call groupby, and call merge; nothing more. If you’ve never joined two tables, skim the merge doc linked below first.

For where to write and run Python (install, virtual environment, per-OS terminal, running a script step by step), see Start Here guide. No account and no GPU — requires_signup is empty.

5. Setup & data

The data is synthetic, generated locally by data/make_data.py from a fixed seed (byte-identical every run) — see Sources. Generate it once:

python data/make_data.py

That writes data/sales_a.csv (Part A), plus data/orders_b.csv and data/products_b.csv (Part B). Nothing is downloaded; there is no external source. Synthetic is the right call here: the lesson is whether normalising the key re-joins the fragments and recovers the dropped rows, and only generated data lets you know, in advance, exactly which spelling belongs to which real product. (A real retailer’s catalogue would also be proprietary — see Sources.)

6. Part A — Guided Build

Prefer the browser? If this project shows a ▶ Run button, you can run Part A right on the page — no setup. The commands below are for running on your own computer.

▶ Try it now — run the real Part A right here in your browser. This one button makes the data and runs Part A for you — you do not need to install anything, and you do not need to type any of the python … commands anywhere on this page (those are only for running on your own computer instead). First run loads Python + this project’s libraries: usually ~10–20 s, a little longer for machine-learning projects.

Run the guided script (or paste it cell-by-cell — each # %% is one step):

python part_a/analyze_sales.py

It walks six steps and writes part_a/product_totals_a.csv. The shape of the argument:

STEP 1. Load 180 sale lines. Checkpoint: the raw product keys carry ragged padding, mixed case, and doubled internal spaces — ' WIRELESS MOUSE ', 'wireless mouse'.
STEP 2. The naive answer: groupby on the raw text. Checkpoint: about 132 distinct raw keys from 5 real products, and the “top” group is a single fragment worth about $82.33 — the ranking is junk.
STEP 3. Zoom in on one product: 'wireless mouse' is spread across 34 raw spellings over 51 rows. That’s why no raw group looks big.
STEP 4. Normalise the key, then group. Checkpoint: exactly 5 products; the real best-seller is Wireless Mouse at ~$1,219.81, then Laptop Stand at ~$1,000.52. The naive ranking never had Wireless Mouse on top — it was in 34 pieces.
STEP 5. Why the full clean and not a shortcut: .str.lower() alone still leaves 69 distinct keys (padding and doubled spaces survive); the full normalise leaves 5.
STEP 6. Write the corrected per-product totals to part_a/product_totals_a.csv.

Why this and not that

Why not just .str.lower()? Case is one of three defects. Lowering fixes 'WIRELESS MOUSE' but not ' wireless mouse' (padding) or 'wireless mouse' (a doubled space). The idiom " ".join(s.split()) handles all whitespace — both ends and the interior — in one move, because str.split() with no argument splits on any run of whitespace and drops the empties; .casefold() then handles case (and is stricter than .lower() for non-ASCII text). Together they are the whole normalisation.
Why normalise into a new column instead of overwriting product? Keep the original text so you can still show a human “this is what was actually typed,” while you group and join on the clean key. Destroying the raw value throws away your audit trail.

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

Function	What it does here
`pandas.read_csv`	Load `sales_a.csv` into a DataFrame
`str.split`	With no argument, split on any run of whitespace — the collapse half of the clean
`str.casefold`	Aggressive lowercase — the case half of the clean
`Series.map`	Apply `normalise` to every product key
`DataFrame.groupby`	Total revenue per key — naive on raw text (STEP 2), correct on the clean key (STEP 4)

If something looks off

If your normalised group count isn’t 5, print sales["norm"].unique() — a stray character that isn’t whitespace or case (a hidden tab, a non-breaking space) would survive; here there are none, so 5 is the number.
If the naive top group already looks like the real best-seller, check you grouped on product (raw) in STEP 2 and norm (clean) in STEP 4 — it’s easy to clean the column too early.
A number that surprises you is the point of this project, not a bug — read it against the checkpoints above before assuming you broke something.

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 skill, new failure mode. A grocery store’s orders_b.csv gives order lines with a messy product key and a quantity; products_b.csv is a clean product master (category, unit price). Every order references a real catalogued product. The starter does the obvious thing — an inner merge on the raw product text — and keeps only about 5% of the order lines, because an order typed ' OLIVE OIL ' never matches the master’s 'Olive Oil', and the merge drops it with no error. The # TODO is the same clean as Part A, now applied to both sides of the join.

Acceptance criteria (checkable)

Your part_b/starter.py writes part_b/out/report.json with:

matched_fraction — the fraction of order lines that found a product in the master,
total_matched_revenue — the summed quantity × unit_price over the matched lines.

Then:

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

You’re done when the tests pass: a correctly normalised join keeps essentially every line (matched_fraction ≥ 0.99 — the reference hits 1.0, all 240 lines) and recovers the full revenue (within 0.5% of the true total; the reference is exact at $8,003.50). The naive raw-key join keeps 13 of 240 lines and about $547 of revenue, and fails both checks.

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

Normalise both keys the same way — the order key and the master key — then merge on the clean key. Cleaning only one side still drops rows.
Don’t fabricate matches: every kept line must trace to a real master product. (You can’t, here — every order references a catalogued item — but don’t reach for a fuzzy or partial match to force it; this is the exact case.)

Hints

Hint 1 — where the rows are going

An inner merge keeps only keys present on both sides. ' OLIVE OIL ' (order) and 'Olive Oil' (master) are different strings, so that pairing is absent from both sides’ intersection and the order line is dropped. Nothing errors — a smaller result is the “error”.

Hint 2 — reuse Part A's clean

The fix is the exact normalisation from Part A: " ".join(str(s).split()).casefold(). Add it as a new column on the orders and on the master.

Hint 3 — the shape of the answer

orders["norm"] = orders["product"].map(normalise), the same for master, then orders.merge(master, on="norm", how="inner"). Merge on "norm", never on the raw "product".

8. Self-check

Part A: your normalised group count (5) should be far smaller than the raw count (~132). If the two are close, you grouped on the raw key by mistake.
Part B: your matched_fraction should be 1.0 (or extremely close), and much higher than the starter’s ~0.05. If it’s still low, you normalised only one side of the join.
Re-running python data/make_data.py never changes the data (it’s seeded), so your numbers shouldn’t move between runs.
If you can state, in one sentence, what normalisation you applied and why you applied it to both sides of the join, you’ve got the transferable skill — the specific numbers are secondary.

9. Stretch

Add a key-collision report to Part A: for each normalised product, list the distinct raw spellings that mapped to it (a groupby("norm")["product"].unique()), so you can hand a human the evidence of what was cleaned.
In Part B, switch the inner merge to a left merge (how="left") on the raw key and count the NaN rows — that’s the number of order lines the inner join was silently dropping. Seeing the loss explicitly is how you’d catch this bug in production before it corrupts a report.
(Genuinely hard.) One order product is 'Sparkling Water' and the master has 'Sparkling Water' — the exact clean handles it. Now imagine the master also had a typo entry 'Sparkilng Water': exact normalisation would leave it a separate product forever. Work out why no amount of whitespace/case cleaning fixes it, and what class of technique (the one vendor-dedupe teaches) you’d need instead — and where that technique risks merging two genuinely different products.

10. Ship it

Put this in a repo README so it counts as a portfolio piece: one sentence on the trap (“a raw groupby fragmented one product across 34 spellings and named an $82 fragment the best-seller”), the before/after group counts side by side (132 → 5), and the one-line normalisation. Add a short note on Part B showing you transferred the same clean to a join — where the failure was silent row loss (240 → 13 lines) rather than fragmentation, and where you had to clean both sides. That last part — recognising the same skill under a different failure mode — is what an interviewer is actually checking for.

11. Sources

See SOURCES.md. The data is synthetic, generated by data/make_data.py from a fixed seed; there is no external source, and nothing is downloaded or committed.

Next up

Finished this one? Continue the Data Engineering track:

Wide to Tidy: melt a spreadsheet report so the next question is one line → · Browse all courses