Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 64 ยท Grouping and Reshaping

Pivot Tables and Crosstabs

Summarise a DataFrame as a grid with pivot_table and crosstab. Learn aggfunc, margins, fill_value, normalize, and when pivot or unstack is the better tool.

A long list of records is easy to store but hard to read. If you have one row per sale, you cannot see at a glance which region sold the most in which quarter. A pivot table fixes that: it takes the values of one column and turns them into column headers, takes the values of another column and uses them as the row labels, and fills the grid with a summary of a third column.

pandas gives you several tools for this shape of work: pivot_table() (group, summarise and spread in one call), pd.crosstab() (a specialised version that counts or normalises), pivot() (a pure reshape with no summarising), and unstack() (move an index level into the columns). This lesson shows what each one does and how to pick between them. It builds on groupby and agg, transform and filter, because a pivot table is a groupby whose result is laid out as a grid.

The data

Every example uses the same nine sales records. Each row is one product sold in one region in one quarter.

flowchart LR
    A["Long table<br/>one row per sale"] --> B["group by<br/>region + quarter"]
    B --> C["aggregate<br/>sum of revenue"]
    C --> D["spread quarter<br/>into columns"]
    D --> E["Pivot table<br/>regions x quarters"]

A pivot table is a groupby followed by moving one of the group keys from the rows into the columns.

pivot_table: group, summarise, spread

The core call names three things: index (what becomes the row labels), columns (what becomes the column headers), and values (what gets summarised). The summary function is aggfunc, which defaults to "mean".

Python Revenue by region and quarter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the first table North sold 30,000 in Q1, which is the sum of two rows (12,000 for laptops and 18,000 for phones). In the second table nothing was specified for aggfunc, so each cell is a mean: North's laptop cell is the mean of 12,000 and 14,400, which is 13,200. Because a mean of integers is generally a float, that table prints decimals. Always pass aggfunc explicitly when you mean "total" so a reader of your code knows what the numbers are.

Note that pivot_table always summarises. If two rows land in the same cell, it combines them with aggfunc; it never fails because of duplicates. That is the biggest difference from pivot(), which you will meet below.

Totals with margins

Add margins=True and pandas appends a total row and a total column. The label defaults to "All"; change it with margins_name.

Margins are calculated from the original rows, not from the cells shown. For a sum or a count that makes no difference, but for a mean the grand total is the mean of all rows, not the mean of the cell means.

Missing cells and fill_value

If a combination never occurs in the data, its cell is empty (NaN). With three dimensions, you quickly get gaps. Pass fill_value to replace them. Choose the value carefully: 0 is right for sums and counts, but for means a zero would claim the average was zero, when it really was unknown.

Python Margins, fill_value and several aggregations

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The third table shows what happens with several aggregations: the column labels become a MultiIndex with three levels (the value column, the function, the product). You can select from it with the techniques in MultiIndex. Often you want a flat table instead; build single-level names from the tuples:

Python
flat = summary.copy()
flat.columns = ["_".join(parts) for parts in flat.columns]
flat = flat.reset_index()      # region becomes a normal column again

Flat names such as revenue_sum_Laptop are easier to export and to join later.

Useful options

Option What it does
aggfunc A name ("sum"), a function, a list of them, or a dict mapping each value column to its own function(s)
fill_value Replaces empty cells in the result
margins, margins_name Adds totals and names them
dropna With True (the default), drops columns whose entries are all NaN
observed For categorical keys: show only the combinations that occur
sort Sorts the result by the row and column labels (on by default)

You can also use more than one column in index: index=["region", "product"] gives a MultiIndex on the rows. Leaving columns out gives a one-dimensional summary, which is just a groupby in a different shape.

crosstab: counting how often combinations occur

pd.crosstab(a, b) builds a frequency table of two (or more) columns. The inputs are Series or arrays, not a DataFrame plus column names, which is the main syntactic difference from pivot_table. With no values, it counts rows. With values and aggfunc, it behaves like pivot_table.

The feature that makes crosstab special is normalize. It turns counts into proportions:

  • normalize="index" makes each row sum to 1 (row percentages).
  • normalize="columns" makes each column sum to 1.
  • normalize="all" makes the whole table sum to 1.
Python Counting and normalising with crosstab

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the second table row by row: North sold laptops in two of its three records, so the laptop share is 0.67. Read the third table column by column: of the five laptop records, North has 0.40 (two of five). Mixing up the two directions is the most common mistake with normalize. Say it in words before you choose: "within each row" is "index", "within each column" is "columns".

If your data is already in a DataFrame, pivot_table and crosstab are interchangeable for most jobs. Use pivot_table for numeric summaries and crosstab when you mainly want counts or percentages, because normalize is built in.

pivot versus pivot_table

DataFrame.pivot() only reshapes. It takes the existing values and moves them into place, without any grouping or arithmetic. Because it never combines values, every (index, columns) pair must appear at most once. If a pair appears twice, there is no single value for that cell and pandas raises a ValueError.

Python pivot fails on duplicates, unstack on a groupby works

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the laptop table, South has no Q2 laptop record, so that cell is NaN and the numbers become floats. pivot leaves such cells empty because it has no fill_value option; call .fillna(0) on the result if you need to.

unstack: the index-level version

unstack() moves one level of a row MultiIndex into the columns. Calling .groupby([...]).sum().unstack() is exactly what pivot_table does internally. Pass the level name (unstack("quarter")) rather than a number so the code keeps working if the order of the keys changes. unstack(fill_value=0) fills the gaps, and the opposite operation, stack(), folds columns back into an index level. The next lesson, Reshaping, covers stack and the rest of the long/wide toolkit.

You want to... Use
Summarise and reshape in one step pivot_table
Count or normalise combinations crosstab
Reshape unique pairs, no arithmetic pivot
Reshape a result that already has a MultiIndex unstack

Choosing the aggregation honestly

A pivot table can hide how much data stands behind each cell. A mean over one row looks as solid as a mean over a thousand. A habit that helps: build the same pivot with aggfunc="count" next to the one you show, or add margins to see the totals. Also check that the numbers you add make sense: a sum of prices is meaningless, while a sum of revenue is not.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does pivot_table do when two rows fall into the same cell?

    pivot_table groups first, so duplicate (index, columns) pairs are summarised with aggfunc. The default is the mean. Only pivot() raises an error on duplicates.
  2. 2. Which call gives each ROW of a crosstab a total of 1?

    normalize="index" divides each row by its own sum. "columns" does the same for columns and "all" divides by the grand total. margins only adds totals, it does not normalize.
  3. 3. A pivot table of sums shows NaN where a combination never occurred. What is the cleanest fix?

    fill_value replaces empty cells in the result. Zero is the right choice for sums and counts. pivot has no such option.
  4. 4. Which statement about unstack is correct?

    unstack turns one level of a row MultiIndex into column labels. It is what groupby(...).sum().unstack(...) does to build a pivot table by hand.

Summary

  • pivot_table(index, columns, values, aggfunc) groups, summarises and lays out the result as a grid. The default aggfunc is the mean, so set it explicitly.
  • margins=True adds totals (named with margins_name), and fill_value fills empty cells.
  • A dict for aggfunc applies a different function to each value column; several functions create MultiIndex columns that you can flatten.
  • pd.crosstab counts combinations and normalize ("index", "columns", "all") converts counts to proportions.
  • pivot only reshapes and fails when (index, columns) pairs repeat; unstack moves an index level into the columns.

Next: Reshaping: melt, stack, wide to long