Units Mismatch: you can't sum a column that mixes cases and eaches

Before you start

New to this? A unit of measure (UoM) is the unit a quantity is counted in — a stock line might be 3 CASES or 3 EACHES (individual units), and those are wildly different amounts. A pack size says how many eaches are in one case (a case of soda might be 24 eaches). The trap this project is about: when one column holds quantities in different units row by row — some in cases, some in eaches — adding the column up gives a nonsense number, because you’ve added “3 cases” to “3 eaches” as if they were the same. Example: 2 cases of a 24-pack plus 5 loose eaches is not “7”; it’s 2×24 + 5 = 53 eaches. This project teaches you to spot a mixed-unit column and always convert every row to one common base unit before you aggregate — the same discipline whether the mix is cases/eaches or kilograms/grams.

1. The Brief

You’re handed a warehouse stock export and asked one innocent question: how many units do we have on hand? You can answer it in one line of pandas — quantity.sum() — and get a number that’s more than ten times too low, because half the rows are logged in CASES and half in EACHES, and summing them treats a 144-count case as a single item. This is the mixed-units trap, and it shows up all over operations: bills of materials that mix kilograms and grams, fuel logged in litres and gallons, bandwidth in MB and GB, times in seconds and minutes. An ops team that sums a mixed-unit column is reporting a number that means nothing, and the error is invisible unless you know to check the units. You’ll build the correct total, see exactly where the missing inventory went, and learn the one rule that fixes the whole family of cases.

2. Difficulty & time

Difficulty 2/10. One concept (convert before you aggregate), a short guided path, and only pandas + a single numpy.where. It sits below three difficulty-3 projects in the ledger: abc-xyz-inventory (two segmentation axes and a Pareto cut), silent-row-loss (a join whose row-count change you must diagnose), and cohort-retention (pivoting a cohort triangle) each carry more moving parts than this does. Here the whole project turns on a single idea — a column that mixes units can’t be summed — applied cleanly, with one lookup join in Part A. It’s a genuine judgment skill, not a syntax drill, which is what keeps it off the very floor of the scale. 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

Spot a quantity column that mixes units of measure, and say in one sentence why summing it is meaningless.
Convert every row to a common base unit — using a per-row lookup (a join) or a fixed conversion factor — and then aggregate correctly.
Quantify how far the naive sum was off, and point to exactly which rows carried the gap.
State the one condition under which a plain sum of the column is the right answer.

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 it. You need to read a CSV with pandas, join two tables, and do arithmetic on columns; nothing more.

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 needed 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/stock_a.csv and data/packs_a.csv (Part A) and data/recipe_b.csv (Part B). Nothing is downloaded; there is no external source. Synthetic is the right call here: the lesson is whether your total matches the true unit-converted number, and only generated data lets you know that truth in advance.

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/convert_units.py

It walks seven steps and writes part_a/eaches_breakdown_a.csv. The shape of the argument:

STEP 1. Load the 23 stock lines. Checkpoint: the uom column holds both CASE and EACH — that mix is the entire problem.
STEP 2. Take the naive answer — quantity.sum(), units ignored. Checkpoint: 1,411. A number, but a meaningless one: it adds “39 cases” to “134 eaches” as if they were the same.
STEP 3. Join the pack-size lookup (packs_a.csv) so every row knows its eaches-per-case. Checkpoint: still 23 rows — the join is a lookup, it doesn’t add or drop rows.
STEP 4. Convert every row to eaches (CASE → quantity × pack, EACH → as-is), then sum. Checkpoint: 15,218 eaches — more than 10× the naive number. This is the real count.
STEP 5. See where the gap lives: the 12 CASE rows carry only 333 in raw quantity but 14,140 once converted; the naive sum threw that multiplier away on every case row.
STEP 6–7. The rule, and a saved per-row breakdown you can eyeball.

Why this and not that

Why not just quantity.sum()? Because the column isn’t in one unit. Summing it answers “the total of a pile of numbers that mean different things” — which is not a quantity of anything. You have to make the units the same before the sum, not after.
Why a join for the pack size, instead of one global multiplier? Because pack size is per-SKU — 24 here, 12 there, 144 for the small parts. A single “cases × 12” shortcut would be wrong for every SKU that isn’t a 12-pack. The lookup carries the right factor onto each row.
Why convert to eaches and not to cases? Either base works, but eaches is the unit every row can express exactly (you can’t always write a loose 5-each row as a whole number of cases). Pick the base that every row converts into cleanly.

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

Function	What it does here
`pandas.read_csv`	Load `stock_a.csv` and `packs_a.csv` into DataFrames
`pandas.DataFrame.merge`	Join each stock row to its SKU’s pack size (STEP 3)
`numpy.where`	Convert per row: `CASE` → `quantity × pack`, else leave as-is (STEP 4)
`pandas.DataFrame.groupby`	Split raw vs converted totals by unit of measure (STEP 5)
`Series.sum`	Total the converted `eaches` column (STEP 4)

If something looks off

If your converted total equals your naive total, your uom column is probably all one value — check that it really contains both CASE and EACH.
If a converted number comes out as NaN, a SKU in the stock file has no matching pack size — the join left it blank. The assert in STEP 3 catches exactly this.
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 setting. A bakery’s recipe_b.csv lists each ingredient’s amount and its unit — some in kilograms, some in grams, some in milligrams. Production wants the total batch mass. The starter prints the naive answer — amount.sum() with units ignored — which is nonsense, because it adds 25 (kg of flour) to 750 (mg of ascorbic acid) as if they were the same number. The # TODO is the same move as Part A: convert every row to one base unit first. But note the twist — there’s no lookup table here. The factors are fixed physical constants (1 kg = 1000 g, 1 mg = 0.001 g) you supply yourself, and there are three units, not two, so reaching for Part A’s merge would be over-engineering it.

Acceptance criteria (checkable)

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

total_mass_g — the total batch mass in grams, after converting every row,
n_ingredients — the number of ingredient rows.

Then:

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

You’re done when the tests pass: n_ingredients is exact and total_mass_g lands within 0.5% of the true converted value (the reference hits it exactly). The naive amount.sum() is about 8.3× too small and fails.

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

The total must be a single number in one unit (grams): convert each row, then sum.
Don’t drop the milligram rows because they look tiny — they belong in the total; they just convert to a small number of grams. Every ingredient row counts.

Hints

Hint 1 — what "convert first" means here

In Part A each row was converted to eaches before summing. Here each row must be converted to grams before summing. The unit column tells you which conversion each row needs.

Hint 2 — where the factors come from

Unlike Part A, there’s no packs_b.csv to join. The factors are constants: kg → 1000, g → 1, mg → 0.001 (grams per unit). Map each row’s unit to its factor.

Hint 3 — the shape of the answer

grams = recipe['amount'] * recipe['unit'].map({'kg': 1000, 'g': 1, 'mg': 0.001}), then total = grams.sum(). It should come out far larger than the naive sum, because the kg rows were being counted as single-digit numbers before.

8. Self-check

Your converted total should be much larger than the naive amount.sum() (the kilogram rows were being counted as tiny numbers) — sanity-check that the flour alone (25 kg) is already 25,000 g, bigger than the entire naive total.
Re-running python data/make_data.py never changes the data (it’s seeded and the recipe is fixed), so your number shouldn’t move between runs.
If you can state, in one sentence, what base unit you converted to and why a raw sum was wrong, you’ve got the transferable skill — the specific numbers are secondary.

9. Stretch

Add a by_unit breakdown to Part B: how many grams does each original unit (kg, g, mg) contribute to the batch, and confirm the three contributions sum to your total.
In Part A, report the answer per SKU in cases and eaches: for each SKU, total its eaches and also express that as whole cases plus a loose-each remainder — the inverse conversion.
(Genuinely hard.) Real exports aren’t this clean. Suppose some rows use uom values you’ve never seen (PLT for pallet, INR for inner pack, a blank, a lowercase case). Design a conversion that fails loudly on any unit it doesn’t recognise — refusing to silently drop or misconvert a row — and argue why “assume eaches when unsure” is the dangerous choice here.

10. Ship it

Put this in a repo README so it counts as a portfolio piece: one sentence on the trap (“a raw sum of a mixed-unit column undercounted inventory by more than 10×”), the two numbers side by side (1,411 vs 15,218), and the one-line rule — convert every row to one base unit before you aggregate. Add a short note on Part B showing you transferred the same idea to a different domain (recipe mass, not warehouse counts) where the conversion came from fixed constants instead of a lookup table. That last part — recognising the same skill under a different surface — 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 Supply Chain track:

ABC/XYZ Inventory: segment by the money and the variability, not the volume → · Browse all courses