# 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 November 19, 2026

By Gregor Dzierzon

## 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:

```python
import pandas as pd

orders = pd.read_csv("orders.csv")
orders.pivot_table(index="neighborhood", columns="size", values="total", aggfunc="sum")
```

```text
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.

```python
orders.pivot_table(
    index="neighborhood", columns="size", values="total",
    aggfunc="sum", fill_value=0, margins=True,
).round(2)
```

```text
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:

```python
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)
```

```text
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:

```python
pd.crosstab(orders["neighborhood"], orders["size"])
```

```text
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:

```python
row_pct = pd.crosstab(orders["neighborhood"], orders["size"], normalize="index") * 100
row_pct.round(1)
```

```text
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

- [Pivot Table](https://www.ask-codey.com/glossary/pivot-table)
- [pivot_table()](https://www.ask-codey.com/glossary/pivot-table-python)
- [crosstab()](https://www.ask-codey.com/glossary/crosstab)

## Related

- [Several Summaries at Once: agg](https://www.ask-codey.com/lessons/pandas-groupby-agg)
- [Shares and Percentages of a Total](https://www.ask-codey.com/lessons/pandas-shares-of-a-total)
- [Totals by Week and Month: resample](https://www.ask-codey.com/lessons/pandas-resample)

Source: https://www.ask-codey.com/lessons/pandas-pivot-table
