Module 6: Reshaping · Lesson 6.1 · about 20 min
Pivot tables
Build Excel-style pivot tables in pandas with pivot_table: rows, columns, values and an aggregation, plus totals, several measures, missing cells filled with zero, and percentages of a row or column.
Module 6 · about 20 minutes · uses Lesson 4.2. You’ll be able to build pivot tables like the ones in Excel, in one line.
In everyday terms: a school timetable: days across the top, periods down the side, and each cell answers one question (which class?). A pivot answers “how much?” in each cell.
1. Rows × columns × values
Excel’s pivot fields map one to one: Rows → index, Columns → columns, Values → values, Summarise by → aggfunc.
2. Totals, zeros and several measures
Several measures at once: values=['revenue', 'qty'], aggfunc={'revenue': 'sum', 'qty': 'mean'}. The result has two-level column names (Lesson 6.4).
3. Percentages
div(..., axis=0) divides each row by its own total: row percentages. Each store’s busiest day stands out.
- index=
- The labels down the side (rows).
- columns=
- The labels across the top.
- values=, aggfunc=
- What goes in each cell and how it is combined (sum, mean, count).
- fill_value=0, margins=True
- Zero for empty cells; an All row and column of totals.
- Physical meaning
- Each cell is the total of the rows that share that row label and column label. Add up a row of cells and you get that label's total; the grand total equals the plain sum of the column, so the pivot only rearranges, never invents.
“An empty cell means zero.” By default it is NaN: no rows had that pair. Fill with 0 only when “no rows” really means “nothing sold”, and remember the mean of an empty cell is not 0.
Watch it happen, one step at a time:
| store | month | qty |
|---|---|---|
| S01 | Jan | 3 |
| S01 | Feb | 4 |
| S02 | Jan | 5 |
| S01 | Jan | 2 |
index='store', columns='month', values='qty', aggfunc='sum'| store | Feb | Jan |
|---|---|---|
| S01 | 4 | 5 |
| S02 | 0 | 5 |
4. Your turn
How it works
Principle: a pivot table is a groupby on two keys followed by an unstack: the second key moves from rows into columns. These give the same numbers:
Pivots are for reading (reports, comparisons across a row). For further analysis, the long shape is usually easier (Lesson 6.5).
5. Practice
- Average discount by category (rows) and store (columns).
- Count of distinct orders by store and year.
- How do you put the weekday columns in calendar order?
Answers
pivot_table(index='category', columns='store_id', values='discount', aggfunc='mean').pivot_table(index='store_id', columns='year', values='order_id', aggfunc='nunique').- Select them in order,
pt[['Mon', 'Tue', ...]], or make weekday an ordered category before pivoting.
New words: pivot_table, index / columns / values, aggfunc, margins, fill_value, unstack.
- pivot_table(index, columns, values, aggfunc) is a two-way groupby as a grid.
- fill_value=0 for empty cells only when that is the truth.
- margins=True adds totals; they should match a plain sum.
- Several values or aggfuncs give a two-level column header.
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.