How to Use pivot_table in pandas: Syntax and Examples

Use pandas.pivot_table() to create a spreadsheet-style pivot table that summarizes data by grouping rows and columns with aggregation functions. Specify data, index (rows), columns, and values to control the output.

Syntax

The pivot_table function has these main parts:

• data: your DataFrame to summarize
• index: column(s) to group as rows
• columns: column(s) to group as columns
• values: column(s) to aggregate
• aggfunc: aggregation function like sum, mean, etc. (default is mean)
• fill_value: value to replace missing data (optional)

python

pandas.pivot_table(data, index=None, columns=None, values=None, aggfunc='mean', fill_value=None)

Example

This example shows how to summarize sales data by Region and Product, calculating the total sales.

python

import pandas as pd

data = {
    'Region': ['East', 'East', 'West', 'West', 'East', 'West'],
    'Product': ['A', 'B', 'A', 'B', 'A', 'B'],
    'Sales': [100, 150, 200, 250, 300, 350]
}
df = pd.DataFrame(data)

pivot = pd.pivot_table(df, index='Region', columns='Product', values='Sales', aggfunc='sum', fill_value=0)
print(pivot)

Output

Product    A    B
Region           
East     400  150
West     200  600

Common Pitfalls

Common mistakes include:

• Not specifying values when multiple numeric columns exist, causing unexpected aggregation.
• Using aggfunc that does not fit the data type (e.g., sum on strings).
• Ignoring missing values which can cause NaNs in the output; use fill_value to fix this.

python

import pandas as pd

data = {'Category': ['X', 'X', 'Y'], 'Value': [10, 20, 30], 'Name': ['a', 'b', 'c']}
df = pd.DataFrame(data)

# Wrong: aggfunc sum on 'Name' (string) causes error
# pd.pivot_table(df, index='Category', values='Name', aggfunc='sum')

# Right: use count or specify numeric column
pivot_correct = pd.pivot_table(df, index='Category', values='Value', aggfunc='sum')
print(pivot_correct)

Output

          Value
Category       
X            30
Y            30

Quick Reference

Parameter	Description	Default
data	DataFrame to pivot	None
index	Column(s) to group as rows	None
columns	Column(s) to group as columns	None
values	Column(s) to aggregate	None
aggfunc	Aggregation function (sum, mean, count, etc.)	mean
fill_value	Value to replace missing data	None

Key Takeaways

Use pandas.pivot_table to summarize data by grouping rows and columns with aggregation.

Always specify index, columns, and values to control the pivot table layout.

Choose an appropriate aggregation function with aggfunc, like sum or mean.

Use fill_value to replace missing values and avoid NaNs in the result.

Avoid aggregating non-numeric data with numeric functions to prevent errors.