Reshaping and Analyzing Multi-Level Data in pandas: Working with MultiIndex Columns and Rows After groupby and pivot_table

Introduction

You've run a groupby with multiple aggregation functions, and suddenly your DataFrame has this strange two-row column header. Or you've used pivot_table and now your columns are a tuple instead of a plain string. The data is right — you can see it — but you can't select it with normal column names. You can't filter it the way you expect. You can't export it cleanly. What exactly are you looking at, and how do you work with it?

This is the MultiIndex situation, and it trips up nearly every analyst who moves from basic pandas into more sophisticated aggregation work. MultiIndex — whether on rows, columns, or both — is pandas' way of representing hierarchical structure in tabular data. It's powerful when you understand it, and genuinely confusing when you don't. The good news: once the model clicks, you'll stop fighting it and start using it deliberately.

By the end of this lesson, you'll be able to work confidently with multi-level column and row indexes that arise from groupby and pivot_table operations. You'll know how to select data from them, flatten them when you need clean output, stack and unstack levels to reshape the structure, and apply real analysis on top. This is the kind of competence that separates someone who runs pivot tables from someone who builds reusable data pipelines.

What you'll learn:

• Why MultiIndex columns and rows arise from groupby and pivot_table, and what the structure actually looks like
• How to select, filter, and slice data from MultiIndex DataFrames using .loc, .xs, and tuple indexing
• How to flatten MultiIndex columns into plain string names for clean output
• How to use stack and unstack to pivot between row-level and column-level hierarchy
• How to build a realistic multi-dimensional sales analysis that demonstrates all of these techniques together

---

Prerequisites

You should already be comfortable with:

• Core pandas operations — loading data, filtering rows, and working with columns (covered in Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks)
• Basic groupby and aggregation (covered in Grouping and Aggregating in pandas: groupby as the PivotTable Replacement)
• Basic reshaping concepts like pivot_table, melt, and stack (covered in Reshaping Data with pivot_table, melt, and stack in pandas)

---

How MultiIndex Structures Are Born

Let's start with the dataset we'll use throughout this lesson. Imagine you're a data analyst at a regional retailer with stores across multiple regions. You have a transaction-level sales file with order dates, categories, regions, reps, and financial figures.

import pandas as pd
import numpy as np

# Realistic sales dataset
np.random.seed(42)

n = 500
regions = ['Northeast', 'Southeast', 'Midwest', 'West']
categories = ['Electronics', 'Apparel', 'Home & Garden', 'Sporting Goods']
reps = ['Alice', 'Bob', 'Carol', 'David', 'Eve']

df = pd.DataFrame({
    'order_date': pd.date_range('2023-01-01', periods=n, freq='D').to_series().sample(n, replace=True).values,
    'region': np.random.choice(regions, n),
    'category': np.random.choice(categories, n),
    'rep': np.random.choice(reps, n),
    'revenue': np.random.uniform(200, 5000, n).round(2),
    'units': np.random.randint(1, 50, n),
    'cost': np.random.uniform(100, 3000, n).round(2),
})

df['order_date'] = pd.to_datetime(df['order_date'])
df['profit'] = (df['revenue'] - df['cost']).round(2)

print(df.shape)
print(df.dtypes)
print(df.head())

Now let's create a MultiIndex — first through groupby with multiple aggregation functions:

# Multiple aggregation functions on a single groupby
summary = df.groupby(['region', 'category']).agg(
    total_revenue=('revenue', 'sum'),
    avg_revenue=('revenue', 'mean'),
    total_units=('units', 'sum'),
    total_profit=('profit', 'sum'),
    order_count=('revenue', 'count')
).round(2)

print(summary)
print(type(summary.index))  # MultiIndex on rows

This gives you a MultiIndex on the rows — the index has two levels: region and category. Now look at this alternative:

# Using agg with a dictionary — creates MultiIndex on COLUMNS
summary_multi = df.groupby(['region', 'category']).agg({
    'revenue': ['sum', 'mean', 'std'],
    'profit': ['sum', 'mean'],
    'units': 'sum'
})

print(summary_multi)
print(summary_multi.columns)  # MultiIndex on columns

The output shows something like:

          revenue                    profit              units
               sum      mean      std    sum    mean     sum
region    category
Midwest   Apparel   ...       ...       ...    ...    ...     ...

The columns are now a MultiIndex with two levels: the original column name and the aggregation function. This is the structure that confuses everyone the first time.

pivot_table creates it too — but usually with MultiIndex on rows:

# pivot_table with multiple index and column levels
pt = pd.pivot_table(
    df,
    values='revenue',
    index=['region', 'category'],
    columns='rep',
    aggfunc='sum',
    fill_value=0
).round(2)

print(pt)
print(type(pt.index))    # MultiIndex
print(type(pt.columns))  # Index (single level — just rep names)

---

Understanding the MultiIndex Object

Before you can work with a MultiIndex, you need to see it clearly. Let's inspect it.

# Look at the column MultiIndex from our agg example
print(summary_multi.columns)
# MultiIndex([( 'revenue',  'sum'),
#             ( 'revenue', 'mean'),
#             ( 'revenue',  'std'),
#             (  'profit',  'sum'),
#             (  'profit', 'mean'),
#             (  'units',   'sum')],
#            )

print(summary_multi.columns.names)   # ['None', 'None'] — unnamed levels
print(summary_multi.columns.nlevels) # 2

# Look at the row MultiIndex
print(summary_multi.index)
# MultiIndex([( 'Midwest',        'Apparel'),
#             ( 'Midwest', 'Electronics'),
#             ...
#            names=['region', 'category'])

print(summary_multi.index.names)     # ['region', 'category']
print(summary_multi.index.nlevels)   # 2

The named row index comes from the groupby key columns — those names are preserved. The column MultiIndex is unnamed by default (you'll fix that shortly). Each element in a MultiIndex is a tuple of values, one per level.

You can extract individual levels:

# Get unique values at each level of the column MultiIndex
print(summary_multi.columns.get_level_values(0))  # ['revenue', 'revenue', 'revenue', 'profit', ...]
print(summary_multi.columns.get_level_values(1))  # ['sum', 'mean', 'std', 'sum', ...]

# Get unique values at each level of the row MultiIndex
print(summary_multi.index.get_level_values('region').unique())
print(summary_multi.index.get_level_values('category').unique())

---

Selecting Data from MultiIndex Columns

This is where most people get stuck. You try summary_multi['revenue'] and get a sub-DataFrame. You try summary_multi['revenue']['sum'] and it works but feels awkward. Here's the full toolkit.

Selecting a top-level column group

# Select all 'revenue' columns — returns a DataFrame
revenue_cols = summary_multi['revenue']
print(revenue_cols)
#                       sum      mean       std
# region    category
# Midwest   Apparel    ...      ...        ...

This is legitimate and useful — you often want to hand off just the revenue metrics to further analysis.

Selecting a specific metric with a tuple

# Select a single column using a tuple — returns a Series
rev_sum = summary_multi[('revenue', 'sum')]
print(rev_sum)
print(type(rev_sum))  # Series

# Or chained — less explicit but equivalent
rev_sum_alt = summary_multi['revenue']['sum']

Using `.loc` with MultiIndex columns

When you combine MultiIndex rows AND columns, .loc requires careful syntax:

# Both rows and columns have hierarchy
# Select specific region (row level 0), all columns
midwest = summary_multi.loc['Midwest']
print(midwest)

# Select specific region AND category
midwest_apparel = summary_multi.loc[('Midwest', 'Apparel')]
print(midwest_apparel)

# Select specific metric for a specific row
one_value = summary_multi.loc[('Midwest', 'Apparel'), ('revenue', 'sum')]
print(one_value)

Using `.xs` (cross-section) for cleaner slicing

.xs is purpose-built for selecting a single level value from a MultiIndex. It's cleaner than .loc when you want to fix one level without specifying the other.

# Select all 'Midwest' rows using xs on the row index
midwest_xs = summary_multi.xs('Midwest', level='region')
print(midwest_xs)

# Select all 'sum' columns using xs on the column axis
sums_xs = summary_multi.xs('sum', level=1, axis=1)
print(sums_xs)
# Returns: revenue sum, profit sum, units sum — all in one clean DataFrame

# You can also use xs to go deeper on both axes
midwest_sums = summary_multi.xs('Midwest', level='region').xs('sum', level=1, axis=1)
print(midwest_sums)

---

Selecting Data from MultiIndex Rows

The row MultiIndex is common when you've grouped by multiple columns or used pivot_table with multiple index columns. The tools here mirror what we just saw, but the syntax has some quirks.

# Use our summary DataFrame with MultiIndex rows and regular columns
print(summary.head(10))
print(summary.index)
# MultiIndex([('Midwest',        'Apparel'),
#             ('Midwest',  'Electronics'),
#             ('Midwest', 'Home & Garden'),
#             ...
#            ], names=['region', 'category'])

Basic `.loc` selection

# Select one top-level key — returns all categories in Midwest
summary.loc['Midwest']

# Select a specific combination
summary.loc[('Midwest', 'Apparel')]

# Select a range — uses slice
summary.loc['Midwest':'Northeast']

Slicing with `pd.IndexSlice`

For more surgical slicing across both levels, pd.IndexSlice gives you readable syntax without tuple gymnastics:

idx = pd.IndexSlice

# Select specific regions, all categories, specific columns
subset = summary.loc[idx[['Midwest', 'West'], :], ['total_revenue', 'total_profit']]
print(subset)

# Select specific category across all regions
electronics = summary.loc[idx[:, 'Electronics'], :]
print(electronics)

pd.IndexSlice is underused. The : notation means "all values at this level," which lets you specify exactly which levels you're filtering and which you're keeping open.

Sorting the index matters

MultiIndex .loc slicing requires the index to be sorted, or you'll get a UnsortedIndexError. Always sort before slicing:

summary_sorted = summary.sort_index()
# Now slicing works reliably
subset = summary_sorted.loc[idx['Midwest':'Northeast', 'Apparel':'Electronics'], :]

---

Flattening MultiIndex Columns for Clean Output

Here's the practical reality: MultiIndex columns are great for analysis, but when you're exporting to Excel, writing to a database, or handing a DataFrame to a downstream function, you need plain string column names. Flattening is the most common operation after building a multi-aggregation summary.

Method 1: Join the levels with a separator

# Flatten MultiIndex columns by joining level values
summary_multi.columns = ['_'.join(col).strip() for col in summary_multi.columns.values]
print(summary_multi.columns)
# Index(['revenue_sum', 'revenue_mean', 'revenue_std', 'profit_sum', 'profit_mean', 'units_sum'])

This is the standard approach. The .values gives you the tuples, and you join each tuple into a single string.

Method 2: Rename with `.to_flat_index()` and a mapping

# More explicit control over names
flat_index = summary_multi.columns.to_flat_index()
print(flat_index)  # [('revenue', 'sum'), ('revenue', 'mean'), ...]

# Map to custom names
name_map = {
    ('revenue', 'sum'): 'total_revenue',
    ('revenue', 'mean'): 'avg_revenue',
    ('revenue', 'std'): 'revenue_stddev',
    ('profit', 'sum'): 'total_profit',
    ('profit', 'mean'): 'avg_profit',
    ('units', 'sum'): 'total_units',
}

summary_multi.columns = [name_map.get(col, '_'.join(col)) for col in flat_index]
print(summary_multi.columns)

This is worth doing for output that other people will read. revenue_sum is fine internally; total_revenue is what goes in the report.

Method 3: Use named aggregations to avoid MultiIndex altogether

If you know you'll need flat columns, just use the named aggregation syntax from the start:

# Named aggregations produce a flat column index
summary_flat = df.groupby(['region', 'category']).agg(
    total_revenue=('revenue', 'sum'),
    avg_revenue=('revenue', 'mean'),
    revenue_stddev=('revenue', 'std'),
    total_profit=('profit', 'sum'),
    avg_profit=('profit', 'mean'),
    total_units=('units', 'sum')
).round(2)

print(summary_flat.columns)
# Index(['total_revenue', 'avg_revenue', 'revenue_stddev', 'total_profit', 'avg_profit', 'total_units'])

This is the cleanest approach when you're building pipeline output. Reserve the dictionary-of-lists syntax for exploratory work where you need to quickly compare multiple stats on the same column.

---

Resetting the Row Index

After groupby, your grouping columns become the index. This is often inconvenient — you want them as regular columns for filtering, exporting, or merging. Use reset_index():

# Move MultiIndex levels back to regular columns
summary_reset = summary_flat.reset_index()
print(summary_reset.columns)
# Index(['region', 'category', 'total_revenue', 'avg_revenue', ...])
print(summary_reset.dtypes)

Now region and category are plain columns again. You can use Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks techniques on them without any MultiIndex considerations.

For the reverse — turning columns into an index — use set_index():

# Reconstruct the MultiIndex from columns
summary_indexed = summary_reset.set_index(['region', 'category'])
print(summary_indexed.index)

---

Stack and Unstack: Pivoting the Hierarchy

stack and unstack are the tools for moving levels between the row index and column index. They're how you reshape a wide aggregation result into a long format suitable for analysis or visualization, or vice versa.

`unstack`: Move a row level into columns

# Start with our MultiIndex row summary (region x category)
print(summary_flat.head())

# Unstack 'category' — each category becomes a column group
wide = summary_flat['total_revenue'].unstack('category')
print(wide)
#                  Apparel  Electronics  Home & Garden  Sporting Goods
# region
# Midwest          ...      ...          ...            ...
# Northeast        ...      ...          ...            ...

Now you have a clean region × category matrix for revenue — exactly what you'd want for a heatmap or an Excel table. You can unstack any level by name or position.

# Unstack with multiple columns in the aggregation
wide_full = summary_flat.unstack('category')
print(wide_full)
print(wide_full.columns)
# MultiIndex: (metric_name, category_name) for every combination

When you unstack a MultiIndex row index into columns, you get a MultiIndex on the columns. This is the hierarchy expanding outward.

`stack`: Move a column level into rows

The reverse operation pulls column levels into the row index:

# Start with pivot_table output (region x rep, with revenue values)
pt_reset = pt.copy()  # Our pivot table: regions as rows, reps as columns

# Stack 'rep' level into the row index
long = pt_reset.stack('rep')
print(long)
print(type(long.index))  # MultiIndex: (region, category, rep)

stack is what you use when you need to go from wide format (one column per category or rep) to long format (one row per observation). This is the pandas equivalent of Excel's Power Query "Unpivot Columns."

A practical reshape: long to wide and back

# Build a meaningful multi-level structure
monthly = df.copy()
monthly['month'] = monthly['order_date'].dt.to_period('M')

# Aggregate: region x month, then unstack month into columns
region_monthly = monthly.groupby(['region', 'month'])['revenue'].sum().round(2)
print(region_monthly.head(10))
print(type(region_monthly.index))  # MultiIndex

# Wide format: regions as rows, months as columns
region_monthly_wide = region_monthly.unstack('month')
print(region_monthly_wide)
# This is a clean time-series table, perfect for a report

# Stack it back to long for plotting
region_monthly_long = region_monthly_wide.stack('month').reset_index()
print(region_monthly_long.head())
# Now it's: region | month | revenue — great for seaborn line charts

This long format feeds directly into visualization libraries. See Visualizing Data with matplotlib and seaborn: Charts That Explain Your Analysis for how to build those charts from this structure.

---

Multi-Level Pivot Tables

pivot_table can create MultiIndex on both axes simultaneously when you specify multiple values or multiple index columns:

# Multiple values — creates MultiIndex on columns
pt_multi = pd.pivot_table(
    df,
    values=['revenue', 'profit', 'units'],
    index=['region', 'category'],
    columns='rep',
    aggfunc='sum',
    fill_value=0
).round(2)

print(pt_multi.columns)
# MultiIndex: (metric, rep_name)
print(pt_multi.index)
# MultiIndex: (region, category)

Now both axes are hierarchical. Selecting a value requires specifying both levels on both axes — this is where xs really earns its keep:

# Select revenue for all reps in Midwest
midwest_revenue = pt_multi.xs('Midwest', level='region')['revenue']
print(midwest_revenue)

# Select Alice's numbers for all region/category combos
alice_all = pt_multi.xs('Alice', level='rep', axis=1)
print(alice_all)

# Select revenue only, for all regions and reps
revenue_only = pt_multi['revenue']
print(revenue_only)
# Clean DataFrame: region x category rows, rep columns

Flattening this for output:

# Flatten column MultiIndex
pt_multi_flat = pt_multi.copy()
pt_multi_flat.columns = [f"{metric}_{rep}" for metric, rep in pt_multi_flat.columns]
pt_multi_flat = pt_multi_flat.reset_index()
print(pt_multi_flat.head())

Now you have a flat DataFrame that can go directly to Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work or any downstream system.

---

Hands-On Exercise: Building a Multi-Dimensional Sales Report

Let's put everything together. Your task is to build a complete regional performance report that answers three questions:

1. Which region × category combinations generate the most revenue and profit margin?
2. How does each region perform month-over-month across the year?
3. Which sales reps outperform the regional average in each category?

Step 1: Region × Category Performance Summary

# Core aggregation
perf = df.groupby(['region', 'category']).agg(
    total_revenue=('revenue', 'sum'),
    total_cost=('cost', 'sum'),
    total_profit=('profit', 'sum'),
    total_units=('units', 'sum'),
    order_count=('revenue', 'count')
).round(2)

# Calculate derived metrics
perf['avg_order_value'] = (perf['total_revenue'] / perf['order_count']).round(2)
perf['profit_margin_pct'] = (perf['total_profit'] / perf['total_revenue'] * 100).round(1)

# Sort by profit margin within each region
perf_sorted = perf.sort_values(['region', 'profit_margin_pct'], ascending=[True, False])
print(perf_sorted)

# Find the best category per region
best_category = perf_sorted.groupby(level='region')['profit_margin_pct'].idxmax()
print("\nBest category by margin per region:")
print(perf_sorted.loc[best_category, 'profit_margin_pct'])

Step 2: Monthly Revenue Trend by Region

# Monthly aggregation
df['month'] = df['order_date'].dt.to_period('M')

monthly_region = df.groupby(['region', 'month'])['revenue'].sum().round(2)

# Unstack months into columns for a time-series table
trend_wide = monthly_region.unstack('month')

# Fill missing months with 0
trend_wide = trend_wide.fillna(0)

# Add a total and month-over-month change for the last two months
trend_wide['total'] = trend_wide.sum(axis=1).round(2)

# Get last two month columns (sorted)
month_cols = sorted([c for c in trend_wide.columns if c != 'total'])
if len(month_cols) >= 2:
    last = month_cols[-1]
    second_last = month_cols[-2]
    trend_wide['mom_change_pct'] = (
        (trend_wide[last] - trend_wide[second_last]) / trend_wide[second_last] * 100
    ).round(1)

print(trend_wide[['total', 'mom_change_pct']])

Step 3: Rep Performance vs. Regional Average

# Rep-level aggregation
rep_perf = df.groupby(['region', 'category', 'rep']).agg(
    total_revenue=('revenue', 'sum'),
    order_count=('revenue', 'count')
).round(2)
rep_perf['avg_order_value'] = (rep_perf['total_revenue'] / rep_perf['order_count']).round(2)

# Calculate regional category average (two-level groupby on the MultiIndex)
regional_avg = rep_perf['total_revenue'].groupby(level=['region', 'category']).transform('mean').round(2)

# Compare rep to average
rep_perf['regional_avg_revenue'] = regional_avg
rep_perf['vs_avg_pct'] = (
    (rep_perf['total_revenue'] - rep_perf['regional_avg_revenue']) 
    / rep_perf['regional_avg_revenue'] * 100
).round(1)

# Find reps who outperform in every region/category
outperformers = rep_perf[rep_perf['vs_avg_pct'] > 0].sort_values('vs_avg_pct', ascending=False)
print(outperformers.head(10))

# Unstack rep for a comparison view
rep_revenue_wide = rep_perf['total_revenue'].unstack('rep').fillna(0)
print(rep_revenue_wide)

Step 4: Export the Report

# Flatten everything and prepare for export
perf_export = perf_sorted.reset_index()
trend_export = trend_wide.reset_index()
rep_export = rep_revenue_wide.reset_index()

# Export to Excel with multiple sheets
with pd.ExcelWriter('sales_report.xlsx', engine='openpyxl') as writer:
    perf_export.to_excel(writer, sheet_name='Region_Category_Summary', index=False)
    trend_export.to_excel(writer, sheet_name='Monthly_Trends', index=False)
    rep_export.to_excel(writer, sheet_name='Rep_Comparison', index=False)

print("Report exported successfully.")

This is a complete, three-sheet analysis report built entirely from MultiIndex operations. For more on working with dates and time periods in the monthly trend step, see Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows.

---

Common Mistakes & Troubleshooting

Mistake 1: Trying to access a MultiIndex column with a plain string

# This fails if 'revenue' is a MultiIndex top level, not a single column
summary_multi['revenue_sum']   # KeyError

# This works
summary_multi[('revenue', 'sum')]
# Or after flattening:
summary_flat['revenue_sum']    # Works fine

Mistake 2: Forgetting that `reset_index()` is needed for filtering

After groupby, your grouping columns are in the index — not filterable with normal boolean masks on columns.

# This fails — 'region' is in the index, not a column
summary_flat[summary_flat['region'] == 'Midwest']   # KeyError

# Fix 1: Reset the index first
summary_flat.reset_index().query("region == 'Midwest'")

# Fix 2: Filter using .loc on the index
summary_flat.loc['Midwest']

Mistake 3: Stacking/unstacking without handling NaN

When you unstack, combinations that don't exist in your data become NaN. Always decide whether to fill them:

# Unstack may introduce NaN for missing combinations
wide = region_monthly.unstack('month')
wide.isna().sum().sum()  # Check how many gaps you have

# Fill based on your business logic
wide.fillna(0, inplace=True)    # Revenue of 0 for months with no sales
# OR
wide.fillna(method='ffill', axis=1)  # Forward-fill across months (for running values)

Mistake 4: Column names after flattening are wrong

# When one level has empty strings (e.g., single-function agg)
summary = df.groupby('region').agg({'revenue': 'sum'})
# Columns: MultiIndex([('revenue', 'sum')])
# After join: 'revenue_sum' — fine

# But sometimes the second level is empty:
summary2 = df.groupby('region').agg({'revenue': ['sum'], 'units': 'sum'})
summary2.columns = ['_'.join(col).strip('_') for col in summary2.columns]
# Use .strip('_') to handle cases where one level is empty string

Mistake 5: Using `.loc` with a single string on a MultiIndex and getting a DataFrame when you expected a row

# This returns a DataFrame (all categories in Midwest) — not a single row
summary.loc['Midwest']

# This returns a Series (the specific Midwest + Apparel row)
summary.loc[('Midwest', 'Apparel')]

The behavior is correct, but it surprises people. The first .loc['Midwest'] drops the first index level and returns everything at that level.

Mistake 6: `stack` is deprecated syntax in newer pandas

In pandas 2.1+, the calling convention for stack changed. If you get a FutureWarning:

# Old syntax (pandas < 2.1)
long = wide.stack()

# New syntax — specify the level explicitly
long = wide.stack(future_stack=True)   # pandas 2.1 transition
# Or just explicitly name the level
long = wide.stack(level=-1)

---

Summary & Next Steps

You now have a complete toolkit for working with multi-level data in pandas:

• You understand why MultiIndex arises from groupby and pivot_table operations, and how to diagnose whether hierarchy is on rows, columns, or both
• You can select from MultiIndex DataFrames using tuple indexing, .loc with pd.IndexSlice, and .xs for cross-section slices
• You know three approaches to flattening MultiIndex columns — join-based, mapping-based, and prevention through named aggregations
• You can reshape between row-level and column-level hierarchy using stack and unstack, and you know when to apply each
• You've built a complete multi-dimensional sales report that demonstrates all these techniques on realistic data

The natural next topics to build on this foundation:

• For time-series grouping and resampling patterns, dig into Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows
• For calculating running totals and rankings within groups — useful after you've built a MultiIndex summary — see Calculating Running Totals, Cumulative Averages, and Ranked Rows in pandas with expanding, cumsum, and rank
• Once your analysis is solid, turn it into a formatted, automated report with Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work

MultiIndex mastery is one of those competencies that pays compound dividends. Once you stop fighting the structure and start using it deliberately, your analysis code gets shorter, your reports get more flexible, and the gap between "raw data" and "insight" shrinks significantly.