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

By

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.

Key terms

Related