All lessons

Module 6: Reshaping · Lesson 6.3 · about 15 min

Long to wide with pivot, stack and unstack

Reshape long tables into wide ones with pivot (no aggregation) and move index levels between rows and columns with stack and unstack; know when pivot fails and pivot_table is needed.

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

Module 6 · about 15 minutes · uses Lessons 6.1 and 6.2. You’ll be able to move data between long and wide shapes in both directions.

pivot spreads a long table back into a grid without adding anything up; unstack moves an index level into the columns, and stack moves it back.

In everyday terms: folding a long receipt into a card: the same lines, laid out in a grid. Unfold it again and nothing is lost.

1. pivot: rearrange without combining

Python

pivot only moves values; each (index, column) pair must appear once. If a pair repeats, it raises an error, and you need pivot_table with an aggfunc to decide how to combine them:

Python

2. stack and unstack

Pythonorders.csv

unstack(level=0) moves the outer level instead; unstack(fill_value=0) fills gaps.

df.pivot(index, columns, values)
Reshapes; fails if a row/column pair appears twice.
pivot_table
Use instead when pairs repeat: it aggregates them.
unstack()
Moves the innermost row label into the columns.
stack()
Moves the columns back into the innermost row label.
Physical meaning
pivot, stack and unstack only move cells around: the same numbers in a different arrangement. If two rows claim the same cell, pivot refuses, because putting both in one cell would mean adding or choosing, which is pivot_table's job.

“pivot and pivot_table are interchangeable.” pivot errors on duplicate pairs (“Index contains duplicate entries”) instead of summing. That error is useful: it tells you the data has a grain you did not expect.

Watch it happen, one step at a time:

A groupby result (two row labels).
storeqtylines
S0114
S0122
S0213
unstack('qty'). The qty labels move across the top.
Result.
store12
S0142
S023NaN
Fill. .unstack(fill_value=0) puts 0 where S02 had no qty-2 lines.
stack() goes back to three rows (the empty cell is dropped).

3. Your turn

Your turnorders.csv
How it works

Principle: a DataFrame is a grid indexed by row labels and column labels. stack moves the column labels into the innermost row index (wide → long); unstack moves the innermost row index into the column labels (long → wide). pivot is set_index([index, columns])[values].unstack(); melt is roughly set_index(ids).stack() with the names reset.

Want Use
long → wide, one value per cell pivot
long → wide, combining duplicates pivot_table
wide → long melt (or stack)
move an index level stack / unstack

4. Practice

  1. When does pivot fail?
  2. Turn a groupby(['category', 'month'])['revenue'].sum() into a category × month grid.
  3. What does stack do to a store × year grid?
Answers
  1. When an (index, columns) pair appears more than once.
  2. .unstack() (with fill_value=0 if needed).
  3. Makes it long again: a Series indexed by (store, year).

New words: pivot, stack, unstack, index level.

  • pivot reshapes long to wide; it never aggregates.
  • Duplicate pairs: use pivot_table (or fix the grain).
  • unstack moves a row level to columns; stack moves it back.
  • fill_value=0 fills cells with no rows, when zero is true.

Check yourself

1pivot raises "Index contains duplicate entries". Why?
2What does unstack() do to a Series with a two-level index (store, year)?
3After a groupby on two keys, which turns the result into a store × year grid?

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.