Learn how to transform data between wide and long formats using pandas' pivot_table, melt, and stack functions. This hands-on lesson builds real analyst fluency — with realistic datasets, composable workflows, and production-ready techniques.
If you've ever received a sales report where each month lives in its own column, you know the pain of trying to analyze it programmatically. Or maybe you've exported a database query and ended up with a perfectly normalized long table — but your stakeholder needs a cross-tab summary with products as rows and regions as columns. Data rarely arrives in exactly the shape you need for the task at hand. That's not a sign of bad data management; it's just reality.
This lesson is about taking control of that shape. We'll cover three of pandas' most powerful reshaping tools: pivot_table for collapsing long data into a summarized wide format (think Excel PivotTable, but in code), melt for going the other direction — unpacking wide data into long format — and stack and unstack for rotating a DataFrame's columns into row levels and back again. By the end, you'll be able to look at any awkwardly shaped dataset and know exactly which tool to reach for and why.
What you'll learn:
pivot_table works under the hood and how to use it to summarize multi-dimensional datamelt and how to unpivot wide data into analysis-ready long formatstack and unstack manipulate hierarchical (MultiIndex) DataFramesYou should be comfortable loading DataFrames and selecting and filtering data with loc, iloc, and boolean masks. You should also understand basic aggregation — if you haven't worked through groupby as the PivotTable replacement, skim that first, because pivot_table is closely related. You don't need to know anything about MultiIndexes going in — we'll build up to those naturally.
Before touching any functions, let's establish a mental model. Data can be organized in two fundamental orientations:
Wide format: Each subject (product, customer, region) is a single row, and repeated measurements across time or categories become separate columns. This is the natural output of many reporting tools and is easy for humans to scan.
Long format: Every single observation is its own row. Each row has a "variable" column that identifies what was measured and a "value" column for the measurement itself. This is what databases prefer, what visualization libraries like Seaborn and Plotly expect, and what groupby aggregations work best on.
Here's the same data in both shapes:
Wide:
product Jan Feb Mar
Widget A 1200 1400 1100
Widget B 800 950 870
Long:
product month sales
Widget A Jan 1200
Widget A Feb 1400
Widget A Mar 1100
Widget B Jan 800
Widget B Feb 950
Widget B Mar 870
Neither format is inherently better. They're tools for different jobs. pivot_table moves you from long → wide (with aggregation). melt moves you from wide → long. stack and unstack handle a special case: rotating between column levels and row index levels. Knowing which direction you need to travel determines which function you use.
We'll use a realistic sales dataset throughout this lesson — the kind you'd get from an ERP system or data warehouse export. Let's build it:
import pandas as pd
import numpy as np
# Simulate a sales transaction log
np.random.seed(42)
regions = ['Northeast', 'Southeast', 'Midwest', 'West']
products = ['Widget A', 'Widget B', 'Gadget Pro', 'Gadget Lite']
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
reps = {
'Northeast': ['Alice', 'Bob'],
'Southeast': ['Carlos', 'Diana'],
'Midwest': ['Eve', 'Frank'],
'West': ['Grace', 'Henry'],
}
rows = []
for _ in range(400):
region = np.random.choice(regions)
product = np.random.choice(products)
month = np.random.choice(months)
rep = np.random.choice(reps[region])
units = np.random.randint(10, 200)
price = {'Widget A': 49.99, 'Widget B': 34.99,
'Gadget Pro': 129.99, 'Gadget Lite': 79.99}[product]
revenue = round(units * price, 2)
rows.append([region, product, month, rep, units, revenue])
df = pd.DataFrame(rows, columns=[
'region', 'product', 'month', 'rep', 'units', 'revenue'
])
print(df.shape)
print(df.head(10))
This gives us 400 transaction rows — one per sale — with region, product, month, rep, units sold, and revenue. It's already in long format, which makes it perfect for pivoting.
pivot_table does two things simultaneously: it reshapes and it aggregates. This is the key distinction from simple reshaping — you can't pivot 400 rows into a clean product × region grid without collapsing the duplicates somehow. pivot_table handles that collapse with an aggregation function.
The mental model: you're telling pandas "I want rows defined by this column, columns defined by that column, values filled with this metric, and aggregate with this function wherever rows and columns overlap."
# Total revenue by product (rows) and region (columns)
revenue_pivot = pd.pivot_table(
df,
values='revenue',
index='product',
columns='region',
aggfunc='sum'
)
print(revenue_pivot)
Output (approximate):
region Midwest Northeast Southeast West
product
Gadget Lite ... ... ... ...
Gadget Pro ... ... ... ...
Widget A ... ... ... ...
Widget B ... ... ... ...
Each cell is the total revenue for that product-region combination. Any combination with no transactions gets NaN by default — which rarely happens with enough data, but you should be prepared for it.
Tip
The fill_value parameter is your friend for sparse pivots. pd.pivot_table(..., fill_value=0) replaces NaN with zero, which is usually what you want for revenue and count metrics. Be careful using it for averages, though — replacing a missing average with 0 distorts your summary.
The aggfunc parameter accepts any function name as a string, a callable, or a list of functions:
# Average units sold per transaction, by product and region
avg_units = pd.pivot_table(
df,
values='units',
index='product',
columns='region',
aggfunc='mean'
).round(1)
# Count of transactions by product and region
transaction_count = pd.pivot_table(
df,
values='revenue', # any column works for counting
index='product',
columns='region',
aggfunc='count'
)
You can also pass a list of functions to get multiple summaries at once:
multi_agg = pd.pivot_table(
df,
values='revenue',
index='product',
columns='region',
aggfunc=['sum', 'mean', 'count']
)
print(multi_agg.columns)
# MultiIndex([('sum', 'Midwest'), ('sum', 'Northeast'), ...
# ('mean', 'Midwest'), ...
# ('count', 'Midwest'), ...])
This returns a DataFrame with a MultiIndex on the columns — one level for the function name, one for the region. We'll revisit MultiIndexes when we get to stack.
# Pivot with both revenue and units, plus row/column totals
full_pivot = pd.pivot_table(
df,
values=['revenue', 'units'],
index='product',
columns='region',
aggfunc='sum',
fill_value=0,
margins=True, # adds "All" row and column
margins_name='Total' # rename from default "All"
)
print(full_pivot)
The margins=True option appends a "Total" row and column that aggregates across the entire axis — equivalent to Excel's grand totals in a PivotTable. This is enormously useful when you're building reports directly from pandas.
You can pass a list to index or columns to create hierarchical groupings:
# Revenue by product + month (rows) and region (columns)
monthly_pivot = pd.pivot_table(
df,
values='revenue',
index=['product', 'month'],
columns='region',
aggfunc='sum',
fill_value=0
)
print(monthly_pivot.head(12))
Now the row index has two levels: product and month. This is a MultiIndex, and it's where stack and unstack become relevant — but we'll get to that shortly.
Warning
When you use multiple index values, the order of categories in your output depends on the sort order of those columns. Months will sort alphabetically (Apr, Feb, Jan, Jun, Mar, May) rather than chronologically unless you convert the month column to a proper datetime or an ordered Categorical before pivoting. This is one of the most common sources of misleading pivot output.
To fix month ordering:
month_order = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
df['month'] = pd.Categorical(df['month'], categories=month_order, ordered=True)
monthly_pivot = pd.pivot_table(
df,
values='revenue',
index=['product', 'month'],
columns='region',
aggfunc='sum',
fill_value=0
)
Now months appear in calendar order. This small step makes a big difference in any report or chart.
melt is the inverse of pivot_table. You use it when you receive data in wide format — think spreadsheets with one column per month, one column per store, or one column per survey question — and you need to get it into long format for analysis or visualization.
This scenario is extremely common when working with financial reports, survey exports, or any data that was manually maintained in a spreadsheet. If you've been loading and cleaning spreadsheets as described in loading CSV and Excel files and exploring data, you've almost certainly encountered this.
Let's create a realistic wide-format report — the kind you'd receive from a finance team:
# Monthly revenue report — wide format, one column per month
wide_revenue = pd.DataFrame({
'product': ['Widget A', 'Widget B', 'Gadget Pro', 'Gadget Lite'],
'category': ['Widget', 'Widget', 'Gadget', 'Gadget'],
'Jan': [14800, 9200, 28400, 17600],
'Feb': [17200, 10500, 31200, 19800],
'Mar': [13400, 8700, 24600, 15200],
'Apr': [18900, 11200, 33800, 21400],
'May': [16700, 9900, 29900, 18700],
'Jun': [20100, 12400, 37200, 23100],
})
print(wide_revenue)
This is readable at a glance for a human, but try running a groupby on it. You can't — month is spread across six columns rather than being a single categorical variable. melt fixes that.
long_revenue = pd.melt(
wide_revenue,
id_vars=['product', 'category'], # columns to keep as-is
value_vars=['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'], # columns to unpivot
var_name='month', # name for the new "variable" column
value_name='revenue' # name for the new "value" column
)
print(long_revenue.shape) # (24, 4) — 4 products × 6 months
print(long_revenue.head(8))
Output:
product category month revenue
0 Widget A Widget Jan 14800
1 Widget B Widget Jan 9200
2 Gadget Pro Gadget Jan 28400
3 Gadget Lite Gadget Jan 17600
4 Widget A Widget Feb 17200
5 Widget B Widget Feb 10500
6 Gadget Pro Gadget Feb 31200
7 Gadget Lite Gadget Feb 19800
The id_vars are your anchor columns — the ones that identify each row and should be repeated. The value_vars are the columns you want to collapse into rows. If you omit value_vars, pandas will use every column not in id_vars, which is convenient when you have many columns to unpivot.
Key insight
After melt, you almost always want to sort the result for readability: long_revenue.sort_values(['product', 'month']). The default output order follows column order from the original wide table, which often isn't what you want.
These two operations compose naturally. Here's a workflow you'll use constantly:
# Step 1: Receive messy wide data from finance
wide_revenue = pd.DataFrame({
'product': ['Widget A', 'Widget B', 'Gadget Pro', 'Gadget Lite'],
'category': ['Widget', 'Widget', 'Gadget', 'Gadget'],
'Jan': [14800, 9200, 28400, 17600],
'Feb': [17200, 10500, 31200, 19800],
'Mar': [13400, 8700, 24600, 15200],
})
# Step 2: Melt to long format
long = pd.melt(
wide_revenue,
id_vars=['product', 'category'],
var_name='month',
value_name='revenue'
)
# Step 3: Now you can aggregate properly
category_summary = long.groupby(['category', 'month'])['revenue'].sum().reset_index()
print(category_summary)
# Step 4: Or re-pivot into a different shape
category_pivot = pd.pivot_table(
long,
values='revenue',
index='category',
columns='month',
aggfunc='sum'
)
print(category_pivot)
This pattern — melt to normalize, then re-pivot or aggregate into the exact shape you need — is one of the most powerful data wrangling workflows in pandas.
Sometimes wide data has multiple measurement columns per time period: Jan_revenue, Jan_units, Feb_revenue, Feb_units, and so on. melt handles one value type at a time, so you'll need to melt twice and then merge, or use a more advanced approach:
# Wide data with paired columns
wide_multi = pd.DataFrame({
'product': ['Widget A', 'Widget B'],
'Jan_revenue': [14800, 9200],
'Jan_units': [296, 263],
'Feb_revenue': [17200, 10500],
'Feb_units': [344, 300],
})
# Melt revenue columns
rev_long = pd.melt(
wide_multi,
id_vars=['product'],
value_vars=['Jan_revenue', 'Feb_revenue'],
var_name='month_metric',
value_name='revenue'
)
rev_long['month'] = rev_long['month_metric'].str.replace('_revenue', '')
rev_long = rev_long.drop(columns='month_metric')
# Melt units columns
units_long = pd.melt(
wide_multi,
id_vars=['product'],
value_vars=['Jan_units', 'Feb_units'],
var_name='month_metric',
value_name='units'
)
units_long['month'] = units_long['month_metric'].str.replace('_units', '')
units_long = units_long.drop(columns='month_metric')
# Merge back together
combined = rev_long.merge(units_long, on=['product', 'month'])
print(combined)
If you need a refresher on merging DataFrames like this, see joining DataFrames with pandas merge.
stack and unstack operate on the DataFrame's index and column hierarchy — they don't perform aggregation. This makes them more surgical than pivot_table and melt, but also more confusing at first.
The easiest way to understand them: stack takes a level of column labels and rotates it down into the row index. unstack does the reverse — it takes a level of the row index and rotates it out into columns.
Let's start with a clean pivot to create a MultiIndex structure:
# Create a MultiIndex pivot — both product and month in the rows
monthly_region = pd.pivot_table(
df,
values='revenue',
index=['product', 'month'],
columns='region',
aggfunc='sum',
fill_value=0
)
print(monthly_region.shape) # (24, 4) — 24 product-month combos × 4 regions
print(monthly_region.index)
# MultiIndex([('Gadget Lite', 'Apr'), ('Gadget Lite', 'Feb'), ...])
print(monthly_region.columns)
# Index(['Midwest', 'Northeast', 'Southeast', 'West'])
# Stack the region columns into the row index
stacked = monthly_region.stack()
print(stacked.shape) # (96, ) — a Series now
print(type(stacked)) # <class 'pandas.core.series.Series'>
print(stacked.head(8))
Output:
product month region
Gadget Lite Apr Midwest ...
Northeast ...
Southeast ...
West ...
Feb Midwest ...
Northeast ...
Southeast ...
West ...
dtype: float64
stack collapsed the four region columns into a third level of the row index, and the result is a Series (since there's only one remaining "value"). If the DataFrame had multiple value columns, stack would produce a DataFrame with one column per remaining value.
To get this back to a flat DataFrame format:
stacked_df = stacked.reset_index()
stacked_df.columns = ['product', 'month', 'region', 'revenue']
print(stacked_df.head())
Note
stack by default drops rows where the value is NaN. If you have a sparse pivot with missing product-region combinations, use stack(dropna=False) to preserve those rows. This is important for downstream analysis where you need a complete grid.
unstack is the reverse. Starting from a grouped Series, it rotates a level of the index out into columns:
# Group the original long data to get a Series with MultiIndex
region_product = df.groupby(['region', 'product'])['revenue'].sum()
print(region_product.head(8))
print(region_product.index)
# MultiIndex([('Midwest', 'Gadget Lite'), ('Midwest', 'Gadget Pro'), ...])
# Unstack the product level out to columns
unstacked = region_product.unstack(level='product')
print(unstacked)
Output:
product Gadget Lite Gadget Pro Widget A Widget B
region
Midwest ... ... ... ...
Northeast ... ... ... ...
Southeast ... ... ... ...
West ... ... ... ...
You can specify which level to unstack with level parameter — either an integer (0, 1, -1) or the level name as a string. unstack() with no arguments unstacks the innermost level by default.
Here's the practical decision tree:
| Situation | Use |
|---|---|
| Long transactional data → cross-tab summary | pivot_table |
| Wide spreadsheet → analysis-ready long data | melt |
| MultiIndex Series → cross-tab DataFrame | unstack |
| Cross-tab DataFrame → MultiIndex Series | stack |
| Rotating column names into row levels | stack |
stack and unstack shine when you're working downstream of a groupby or an existing pivot_table — when you already have a MultiIndex structure and you want to reshape it without recomputing the aggregation from scratch.
Let's pull these tools together into a realistic scenario. You've received raw transaction data (our df from above) and a stakeholder wants:
import pandas as pd
# --- Step 1: Build the master pivot ---
master = pd.pivot_table(
df,
values='revenue',
index='product',
columns=['region', 'month'], # two levels on columns
aggfunc='sum',
fill_value=0
)
# The columns are now a MultiIndex: (region, month)
print(master.columns[:6])
# MultiIndex([('Midwest', 'Apr'), ('Midwest', 'Feb'), ...])
# --- Step 2: Sort months properly ---
month_order = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
df['month'] = pd.Categorical(df['month'], categories=month_order, ordered=True)
master = pd.pivot_table(
df,
values='revenue',
index='product',
columns=['region', 'month'],
aggfunc='sum',
fill_value=0
)
# --- Step 3: Build per-region tables using xs (cross-section) ---
regions = df['region'].unique()
region_tables = {}
for region in regions:
region_df = master.xs(region, axis=1, level='region')
# Add a totals column
region_df['Total'] = region_df.sum(axis=1)
# Add a totals row
region_df.loc['Total'] = region_df.sum()
region_tables[region] = region_df
# Inspect one table
print(region_tables['Northeast'])
# --- Step 4: Calculate product share of monthly revenue ---
# Start fresh from long format
monthly_totals = df.groupby(['month', 'product'])['revenue'].sum().unstack('product')
monthly_totals = monthly_totals.reindex(columns=df['product'].cat.categories
if hasattr(df['product'], 'cat')
else monthly_totals.columns)
# Calculate share
monthly_share = monthly_totals.div(monthly_totals.sum(axis=1), axis=0).round(4) * 100
print("\nProduct share of monthly revenue (%):")
print(monthly_share.round(1))
# --- Step 5: Export to Excel with multiple sheets ---
with pd.ExcelWriter('sales_report.xlsx', engine='openpyxl') as writer:
for region, table in region_tables.items():
table.to_excel(writer, sheet_name=region)
monthly_share.to_excel(writer, sheet_name='Revenue Share %')
print("\nReport exported to sales_report.xlsx")
Tip
xs (cross-section) is a clean way to slice a specific label from a MultiIndex axis without having to drop or reset the index. master.xs('Northeast', axis=1, level='region') pulls just the Northeast columns, preserving the month level. It's more readable than chaining bracket notation on a MultiIndex.
This workflow demonstrates the full cycle: start with raw long data, build a MultiIndex pivot, slice it per-region, compute a derived metric using unstack, and ship it to Excel. That's production-grade reshaping.
Work through the following using the df dataset we built at the top of the lesson (or recreate it):
Part 1 — pivot_table
rep as rows and product as columns. Use aggfunc='mean' and round to one decimal.Part 2 — melt
reset_index() so rep becomes a regular column.melt to convert it to long format with columns: rep, product, avg_units.avg_units > 100. How many reps-product combinations beat that threshold?Part 3 — stack/unstack
df, use groupby to compute total revenue and total units by region and product. The result should be a DataFrame with a MultiIndex row and two columns.stack to rotate the metric columns (revenue, units) into the row index.unstack on the product level to get a table where rows are region+metric and columns are products.Challenge: Take the result of Part 3 and sort it so that revenue rows appear before units rows within each region.
If you expect a 1:1 row mapping and get aggregated values instead, it's because pivot_table always collapses duplicates. If you have unique index-column combinations and just want to reshape without aggregation, use df.set_index(['product', 'month']).unstack('month') instead.
Sparse combinations produce NaN in pivot_table. Always check your output:
print(pivot.isna().sum().sum()) # count total NaN values
Use fill_value=0 for count and sum metrics. For means and ratios, leave NaN as-is — a missing observation is different from a zero.
This usually means your value_vars column names don't exactly match the DataFrame's column names. Check:
print(df.columns.tolist()) # look for hidden spaces or case differences
Column names with leading/trailing spaces are a classic messy data problem — df.columns = df.columns.str.strip() before melting is good defensive practice.
By default, stack drops rows where all values are NaN. If your downstream analysis needs a complete grid:
stacked = df.stack(dropna=False)
When you have a MultiIndex with three or more levels, you need to specify level carefully:
# These are equivalent if 'region' is level 1
series.unstack(level=1)
series.unstack(level='region')
# unstack(-1) always unstacks the innermost level — useful as a default
series.unstack()
When in doubt, print the index and inspect the level names before unstacking:
print(series.index.names) # ['region', 'product', 'month']
print(series.index.levels) # shows values at each level
Warning
After several stack / unstack / reset_index operations, it's easy to end up with unnamed index levels or column levels labelled None. If something looks off, always check df.index.names and df.columns.names before continuing. A quick df.rename_axis(None, axis=1) removes column axis names when they clutter your output.
After pivot_table, the column axis often retains a name like region or month, which shows up in Excel exports and display output. Clean it with:
pivot.columns.name = None
Here's what you now have in your toolkit:
pivot_table reshapes long transactional data into summarized wide format. It requires an aggregation function because it handles duplicate index-column combinations. Use it when you need the equivalent of an Excel PivotTable, built repeatably in code.
melt does the reverse — takes wide data with many columns and collapses it into long format with one row per observation. It's essential when working with spreadsheet-style reports and makes your data compatible with groupby, visualization libraries, and database loading.
stack rotates column labels into the row index, producing a more compact MultiIndex representation. unstack does the reverse. Together they're the precision instruments you reach for when working with already-aggregated MultiIndex structures.
The deeper skill here is recognizing the shape your data is in and the shape you need. Once you've internalized the mental model — long for computation, wide for display — you'll start to see reshaping opportunities everywhere.
Where to go next:
Reshaping is the connective tissue of data analysis. Once you're fluent in it, you spend a lot less time fighting your data and a lot more time understanding it.