How-to

Advanced Data Transformation And CleaningDeep dive

Read Excel with Multi-Row Headers in pandas

Q: Can pandas read three header rows?

Yes. Pass header=[0, 1, 2] and you get a three-level MultiIndex. Selection with xs takes the level number, so level=2 slices across the bottom row of labels.

Load a sheet whose column headers span two rows — a merged quarter above Revenue and Units — into a MultiIndex, then select, flatten or reshape it into a tidy table.

Messy spreadsheets Reading Excel files pandas

Finance and operations workbooks love a two-storey header: a merged Q1 cell sitting above Revenue and Units, then Q2 above the same pair again. It reads well on screen and badly in pandas, where a default read_excel takes only the top row as the header, turns the merged gaps into Unnamed: 2 columns and pushes the real sub-headers down into the first data row. This guide from Extracting Data from Messy Excel Sheets shows how to read those headers properly and then decide what shape you actually want the data in.

Prerequisites

Bash

pip install pandas openpyxl

This builds quarterly.xlsx with merged quarter cells, matching the diagram:

Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Quarterly"
ws.append(["Region", "Q1", None, "Q2", None])
ws.append([None, "Revenue", "Units", "Revenue", "Units"])
ws.append(["North", 1200, 30, 1500, 34])
ws.append(["South", 900, 22, 1100, 25])
ws.merge_cells("B1:C1")
ws.merge_cells("D1:E1")
ws.merge_cells("A1:A2")
wb.save("quarterly.xlsx")

Step 1 — Pass both header rows to read_excel

header accepts a list of row positions. Give it [0, 1] and pandas builds a two-level MultiIndex for the columns, forward-filling the blank cells that a merge leaves behind so that both Units columns know which quarter they belong to.

Python

import pandas as pd

df = pd.read_excel("quarterly.xlsx", sheet_name="Quarterly", header=[0, 1])
print(df.columns.tolist())
# [('Region', 'Unnamed: 0_level_1'), ('Q1', 'Revenue'), ('Q1', 'Units'),
#  ('Q2', 'Revenue'), ('Q2', 'Units')]

The one awkward column is Region. Its second-level cell is empty, and pandas cannot forward-fill across levels, so it invents Unnamed: 0_level_1. The fix is to make that column the index, which takes it out of the column hierarchy entirely.

Python

df = pd.read_excel("quarterly.xlsx", sheet_name="Quarterly", header=[0, 1], index_col=0)
df = df.rename_axis("Region")
print(df)
#        Q1            Q2
#   Revenue Units Revenue Units
# Region
# North    1200    30    1500    34
# South     900    22    1100    25

This forward fill happens whether the quarter labels are merged cells or simply typed once with blanks beside them — pandas treats both the same way — so you do not need to unmerge anything first.

Step 2 — Select from the MultiIndex

A MultiIndex is more convenient than it first appears. Indexing with the top-level label returns that quarter's sub-table, and xs slices across the second level to line up one measure for every quarter.

Python

q1 = df["Q1"]                                  # Revenue and Units for Q1
revenue = df.xs("Revenue", axis=1, level=1)    # Revenue for every quarter
north_q2_units = df.loc["North", ("Q2", "Units")]

print(q1)
print(revenue)
print(north_q2_units)                          # 34

revenue is often the frame you actually want for a report: one row per region, one column per quarter, with the quarter labels promoted to plain column names. Growth between quarters is then a single pct_change(axis=1) away.

Step 3 — Flatten the headers for export

Most destinations cannot take a MultiIndex. A database table, a CSV consumer or a plain to_excel without merged headers all want one string per column. Join the levels with a space — or an underscore if the names will become SQL identifiers.

Python

flat = df.copy()
flat.columns = [f"{quarter} {measure}" for quarter, measure in flat.columns]
flat = flat.reset_index()
print(flat.columns.tolist())
# ['Region', 'Q1 Revenue', 'Q1 Units', 'Q2 Revenue', 'Q2 Units']
flat.to_csv("quarterly_flat.csv", index=False)

If some columns have only one meaningful level, filter the empty parts out when you join so you do not end up with trailing spaces: " ".join(p for p in col if p and not str(p).startswith("Unnamed")).

Step 4 — Reshape into long format

The quarter is really a value, not part of a column name. Stacking the top level moves it into its own column, which is the shape that grouping, charting and database loads all prefer — and it keeps working when next quarter's pair of columns is added.

Python

long = (
    df.stack(level=0, future_stack=True)
      .rename_axis(["Region", "Quarter"])
      .reset_index()
)
print(long)
#   Region Quarter  Revenue  Units
# 0  North      Q1     1200     30
# 1  North      Q2     1500     34
# 2  South      Q1      900     22
# 3  South      Q2     1100     25

print(long.groupby("Quarter")[["Revenue", "Units"]].sum())

future_stack=True opts into the newer stacking implementation, which keeps missing values instead of silently dropping rows. That is the behaviour you want for report data, where an empty cell usually means zero activity rather than a row to discard. The reverse operation — long back to a wide presentation table — is covered in Unpivot a Wide Excel Sheet with pandas melt alongside pivot_table.

Step 5 — Build the header by hand when the layout is irregular

header=[0, 1] needs the two header rows to be adjacent and to start at a known position. When a title block sits above them, or when the top row has notes in it, read the sheet raw and assemble the MultiIndex yourself. This also gives you a place to clean labels before they become column names.

Python

raw = pd.read_excel("quarterly.xlsx", sheet_name="Quarterly", header=None)

top = raw.iloc[0].ffill().astype(str).str.strip()
sub = raw.iloc[1].fillna("").astype(str).str.strip()

columns = pd.MultiIndex.from_arrays([top, sub])
manual = (
    raw.iloc[2:]
       .set_axis(columns, axis=1)
       .set_index(("Region", ""))
       .rename_axis("Region")
       .rename_axis([None, None], axis=1)
)
manual = manual.apply(pd.to_numeric)
print(manual.equals(df))

The row positions 0 and 1 are the only assumptions left, and they can come from a search rather than a constant — the approach in Find the Header Row in an Excel Sheet with a Shifting Layout. Because the frame was read with header=None, the numeric columns start out as object, which is why the final apply(pd.to_numeric) is needed.

Step 6 — Validate the header before trusting it

A two-row header is a contract between the person maintaining the workbook and your script, and it is the kind of contract that gets broken quietly. Someone inserts a Margin measure under each quarter, or types Q3 with a trailing space, and every downstream selection either fails with a KeyError or — worse — succeeds on the wrong columns. A short check straight after the read turns that into a clear message at the point where the file came in.

Python

EXPECTED_MEASURES = {"Revenue", "Units"}

def check_header(frame: pd.DataFrame) -> None:
    quarters = frame.columns.get_level_values(0).unique()
    problems = []
    for quarter in quarters:
        if not str(quarter).startswith("Q") or str(quarter) != str(quarter).strip():
            problems.append(f"unexpected quarter label {quarter!r}")
        measures = set(frame[quarter].columns)
        if measures != EXPECTED_MEASURES:
            problems.append(f"{quarter}: measures {sorted(measures)} != {sorted(EXPECTED_MEASURES)}")
    if problems:
        raise ValueError("header check failed:\n  " + "\n  ".join(problems))

check_header(df)
print("header OK:", list(df.columns.get_level_values(0).unique()))

Checking the set of measures under each quarter, rather than the total column count, gives an error that names the exact group that changed. It also lets a new quarter through without complaint, which is the one change to this layout that is expected every three months. For wider schema checks — types, ranges, required columns — the same idea scales up into the approach in Validate Excel Columns Before Import with pandas.

Common pitfalls

Symptom	Cause	Fix
`Unnamed: 0_level_1` in the columns	A column with only a top-level label	Use `index_col=0`, or rename that tuple after reading
Sub-header values appear as the first data row	Only `header=0` was passed	Pass `header=[0, 1]`
`ValueError` about header rows	`usecols` or `skiprows` changed the row positions	Count header positions after skipping rows
Quarter missing from some columns	A top-level label typed only in a later column	Forward fill needs the label on the leftmost cell of each group
Rows vanish after `stack`	The legacy stack drops all-NaN rows	Pass `future_stack=True`
Column names end in a space	Flattening joined an empty level	Filter empty parts before joining

The headers in hand-built workbooks also carry invisible passengers — trailing spaces, non-breaking spaces, line breaks typed with Alt+Enter. A header that prints as Revenue but fails df["Q1"]["Revenue"] almost always has one; normalise labels with .str.strip() and .str.replace(r"\s+", " ", regex=True), as in Strip Whitespace and Normalise Text Columns with pandas.

Performance and scale notes

A multi-row header costs nothing extra to parse; it is the same cells read once. The difference shows up afterwards. MultiIndex selection is fast, but a wide sheet with dozens of quarters and many measures is far easier to work with after one stack, because every later operation runs on a few named columns instead of a few hundred tuples.

If the workbook is large, restrict the read with usecols so that pandas only parses the regions and quarters you need. The header rows are included in the slice automatically as long as the column range covers them.

Conclusion

Tell read_excel how many header rows there are with header=[0, 1], move the label column into the index so it does not end up Unnamed, and let pandas forward-fill the merged quarter labels for you. From there, choose the shape by destination: keep the MultiIndex for interactive selection, flatten the names for CSV or SQL, and stack the top level whenever the data is heading into grouping, charts or a database. When the header rows move, read the sheet raw and build the MultiIndex yourself.

Frequently asked questions

Can pandas read three header rows? Yes. Pass header=0, 1, 2 and you get a three-level MultiIndex. Selection with xs takes the level number, so level=2 slices across the bottom row of labels.

Do I need to unmerge the cells first? No. Merged cells store their value only in the top-left cell, and pandas forward-fills the blanks to the right when it builds the header levels. Unmerging with openpyxl gives the same result.

How do I write a MultiIndex back to Excel with merged headers? Call to_excel on the DataFrame as it is. pandas writes the levels as separate header rows and merges repeated top-level labels automatically; pass merge_cells=False if you want every cell filled instead.

Why does my index column show up as a header level? When index_col is set, pandas writes the index name on the header row. Clear it with rename_axis(None) if you do not want it, or reset_index() to turn it back into a normal column.

Up one level: Extracting Data from Messy Excel Sheets — reading worksheets that were designed for people, not parsers.
Handle Merged Cells When Reading Excel with pandas — merged cells in the body of a sheet rather than its header.
Read Multiple Tables from One Excel Sheet — splitting a sheet into blocks before reading each header.
Unpivot a Wide Excel Sheet with pandas melt — the single-header version of going wide to long.
Skip Rows and Set the Header When Reading Excel with pandas — header and skiprows for simpler layouts.

More on messy spreadsheets