Pivot Tables and crosstab
pivot_table turns a summary of two columns into a grid, one value down the side (index) and one across the top (columns), with the cells worked out from values by aggfunc; fill_value puts 0 in an empty cell, margins adds the totals, and crosstab counts every combination of two columns, as row percentages with normalize='index'.
Journeyman · Python · Updated
After this lesson you can
- Build a pivot table with pivot_table, using index, columns, values, aggfunc, fill_value and margins
- Count combinations of two columns with crosstab, and show each row as percentages with normalize='index'
Key takeaways
- pivot_table(index=, columns=, values=, aggfunc=) builds a grid, rows from one column, columns from another, and a summary in every cell.
- fill_value=0 puts 0 where a combination has no rows, and margins=True adds an All row and column with the totals.
- The columns come out in alphabetical order, and reindex(columns=[...]) puts them in the order you list.
- pd.crosstab counts how often each combination of two columns happens, and normalize="index" turns each row into shares that add up to 1.
Crust & Co. Pizza's owner wants one grid on the wall: the five neighborhoods down the side, the four pizza sizes across the top, and the sales in every square, with totals along the edges. If you've made a PivotTable in a spreadsheet, that's exactly it, and pandas makes the same grid from code. Reports, class assignments and job interviews all ask for tables like this one: sales by region and quarter, attendance by class and weekday, answers by question and group.
The data is the shop's delivery orders, orders.csv: 3,006 orders over 13 weeks, with each order's order_id, neighborhood, size (small, medium, large or party) and total in dollars, among other columns.
From a long list to a grid: pivot_table
Grouping by two columns, as Several Summaries at Once: agg does, answers the question as a long list of 19 rows, and comparing Elm Park's party sales with Hillside's means hunting down the list. A pivot table lays the same numbers out as a grid:
import pandas as pd
orders = pd.read_csv("orders.csv")
orders.pivot_table(index="neighborhood", columns="size", values="total", aggfunc="sum")
size large medium party small
neighborhood
Elm Park 6457.62 7355.85 3432.39 3137.74
Hillside 5278.00 5435.56 3474.36 3113.53
Lakeshore 4613.14 4277.95 2003.12 2200.63
Old Town 4543.30 4758.68 2371.77 2575.30
Westgate 3777.52 3694.71 NaN 2000.06
Each argument answers one question about the grid:
index="neighborhood": what goes down the side, one row for each value.columns="size": what goes across the top, one column for each value.values="total": which column fills the squares.aggfunc="sum": how to summarize the orders in each square ("aggregate function";"mean"and"count"work too).
Westgate's party square shows NaN ("not a number", pandas' mark for a missing value), because the shop doesn't deliver party pizzas to Westgate, so that square has no orders at all.
Filling the gaps and adding totals
No orders means $0 of sales, so 0 is the honest value for that square. fill_value=0 puts it there. margins=True adds the totals a spreadsheet would: an All column with each row's total and an All row with each column's total.
orders.pivot_table(
index="neighborhood", columns="size", values="total",
aggfunc="sum", fill_value=0, margins=True,
).round(2)
size large medium party small All
neighborhood
Elm Park 6457.62 7355.85 3432.39 3137.74 20383.60
Hillside 5278.00 5435.56 3474.36 3113.53 17301.45
Lakeshore 4613.14 4277.95 2003.12 2200.63 13094.84
Old Town 4543.30 4758.68 2371.77 2575.30 14249.05
Westgate 3777.52 3694.71 0.00 2000.06 9472.29
All 24669.58 25522.75 11281.64 13027.26 74501.23
The bottom-right corner is the grand total, $74,501.23. A filled 0 is honest here, because a missing square really means "nothing sold". When a missing square means "we don't know", leave it as NaN.
Counting, in the order you want
To count orders instead of adding dollars, use aggfunc="count" on a column every order has, such as order_id. The sizes come out in alphabetical order, which isn't how anyone thinks of them. reindex(columns=[...]) puts the columns in the order you list, All included:
sizes = ["small", "medium", "large", "party", "All"]
counts = orders.pivot_table(
index="neighborhood", columns="size", values="order_id",
aggfunc="count", fill_value=0, margins=True,
)
counts.reindex(columns=sizes)
size small medium large party All
neighborhood
Elm Park 158 321 252 86 817
Hillside 153 238 206 87 684
Lakeshore 109 186 178 51 524
Old Town 132 207 174 59 572
Westgate 102 162 145 0 409
All 654 1114 955 283 3006
reindex(index=[...]) does the same for the rows, such as the days of the week from Monday to Sunday.
Counting combinations: crosstab
Counting how often each pair of values happens together is common enough to have its own tool. pd.crosstab (short for cross tabulation, the statistics-class name for this table) takes two columns and counts every combination, with no values or aggfunc needed:
pd.crosstab(orders["neighborhood"], orders["size"])
size large medium party small
neighborhood
Elm Park 252 321 86 158
Hillside 206 238 87 153
Lakeshore 178 186 51 109
Old Town 174 207 59 132
Westgate 145 162 0 102
It fills empty squares with 0 by itself, and takes margins=True too. Its most useful extra is normalize="index": each row becomes shares of that row's total, adding up to 1, and times 100 makes them percentages:
row_pct = pd.crosstab(orders["neighborhood"], orders["size"], normalize="index") * 100
row_pct.round(1)
size large medium party small
neighborhood
Elm Park 30.8 39.3 10.5 19.3
Hillside 30.1 34.8 12.7 22.4
Lakeshore 34.0 35.5 9.7 20.8
Old Town 30.4 36.2 10.3 23.1
Westgate 35.5 39.6 0.0 24.9
Read each row on its own: of Hillside's orders, 12.7% are party pizzas. Row percentages compare neighborhoods of different sizes fairly, which raw counts can't.
Check yourself
2 questions to try on the lesson page.
So what
When a question has two parts, such as "by neighborhood and by size", a pivot table shows every answer at once in a grid people can read across and down, with honest zeros, totals on the edges and, through crosstab, percentages that compare fairly.