If you’ve built a pandas pivot table on sales data, you’ve probably seen $3,773.75 where you expected a regional total. That happens because pivot_table() averages by default; pass aggfunc="sum" and you get totals.
In this tutorial, I’ll walk through five pandas pivot table examples on one small sales dataset, from a one-line summary to grand totals, months in the right order and a chart.
Everything below ran on Python 3.12.5 and pandas 3.0.6.
The sample sales data for these pandas pivot table examples
Every example imports q1_orders.py: twelve Q1 2026 orders across three US regions, six reps and three products, in USD.
import pandas as pd
pd.options.display.float_format = "{:,.2f}".format
orders = pd.DataFrame({
"Month": ["Jan", "Jan", "Jan", "Jan", "Feb", "Feb",
"Feb", "Feb", "Mar", "Mar", "Mar", "Mar"],
"Region": ["East", "East", "Midwest", "West", "East", "Midwest",
"Midwest", "West", "East", "Midwest", "West", "West"],
"Rep": ["Emily Carter", "James Wilson", "Olivia Brown", "Liam Davis",
"Emily Carter", "Noah Garcia", "Olivia Brown", "Ava Miller",
"James Wilson", "Noah Garcia", "Liam Davis", "Ava Miller"],
"Product": ["Laptop", "Monitor", "Laptop", "Monitor", "Headset",
"Monitor", "Headset", "Laptop", "Laptop", "Laptop",
"Laptop", "Monitor"],
"Units": [4, 6, 3, 5, 10, 4, 8, 2, 5, 2, 3, 7],
"Revenue": [5196.00, 1914.00, 3897.00, 1595.00, 1490.00, 1276.00,
1192.00, 2598.00, 6495.00, 2598.00, 3897.00, 2233.00],
})Two details matter later: West never sold a headset, and East sold laptops twice (the rows total $34,381.00). Building your own? See converting a list to a pandas DataFrame.
Method 1: Total one column per group with pivot_table() (Recommended)
Use this when you want one number per group. The first call leaves aggfunc out; the second asks for a sum:
from q1_orders import orders
avg = orders.pivot_table(index="Region", values="Revenue")
total = orders.pivot_table(index="Region", values="Revenue",
aggfunc="sum")
print(avg, total, sep="\n\n")Output:
Revenue
Region
East 3,773.75
Midwest 2,240.75
West 2,580.75
Revenue
Region
East 15,095.00
Midwest 8,963.00
West 10,323.00East shows $3,773.75 on average per order, but $15,095.00 in total.
How does this code work?
index makes one row per region, values picks the column, and aggfunc decides how to combine it. The default is "mean", so for money you’ll want "sum". Under the hood it’s a grouped aggregation, the same idea as groupby in Python pandas.
Method 2: Build a two-way pandas pivot table with columns and fill_value
Here you get regions down and products across. Same orders, plus columns="Product":
from q1_orders import orders
by_product = orders.pivot_table(index="Region", columns="Product",
values="Revenue", aggfunc="sum")
print(by_product, "\n")
filled = orders.pivot_table(index="Region", columns="Product",
values="Revenue", aggfunc="sum",
fill_value=0)
print(filled)Output:
Product Headset Laptop Monitor
Region
East 1,490.00 11,691.00 1,914.00
Midwest 1,192.00 6,495.00 1,276.00
West NaN 6,495.00 3,828.00
Product Headset Laptop Monitor
Region
East 1,490.00 11,691.00 1,914.00
Midwest 1,192.00 6,495.00 1,276.00
West 0.00 6,495.00 3,828.00
How does this code work?
Each product becomes a column, and each cell sums the matching orders. East’s two laptop orders became one $11,691.00 cell.
West has no headset orders, so you get NaN until fill_value=0 fills it. If you only need counts, a pandas crosstab is a shorter call.
Method 3: Use several aggregation functions in one pivot table
Want total revenue, the largest order and units in one table? Give aggfunc a dictionary:
from q1_orders import orders
stats = orders.pivot_table(
index="Region",
values=["Revenue", "Units"],
aggfunc={"Revenue": ["sum", "max"], "Units": "sum"},
)
print(stats, "\n")
stats.columns = ["_".join(col) for col in stats.columns]
print(stats.columns.tolist())Output:
Revenue Units
max sum sum
Region
East 6,495.00 15,095.00 25
Midwest 3,897.00 8,963.00 17
West 3,897.00 10,323.00 17
['Revenue_max', 'Revenue_sum', 'Units_sum']How does this code work?
Each column maps to one function or a list, which gives two-level labels like ('Revenue', 'sum').
Notice max came out before sum, not in my order. For named aggregations, use pandas groupby aggregation functions instead.
Method 4: Add grand totals with margins=True
Your manager will ask for totals. Same two-way table, now with margins=True:
from q1_orders import orders
report = orders.pivot_table(index="Region", columns="Product",
values="Revenue", aggfunc="sum",
fill_value=0, margins=True,
margins_name="Total")
print(report, "\n")
avg = orders.pivot_table(index="Region", columns="Product",
values="Revenue", aggfunc="mean",
margins=True)
print(avg)Output:
Product Headset Laptop Monitor Total
Region
East 1,490.00 11,691.00 1,914.00 15,095.00
Midwest 1,192.00 6,495.00 1,276.00 8,963.00
West 0.00 6,495.00 3,828.00 10,323.00
Total 2,682.00 24,681.00 7,018.00 34,381.00
Product Headset Laptop Monitor All
Region
East 1,490.00 5,845.50 1,914.00 3,773.75
Midwest 1,192.00 3,247.50 1,276.00 2,240.75
West NaN 3,247.50 1,914.00 2,580.75
All 1,341.00 4,113.50 1,754.50 2,865.08
How does this code work?
margins=True adds a row and a column, and margins_name renames them from All. The $34,381.00 corner matches the sample’s total.
Important: the margins use the same aggfunc as the table. With "mean", the All column is each region’s average order and the corner is $2,865.08, not a grand total.
To send it to a manager, write the DataFrame to Excel.
Method 5: Pivot on two index levels and keep months in order
Now rows by region and rep, with a column per month. Here’s the catch with text months:
import pandas as pd
from q1_orders import orders
rows = ["Region", "Rep"]
first = orders.pivot_table(index=rows, columns="Month",
values="Revenue", aggfunc="sum")
print(first.columns.tolist(), "\n")
orders["Month"] = pd.Categorical(orders["Month"], ordered=True,
categories=["Jan", "Feb", "Mar"])
by_month = orders.pivot_table(index=rows, columns="Month",
values="Revenue", aggfunc="sum",
fill_value=0)
print(by_month)Output:
['Feb', 'Jan', 'Mar']
Month Jan Feb Mar
Region Rep
East Emily Carter 5,196.00 1,490.00 0.00
James Wilson 1,914.00 0.00 6,495.00
Midwest Noah Garcia 0.00 1,276.00 2,598.00
Olivia Brown 3,897.00 1,192.00 0.00
West Ava Miller 0.00 2,598.00 2,233.00
Liam Davis 1,595.00 0.00 3,897.00
How does this code work?
A list in index builds a MultiIndex, so each rep sits under their region. pandas sorts text months alphabetically, so Feb lands before Jan.
An ordered pd.Categorical tells pandas the real order. In pandas 3, observed=True is the default, so categories with no rows are left out.
Call reset_index() when you need plain columns back; getting index values from a pandas DataFrame explains the index.
How do you sort, filter and chart a pandas pivot table?
A pivot table is a DataFrame, so you can sort it, filter it with query() and chart it:
import matplotlib.pyplot as plt
from q1_orders import orders
by_product = orders.pivot_table(index="Region", columns="Product",
values="Revenue", aggfunc="sum",
fill_value=0)
report = by_product.assign(Total=by_product.sum(axis=1))
report = report.sort_values("Total", ascending=False)
print(report, "\n")
print("Monitor over $1,500:", report.query("Monitor > 1500").index.tolist())
ax = report.drop(columns="Total").plot.bar(stacked=True, rot=0,
figsize=(7, 4.5))
ax.set_title("Q1 2026 revenue by region and product")
ax.set_ylabel("Revenue (USD)")
ax.yaxis.set_major_formatter("${x:,.0f}")
plt.tight_layout()
plt.show()Output:
Product Headset Laptop Monitor Total
Region
East 1,490.00 11,691.00 1,914.00 15,095.00
West 0.00 6,495.00 3,828.00 10,323.00
Midwest 1,192.00 6,495.00 1,276.00 8,963.00
Monitor over $1,500: ['East', 'West']
query() filters on a pivot column by name; for more complex rules see filtering pandas on multiple conditions. Each product column becomes one colored segment, the same layout as a stacked bar plot in pandas.
Pro Tip: drop the Total column before plotting, or it shows up as a fourth segment and doubles every bar.