Pandas Data Reshaping (pivot / melt / stack / unstack)

Data reshaping is a common operation in data analysis, used to change the layout structure of data. Pandas provides functions such as pivot, melt, stack, and unstack to convert between long format and wide format.

pivot Pivot Table

pivotConverts long format data to wide format, similar to Excel's pivot table feature.

Basic Usage

Example

import pandas as pd

# Create long format data
df = pd.DataFrame({
    "Date": ["2024-01-01", "2024-01-01", "2024-01-02", "2024-01-02"],
    "Product": ["A", "B", "A", "B"],
    "Sales": [100, 150, 120, 90]
})

print("Long format data:")
print(df)
print()

# Convert to wide format
pivot_df = df.pivot(index="Date", columns="Product", values="Sales")
print("Wide format data:")
print(pivot_df)

Multiple Index

Example

import pandas as pd

df = pd.DataFrame({
    "Year": ["2024", "2024", "2024", "2024"],
    "Quarter": ["Q1", "Q1", "Q2", "Q2"],
    "Product": ["A", "B", "A", "B"],
    "Sales": [100, 150, 120, 90]
})

print("Data:")
print(df)
print()

# Multiple index pivot
pivot_df = df.pivot(index=["Year", "Quarter"], columns="Product", values="Sales")
print("Multiple index pivot:")
print(pivot_df)

Aggregate Function

Example

import pandas as pd

# Case with duplicate values
df = pd.DataFrame({
    "Date": ["2024-01-01", "2024-01-01", "2024-01-01", "2024-01-02"],
    "Product": ["A", "A", "B", "B"],
    "Sales": [100, 110, 80, 90]
})

print("Data with duplicate values:")
print(df)
print()

# pivot does not support duplicate values by default, you need to use pivot_table and specify an aggregate function
pivot_df = df.pivot_table(index="Date", columns="Product", values="Sales", aggfunc="sum")
print("Aggregating with sum:")
print(pivot_df)
print()

# Use mean
print("Aggregating with mean:")
print(df.pivot_table(index="Date", columns="Product", values="Sales", aggfunc="mean"))

pivotDuplicate values are not allowed; when there are duplicate values, usepivot_tableand specify an aggregate function.

melt Unpivot

meltConverts wide format data to long format, the reverse operation of pivot.

Example

import pandas as pd

# Create wide format data
df = pd.DataFrame({
    "Date": ["2024-01-01", "2024-01-02"],
    "A": [100, 120],
    "B": [150, 90]
})

print("Wide format data:")
print(df)
print()

# Convert to long format
melted = df.melt(id_vars="Date", var_name="Product", value_name="Sales")
print("Long format data:")
print(melted)

Keep Multiple Columns Unchanged

Example

import pandas as pd

df = pd.DataFrame({
    "City": ["Beijing", "Shanghai"],
    "2023 Revenue": [1000, 800],
    "2023 Profit": [200, 150],
    "2024 Revenue": [1200, 950],
    "2024 Profit": [250, 180]
})

print("Wide format:")
print(df)
print()

# Keep "City" unchanged, convert other columns to long format
melted = df.melt(
    id_vars="City",
    var_name="Indicator",
    value_name="Value"
)
print("Long format:")
print(melted)

stack and unstack

stack and unstack are reshaping functions dedicated to MultiIndex.

unstack

Example

import pandas as pd

# Create data with multiple index levels
df = pd.DataFrame({
    "A": [1, 2, 3, 4],
    "B": [5, 6, 7, 8]
}, index=pd.MultiIndex.from_tuples(
    [("X", 1), ("X", 2), ("Y", 1), ("Y", 2)],
    names=["Category", "Number"]
))

print("Multi-level index data:")
print(df)
print()

# unstack converts inner index to columns
print("After unstack:")
print(df.unstack())

stack

Example

import pandas as pd

# Create wide format (with column index)
df = pd.DataFrame({
    ("A", "X"): [1, 2],
    ("A", "Y"): [3, 4],
    ("B", "X"): [5, 6],
    ("B", "Y"): [7, 8]
})

print("With multi-level column index:")
print(df)
print()

# stack converts column index to inner index
print("After stack:")
print(df.stack())

Practice: Business Report Conversion

Example

import pandas as pd

# Simulate business data - sales records
sales = pd.DataFrame({
    "Date": ["2024-01"] * 4,
    "Product": ["Phone", "Computer", "Tablet", "Headphones"],
    "Channel": ["Online", "Online", "Offline", "Online"],
    "Sales Amount": [10000, 20000, 8000, 5000]
})

print("Raw sales data:")
print(sales)
print()

# Convert using pivot
pivot = sales.pivot_table(
    index="Product",
    columns="Channel",
    values="Sales Amount",
    aggfunc="sum",
    fill_value=0
)
print("Channel sales pivot:")
print(pivot)
print()

# Restore to long format
print("Restored to long format:")
print(pivot.reset_index().melt(id_vars="Product", var_name="Channel", value_name="Sales Amount"))

Reshaping Function Selection

Function Purpose Typical Scenario
pivot Long → Wide Row-column conversion
pivot_table Long → Wide (with aggregation) When there are duplicate values
melt Wide → Long Data tidying
unstack Index → Column Multi-level index
stack Column → Index Multi-level index

The purpose of data reshaping is to make data more suitable for analysis or presentation. Choose the appropriate conversion method based on downstream requirements.

Other Extensions