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.
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.
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
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:
2. stack and unstack
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:
| store | qty | lines |
|---|---|---|
| S01 | 1 | 4 |
| S01 | 2 | 2 |
| S02 | 1 | 3 |
| store | 1 | 2 |
|---|---|---|
| S01 | 4 | 2 |
| S02 | 3 | NaN |
.unstack(fill_value=0) puts 0 where S02 had no qty-2 lines.3. Your turn
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
- When does
pivotfail? - Turn a
groupby(['category', 'month'])['revenue'].sum()into a category × month grid. - What does
stackdo to a store × year grid?
Answers
- When an (index, columns) pair appears more than once.
.unstack()(withfill_value=0if needed).- 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
Sources
- pandas documentation, User Guide: Reshaping and pivot tables https://pandas.pydata.org/docs/user_guide/reshaping.html
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
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.