← All Kits · Python Kit

pivot_table in pandas: The Excel Pivot, Written Down

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

Everybody who has used Excel knows what a pivot table does. pivot_table is the same operation with the choices written down instead of dragged, which means it re-runs next month without anybody remembering where the fields went.

What you do: name four things. Rows, columns, the value, and how to aggregate it. The fourth one has a default you almost never want.

The short version. aggfunc defaults to mean. Pass it explicitly, every time.

The table

import pandas as pd

df = pd.DataFrame({
    'order_id': [101, 102, 103, 104, 105, 106, 107, 108],
    'region':   ['North','North','South','South','East','West','West','East'],
    'month':    ['Jan','Feb','Jan','Feb','Jan','Jan','Feb','Feb'],
    'amount':   [120, 80, 200, 50, 300, 90, 60, 150],
    'status':   ['shipped','shipped','cancelled','shipped',
                 'shipped','pending','shipped','cancelled'],
})

The four arguments

df.pivot_table(index='region', columns='month', values='amount',
               aggfunc='sum', fill_value=0, margins=True, margins_name='Total')

Four arguments do the work. index is the rows, columns is the columns, values is the number, aggfunc is what to do with it. fill_value replaces the NaN in combinations that never occurred, and margins adds the totals.

Note the column order: Feb before Jan, because pandas sorts the column values alphabetically and has no idea they are months. Fixing that is a separate step, below.

The fill value is a decision

A missing combination is not the same as a zero. If West genuinely had no February orders, fill_value=0 is honest. If February data has not loaded yet, a zero is a fabricated observation that will be charted as a real month with no sales.

When you are not sure, leave the NaN in. It is visible, it propagates through arithmetic, and it forces the question. That is the same argument as in fillna and dropna.

Several values, several aggregations

df.pivot_table(index='region', values=['amount', 'order_id'],
               aggfunc={'amount': 'sum', 'order_id': 'count'})

A dictionary lets each column have its own function, which is how you get a total and a count side by side. Passing a list such as aggfunc=['sum','mean'] applies every function to every value column and produces a multi-level column index, which is powerful and hard to read. Prefer the dictionary.

Fixing the column order

months = ['Jan','Feb','Mar','Apr','May','Jun',
          'Jul','Aug','Sep','Oct','Nov','Dec']
df['month'] = pd.Categorical(df['month'], categories=months, ordered=True)

df.pivot_table(index='region', columns='month', values='amount',
               aggfunc='sum', observed=True)

Making the column a Categorical with a defined order fixes the sorting everywhere in pandas, not only in this pivot: it also fixes groupby order and chart axis order. It is the right fix rather than reindexing the result afterwards.

Getting back to long

wide = df.pivot_table(index='region', columns='month',
                      values='amount', aggfunc='sum').reset_index()

long = wide.melt(id_vars='region', var_name='month', value_name='amount')

Wide is for reading, long is for computing. Charting libraries, databases and further groupbys all want long. Pivot at the last possible moment, immediately before a human looks at it.

pivot, pivot_table and groupby

pivot failing on duplicates is a feature. It means your key was not unique, which is the same finding as fan-out in a SQL join, and it is better to hear about it than to have it silently averaged.

How to apply this to your own work

1. Pass aggfunc explicitly in every pivot you write, even when the default happens to be right.
2. Check the grand total against the ungrouped sum before believing any pivot.
3. Decide consciously whether a blank cell is a zero or an unknown.
4. Set month and other ordered text columns to Categorical once, at load time.
5. Keep the data long through the pipeline and pivot only for display.

The one habit to keep

Reconcile the corner. The bottom right cell of a pivot with margins should equal the sum of the original column, and if it does not, something was filtered, dropped or averaged on the way.

Does the grand total of your last pivot match the raw column total?