Pandas DataFrame.pivot_table() Function
Common Pandas Functionsdf.pivot_table()is a member method of DataFrame, used tocreate a pivot table. It ispivot()an enhanced version of, supports aggregation operations, and can handle duplicate combinations of row and column indices.
Pivot table is one of the most commonly used tools in data analysis. It can group, summarize, and reshape data from different perspectives, making it easier to discover patterns and trends in data.
Word Definition： pivot_tablemeans "pivot table", a table that can dynamically change data layout and supports automatic aggregation of duplicate data.
Basic Syntax and Parameters
df.pivot_table()is an instance method of DataFrame, called via the dot operator.
Syntax Format
DataFrame.pivot_table(values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All')
Parameter Description
- Parameter：
index- Type: column name, label, list of labels, or None.
- Description: The column used as the row index. It can be a single column or multiple columns (list); multiple columns create a hierarchical index.
- Parameter：
columns- Type: column name or label.
- Description: The column used as the column index, used to expand unique values into column names.
- Parameter：
values- Type: column name, label, list of labels, or None.
- Description: The numeric columns to aggregate. If not specified, all numeric columns are aggregated.
- Parameter：
aggfunc- Type: function, string, or list of functions.
- Description: Aggregate function. Common values include
'sum'(sum),'mean'(mean, default),'count'(count),'min'(min),'max'(max),'median'(median),'std'(standard deviation), etc.
- Parameter：
fill_value- Type: scalar or None.
- Description: The value used to fill missing values. Defaults to None, preserving NaN.
- Parameter：
margins- Type: boolean.
- Description: Whether to add row and column totals. Defaults to False. When set to True, summary data is added at the end of rows and columns.
- Parameter：
margins_name- Type: string.
- Description: The name of the total row/column, defaults to 'All'. Only valid when margins=True.
Function Description
- Return Value: Returns a DataFrame, i.e., the pivot table.
- Features: Unlike
pivot(),pivot_table()does not raise an error for duplicate index+columns combinations, and automatically usesaggfuncfor aggregation.
Examples
Let's thoroughly master through a series of examples from simple to complexdf.pivot_table()the usage of.
Example 1: Basic Usage - Create a Simple Pivot Table
Example
import pandas as pd
# 1. Create sales data
sales = pd.DataFrame({
'product': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C'],
'region': ['North', 'North', 'North', 'South', 'South', 'South', 'East', 'East', 'East', 'West', 'West', 'West'],
'sales': [100, 150, 200, 180, 170, 160, 190, 200, 210, 220, 230, 240]
})
print("=== Sales Data ===")
print(sales)
# 2. Create pivot table - summarize sales by product and region (default aggregate function is mean)
pivot = sales.pivot_table(values='sales', index='product', columns='region', aggfunc='sum')
print("n=== df.pivot_table() Sales Pivot Table (Sum)===")
print(pivot)
# 1. Create sales data
sales = pd.DataFrame({
'product': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C'],
'region': ['North', 'North', 'North', 'South', 'South', 'South', 'East', 'East', 'East', 'West', 'West', 'West'],
'sales': [100, 150, 200, 180, 170, 160, 190, 200, 210, 220, 230, 240]
})
print("=== Sales Data ===")
print(sales)
# 2. Create pivot table - summarize sales by product and region (default aggregate function is mean)
pivot = sales.pivot_table(values='sales', index='product', columns='region', aggfunc='sum')
print("n=== df.pivot_table() Sales Pivot Table (Sum)===")
print(pivot)
Expected output:
=== 销售数据 ===
product region sales
0 A North 100
1 B North 150
2 C North 200
3 A South 180
4 B South 170
5 C South 160
6 A East 190
7 B East 200
8 C East 210
9 A West 220
10 B West 230
11 C West 240
=== df.pivot_table() 销售透视表（求和）===
region East North South West
product
A 190 100 180 220
B 200 150 170 230
C 210 200 160 240
Code explanation:
- The original data is in long format, with each row recording the sales of a product in a certain region.
pivot_table()Uses product as the row index, region as the column index, and sales as the values.- Use
aggfunc='sum'to sum and summarize the sales. - The result is a clear two-dimensional table, making it easy to compare the sales performance of different products in different regions.
Example 2: Using Different Aggregate Functions
aggfuncThe parameter can specify multiple aggregation methods to analyze data.
Example
import pandas as pd
import numpy as np
# 1. Create richer sales data (each product has multiple records)
sales = pd.DataFrame({
'product': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'],
'region': ['North', 'North', 'South', 'South', 'East', 'East', 'West', 'West', 'North', 'South'],
'sales': [100, 150, 200, 180, 190, 200, 220, 230, 110, 190],
'quantity': [10, 15, 20, 18, 19, 20, 22, 23, 11, 19]
})
print("=== Sales Data ===")
print(sales)
# 2. Calculate average sales (default aggregate function)
print("n=== Average Sales ===")
mean_result = sales.pivot_table(values='sales', index='product', columns='region', aggfunc='mean')
print(mean_result.round(1))
# 3. Use multiple aggregate functions simultaneously (pass a dictionary)
print("n=== Multiple Aggregate Functions ===")
multi_agg = sales.pivot_table(
values='sales',
index='product',
columns='region',
aggfunc={'sales': ['sum', 'mean', 'max']}
)
print(multi_agg)
# 4. Different columns use different aggregate functions
print("n=== Different Columns Different Aggregations ===")
diff_agg = sales.pivot_table(
values=['sales', 'quantity'],
index='product',
columns='region',
aggfunc={'sales': 'sum', 'quantity': 'mean'}
)
print(diff_agg)
import numpy as np
# 1. Create richer sales data (each product has multiple records)
sales = pd.DataFrame({
'product': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'],
'region': ['North', 'North', 'South', 'South', 'East', 'East', 'West', 'West', 'North', 'South'],
'sales': [100, 150, 200, 180, 190, 200, 220, 230, 110, 190],
'quantity': [10, 15, 20, 18, 19, 20, 22, 23, 11, 19]
})
print("=== Sales Data ===")
print(sales)
# 2. Calculate average sales (default aggregate function)
print("n=== Average Sales ===")
mean_result = sales.pivot_table(values='sales', index='product', columns='region', aggfunc='mean')
print(mean_result.round(1))
# 3. Use multiple aggregate functions simultaneously (pass a dictionary)
print("n=== Multiple Aggregate Functions ===")
multi_agg = sales.pivot_table(
values='sales',
index='product',
columns='region',
aggfunc={'sales': ['sum', 'mean', 'max']}
)
print(multi_agg)
# 4. Different columns use different aggregate functions
print("n=== Different Columns Different Aggregations ===")
diff_agg = sales.pivot_table(
values=['sales', 'quantity'],
index='product',
columns='region',
aggfunc={'sales': 'sum', 'quantity': 'mean'}
)
print(diff_agg)
Expected output:
=== 销售数据 ===
product region sales quantity
0 A North 100 10
1 B North 150 15
2 A South 200 20
3 B South 180 18
4 A East 190 19
5 B East 200 20
6 A West 220 22
7 B West 230 23
8 A North 110 11
9 B South 190 19
=== 销售额平均值 ===
region East North South West
product
A 190.0 105.0 200.0 220.0
B 200.0 150.0 185.0 230.0
=== 多聚合函数 ===
sales
sum mean max
region East North South West East North South West East North South West
product
A 190 210 200 220 190 105 200 220 190 110 200 220
B 200 150 370 230 200 150 185 230 200 150 190 230
=== 不同列不同聚合 ===
sales quantity
region East North South West East North South West
product
A 190 210 200 220 19.0 10.5 20.0 22.0
B 200 150 370 230 20.0 15.0 18.5 23.0
Code explanation:
aggfuncCan be a single string (such as 'sum', 'mean'), or a list or dictionary.- When passing a dictionary, different aggregate functions can be specified for different columns.
- The average sales of Product A in the North region is 105 ((100+110)/2), which demonstrates pivot_table()'s ability to automatically handle duplicate values.
Example 3: Using margins to Add Totals
Usemarginsthe parameter can add row and column totals, making it easier to view summary data.
Example
import pandas as pd
# 1. Create sales data
sales = pd.DataFrame({
'product': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'],
'region': ['North', 'North', 'South', 'South', 'East', 'East', 'West', 'West'],
'sales': [100, 150, 200, 180, 190, 200, 220, 230]
})
# 2. Pivot table without totals
print("=== No Totals ===")
no_margins = sales.pivot_table(values='sales', index='product', columns='region', aggfunc='sum')
print(no_margins)
# 3. Add row and column totals
print("n=== Add Totals (margins=True) ===")
with_margins = sales.pivot_table(
values='sales',
index='product',
columns='region',
aggfunc='sum',
margins=True
)
print(with_margins)
# 4. Custom total name
print("n=== Custom Total Name ===")
custom_name = sales.pivot_table(
values='sales',
index='product',
columns='region',
aggfunc='sum',
margins=True,
margins_name='Total'
)
print(custom_name)
# 5. Pivot table with multi-level row index
print("n=== Multi-level Row Index ===")
multi_index = sales.pivot_table(
values='sales',
index=['product', 'region'],
aggfunc='sum'
)
print(multi_index)
# 1. Create sales data
sales = pd.DataFrame({
'product': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'],
'region': ['North', 'North', 'South', 'South', 'East', 'East', 'West', 'West'],
'sales': [100, 150, 200, 180, 190, 200, 220, 230]
})
# 2. Pivot table without totals
print("=== No Totals ===")
no_margins = sales.pivot_table(values='sales', index='product', columns='region', aggfunc='sum')
print(no_margins)
# 3. Add row and column totals
print("n=== Add Totals (margins=True) ===")
with_margins = sales.pivot_table(
values='sales',
index='product',
columns='region',
aggfunc='sum',
margins=True
)
print(with_margins)
# 4. Custom total name
print("n=== Custom Total Name ===")
custom_name = sales.pivot_table(
values='sales',
index='product',
columns='region',
aggfunc='sum',
margins=True,
margins_name='Total'
)
print(custom_name)
# 5. Pivot table with multi-level row index
print("n=== Multi-level Row Index ===")
multi_index = sales.pivot_table(
values='sales',
index=['product', 'region'],
aggfunc='sum'
)
print(multi_index)
Expected output:
=== 无合计 === region East North South West product A 190 100 200 220 B 200 150 180 230 === 添加合计 (margins=True) === region East North South West All product A 190 100 200 220 710 B 200 150 180 230 760 All 390 250 380 450 1470 === 自定义合计名称 === region East North South West 总计 product A 190 100 200 220 710 B 200 150 180 230 760 总计 390 250 380 450 1470
Code explanation:
margins=TrueAdds an "All" row (total) at the bottom and an "All" column (total) on the right side of the pivot table.- You can use
margins_namethe parameter to customize the name of the totals, such as 'Total'. - The All column displays the total sales of each product across all regions, the All row displays the total sales of all products in each region, and the bottom-right corner is the grand total of all data.
Example 4: Using fill_value to Handle Missing Values
Usefill_valuethe parameter can fill missing values generated after pivoting.
Example
import pandas as pd
import numpy as np
# 1. Create data (some product-region combinations do not exist)
sales = pd.DataFrame({
'product': ['A', 'B', 'A', 'B'],
'region': ['North', 'North', 'South', 'South'],
'sales': [100, 150, 200, 180]
})
print("=== Original Data ===")
print(sales)
# 2. Do not fill missing values (default)
print("n=== Missing Values Not Filled ===")
no_fill = sales.pivot_table(values='sales', index='product', columns='region')
print(no_fill)
# 3. Fill missing values with 0
print("n=== Fill Missing Values with 0 ===")
fill_zero = sales.pivot_table(
values='sales',
index='product',
columns='region',
fill_value=0
)
print(fill_zero)
# 4. Demonstrate automatic aggregation of duplicate values
data_with_duplicates = pd.DataFrame({
'product': ['A', 'A', 'B', 'B', 'A'],
'region': ['North', 'North', 'South', 'South', 'North'],
'sales': [100, 120, 200, 180, 90]
})
print("n=== Duplicate Values Data ===")
print(data_with_duplicates)
print("n=== Automatic Aggregation (pivot raises an error, pivot_table sums automatically) ===")
auto_agg = data_with_duplicates.pivot_table(
values='sales',
index='product',
columns='region',
aggfunc='sum'
)
print(auto_agg)
import numpy as np
# 1. Create data (some product-region combinations do not exist)
sales = pd.DataFrame({
'product': ['A', 'B', 'A', 'B'],
'region': ['North', 'North', 'South', 'South'],
'sales': [100, 150, 200, 180]
})
print("=== Original Data ===")
print(sales)
# 2. Do not fill missing values (default)
print("n=== Missing Values Not Filled ===")
no_fill = sales.pivot_table(values='sales', index='product', columns='region')
print(no_fill)
# 3. Fill missing values with 0
print("n=== Fill Missing Values with 0 ===")
fill_zero = sales.pivot_table(
values='sales',
index='product',
columns='region',
fill_value=0
)
print(fill_zero)
# 4. Demonstrate automatic aggregation of duplicate values
data_with_duplicates = pd.DataFrame({
'product': ['A', 'A', 'B', 'B', 'A'],
'region': ['North', 'North', 'South', 'South', 'North'],
'sales': [100, 120, 200, 180, 90]
})
print("n=== Duplicate Values Data ===")
print(data_with_duplicates)
print("n=== Automatic Aggregation (pivot raises an error, pivot_table sums automatically) ===")
auto_agg = data_with_duplicates.pivot_table(
values='sales',
index='product',
columns='region',
aggfunc='sum'
)
print(auto_agg)
Expected output:
=== 原始数据 === product region sales 0 A North 100 1 B North 150 2 A South 200 3 B South 180 === 不填充缺失值 === region North South product A 100.0 200.0 B 150.0 180.0 === 填充缺失值为0 === region North South product A 100 200 B 150 180 === 重复值数据 === product region sales 0 A North 100 1 A North 120 2 B South 200 3 B South 180 4 A North 90 === 自动聚合（pivot会报错，pivot_table自动求和）=== region North South product A 310 NaN B NaN 380
Code explanation:
fill_value=0Missing values can be filled with 0 for easier subsequent calculations.- When there are duplicate product-region combinations,
pivot_table()automatically usesaggfuncfor aggregation, whilepivot()raises an error. - The total sales of Product A in the North region is 310 (100+120+90), which is the result of automatic summation.
Notes
Important notes:
pivot_table()andpivot()The main difference lies in the handling of duplicate values: pivot_table automatically aggregates, while pivot raises an error.- When
valuesWhen the parameter is not specified, all numeric columns will be aggregated.marginsThe total rows/columns added by the parameter may affect subsequent data processing; use with caution.- When processing large-scale data, pivot_table may consume a lot of memory; pay attention to data volume control.
Other Extensions