All lessons

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.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

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.

melt turns a wide table (one column per month) into a long one (a month column and a value column), the shape pandas groups, filters and plots easily.

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.

Python

3 stores × 3 months = 9 rows. id_vars are kept on every row; every other column is “melted”.

2. Then analyse

Python

3. Headers that hold two things

When column names combine two facts (sales_2025, sales_2026, units_2025…), melt, then split the name:

Python

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:

Wide.
storeJanFebMar
S01346
S02521
Choose. df.melt(id_vars='store', var_name='month', value_name='qty')
Each cell becomes a row.
storemonthqty
S01Jan3
S02Jan5
S01Feb4
S02Feb2
S01Mar6
S02Mar1
Count. 2 rows × 3 months = 6 rows.
Now analyse. long.groupby('month')['qty'].mean(): one line, any number of months.

4. Your turn

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

  1. A table has product and one column per store. Melt it to product, store, units.
  2. Why does groupby('month') fail on the wide table?
  3. Which method undoes a melt?
Answers
  1. df.melt(id_vars='product', var_name='store', value_name='units').
  2. There is no month column; months are column names.
  3. pivot or pivot_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

1A sheet has columns store, Jan, Feb, Mar. What does melt(id_vars="store") give?
2Why melt before plotting or grouping by month?
3Which options name the new columns?

Sources

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.