Pandas Pivot Tables
last modified November 17, 2026 Python 3.15
A pivot table is a summary table that regroups data by two or more
categories: one category labels the rows, another labels the columns, and the
cells hold a computed value such as a total or an average. The idea is familiar
from spreadsheet programs, and Pandas offers it through the
pivot_table method. It is especially useful when the original data
is a long list of observations that we want to view as a compact cross-tabulation.
The examples were run with pandas 3.1.1. Each one builds a small DataFrame so it can be copied and run on its own.
The default: mean aggregation
The four main arguments of pivot_table are index, the
column whose distinct values become the row labels; columns, the
column whose values become the column labels; values, the column to
summarise; and aggfunc, the function used to summarise it. When
aggfunc is left out, Pandas uses the mean, the arithmetic
average, as the default.
import pandas as pd
df = pd.DataFrame({
'city': ['Bratislava', 'Bratislava', 'Bratislava', 'Bratislava',
'Prague', 'Prague', 'Prague', 'Prague'],
'product': ['A', 'A', 'B', 'B', 'A', 'A', 'B', 'B'],
'sales': [100, 200, 150, 250, 300, 400, 350, 450]
})
pt = df.pivot_table(index='city', columns='product', values='sales')
print(pt)
Each city and product combination appears twice in the data. For Bratislava and product A the two sales figures are 100 and 200, so the cell holds their average, 150. The other cells are averaged in the same way, which is why the result contains decimal values.
$ python pivot_default.py product A B city Bratislava 150.0 200.0 Prague 350.0 400.0
Choosing an aggregation with aggfunc
To get totals instead of averages we pass aggfunc='sum'. The
function can be given as a string holding its name or as a real Python
function; common choices include 'sum', 'mean',
'count' and 'max'. Every cell is then computed by
applying that function to all the rows that share its row and column labels.
import pandas as pd
df = pd.DataFrame({
'city': ['Bratislava', 'Bratislava', 'Bratislava', 'Bratislava',
'Prague', 'Prague', 'Prague', 'Prague'],
'product': ['A', 'A', 'B', 'B', 'A', 'A', 'B', 'B'],
'sales': [100, 200, 150, 250, 300, 400, 350, 450]
})
pt = df.pivot_table(index='city', columns='product', values='sales', aggfunc='sum')
print(pt)
Now the same data produces totals rather than averages: Bratislava A is
100 + 200 = 300, and Prague B is 350 + 450 = 800. Comparing this table with
the previous one shows that aggfunc is the only thing that
changed.
$ python pivot_aggfunc.py product A B city Bratislava 300 400 Prague 700 800
Filling missing cells with fill_value
When no row exists for a particular row and column combination, Pandas leaves
the cell empty, shown as NaN, the marker for a missing value. The
fill_value argument replaces those gaps with a chosen value. This
is common with counts and sums, where a missing combination really means zero.
import pandas as pd
df = pd.DataFrame({
'city': ['Bratislava', 'Bratislava', 'Prague'],
'product': ['A', 'B', 'A'],
'sales': [100, 200, 300]
})
pt = df.pivot_table(index='city', columns='product', values='sales',
aggfunc='sum', fill_value=0)
print(pt)
There is no record for Prague and product B, so that cell would be
NaN without fill_value. Setting it to 0 records the
fact that nothing was sold for that combination, and also keeps the numbers in
the table nicely typed as integers.
$ python pivot_fill.py product A B city Bratislava 100 200 Prague 300 0
Adding totals with margins
Setting margins=True adds an extra row and an extra column labelled
All. These hold the totals computed across each row and each
column of the pivot table, plus a grand total in the bottom-right corner. The
extra totals are called margins.
import pandas as pd
df = pd.DataFrame({
'city': ['Bratislava', 'Bratislava', 'Prague', 'Prague'],
'product': ['A', 'B', 'A', 'B'],
'sales': [100, 200, 300, 400]
})
pt = df.pivot_table(index='city', columns='product', values='sales',
aggfunc='sum', margins=True)
print(pt)
The All column gives 300 for Bratislava and 700 for Prague, while
the All row gives 400 for product A and 600 for product B. The
bottom-right value, 1000, is the sum of every sale. Note that margins apply the
chosen aggregation function, so with the default mean they would be averages
instead of totals.
$ python pivot_margins.py product A B All city Bratislava 100 200 300 Prague 300 400 700 All 400 600 1000
Pivot tables with several values
The values argument can be a list of column names. Pandas then
computes the aggregation for each of them and groups the resulting columns
under a higher-level header. This makes it possible to compare two measures,
such as sales and quantity sold, in a single table.
import pandas as pd
df = pd.DataFrame({
'city': ['Bratislava', 'Bratislava', 'Prague', 'Prague'],
'product': ['A', 'B', 'A', 'B'],
'sales': [100, 200, 300, 400],
'quantity': [2, 4, 6, 8]
})
pt = df.pivot_table(index='city', columns='product',
values=['sales', 'quantity'], aggfunc='sum')
print(pt)
The table has a two-level column header: the top level names the measure,
quantity or sales, and the level below names the
product. Each city therefore has four numbers, one for each combination of
measure and product.
$ python pivot_multi_values.py
quantity sales
product A B A B
city
Bratislava 2 4 100 200
Prague 6 8 300 400
Sorting the result
A pivot table is an ordinary DataFrame, so all the usual DataFrame operations
work on it. In particular sort_values orders the rows by one of
the columns, which is handy for ranking the categories. Passing
ascending=False puts the largest value first.
import pandas as pd
df = pd.DataFrame({
'city': ['Bratislava', 'Prague', 'Vienna'],
'product': ['A', 'B', 'A'],
'sales': [100, 300, 200]
})
pt = df.pivot_table(index='city', columns='product', values='sales',
aggfunc='sum', fill_value=0)
print(pt.sort_values('A', ascending=False))
The rows are now ordered by the A column from largest to smallest:
Vienna with 200, Bratislava with 100 and Prague with 0. To sort by the row
labels instead we could call sort_index on the result.
$ python pivot_sort.py product A B city Vienna 200 0 Bratislava 100 0 Prague 0 300
Pandas pivot tables and Excel
The idea is the same in Pandas and in Excel, but the workflow differs. In Excel a pivot table is usually built by dragging fields into boxes with the mouse, and the resulting table is a live object tied to a source range. In Pandas it is created by a single method call whose arguments describe the layout, and the result is just another DataFrame that we can keep processing in code.
Another difference is that Excel chooses a default aggregation based on the data type, counting text and summing numbers, while Pandas always uses the mean unless we say otherwise. Because the Pandas result is an ordinary DataFrame, it can be sorted, filtered and combined with other tables just like any other DataFrame — something that is more awkward with a spreadsheet pivot table.
Source
Pandas DataFrame.pivot_table Documentation, Pandas Reshaping Documentation, and Pandas DataFrame.sort_values Documentation
In this article we have built pivot tables with index,
columns, values and aggfunc, seen that the
default aggregation is the mean, filled empty cells with fill_value,
added totals with margins, worked with several values and sorted the
result. For related ways of reorganising data see
Pandas reshaping: melt and pivot and the
grouping data tutorial.
Author
List all Pandas tutorials.