Feed to Figure: clean the feed, then report the number it was hiding

Challenge — test yourself

• Combines: Data Engineering × Data Analytics
• Difficulty: Intermediate · 5/10
• You bring: Read CSVs and select rows with pandas, Merge two tables and know an inner join can drop rows, Group and sum to compute a total
• Time: ~1.5–2 h
• You’ll use: pandas
• No guided steps. A working starter that starts wrong, a hidden acceptance test, and your own approach. You do not need to have done a course here first — if the assumed skills above sound doable, dive in.

This is a Challenge, not a tutorial. There’s no guided walkthrough — just a realistic mess, a question, and a test that tells you when you’ve nailed it. You bring the approach. You don’t need to have done any course here first; if the assumed skills below sound doable, dive in.

The situation

Finance quotes completed net revenue off last month’s orders_feed.csv and it looks low. You’ve been handed the raw daily feed and a regions.csv lookup, and asked for the real number — plus an explanation of why the quick read was wrong.

The feed is the kind of thing that actually lands in an inbox:

• the same order sometimes appears twice (an earlier attempt and the final row, same order_id),
• amounts are text — "$1,234.50", "1234.50", "$1234.50" — not numbers,
• status comes in mixed case — Completed, completed, COMPLETED,
• and some orders point at a region_id that isn’t in regions.csv at all.

That last one is the trap: join the feed to regions with a plain inner join and those orders vanish silently, so the revenue total comes out too low and nobody notices.

Your deliverable

Write two files into out/:

• clean_orders.csv — one row per completed order after cleaning: order_id, region_id, region_name, net_amount (unmatched regions labelled Unknown, not dropped).
• report.json — with:
  • clean_completed_orders — how many completed orders remain after de-duplicating,
  • net_revenue — the corrected completed net revenue,
  • revenue_dropped_by_naive_inner_join — how much completed revenue a plain inner join to regions would have silently dropped (this is the “why it looked low”).

Run it

python data/make_data.py        # once — generates data/ (synthetic, seeded)
python starter.py               # your working file; it starts naive and wrong
pytest test_solution.py -q

The starter.py already produces the right output shape with the wrong numbers — fix the three # TODOs. You’re done when all three tests pass: your net_revenue lands within 0.5% of the truth, your completed-order count is exact, and you’ve quantified the join loss. A naive read (inner join, no de-dup, money left as text) understates revenue by ~11% and fails.

Why this is a cross-domain challenge

It’s deliberately not solvable from one skill set:

• Data Engineering — repair the feed so the raw amounts and counts are trustworthy, and make sure attributing a region to each order can’t quietly lose any of them.
• Data Analytics — define the KPI correctly (completed only, once each) and quantify the error the naive pipeline introduced, so the figure is defensible.

Clean the numbers but let the region step drop orders and your figure is confidently low; stop that but miss the repeats and you double-count. Both halves have to be right.

Self-check (before you trust it)

• Completed net revenue should be well above the naive inner-join figure — if they’re equal, you didn’t find the orphan regions.
• Re-running python data/make_data.py never changes the data (it’s seeded), so your number shouldn’t move between runs.
• revenue_dropped_by_naive_inner_join should be a chunky, non-zero slice of the total — that gap is the whole point of the exercise.