Practice Now
Pandas
Python
DataFrame
Hierarchical Index
Data Manipulation

How to flatten a hierarchical index in columns

ML System Design practice on Codemia

Design recommenders, ranking systems and training pipelines the way ML interviews actually ask for them, with worked solutions.

Practice ML system design
Data SciencePython

Introduction

To flatten a hierarchical (MultiIndex) column index in a Pandas DataFrame, join the level names into single strings using a list comprehension like ['_'.join(col).strip() for col in df.columns]. This converts multi-level column headers (e.g., ('price', 'mean')) into flat strings (e.g., 'price_mean'). Flattening is commonly needed after groupby().agg() operations that create MultiIndex columns, or before exporting to CSV/Excel where hierarchical columns are not supported.

How MultiIndex Columns Are Created

python
1import pandas as pd
2import numpy as np
3
4df = pd.DataFrame({
5    'category': ['A', 'A', 'B', 'B', 'A', 'B'],
6    'region': ['East', 'West', 'East', 'West', 'East', 'West'],
7    'price': [10, 20, 30, 40, 15, 25],
8    'quantity': [100, 200, 150, 300, 120, 180]
9})
10
11# groupby + agg creates MultiIndex columns
12summary = df.groupby('category').agg({'price': ['mean', 'sum'], 'quantity': ['min', 'max']})
13print(summary.columns)
14# MultiIndex([('price', 'mean'), ('price', 'sum'),
15#             ('quantity', 'min'), ('quantity', 'max')],)

The resulting DataFrame has two-level column headers. Accessing columns requires tuples: summary[('price', 'mean')].

Method 1: Join Column Levels with Underscore

python
1# Flatten by joining level names
2summary.columns = ['_'.join(col).strip() for col in summary.columns]
3print(summary.columns)
4# Index(['price_mean', 'price_sum', 'quantity_min', 'quantity_max'])
5
6print(summary)
7#          price_mean  price_sum  quantity_min  quantity_max
8# category
9# A              15.0         45           100           200
10# B              31.67        95           150           300

This is the most common approach. '_'.join(col) concatenates each tuple's elements with an underscore.

Method 2: get_level_values (Keep One Level)

python
1# Keep only the second level (aggregation function names)
2summary.columns = summary.columns.get_level_values(1)
3print(summary.columns)
4# Index(['mean', 'sum', 'min', 'max'])
5
6# Keep only the first level (column names)
7summary.columns = summary.columns.get_level_values(0)
8print(summary.columns)
9# Index(['price', 'price', 'quantity', 'quantity'])  # Duplicates!

Use get_level_values() when one level provides sufficient information. Be careful with the first level — it often produces duplicate column names.

Method 3: droplevel

python
1# Drop a specific level
2summary.columns = summary.columns.droplevel(0)  # Drop 'price'/'quantity' level
3print(summary.columns)
4# Index(['mean', 'sum', 'min', 'max'])
5
6# Or drop the second level
7summary.columns = summary.columns.droplevel(1)  # Drop 'mean'/'sum'/etc. level

droplevel() removes one level entirely. Use this when one level is redundant.

Method 4: map with f-string or Format

python
1# Custom formatting with map
2summary.columns = summary.columns.map(lambda x: f'{x[0]}_{x[1]}')
3print(summary.columns)
4# Index(['price_mean', 'price_sum', 'quantity_min', 'quantity_max'])
5
6# More readable names
7summary.columns = summary.columns.map(lambda x: f'{x[1]}_of_{x[0]}')
8print(summary.columns)
9# Index(['mean_of_price', 'sum_of_price', 'min_of_quantity', 'max_of_quantity'])

Using map gives full control over how level names are combined.

Method 5: to_flat_index (Pandas 0.24+)

python
1# Convert MultiIndex to flat tuples
2summary.columns = summary.columns.to_flat_index()
3print(summary.columns)
4# Index([('price', 'mean'), ('price', 'sum'),
5#        ('quantity', 'min'), ('quantity', 'max')])
6
7# Then convert tuples to strings
8summary.columns = ['_'.join(col) for col in summary.columns.to_flat_index()]

to_flat_index() converts the MultiIndex to a regular Index of tuples, which you can then format as needed.

Method 6: reset_index with Named Aggregations

Avoid MultiIndex columns entirely by using named aggregations:

python
1summary = df.groupby('category').agg(
2    price_mean=('price', 'mean'),
3    price_sum=('price', 'sum'),
4    quantity_min=('quantity', 'min'),
5    quantity_max=('quantity', 'max')
6)
7print(summary.columns)
8# Index(['price_mean', 'price_sum', 'quantity_min', 'quantity_max'])

Named aggregations produce flat columns directly — no flattening step needed. This is the cleanest approach when you know the aggregations upfront.

Flattening Row MultiIndex

The same techniques work for hierarchical row indices:

python
1# Flatten row index
2pivot = df.pivot_table(values='price', index=['category', 'region'], aggfunc='mean')
3print(pivot.index)
4# MultiIndex([('A', 'East'), ('A', 'West'), ('B', 'East'), ('B', 'West')])
5
6# Flatten
7pivot.index = ['_'.join(idx) for idx in pivot.index]
8print(pivot.index)
9# Index(['A_East', 'A_West', 'B_East', 'B_West'])
10
11# Or use reset_index to move index levels to columns
12pivot = pivot.reset_index()

Three or More Levels

python
1# Handle 3+ level MultiIndex
2multi = pd.MultiIndex.from_tuples([
3    ('2025', 'Q1', 'revenue'),
4    ('2025', 'Q1', 'cost'),
5    ('2025', 'Q2', 'revenue'),
6    ('2025', 'Q2', 'cost')
7])
8df_multi = pd.DataFrame(np.random.randn(3, 4), columns=multi)
9
10# Flatten all levels
11df_multi.columns = ['_'.join(col) for col in df_multi.columns]
12print(df_multi.columns)
13# Index(['2025_Q1_revenue', '2025_Q1_cost', '2025_Q2_revenue', '2025_Q2_cost'])

The '_'.join(col) pattern works regardless of how many levels the MultiIndex has.

Common Pitfalls

  • Empty string levels: After some operations, one level may contain empty strings. '_'.join(('price', '')) produces 'price_' with a trailing underscore. Use .strip('_') to clean it: ['_'.join(col).strip('_') for col in df.columns].
  • Duplicate column names after flattening: If you use get_level_values(0), columns like ('price', 'mean') and ('price', 'sum') both become 'price'. Pandas allows duplicate column names but operations on them become ambiguous.
  • Numeric level values: If levels contain numbers (e.g., year as int), '_'.join() fails because join requires strings. Convert first: ['_'.join(str(c) for c in col) for col in df.columns].
  • Forgetting to flatten before to_csv: df.to_csv() writes MultiIndex columns as multiple header rows, which can confuse downstream tools. Flatten before exporting.
  • reset_index surprises: df.reset_index() flattens the row index but not the column index. To flatten both, flatten columns first, then call reset_index().

Summary

  • Flatten MultiIndex columns with df.columns = ['_'.join(col).strip() for col in df.columns]
  • Use get_level_values() or droplevel() to keep or remove specific levels
  • Use map with a lambda for custom name formatting
  • Named aggregations (agg(name=('col', 'func'))) avoid MultiIndex columns entirely
  • The '_'.join() pattern works for any number of levels
  • Always check for empty strings, duplicates, and non-string level values after flattening
Related reading
Free course
Beginner
7 lessons
2 hours
Tackling System Design Interview Problems

A short course that equips you with the skills to approach system design interviews methodically.

Start the free course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

ML System Design practice on Codemia

Design recommenders, ranking systems and training pipelines the way ML interviews actually ask for them, with worked solutions.

Practice ML system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.