Module 6: Reshaping · Lesson 6.2 · about 15 min
Wide to long with melt
Turn wide tables (one column per month, per year or per question) into long, tidy tables with melt: id columns stay, the other column names become values in one column, and their numbers go in another.
Module 6 · about 15 minutes · uses Lesson 6.1. You’ll be able to turn spreadsheet-style wide tables into long ones you can analyse.
In everyday terms: unpicking a calendar grid into a diary: instead of one box per day across the page, you write one line per day: “Monday, 3 visits”.
1. The wide table problem
Budgets and exports often arrive wide: one column per month. Easy to read, hard to analyse: “total for Q1”, “months above target”, “trend per store” all need month as a column.
3 stores × 3 months = 9 rows. id_vars are kept on every row; every other column is “melted”.
2. Then analyse
3. Headers that hold two things
When column names combine two facts (sales_2025, sales_2026, units_2025…), melt, then split the name:
That’s a melt followed by a pivot: the measures end up as columns and the years as rows, the most useful shape here.
- id_vars=
- Columns that stay as they are (they identify the row).
- value_vars=
- Columns to unpivot (default: all the others).
- var_name=, value_name=
- Names for the new "which column" and "the value" columns.
- Rows after melt
- rows × number of melted columns.
- Physical meaning
- No number changes: each cell of the wide table becomes one row of the long table, carrying its row label and its column name with it. A 3 × 4 grid of values becomes 12 rows.
“Wide is tidier because it is shorter.” Short for eyes, awkward for code: to average across months in wide form you must name every month column. Long form needs one groupby.
Watch it happen, one step at a time:
| store | Jan | Feb | Mar |
|---|---|---|---|
| S01 | 3 | 4 | 6 |
| S02 | 5 | 2 | 1 |
df.melt(id_vars='store', var_name='month', value_name='qty')| store | month | qty |
|---|---|---|
| S01 | Jan | 3 |
| S02 | Jan | 5 |
| S01 | Feb | 4 |
| S02 | Feb | 2 |
| S01 | Mar | 6 |
| S02 | Mar | 1 |
long.groupby('month')['qty'].mean(): one line, any number of months.4. Your turn
How it works
Principle: melt is the reverse of pivot. A wide table with k measured columns and n rows becomes a long table with n × k rows: each (row, column) cell becomes a row of its own, keeping the id columns.
Excel: Power Query calls this “Unpivot columns”. SQL: UNPIVOT, or a UNION ALL of one select per column.
5. Practice
- A table has
productand one column per store. Melt it toproduct, store, units. - Why does
groupby('month')fail on the wide table? - Which method undoes a melt?
Answers
df.melt(id_vars='product', var_name='store', value_name='units').- There is no month column; months are column names.
pivotorpivot_table(next lesson), with the melted names as columns.
New words: wide, long, melt, id_vars, var_name, value_name, unpivot.
- melt: id_vars stay, other columns become name/value pairs.
- Rows after melt = rows × melted columns.
- Name the new columns with var_name and value_name.
- Long form is the shape for groupby, filters and charts.
Check yourself
Sources
- pandas documentation, User Guide: Reshaping and pivot tables https://pandas.pydata.org/docs/user_guide/reshaping.html
- Wickham, H. (2014), Tidy Data, Journal of Statistical Software 59(10) https://doi.org/10.18637/jss.v059.i10
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.