Pandas df.unstack() Function
Pandas Common Functionsdf.unstack()is a member method of DataFrame, used toconvert row indexes to column indexes. It "unpacks" data with hierarchical indexing, moving one or more levels of the row index into columns.
This is an important operation for data reshaping, usually used in conjunction withstack()to achieve mutual conversion of data.
Word Definition： unstackIt means "to unpack, to unfold"; here it refers to "unfolding" the row index into columns, converting a long table into a wide table.
Basic Syntax and Parameters
df.unstack()It is an instance method of DataFrame, called via the dot operator.
Syntax Format
DataFrame.unstack(level=-1, fill_value=None)
Parameter Description
- Parameter：
level- Type: int, str, list of int, or list of str.
- Description: Specifies the level to unstack into columns. For a multi-level index, you can specify a specific level number (starting from 0) or a level name. The default is -1, meaning the innermost level.
- Parameter：
fill_value- Type: scalar or None.
- Description: The value used to fill missing values generated by the unstack operation. The default is None, keeping NaN.
Function Description
- Return Value: Returns a Series or a DataFrame. If only one row level of the original DataFrame is unstacked, it returns a Series; otherwise, it returns a DataFrame.
- Effect: Moves one or more levels of the row index to the column index, producing a wider data format. The unstacked data is in "wide format", making it easy to view and compare.
Examples
Through a series of examples from simple to complex, let us thoroughly masterdf.unstack()the usage of it.
Example 1: Basic Usage - Converting Rows to Columns
Example
import pandas as pd
# 1. Create long-format data
df = 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 long-format data ===")
print(df)
# 2. Set up hierarchical indexing
df_indexed = df.set_index(['product', 'region'])
print("n=== After setting up hierarchical indexing ===")
print(df_indexed)
# 3. Use df.unstack() to convert the inner index to columns
unstacked = df_indexed.unstack()
print("n=== df.unstack() result ===")
print(unstacked)
# 4. Simplified view
print("n=== Sales only ===")
print(unstacked['sales'])
# 1. Create long-format data
df = 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 long-format data ===")
print(df)
# 2. Set up hierarchical indexing
df_indexed = df.set_index(['product', 'region'])
print("n=== After setting up hierarchical indexing ===")
print(df_indexed)
# 3. Use df.unstack() to convert the inner index to columns
unstacked = df_indexed.unstack()
print("n=== df.unstack() result ===")
print(unstacked)
# 4. Simplified view
print("n=== Sales only ===")
print(unstacked['sales'])
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
n=== 设置层次化索引后 ===
sales
product region
A North 100
South 150
B North 200
South 180
C North 220
South 250
n=== df.unstack() 结果 ===
sales
region North South
product
A 100 150
B 200 180
C 220 250
n=== 只看销售额 ===
region North South
product
A 100 150
B 200 180
C 220 250
Code explanation:
- The original data is in long format, where each row records the sales amount of a product in a region.
- Use
set_index()to create a hierarchical index (product as the outer level, region as the inner level). unstack()The inner index region is moved to the column position.- The result is in wide format, allowing intuitive comparison of sales data across different products and regions.
Example 2: Unstacking a Multi-Level Index
When a DataFrame has a multi-level row index, you can specify which level to unstack.
Example
import pandas as pd
# 1. Create data with a multi-level row index
df = 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(df)
# 2. Set up the multi-level index
df_indexed = df.set_index(['year', 'quarter', 'product'])
print("n=== After setting up the multi-level index ===")
print(df_indexed)
# 3. Unstack the innermost level (product, default level=-1)
print("n=== Unstacked product level (default) ===")
unstacked_inner = df_indexed.unstack()
print(unstacked_inner['sales'])
# 4. Unstack the middle level (quarter)
print("n=== Unstacked quarter level ===")
unstacked_quarter = df_indexed.unstack(level='quarter')
print(unstacked_quarter['sales'])
# 5. Unstack the outer level (year)
print("n=== Unstacked year level ===")
unstacked_year = df_indexed.unstack(level='year')
print(unstacked_year['sales'])
# 6. Unstack multiple levels at the same time
print("n=== Unstacked year and product together ===")
unstacked_multi = df_indexed.unstack(level=['year', 'product'])
print(unstacked_multi['sales'])
# 1. Create data with a multi-level row index
df = 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(df)
# 2. Set up the multi-level index
df_indexed = df.set_index(['year', 'quarter', 'product'])
print("n=== After setting up the multi-level index ===")
print(df_indexed)
# 3. Unstack the innermost level (product, default level=-1)
print("n=== Unstacked product level (default) ===")
unstacked_inner = df_indexed.unstack()
print(unstacked_inner['sales'])
# 4. Unstack the middle level (quarter)
print("n=== Unstacked quarter level ===")
unstacked_quarter = df_indexed.unstack(level='quarter')
print(unstacked_quarter['sales'])
# 5. Unstack the outer level (year)
print("n=== Unstacked year level ===")
unstacked_year = df_indexed.unstack(level='year')
print(unstacked_year['sales'])
# 6. Unstack multiple levels at the same time
print("n=== Unstacked year and product together ===")
unstacked_multi = df_indexed.unstack(level=['year', 'product'])
print(unstacked_multi['sales'])
Expected output:
=== 原始数据 ===
year quarter product sales
0 2023 Q1 A 100
1 2023 Q1 B 150
2 2023 Q2 A 200
3 2023 Q2 B 180
4 2024 Q1 A 220
5 2024 Q1 B 250
6 2024 Q2 A 230
7 2024 Q2 B 270
n=== 设置多层索引 ===
sales
year quarter product
2023 Q1 A 100
B 150
Q2 A 200
B 180
2024 Q1 A 220
B 250
Q2 A 230
B 270
n=== 展开 product 层（默认）===
product A B
year quarter
2023 Q1 100 150
Q2 200 180
2024 Q1 220 250
Q2 230 270
n=== 展开 quarter 层 ===
quarter Q1 Q2
year product
2023 A 100 200
B 150 180
2024 A 220 230
B 250 270
n=== 展开 year 层 ===
year 2023 2024
quarter product
Q1 A 100 220
B 150 250
Q2 A 200 230
B 180 270
n=== 同时展开 year 和 product ===
year 2023 2024
product A B A B
quarter
Q1 100 150 220 250
Q2 200 180 230 270
Code explanation:
- For a DataFrame with a multi-level index, you can use the
levelparameter to specify which level to unstack. - By default,
level=-1the innermost level (product) is unstacked. - Unstacking different levels produces different data layouts, making it convenient to analyze data from different perspectives.
Example 3: Handling Missing Values with fill_value
Use thefill_valueparameter to fill missing values generated after unstacking.
Example
import pandas as pd
import numpy as np
# 1. Create incomplete data (not all product-region combinations have data)
df = pd.DataFrame({
'product': ['A', 'A', 'B', 'C'],
'region': ['North', 'South', 'North', 'East'],
'sales': [100, 150, 200, 220]
})
print("=== Original data (incomplete) ===")
print(df)
# 2. Create a hierarchical index and unstack
df_indexed = df.set_index(['product', 'region'])
print("n=== Hierarchical index ===")
print(df_indexed)
# 3. Do not fill missing values
print("n=== Without filling missing values ===")
unstacked = df_indexed.unstack()
print(unstacked)
print(f"Number of missing values: {unstacked['sales'].isna().sum().sum()}")
# 4. Use fill_value=0 to fill missing values
print("n=== fill_value=0 ===")
unstacked_fill = df_indexed.unstack(fill_value=0)
print(unstacked_fill)
print(f"Number of missing values: {unstacked_fill['sales'].isna().sum().sum()}")
import numpy as np
# 1. Create incomplete data (not all product-region combinations have data)
df = pd.DataFrame({
'product': ['A', 'A', 'B', 'C'],
'region': ['North', 'South', 'North', 'East'],
'sales': [100, 150, 200, 220]
})
print("=== Original data (incomplete) ===")
print(df)
# 2. Create a hierarchical index and unstack
df_indexed = df.set_index(['product', 'region'])
print("n=== Hierarchical index ===")
print(df_indexed)
# 3. Do not fill missing values
print("n=== Without filling missing values ===")
unstacked = df_indexed.unstack()
print(unstacked)
print(f"Number of missing values: {unstacked['sales'].isna().sum().sum()}")
# 4. Use fill_value=0 to fill missing values
print("n=== fill_value=0 ===")
unstacked_fill = df_indexed.unstack(fill_value=0)
print(unstacked_fill)
print(f"Number of missing values: {unstacked_fill['sales'].isna().sum().sum()}")
Expected output:
=== 原始数据（不完整）===
product region sales
0 A North 100
1 A South 150
2 B North 200
3 C East 220
n=== 层次化索引 ===
sales
product region
A North 100
South 150
B North 200
C East 220
n=== 不填充缺失值 ===
sales
region East North South
product
A NaN 100.0 150.0
B NaN 200.0 NaN
C 220.0 NaN NaN
缺失值数量: 5
n=== fill_value=0 ===
sales
region East North South
product
A 0 100 150
B 0 200 0
C 220 0 0
缺失值数量: 0
Code explanation:
- When the original data is incomplete, unstack produces missing values (NaN).
- Using the
fill_valueparameter, you can specify a fill value to avoid errors in subsequent calculations. - It is usually filled with 0 to indicate "no data" or "this combination does not exist".
Example 4: Combining unstack and stack
unstack()andstack()They are inverse operations; using them together enables flexible data reshaping.
Example
import pandas as pd
# 1. Create initial wide-format data
df = pd.DataFrame({
'product': ['A', 'B', 'C'],
'North': [100, 150, 200],
'South': [180, 170, 160],
'East': [190, 200, 210]
})
print("=== Original wide-format data ===")
print(df)
# 2. First use stack to convert to long format
print("n=== step 1: stack converts to long format ===")
stacked = df.set_index('product').stack()
print(stacked)
# 3. Then unstack back (verify reversibility)
print("n=== step 2: unstack converts back to wide format ===")
restored = stacked.unstack()
print(restored)
# 4. Practical: rearrange the data
print("n=== Practical: move region to columns ===")
# Original: product as row index, region as columns
# Target: region as row index, product as columns
result = df.set_index('product').unstack().unstack(level=-1)
print(result)
# 5. Use reorder_levels to adjust the level order
print("n=== Using reorder_levels to adjust levels ===")
# Create a two-level index
df_multi = pd.DataFrame(
[[100, 200], [150, 180], [190, 210]],
index=pd.MultiIndex.from_tuples([
('A', 'North'), ('B', 'North'), ('C', 'North')
], names=['product', 'region']),
columns=['2023', '2024']
)
print("Original data:")
print(df_multi)
# Adjust the level order
print("nAfter adjustment (year on the outer level):")
print(df_multi.unstack(level='region'))
# 1. Create initial wide-format data
df = pd.DataFrame({
'product': ['A', 'B', 'C'],
'North': [100, 150, 200],
'South': [180, 170, 160],
'East': [190, 200, 210]
})
print("=== Original wide-format data ===")
print(df)
# 2. First use stack to convert to long format
print("n=== step 1: stack converts to long format ===")
stacked = df.set_index('product').stack()
print(stacked)
# 3. Then unstack back (verify reversibility)
print("n=== step 2: unstack converts back to wide format ===")
restored = stacked.unstack()
print(restored)
# 4. Practical: rearrange the data
print("n=== Practical: move region to columns ===")
# Original: product as row index, region as columns
# Target: region as row index, product as columns
result = df.set_index('product').unstack().unstack(level=-1)
print(result)
# 5. Use reorder_levels to adjust the level order
print("n=== Using reorder_levels to adjust levels ===")
# Create a two-level index
df_multi = pd.DataFrame(
[[100, 200], [150, 180], [190, 210]],
index=pd.MultiIndex.from_tuples([
('A', 'North'), ('B', 'North'), ('C', 'North')
], names=['product', 'region']),
columns=['2023', '2024']
)
print("Original data:")
print(df_multi)
# Adjust the level order
print("nAfter adjustment (year on the outer level):")
print(df_multi.unstack(level='region'))
Expected output:
=== 原始宽格式数据 === product North South East 0 A 100 180 190 1 B 150 170 ...
Code explanation:
stack()andunstack()They are a pair of inverse operations.- Applying stack first and then unstack can theoretically restore the original shape (ignoring differences in fill values).
- Different data layouts can be achieved through different combinations of levels.
- In actual data analysis, it is often necessary to repeatedly adjust the data shape to suit different analysis needs.
Other ExtensionsTip:
unstack()andstack()It is a core tool for data reshaping.unstack()It "unpacks" rows into columns (long to wide),stack()It "stacks" columns into rows (wide to long).