Pandas: MultiIndex

Q: How do I flatten a MultiIndex?

For row-level MultiIndex → use df.reset_index() to convert levels into regular columns. For column-level MultiIndex → rename columns by joining level values: ['_'.join(col) for col in df.columns] . After groupby, use as_index=False to avoid creating a MultiIndex (but you lose hierarchical selection capability).

Q: What is get_level_values for?

df.index.get_level_values('city') returns all values at a given level (e.g., ['NYC','NYC','LA','LA',...]). Useful for conditional selection: df[df.index.get_level_values('city') == 'NYC'] . Also works with groupby: df.groupby(df.index.get_level_values(0)).sum() .

Last updated: 2026-08-26

When data has a hierarchical structure — grouped by city then by product, or by year then by quarter — a single-level index is not enough. MultiIndex (hierarchical indexing) gives layered data a layered index: select the first level, then drill into the second, navigating just like a folder hierarchy. This section covers creating, selecting, reordering, and combining MultiIndex with stack/unstack.

⚠️ Note: The code below requires a local Python environment to run.

1. What You Will Learn

❶ MultiIndex concepts
❷ Creation methods (from_tuples / from_product / from_frame)
❸ Hierarchical selection (loc / xs)
❹ swaplevel / reorder_levels
❺ sort_index and flattening

2. Bob's Multi-City, Multi-Category Sales Data

(1) Pain Point: Two-Level Grouped Results Are Hard to Select

After grouping by city + category, Bob ends up with a two-level index and doesn't know how to select a specific city:

PYTHON

import pandas as pd

df = pd.DataFrame({
    'city': ['NYC', 'NYC', 'LA', 'LA', 'Chicago', 'Chicago'],
    'category': ['Electronics', 'Clothing', 'Electronics', 'Clothing', 'Electronics', 'Clothing'],
    'sales': [5000, 800, 3000, 600, 2000, 400]
})
result = df.groupby(['city', 'category'])['sales'].sum()
print(result)
# city     category
# Chicago  Clothing       400
#          Electronics   2000
# LA       Clothing       600
#          Electronics   3000
# NYC      Clothing       800
#          Electronics   5000
# How to select just NYC?

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) Solution: MultiIndex Selection

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: MultiIndex Selection (Difficulty ⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'city': ['NYC', 'NYC', 'LA', 'LA', 'Chicago', 'Chicago'],
    'category': ['Electronics', 'Clothing', 'Electronics', 'Clothing', 'Electronics', 'Clothing'],
    'sales': [5000, 800, 3000, 600, 2000, 400]
})
result = df.groupby(['city', 'category'])['sales'].sum()

# Select by MultiIndex label
print(result.loc['NYC'])
# category
# Clothing       800
# Electronics   5000

# Select specific (city, category)
print(result.loc[('NYC', 'Electronics')])  # 5000

# xs: cross-section selection
print(result.xs('Electronics', level='category'))
# city
# Chicago    2000
# LA         3000
# NYC        5000

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

3. Creating a MultiIndex

(1) Three Creation Methods

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: MultiIndex Creation Methods (Difficulty ⭐⭐)

PYTHON

import pandas as pd

# 1. from_tuples — explicit pairs
index = pd.MultiIndex.from_tuples([
    ('NYC', 'Electronics'), ('NYC', 'Clothing'),
    ('LA', 'Electronics'), ('LA', 'Clothing')
], names=['city', 'category'])
df1 = pd.DataFrame({'sales': [5000, 800, 3000, 600]}, index=index)
print("from_tuples:")
print(df1)

# 2. from_product — cartesian product (most common)
index2 = pd.MultiIndex.from_product(
    [['NYC', 'LA', 'Chicago'], ['Electronics', 'Clothing']],
    names=['city', 'category']
)
df2 = pd.DataFrame({
    'sales': [5000, 800, 3000, 600, 2000, 400]
}, index=index2)
print("\nfrom_product:")
print(df2)

# 3. from_frame — from DataFrame
df_index = pd.DataFrame({
    'city': ['NYC', 'NYC', 'LA', 'LA'],
    'category': ['Electronics', 'Clothing', 'Electronics', 'Clothing']
})
index3 = pd.MultiIndex.from_frame(df_index)
df3 = pd.DataFrame({'sales': [5000, 800, 3000, 600]}, index=index3)

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

4. Hierarchical Selection

(1) loc vs xs

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Hierarchical Selection Methods (Difficulty ⭐⭐)

PYTHON

import pandas as pd

index = pd.MultiIndex.from_product(
    [['NYC', 'LA'], ['Electronics', 'Clothing', 'Home']],
    names=['city', 'category']
)
df = pd.DataFrame({
    'sales': [5000, 800, 300, 3000, 600, 200],
    'profit': [1500, 200, 50, 800, 100, 30]
}, index=index)

# loc: select first level
print(df.loc['NYC'])

# loc: select both levels
print(df.loc[('NYC', 'Electronics')])

# xs: cross-section (select at ANY level)
print(df.xs('Electronics', level='category'))
# Returns all cities' Electronics data

# xs with drop_level=False
print(df.xs('NYC', level='city', drop_level=False))

# Partial selection with slice
print(df.loc[('NYC', slice(None)), :])  # all categories in NYC

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) loc vs xs Comparison

Feature	loc	xs
Select one level	✅ `loc['NYC']`	✅ `xs('NYC', level=0)`
Select second level	Complex `loc[(slice(None),'Elec')]`	Simple `xs('Elec', level=1)`
Multiple levels at once	✅ `loc[('NYC','Elec')]`	❌ Only one level at a time
Preserve level	Preserved by default	Controlled by drop_level

5. swaplevel / sort_index

(1) Swapping Levels

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: swaplevel for Reordering Levels (Difficulty ⭐⭐)

PYTHON

import pandas as pd

index = pd.MultiIndex.from_product(
    [['NYC', 'LA'], ['Q1', 'Q2', 'Q3', 'Q4']],
    names=['city', 'quarter']
)
df = pd.DataFrame({
    'sales': [1200, 1300, 1100, 1400, 800, 900, 700, 1000]
}, index=index)

# Swap levels
df_swapped = df.swaplevel()
print(df_swapped.head(4))
# city    quarter
# Q1      NYC        1200
# Q2      NYC        1300
# Q3      NYC        1100
# Q4      NYC        1400

# sort_index after swap (important!)
df_sorted = df_swapped.sort_index()
print(df_sorted.head(4))

# reorder_levels: arbitrary reordering
df_reordered = df.reorder_levels(['quarter', 'city'])

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

⚠️ Note: After swaplevel or reorder_levels, you must call sort_index() — otherwise subsequent selection operations may produce errors or run slowly.

6. MultiIndex with stack/unstack

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: stack/unstack with MultiIndex (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'city': ['NYC', 'NYC', 'LA', 'LA'],
    'category': ['Electronics', 'Clothing', 'Electronics', 'Clothing'],
    'Q1': [1200, 400, 800, 200],
    'Q2': [1300, 500, 900, 300]
})

# set_index → MultiIndex DataFrame
multi_df = df.set_index(['city', 'category'])
print(multi_df)

# unstack: category level → columns
wide = multi_df.unstack(level='category')
print(wide)
#          Q1                 Q2
# category Clothing Electronics  Clothing Electronics
# city
# LA            200         800       300         900
# NYC           400        1200       500        1300

# stack: columns → index level (reverse)
back = wide.stack(level='category')
print(back)

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

7. Flattening with reset_index

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Flattening a MultiIndex (Difficulty ⭐)

PYTHON

import pandas as pd

index = pd.MultiIndex.from_product(
    [['NYC', 'LA'], ['Q1', 'Q2']],
    names=['city', 'quarter']
)
df = pd.DataFrame({'sales': [1200, 1300, 800, 900]}, index=index)

# reset_index: MultiIndex → regular columns
flat = df.reset_index()
print(flat)
#    city quarter  sales
# 0   NYC      Q1   1200
# 1   NYC      Q2   1300
# 2    LA      Q1    800
# 3    LA      Q2    900

# Flatten column MultiIndex
wide = df.unstack()
flat_cols = wide.copy()
flat_cols.columns = [f'{a}_{b}' for a, b in flat_cols.columns]
print(flat_cols)

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

8. Full Example: Multi-City, Multi-Category Sales Analysis

(6) ▶ MultiIndex Hierarchical Structure

graph TB
    A[DataFrame] --> B[Outer index: city]
    B --> C[NYC]
    B --> D[LA]
    B --> E[Chicago]
    C --> F[Inner index: category]
    F --> G[Electronics]
    F --> H[Clothing]
    F --> I[Home]
    D --> J[Electronics]
    D --> K[Clothing]
    E --> L[Electronics]
    E --> M[Clothing]

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Full MultiIndex Workflow Analysis (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd
import numpy as np

# ============================================
# Comprehensive example: Multi-city Multi-category
# sales analysis with MultiIndex
# ============================================

# 1. Create hierarchical data
np.random.seed(42)
index = pd.MultiIndex.from_product(
    [['NYC', 'LA', 'Chicago', 'Houston'],
     ['Electronics', 'Clothing', 'Home'],
     ['Q1', 'Q2', 'Q3', 'Q4']],
    names=['city', 'category', 'quarter']
)
df = pd.DataFrame({
    'sales': np.random.randint(100, 5000, 48),
    'profit': np.random.randint(10, 1500, 48)
}, index=index)

# 2. View structure
print(f"Levels: {df.index.names}")
print(f"Shape: {df.shape}")

# 3. Select by city
print("\n=== NYC Sales ===")
print(df.loc['NYC', 'sales'].unstack(level='quarter'))

# 4. xs: all Electronics across cities
print("\n=== Electronics by City ===")
elec = df.xs('Electronics', level='category')[['sales']]
print(elec.unstack(level='quarter'))

# 5. swaplevel → sort → select
df2 = df.swaplevel('city', 'quarter').sort_index()
print("\n=== Q1 across cities ===")
print(df2.loc['Q1'])

# 6. Unstack for wide report
report = df['sales'].unstack(level=['category', 'quarter'])
print("\n=== Wide Report (first 2 cities) ===")
print(report.head(2))

# 7. Reset for export
flat = df.reset_index()
print(f"\nFlat shape: {flat.shape}")

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

❓ FAQ

Q When should I use a MultiIndex?

A When your data has a natural hierarchical structure — city + category, year + quarter, department + team. Grouping by multiple columns automatically produces a MultiIndex, and set_index with multiple columns does too. For simple data, a single-level index is more convenient — don't force a MultiIndex where it isn't needed.

Q What's the difference between xs and loc?

A xs can select at any level (e.g., directly select category='Electronics' without first selecting city). loc can only select from the outer level inward (city first, then category). xs is more flexible but cannot select multiple levels simultaneously; loc supports loc[('NYC','Electronics')] but second-level selection syntax is more complex.

Q Does swaplevel change the data?

A No — swaplevel only swaps the order of index levels; the data itself remains unchanged. However, you must call sort_index() after swapping, otherwise subsequent selections may produce errors or poor performance. Recommended workflow: swaplevel → sort_index → select.

Q How do I flatten a MultiIndex?

A For row-level MultiIndex → use df.reset_index() to convert levels into regular columns. For column-level MultiIndex → rename columns by joining level values: ['_'.join(col) for col in df.columns]. After groupby, use as_index=False to avoid creating a MultiIndex (but you lose hierarchical selection capability).

Q How is MultiIndex performance?

A MultiIndex lookups are O(1) (hash-based under the hood), so performance is excellent. However, efficiency is only guaranteed after sort_index() — an unsorted MultiIndex selection can degrade to O(n). For large datasets: call sort_index() immediately after creation, and use is_unique to verify uniqueness.

Q What is get_level_values for?

A df.index.get_level_values('city') returns all values at a given level (e.g., ['NYC','NYC','LA','LA',...]). Useful for conditional selection: df[df.index.get_level_values('city') == 'NYC']. Also works with groupby: df.groupby(df.index.get_level_values(0)).sum().

Q How do I choose between from_product and from_tuples?

A from_product generates the Cartesian product (all combinations) — ideal for complete grid data (3 cities × 4 quarters = 12 rows). from_tuples specifies exact combinations — ideal for incomplete data (some cities missing certain categories). from_product is more concise; from_tuples is more precise.

📖 Summary

MultiIndex provides hierarchical indexing for layered data, enabling folder-like navigation
Creation: from_product (complete grid), from_tuples (specific combinations), from_frame (convert from DataFrame)
Selection: loc goes outer-to-inner; xs cross-selects at any level
swaplevel/reorder_levels swap level order — always call sort_index() afterward
unstack moves row levels to columns; stack moves column levels to rows — both work naturally with MultiIndex
reset_index flattens row-level MultiIndex; renaming columns flattens column-level MultiIndex

📝 Exercises

Basic (Difficulty ⭐): Use from_product to create a MultiIndex DataFrame with 3 cities × 2 categories, then use loc and xs to select a specific city and a specific category respectively.
Intermediate (Difficulty ⭐⭐): Create a city × quarter MultiIndex DataFrame, swaplevel to swap levels → sort_index → unstack to a wide table → stack back and verify the round-trip.
Challenge (Difficulty ⭐⭐⭐): Simulate 48 rows of data with 4 regions × 3 categories × 4 quarters, then complete: xs cross-level selection → swaplevel reordering → multi-level unstack to wide format → reset_index for export → groupby to re-aggregate.

← Previous: Advanced Time Series · Next: Window Functions →

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Pandas: MultiIndex

1. What You Will Learn

2. Bob's Multi-City, Multi-Category Sales Data

(1) Pain Point: Two-Level Grouped Results Are Hard to Select

(2) Solution: MultiIndex Selection

▶ Example

3. Creating a MultiIndex

(1) Three Creation Methods

▶ Example

4. Hierarchical Selection

(1) loc vs xs

▶ Example

(2) loc vs xs Comparison

5. swaplevel / sort_index

(1) Swapping Levels

▶ Example

6. MultiIndex with stack/unstack

▶ Example

7. Flattening with reset_index

▶ Example

8. Full Example: Multi-City, Multi-Category Sales Analysis

(6) ▶ MultiIndex Hierarchical Structure

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好