Summing with pivot tables

Summing values in pivot tables is one of the most common and useful applications of pivot_table() in Pandas. By setting aggfunc='sum' (or simply aggfunc='sum'), you can quickly create cross-tabulations that show totals — such as total sales by region and month, total revenue by product and customer, or total hours by department and week.

In 2026, summing in pivot tables remains essential for financial reporting, sales dashboards, inventory summaries, and any time you need aggregated totals across dimensions. Here’s a practical guide with real examples you can copy and adapt.

1. Basic Setup & Sample Data


import pandas as pd

data = {
    'Region': ['North', 'North', 'South', 'South', 'West', 'West'],
    'Month': ['Jan', 'Feb', 'Jan', 'Feb', 'Jan', 'Feb'],
    'Salesperson': ['Alice', 'Bob', 'Charlie', 'Dave', 'Eve', 'Frank'],
    'Sales': [100, 200, 150, 50, 75, 125],
    'Profit': [20, 50, 30, 10, 15, 25]
}

df = pd.DataFrame(data)
print(df)

2. Simple Sum Pivot Table: Total Sales by Region and Month

The most common use — sum one metric across two dimensions.


pivot_sum = pd.pivot_table(
    df,
    values='Sales',
    index='Region',
    columns='Month',
    aggfunc='sum',          # explicit sum (default is mean, so always specify!)
    fill_value=0,           # replace missing combinations with 0
    margins=True            # add grand totals row/column
)

print(pivot_sum)

Output (clean totals, no NaN):


Month     Feb  Jan  All
Region                  
North     200  100  300
South      50  150  200
West      125   75  200
All       375  325  700

3. Summing Multiple Columns with Different Statistics

Sum sales but take mean profit — use a dictionary in aggfunc.


pivot_multi = pd.pivot_table(
    df,
    values=['Sales', 'Profit'],
    index='Region',
    columns='Month',
    aggfunc={'Sales': 'sum', 'Profit': 'mean'},
    fill_value=0,
    margins=True
)

print(pivot_multi)

4. Real-World Example: Total Sales by Region and Salesperson

Classic sales report — total sales broken down by region and person.


pivot_sales = pd.pivot_table(
    df,
    values='Sales',
    index='Region',
    columns='Salesperson',
    aggfunc='sum',
    fill_value=0,
    margins=True
)

print(pivot_sales)

Output (row/column totals included):


Salesperson  Alice  Bob  Charlie  Dave  Eve  Frank  All
Region                                                  
North          100  200        0     0    0      0  300
South            0    0      150    50    0      0  200
West             0    0        0     0   75    125  200
All            100  200      150    50   75    125  700

5. Modern Alternative in 2026: Polars

For large datasets, Polars is often faster and more memory-efficient with a similar pivot API.


import polars as pl

df_pl = pl.DataFrame(data)
pivot_pl = df_pl.pivot(
    index="Region",
    columns="Month",
    values="Sales",
    aggregate_function="sum"
).fill_null(0)
print(pivot_pl)

Best Practices & Common Pitfalls

Always specify aggfunc='sum' — default is 'mean', which surprises many users
Use fill_value=0 for clean tables — especially with sales, counts, or money
Add margins=True for automatic grand totals — very useful in reports
Reset index after pivot if you need Region/Month as regular columns: .reset_index()
For huge data, prefer Polars or chunked processing
Visualize: pivot_sales.plot(kind='bar', stacked=True) for instant insights

Conclusion

Summing in pivot tables with aggfunc='sum' is the fastest way to create clean total-based cross-tabs — total sales by region and month, revenue by product and quarter, etc. In 2026, use Pandas for readability and flexibility on small-to-medium data, and Polars for speed on large datasets. Master values, index, columns, aggfunc, fill_value, and margins, and you'll build professional, accurate summary reports in minutes.

Next time you need totals across categories — reach for pivot_table with aggfunc='sum' first.