Wide to Tidy: melt a spreadsheet report so the next question is one line

Project at a glance

• Track: Data Engineering
• Difficulty: Absolute beginner · 2/10
• Time: ~0.5 h for Part A · ~1.5 h more for the optional Part B
• You’ll use: pandas
• ▶ Runs in your browser (nothing to install) — Part A uses only pandas, numpy and the Python standard library — all run in the browser. A full local setup is documented below too.
• New to this? This is a beginner project — you can’t break anything. Never coded? You can still run and read every step — writing your own code is only the optional Part B. Click ▶ Run below to start in your browser.

Before you start

New to this? A table is wide when one kind of fact is spread across many columns — a sales report with a Jan column, a Feb column, a Mar column, and so on. It is tidy (also called “long”) when every row is one observation and every fact is its own column: instead of twelve month columns, you have a single month column and a single sales column, with one row per region-and-month. A tiny example — this wide row:

region  Jan  Feb  Mar
North   413  426  417

becomes these three tidy rows:

region  month  sales
North   Jan    413
North   Feb    426
North   Mar    417

Nothing was lost — the month simply moved out of the column header and into a cell. This project teaches the one pandas move that does that (melt) and, more importantly, why you reach for it: once the month is a real column, questions like “total per region” or “which month was highest” are a single line, where the wide layout forces you to name every column.

1. The Brief

A finance team emails you the year’s sales the way finance always does: a wide spreadsheet, one row per region and one column per month, Jan through Dec. Then they ask two ordinary questions — “what’s each region’s total for the year?” and “what was our single biggest month?” — and you discover the layout fights you: every answer means naming twelve columns by hand or writing a loop across them. Reshape the table so each number is its own row (region, month, sales) and both questions collapse to one line each. This “wide report in, tidy table out” step is the quiet first move of almost every real analysis — dashboards, cohort tables, sensor feeds, survey exports all arrive wide — and getting it wrong (or grinding through it by hand) is where a lot of beginner pipelines silently go sideways. You’ll melt the report to tidy form, answer both questions, then in Part B do it on a messier feed where the reshape is only half the job.

2. Difficulty & time

Difficulty 2/10. One tool (pandas), one file, one concept — melt — and Part A’s path is fully specified, which puts it at the low end of the scale. It sits below the difficulty-3 data-engineering projects csv-to-parquet, data-contracts, and idempotent-loads: each of those carries more moving parts (row-group statistics and predicate pushdown; a multi-rule contract validator; an upsert key with re-run semantics), whereas this turns on a single idea — move the fact out of the header — applied cleanly. It is a peer of the other difficulty-2 entry averaging-averages (both are one-concept judgment skills, not syntax drills). What keeps it off the floor of the scale is that the judgment — recognising when data is trapped in the headers, and in Part B that an offline sensor is missing rather than zero — matters more than the one function call. 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

• Melt a wide, one-column-per-period table into tidy long form and explain, in one sentence, why the tidy version makes the next group-by or filter trivial.
• Look at a table and spot when a fact you want to group or filter by is stranded in the column headers (a month, a station, a question) rather than living in a column.
• Reshape while keeping the right identifier columns fixed (id_vars), including when there is more than one of them.
• Fix a value column whose type is wrong after reshaping (text that should be numbers), and decide correctly what a missing/“offline” cell should mean before you aggregate.

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 and call one reshape function; 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 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 — regional sales, wide by month) and data/readings_b.csv (Part B — air-quality readings, wide by station). Nothing is downloaded; there is no external source. Synthetic is the right call here: the whole point is to check your tidy-form answer against a known truth, and only generated data lets us know the seasonal shape, the weekend dip, and the offline dropouts in advance. Install the pinned deps first:

pip install -r requirements.txt

6. Part A — Guided Build

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

python part_a/reshape_sales.py

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

• STEP 1. Load the wide board report. Checkpoint: 5 rows x 13 columns (region + 12 months). Notice the month is stored in the column name, not in a cell — that’s the problem.
• STEP 2. Melt to tidy: id_vars="region", the twelve months as value_vars. Checkpoint: 60 rows x 3 columns (region, month, sales). The month moved into a real column; nothing lost.
• STEP 3. Question 1 — total sales per region, one groupby. Checkpoint: North leads at 5352, then South 4610, East 3973, West 3281, Online 3057 (GBP thousands).
• STEP 4. Question 2 — the single biggest month for any region, one idxmax. Checkpoint: North in Nov at 522.
• STEP 5–6. The rule (headers that you want to group by belong in a column), then save the tidy table.

Why this and not that

• Why melt instead of just summing the month columns? You can write wide[months].sum(axis=1) for the per-region total — but the moment the question changes (“biggest single month”, “average per quarter”, “compare weekdays”) you’re back to hand-naming columns or writing a loop. Melt pays for itself once because every later question becomes a group-by on a column that now exists.
• Why does the month belong in a column and not a header? A column header is metadata — pandas won’t group or filter on it. The month is data you want to slice by, so it has to be a value in a column. Melt is the move that turns headers back into data.
• The reverse exists too. When a human needs the grid back (a report, a heatmap), pivot_table turns tidy back into wide. Tidy is for computing; wide is for eyeballing.

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

Function	What it does here
pandas.read_csv	Load the wide report into a DataFrame (STEP 1)
DataFrame.melt	Unpivot the month columns into tidy rows (STEP 2)
DataFrame.groupby	Total sales per region on the tidy table (STEP 3)
Series.idxmax	Row label of the single biggest observation (STEP 4)

If something looks off

• If STEP 2 gives you far more than 60 rows, check value_vars lists exactly the twelve months and nothing else — if region slipped into value_vars it gets melted into the values.
• If your per-region totals look tiny, you may have summed one month instead of grouping the tidy sales column — the group-by is over the whole sales column, not a single month.
• A number that matches the checkpoints means you reshaped correctly; the reshape is the skill, the specific totals are secondary.

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 runs and prints an answer; your job is to finish the two # TODOs 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.

Same core skill (melt a wide table to tidy, then the question is one line), new setting and two new wrinkles. A city’s air-quality network emails readings_b.csv: one row per date, one column per monitoring station (Meadow, Central, Harbour, Roadside), plus a day_type column marking each date weekday or weekend. Ops asks: on weekends only, which station is the dirtiest (highest mean PM2.5), and what is that mean? Two things make it more than Part A:

1. Two identifier columns. You must keep both date and day_type as id_vars when you melt — miss one and it gets folded into the readings.
2. A type fix, with a judgment call. When a station drops offline, its cell holds the literal text "offline", so the whole column loads as text. You must coerce it to numbers — and decide what "offline" means. It is missing, not a reading of 0: a dead sensor is not clean air. The starter fills offline with 0 and gets a wrong, too-low answer; your job is to drop it instead.

Acceptance criteria (checkable)

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

• dirtiest_weekend_station — the station with the highest mean weekend reading (a name),
• dirtiest_weekend_mean — that station’s mean weekend PM2.5 (offline dropped, not zeroed),
• n_weekend_readings — the count of real (non-offline) weekend readings.

Then:

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

You’re done when the tests pass: the station name is exact, n_weekend_readings is exact, and dirtiest_weekend_mean lands within 1% of the true value (the reference hits it exactly). The starter’s offline-as-zero answer is about 5.6% too low and over-counts the readings, so it fails two of the three tests.

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

• Reshape to tidy first, keeping both date and day_type as identifiers.
• "offline" must become missing and be dropped before you average — never counted as 0.
• The answer is a mean over individual weekend readings, not a mean of per-day or per-station pre-averages.

Hints

Hint 1 — keep both identifiers when you melt

wide.melt(id_vars=["date", "day_type"], value_vars=["Meadow", "Central", "Harbour", "Roadside"], var_name="station", value_name="reading"). Passing a list of id_vars keeps both columns fixed; everything in value_vars unpivots into the new station/reading pair.

Hint 2 — the type fix, and what offline means

After melting, reading is text (because some cells were "offline"). Convert it with pandas.to_numeric using errors="coerce", which turns "offline" into NaN. Then dropna those rows. Do not fillna(0) — an offline sensor is missing, not a reading of zero.

Hint 3 — the shape of the answer

tidy = tidy[tidy["day_type"] == "weekend"]
per_station = tidy.groupby("station")["reading"].mean()
dirtiest = per_station.idxmax()
dirtiest_mean = per_station.max()
n = len(tidy)          # real weekend readings only, because offline was dropped

8. Self-check

• Your n_weekend_readings should be smaller than the total number of weekend cells, because the offline ones were dropped — if it equals the full grid, you counted the dead sensors.
• The dirtiest weekend mean should be a bit higher than the offline-as-zero starter printed (zeros only ever drag a mean down), and should sit in a plausible PM2.5 range, not near zero.
• 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 was hiding in the column headers and why you melted it, you’ve got the transferable skill — the specific station and mean are secondary.

9. Stretch

• Add a weekday-vs-weekend comparison to Part B: melt once, then group by day_type and station together and confirm every station reads cleaner on weekends (less traffic).
• Go the other way: take your tidy sales table from Part A and rebuild the original wide grid with pivot_table. Confirm it round-trips to the same numbers — melt and pivot are inverses.
• (Genuinely hard.) The offline dropouts are not evenly spread — Roadside goes dark more often than the others. Work out whether dropping offline rows biases the weekend mean (does a sensor tend to fail on higher- or lower-pollution days?), and what you’d need to know about why sensors go offline before you could trust the mean of what’s left. This is the difference between “missing at random” and “missing for a reason”, and it’s why offline-as-zero is only the first mistake.

10. Ship it

Put this in a repo README so it counts as a portfolio piece: one sentence on the move (“the month was trapped in the column headers; melting it into a column turned two fiddly questions into one line each”), the before/after shapes (5 x 13 wide to 60 x 3 tidy), and the two answers. Add a short note on Part B showing you transferred the same reshape to a different feed and handled the two wrinkles it added — two identifier columns and an "offline" sentinel that had to be read as missing, not zero. Recognising the same reshape under a messier 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.

▶ Run Part A in your browser

▶ 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.

Next up

Finished this one? Continue the Data Engineering track:

CSV to Parquet: store a log columnar and read only the bytes you need →  ·  Browse all courses