Skip to content
Tutorial Reference

Python Pandas: How to Flatten MultiIndex Columns in Pandas

Learn how to prevent MultiIndex columns in Pandas aggregations and flatten existing ones using named aggregation, underscore joins, or custom formatting for cleaner DataFrames.

Hierarchical (MultiIndex) columns are a common byproduct of groupby aggregations and pivot table operations in Pandas. While they represent the data structure accurately, they complicate column access, make exports messy, and require tuple-based referencing like df[('Value', 'sum')] instead of simple strings. Flattening these multi-level column names into clean, single-level strings makes your DataFrames easier to work with.

In this guide, you will learn how to prevent MultiIndex columns from forming in the first place, flatten existing ones using different techniques, and handle pivot table columns with custom formatting.

Prevention: Using Named Aggregation

The best way to deal with MultiIndex columns is to avoid creating them. Named aggregation lets you specify both the aggregation function and the output column name in a single step:

import pandas as pd

df = pd.DataFrame({
    'Group': ['A', 'A', 'B', 'B'],
    'Value': [10, 20, 30, 40]
})

result = df.groupby('Group').agg(
    total=('Value', 'sum'),
    average=('Value', 'mean'),
    count=('Value', 'count')
).reset_index()

print(result)

Output:

  Group  total  average  count
0     A     30     15.0      2
1     B     70     35.0      2

The result has clean, descriptive column names with no MultiIndex. This approach is recommended for new code because it produces self-documenting output.

Flattening Existing MultiIndex Columns

When you already have a DataFrame with MultiIndex columns, such as from an aggregation that used a list of functions, you can flatten them by joining the level names:

import pandas as pd

df = pd.DataFrame({
    'Group': ['A', 'A', 'B'],
    'Value': [10, 20, 30],
    'Count': [1, 2, 3]
})

result = df.groupby('Group').agg(['sum', 'mean'])

print("Before flattening:")
print(result.columns.tolist())
print()

result.columns = ['_'.join(col) for col in result.columns]
result = result.reset_index()

print("After flattening:")
print(result)

Output:

Before flattening:
[('Value', 'sum'), ('Value', 'mean'), ('Count', 'sum'), ('Count', 'mean')]

After flattening:
  Group  Value_sum  Value_mean  Count_sum  Count_mean
0     A         30        15.0          3         1.5
1     B         30        30.0          3         3.0

Each MultiIndex column is a tuple like ('Value', 'sum'). The list comprehension joins these tuple elements with an underscore to produce 'Value_sum'.

Using to_flat_index() for Complex Hierarchies

For MultiIndex columns with more than two levels, to_flat_index() provides a more explicit conversion path:

import pandas as pd

df = pd.DataFrame({
    'Group': ['A', 'A', 'B'],
    'Value': [10, 20, 30]
})

result = df.groupby('Group').agg(['sum', 'mean'])

result.columns = ['_'.join(col).strip() for col in result.columns.to_flat_index()]

print(result.columns.tolist())

Output:

['Value_sum', 'Value_mean']

The .strip() call removes any trailing whitespace that can appear when one level has empty strings.

Flattening Pivot Table Columns

Pivot tables frequently produce MultiIndex columns, especially when using multiple aggregation functions:

import pandas as pd

df = pd.DataFrame({
    'Date': ['2024-01', '2024-01', '2024-02'],
    'Category': ['A', 'B', 'A'],
    'Sales': [100, 200, 150]
})

pivot = df.pivot_table(
    values='Sales',
    index='Date',
    columns='Category',
    aggfunc=['sum', 'mean']
)

print("Before flattening:")
print(pivot.columns.tolist())
print()

pivot.columns = ['_'.join(col) for col in pivot.columns]

print("After flattening:")
print(pivot.columns.tolist())
print()

pivot = pivot.reset_index()
print(pivot)

Output:

Before flattening:
[('sum', 'A'), ('sum', 'B'), ('mean', 'A'), ('mean', 'B')]

After flattening:
['sum_A', 'sum_B', 'mean_A', 'mean_B']

      Date  sum_A  sum_B  mean_A  mean_B
0  2024-01  100.0  200.0   100.0   200.0
1  2024-02  150.0    NaN   150.0     NaN

Custom Separators and Formatting

The join-based approach is flexible enough to accommodate different naming conventions:

import pandas as pd

df = pd.DataFrame({
    'Group': ['A', 'A'],
    'Value': [10, 20]
})

result = df.groupby('Group').agg(['sum', 'mean'])

result_dash = result.copy()
result_dash.columns = [' - '.join(col) for col in result_dash.columns]
print(f"Dash separator: {result_dash.columns.tolist()}")

result_reversed = result.copy()
result_reversed.columns = [f"{func}_{col}" for col, func in result_reversed.columns]
print(f"Reversed order: {result_reversed.columns.tolist()}")

result_func = result.copy()
result_func.columns = [func for _, func in result_func.columns]
print(f"Function only: {result_func.columns.tolist()}")

Output:

Dash separator: ['Value - sum', 'Value - mean']
Reversed order: ['sum_Value', 'mean_Value']
Function only: ['sum', 'mean']

When to Use Each Approach

Method When to Use Result Example
Named aggregation Writing new aggregation code total, average
'_'.join(col) Flattening existing MultiIndex Value_sum
.to_flat_index() Complex or deeply nested hierarchies Flat tuples
Custom f-string format Specific naming conventions needed sum_Value

Prefer named aggregation whenever possible to avoid MultiIndex columns entirely. It produces cleaner, self-documenting code from the start.

For existing MultiIndex columns, use '_'.join(col) for col in df.columns to flatten them into readable single-level names.

Always follow with reset_index() if the row index is also hierarchical and needs to be converted to a regular column.

Navigation