Skip to content
Bamboo Weekly

pandas pivot_table

Turn one long column of values into a grid, summarized across two dimensions at once.

pivot_table answers questions with two "for each" clauses in them: total capacity for each region, broken down for each status. Where groupby gives you one row per group, pivot_table spreads a second grouping key across the columns, so you can read the comparison down and across.

Official documentation: DataFrame.pivot_table

The four arguments

df.pivot_table(index='Region',        # one row per unique value here
               columns='Status',      # one column per unique value here
               values='Capacity (MW)', # the numbers to summarize
               aggfunc='sum')          # how to summarize them

Three more that come up constantly:

df.pivot_table(index='Region', columns='Status', values='Capacity (MW)',
               aggfunc='sum',
               fill_value=0,      # show 0 rather than NaN for empty cells
               margins=True,      # add row and column totals
               observed=True)     # categoricals: only combinations that occur

observed defaults to True in Pandas 3.0, so this is now the behavior you get anyway — on older versions it was False, which produced a row for every defined category whether or not any data landed in it. That change is covered in Pandas 3's groupby defaults to observed=True? What does that mean?.

A worked example, on real data

The Global Coal Plant Tracker lists every coal-fired generating unit on earth, one per row, with the region it sits in and whether it is operating, retired, under construction, or merely announced. Bamboo Weekly #64 used this dataset.

The question: how much coal capacity sits in each region, at each stage of life?

import pandas as pd

url = ('https://www.bambooweekly.com/content/files/wp-content/uploads/2024/02/'
       'global-coal-plant-tracker-january-2024.xlsx')

(
    pd.read_excel(url, sheet_name='Units',
                  usecols=['Region', 'Status', 'Capacity (MW)'])
    .pivot_table(index='Region',
                 columns='Status',
                 values='Capacity (MW)',
                 aggfunc='sum')
)

The first few columns of the result:

Status    announced  cancelled  construction  mothballed
Region
Africa       7720.0    71550.0        2805.0      2270.0
Americas      800.0    54070.0         120.0       343.0
Asia        80754.0  1553922.0      193654.0      6133.0
Europe       3560.0   104669.0         735.0     14292.0

Read across a row and you see one region's whole pipeline; read down a column and you compare regions at the same stage. Asia's cancelled capacity alone is larger than every other region's combined — a fact that is invisible in the raw 13,906-row table and awkward to extract with groupby alone.

Three mistakes people make

The default aggfunc is mean, not sum. This is the single most common surprise. If you omit aggfunc, you get averages — which for a question about totals is quietly, plausibly wrong. Always state it.

Reaching for pivot when you want pivot_table. They are different methods. pivot merely reshapes and raises ValueError if any index/column pair occurs more than once, because it has no way to combine them. pivot_table aggregates, so duplicates are exactly what it expects. If pivot is complaining about duplicate entries, you wanted pivot_table.

Reading NaN as zero. An empty cell means no rows matched that combination, which is not the same as a measured zero. If the distinction does not matter for your question, pass fill_value=0 deliberately; if it does, leave the NaN visible so you can see where the data is absent.

pivot_table or groupby?

They overlap, and either can answer many questions. The rule of thumb: if you want the second grouping key spread across columns so you can compare visually, use pivot_table. If you want a tidy result to feed into more Pandas operations, use groupby — a long result chains better than a wide one.

I make the case that they are two views of one idea in Grouping and pivot tables are basically the same thing.

Practice it

Work through a pivot_table exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/pivot-table/

Go deeper

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 73 Bamboo Weekly exercises that use pivot_table on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.