Aggregate Data with Pivot Tables

You’ve filtered and grouped your data, but now you need to summarize it across two dimensions — say, total sales by region and product category. Doing this with repeated groupby calls is verbose and error-prone. Pandas pivot tables give you a clean, spreadsheet-style summary in a single line of code, transforming raw rows into an instant cross-tabulation you can analyze, plot, or export. In this lesson, you’ll master the pivot_table method — the key to aggregating data with pivot tables like a pro.

The problem this lesson solves

When you only need one grouping level, groupby works fine. But real analysis often needs two or more dimensions at once: “How did each product perform in each region?” or “What’s the average score by department and month?”

Doing this manually means chaining multiple groupby calls and merging the results — leading to messy, duplicated code.

Pandas provides a better way: DataFrame.pivot_table(). It lets you:

• Aggregate values using a chosen function (sum, mean, count, etc.)
• Index by one or more columns (rows of the summary)
• Columns from another column (spread into separate columns)
• Fill missing values with your own default

Without pivot tables, you’d spend more time reshaping data than understanding it. This lesson solves that by giving you a clean, efficient tool.

Core concept / mental model

Think of a pivot table as a spreadsheet-style cross-tabulation. It’s like a groupby that runs simultaneously on two axes: rows and columns.

Imagine a table of sales:

A pivot table with index='Region', columns='Product', and values='Sales' becomes:

The row index is the first grouping variable, the column index is the second, and the cell values are the aggregated numbers. This is exactly what Excel pivot tables do — pandas just does it faster and in code.

The key difference from groupby is that pivot tables reshape the output by placing one grouping variable into the columns, making it instantly readable and ready for side-by-side comparison.

How it works step by step

Step 1: Choose your rows, columns, and values

To aggregate data with pivot tables, you need to decide:

• index — the column(s) that become the row labels
• columns — the column(s) that become the column labels
• values — the column(s) to aggregate
• aggfunc — the aggregation function, defaulting to 'mean' (yes, mean, not sum!)

Step 2: Set the aggregation function

Pandas defaults to the mean. If you want totals, use aggfunc='sum'. Other common options:

• 'count' — counts non-null values
• 'min', 'max' — extremes
• 'median' — robust midpoint
• 'std' — standard deviation
• A custom function or a list of functions for multiple aggregations

Step 3: Handle missing data

Pivot tables often produce NaN for combinations that don’t exist. Use fill_value=0 to replace them, or dropna=False to keep them (the default is to drop).

Step 4: Use margins=True for totals

Adding margins=True gives you row and column totals — perfect for quick summaries.

Hands-on walkthrough

Let’s work with a realistic sales dataset. First, create a DataFrame:

import pandas as pd

sales = pd.DataFrame({
    'Region': ['East', 'West', 'East', 'West', 'East', 'West'],
    'Product': ['Widget', 'Gadget', 'Gadget', 'Widget', 'Widget', 'Gadget'],
    'Sales': [100, 150, 200, 80, 120, 170],
    'Month': ['Jan', 'Jan', 'Feb', 'Feb', 'Mar', 'Mar']
})

print(sales)

Output:

  Region Product  Sales Month
0   East  Widget    100   Jan
1   West  Gadget    150   Jan
2   East  Gadget    200   Feb
3   West  Widget     80   Feb
4   East  Widget    120   Mar
5   West  Gadget    170   Mar

Basic pivot table — mean sales by region and product

table = sales.pivot_table(
    index='Region',
    columns='Product',
    values='Sales',
    aggfunc='mean'
)
print(table)

Output:

Product  Gadget  Widget
Region                 
East        200.0   110.0
West        160.0    80.0

Sum with totals and filled missing values

Let’s add a fourth month with no East/Gadget sale to see missing data handled:

sales2 = sales.copy()
sales2.loc[len(sales2)] = ['East', 'Widget', 90, 'Apr']  # no East/Gadget in Apr

pivot_sum = sales2.pivot_table(
    index='Region',
    columns='Product',
    values='Sales',
    aggfunc='sum',
    fill_value=0,
    margins=True,
    margins_name='Total'
)
print(pivot_sum)

Output:

Product   Gadget  Widget  Total
Region                        
East          200     310    510
West          320      80    400
Total         520     390    910

Multiple aggregation functions at once

You might want both sum and mean in one table:

pivot_multi = sales.pivot_table(
    index='Region',
    columns='Product',
    values='Sales',
    aggfunc=['sum', 'mean']
)
print(pivot_multi)

Output:

         sum           mean          
Product Gadget Widget Gadget Widget
Region                              
East     200.0  220.0  200.0  110.0
West     320.0   80.0  160.0   80.0

Using aggfunc with multiple values columns

If you have Sales and Profit, you can pass both in values:

sales['Profit'] = [30, 50, 60, 10, 40, 70]

pivot_profit = sales.pivot_table(
    index='Region',
    columns='Product',
    values=['Sales', 'Profit'],
    aggfunc='sum'
)
print(pivot_profit)

Output:

        Profit           Sales          
Product Gadget Widget Gadget Widget
Region                               
East       60.0   70.0  200.0  220.0
West      120.0   10.0  320.0   80.0

Pro tip: Always double-check the default aggfunc! If you expect totals but see averages, that’s why.

Compare options / when to choose what

Use pivot_table when you need a matrix-style summary. Use groupby when you only need one dimension or plan to chain further operations. Use crosstab for frequency tables (e.g., counting how many orders per customer and month).

Variations worth knowing

• Use pd.crosstab(index, columns, values, aggfunc) for quick count tables.
• Use pd.pivot_table(df, ...) as a function call instead of the method.
• For time-series reshaping, consider pivot (without aggregation) — it requires unique index/value pairs.

Troubleshooting & edge cases

1. Duplicate entries cause an error with pivot

If you use df.pivot() and have duplicate index-column pairs, you’ll get a ValueError. Fix: Use pivot_table with an aggfunc to aggregate duplicates.

# This raises ValueError on duplicate keys
df.pivot(index='Region', columns='Product', values='Sales')

2. NaN values appear unexpectedly

Missing combinations create NaN. Fix: Set fill_value=0 or use dropna=False to keep them, then handle them later (e.g., df.fillna(0)).

3. Default aggfunc is 'mean', not 'sum'

Users often forget this and get averages instead of totals. Fix: Always specify aggfunc='sum' when you need totals.

4. Misleading column names after multi-index aggregation

When using a list of values or aggfunc, the columns become a MultiIndex. Fix: Flatten them with df.columns = ['_'.join(col).strip() for col in df.columns.values].

pivot_flat = pivot_multi.copy()
pivot_flat.columns = ['_'.join(col).strip() for col in pivot_flat.columns.values]
print(pivot_flat)

Output:

         sum_Gadget  sum_Widget  mean_Gadget  mean_Widget
Region                                                    
East          200.0       220.0        200.0        110.0
West          320.0        80.0        160.0         80.0

5. The values column contains missing values

Pivot tables ignore missing values when aggregating by default. Fix: Drop them before pivoting (.dropna()) or use aggfunc that handles NaN (e.g., sum treats as zero).

What you learned & what's next

You now know how to aggregate data with pivot tables — from a single line of pandas code to advanced multi-index summaries. You can:

• Explain the core idea of pivot tables as a two-dimensional cross-tabulation.
• Build a practical pivot table using index, columns, values, and aggfunc.
• Handle missing data with fill_value and totals with margins.
• Choose between pivot_table, groupby, and crosstab based on your task.

Next up: In the next lesson, you’ll take these aggregated summaries and turn them into visualizations — plotting pivot tables with pandas’ built-in line and bar plots to spot trends at a glance.