ZetCode

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.

pivot_default.py
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.

pivot_aggfunc.py
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.

pivot_fill.py
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.

pivot_margins.py
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.

pivot_multi_values.py
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.

pivot_sort.py
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

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Pandas tutorials.