Pandas df.pivot() Function
Pandas Common Functionsdf.pivot()is a member method of DataFrame, used tocreate pivot tables. It can reshape data into a new layout based on the specified row index, column index, and value columns.
andpivot_table()Unlikepivot()does not support aggregation operations, and requires the combination of row index and column index to be unique; otherwise, an error will be raised.
Word Definition： pivotMeans "rotate, pivot." Here it refers to the "rotational" reshaping of data, allowing dynamic switching of row and column dimensions like a Pivot table.
Basic Syntax and Parameters
df.pivot()is an instance method of DataFrame, called via the dot operator.
Syntax Format
DataFrame.pivot(index=None, columns, values=None)
Parameter Description
- Parameter：
index- Type: Column name, label, label list, or None.
- Description: The column used as the row index of the new DataFrame. If None, the existing row index is used.
- Parameter：
columns- Type: Column name or label.
- Description:Required parameter. The column used as the column index of the new DataFrame, used to expand unique values into column names.
- Parameter：
values- Type: Column name, label, label list, or None.
- Description: The data column used to fill the DataFrame. If not specified, all columns except index and columns are used.
Function Description
- Return Value: Returns a new DataFrame, i.e., the pivot table.
- Limitation: If the combination of index and columns contains duplicate values, a ValueError will be raised. In such cases, you should use
pivot_table(), which automatically performs aggregation.
Examples
Let us thoroughly master through a series of examples from simple to complexdf.pivot()the usage of
Example 1: Basic Usage - Creating a Simple Pivot Table
Examples
import pandas as pd
# 1. Create sales data (each row has a product-region-sales combination)
sales = pd.DataFrame({
'product': ['A', 'A', 'B', 'B', 'C', 'C'],
'region': ['North', 'South', 'North', 'South', 'North', 'South'],
'sales': [100, 150, 200, 180, 220, 250]
})
print("=== Original Sales Data ===")
print(sales)
# 2. Use df.pivot() to create a pivot table
# Row index: product, column index: region, value: sales
pivot_result = sales.pivot(index='product', columns='region', values='sales')
print("n=== df.pivot() Pivot Table ===")
print(pivot_result)
# 1. Create sales data (each row has a product-region-sales combination)
sales = pd.DataFrame({
'product': ['A', 'A', 'B', 'B', 'C', 'C'],
'region': ['North', 'South', 'North', 'South', 'North', 'South'],
'sales': [100, 150, 200, 180, 220, 250]
})
print("=== Original Sales Data ===")
print(sales)
# 2. Use df.pivot() to create a pivot table
# Row index: product, column index: region, value: sales
pivot_result = sales.pivot(index='product', columns='region', values='sales')
print("n=== df.pivot() Pivot Table ===")
print(pivot_result)
Expected Output:
=== 原始销售数据 === product region sales 0 A North 100 1 A South 150 2 B North 200 3 B South 180 4 C North 220 5 C South 250 === df.pivot() 透视表 === region North South product A 100 150 B 200 180 C 220 250
Code Explanation:
- The original data is in long format, with each row recording the sales of a product in a certain region.
pivot()Use the product column as the row index, the region column as the column index, and sales as the values.- The result is a clear two-dimensional table that allows intuitive comparison of sales of different products in different regions.
Example 2: Multiple Value Columns - Pivoting Multiple Metrics Simultaneously
You can pivot multiple numeric columns at the same time to obtain a richer data view.
Examples
import pandas as pd
# 1. Create data containing sales and quantity
sales = pd.DataFrame({
'product': ['A', 'A', 'B', 'B', 'A', 'A', 'B', 'B'],
'region': ['North', 'South', 'North', 'South', 'East', 'West', 'East', 'West'],
'sales': [100, 150, 200, 180, 120, 130, 190, 210],
'quantity': [10, 15, 20, 18, 12, 13, 19, 21]
})
print("=== Original Data ===")
print(sales)
# 2. Pivot only sales
print("n=== Sales Pivot Table ===")
sales_pivot = sales.pivot(index='product', columns='region', values='sales')
print(sales_pivot)
# 3. Pivot sales and quantity (without specifying values, use all numeric columns)
print("n=== Multi-metric Pivot ===")
multi_pivot = sales.pivot(index='product', columns='region')
print(multi_pivot)
# 1. Create data containing sales and quantity
sales = pd.DataFrame({
'product': ['A', 'A', 'B', 'B', 'A', 'A', 'B', 'B'],
'region': ['North', 'South', 'North', 'South', 'East', 'West', 'East', 'West'],
'sales': [100, 150, 200, 180, 120, 130, 190, 210],
'quantity': [10, 15, 20, 18, 12, 13, 19, 21]
})
print("=== Original Data ===")
print(sales)
# 2. Pivot only sales
print("n=== Sales Pivot Table ===")
sales_pivot = sales.pivot(index='product', columns='region', values='sales')
print(sales_pivot)
# 3. Pivot sales and quantity (without specifying values, use all numeric columns)
print("n=== Multi-metric Pivot ===")
multi_pivot = sales.pivot(index='product', columns='region')
print(multi_pivot)
Expected Output:
=== 原始数据 ===
product region sales quantity
0 A North 100 10
1 A South 150 15
2 B North 200 20
3 B South 180 18
4 A East 120 12
5 A West 130 13
6 B East 190 19
7 B West 210 21
=== 销售额透视表 ===
region East North South West
product
A 120 100 150 130
B 190 200 180 210
=== 多指标透视 ===
sales quantity
region East North South West East North South West
product
A 120 100 150 130 12 10 15 13
B 190 200 180 210 19 20 18 21
Code Explanation:
- When not specifying
valuesparameter, all columns except index and columns will be used. - Multi-column pivoting produces a multi-level column index, with the first level being the original column names and the second level being the values of the columns parameter.
- This approach facilitates comparing the performance of multiple metrics across different dimensions simultaneously.
Example 3: Multi-level Index - Using Multiple Columns as Row Index
You can use multiple columns to create a pivot table with hierarchical index.
Examples
import pandas as pd
# 1. Create data containing year, quarter, and product information
data = pd.DataFrame({
'year': [2023, 2023, 2023, 2023, 2024, 2024, 2024, 2024],
'quarter': ['Q1', 'Q1', 'Q2', 'Q2', 'Q1', 'Q1', 'Q2', 'Q2'],
'product': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'],
'sales': [100, 150, 200, 180, 220, 250, 230, 270]
})
print("=== Original Data ===")
print(data)
# 2. Use multiple columns as row index (in list form)
print("n=== Multi-level Row Index Pivot ===")
pivot_multi = data.pivot(index=['year', 'quarter'], columns='product', values='sales')
print(pivot_multi)
# 3. Reset index, convert to a regular DataFrame
print("n=== After Reset Index ===")
pivot_reset = pivot_multi.reset_index()
print(pivot_reset)
# 1. Create data containing year, quarter, and product information
data = pd.DataFrame({
'year': [2023, 2023, 2023, 2023, 2024, 2024, 2024, 2024],
'quarter': ['Q1', 'Q1', 'Q2', 'Q2', 'Q1', 'Q1', 'Q2', 'Q2'],
'product': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'],
'sales': [100, 150, 200, 180, 220, 250, 230, 270]
})
print("=== Original Data ===")
print(data)
# 2. Use multiple columns as row index (in list form)
print("n=== Multi-level Row Index Pivot ===")
pivot_multi = data.pivot(index=['year', 'quarter'], columns='product', values='sales')
print(pivot_multi)
# 3. Reset index, convert to a regular DataFrame
print("n=== After Reset Index ===")
pivot_reset = pivot_multi.reset_index()
print(pivot_reset)
Expected Output:
=== 原始数据 === year quarter product sales 0 2023 Q1 A 100 1 2023 Q1 B 150 值会抛出错误 - 这是 pivot() 和 pivot_table() 的关键区别：pivot() 要求数据唯一性，pivot_table() 会自动聚合重复值
Code Explanation:
- By using
index=['year', 'quarter']you can create a pivot table with multi-level row index. - This format facilitates analyzing the performance of different products along the time dimension.
- Use
reset_index()to convert the hierarchical index back to regular columns.
Other ExtensionsNote:If the original data contains the same index + columns combination,
pivot()will raiseValueError: Index contains duplicate entries. In such cases, you should usepivot_table(), which automatically aggregates duplicate values.