All lessons

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.

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

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.

A pivot table is a groupby by two things at once, laid out as a grid: one set of labels down the side, another across the top, a summary in each cell.

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

Pythonorders.csv

Excel’s pivot fields map one to one: Rows → index, Columns → columns, Values → values, Summarise by → aggfunc.

2. Totals, zeros and several measures

Pythonorders.csv, products.csv

Several measures at once: values=['revenue', 'qty'], aggfunc={'revenue': 'sum', 'qty': 'mean'}. The result has two-level column names (Lesson 6.4).

3. Percentages

Pythonorders.csv

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:

Long data.
storemonthqty
S01Jan3
S01Feb4
S02Jan5
S01Jan2
Pick the grid. index='store', columns='month', values='qty', aggfunc='sum'
S01, Jan. Two rows: 3 + 2 = 5.
Result.
storeFebJan
S0145
S0205
S02 had no February rows; fill_value=0 shows 0.
Check. Cells add to 14 = the sum of qty.

4. Your turn

Your turnorders.csv
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:

Pythonorders.csv

Pivots are for reading (reports, comparisons across a row). For further analysis, the long shape is usually easier (Lesson 6.5).

5. Practice

  1. Average discount by category (rows) and store (columns).
  2. Count of distinct orders by store and year.
  3. How do you put the weekday columns in calendar order?
Answers
  1. pivot_table(index='category', columns='store_id', values='discount', aggfunc='mean').
  2. pivot_table(index='store_id', columns='year', values='order_id', aggfunc='nunique').
  3. 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

1In pivot_table(index='store_id', columns='year', values='revenue', aggfunc='sum'), what is in each cell?
2What does margins=True add?
3Some store-month cells have no sales and show NaN. How do you show 0?

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.