Handling Excel Files With Title Rows Above the Header

Open the spreadsheet a finance team sends you and the first row is not a header. Row 1 is the company name, row 2 is "Orders report", row 3 is the date range, row 4 is blank, and the column names begin on row 5. Below the data sit a total row and a note about who approved the figures. A loader that assumes row 1 is the header produces a dataframe whose columns are called Unnamed: 0, Unnamed: 1 and so on, with the real header sitting inside the data. This post builds a reader that finds the header, reads to the end of the data and keeps the row numbers honest. It belongs to the cluster on getting Excel data into a database.

What the problem looks like

Reading the sheet naively:

import pandas as pd

df = pd.read_excel("report.xlsx", dtype=str, keep_default_na=False)
print(df.head(6))

For a report with a three-line title, the output looks like this (blank cells shown as empty text):

      Acme Corp Unnamed: 1 Unnamed: 2
0  Orders report
1  Period: Jan 2024
2
3      Order No   Customer     Amount
4           007       Acme      19.99
5           012     Globex       5.00

pandas took the first row, Acme Corp, as the header, which is why the first column carries that name. The real header is in the data, at index 3, which is sheet row 5. Every column is now of mixed content, and every downstream step is wrong.

Quick fix: header and skiprows

If the layout is fixed, tell pandas where the header is. header takes a zero-based row index within the sheet:

df = pd.read_excel("report.xlsx", header=4, dtype=str, keep_default_na=False)

header=4 means the fifth row of the sheet. Alternatively, skiprows=4 discards the first four rows and uses the next as the header. For this sheet both give the same result. Which one you pick matters for blank rows: skiprows counts physical rows, while header counts rows in the data that pandas read, and blank rows between the title and the header can shift one but not the other. When in doubt, test on the actual file and print the columns.

The fixed version is a trap in the long run. The sender adds a line "Prepared by finance" and the header moves to row 6. Your loader now reads the old row 5, which is the blank or the date line, as the header, and loads garbage without an error.

Find the header by name

Make the loader search for the header instead. Read the top of the sheet with no header, then look for the first row that contains your expected column names:

import pandas as pd

EXPECTED = ["Order No", "Customer", "Amount"]

def find_header_row(path, sheet=0, scan=30):
    top = pd.read_excel(path, sheet_name=sheet, header=None, nrows=scan, dtype=str, keep_default_na=False)
    for i, row in top.iterrows():
        cells = {str(v).strip() for v in row if str(v).strip()}
        if set(EXPECTED) <= cells:
            return i
    raise ValueError(f"no row in the first {scan} contains all of {EXPECTED}")

header_at = find_header_row("report.xlsx")
print(header_at)

4

The function scans up to 30 rows. A row counts as the header when its non-empty, trimmed cells include every expected name. The expected set is a subset check, so extra columns do not break it. If nothing matches, it raises, and the job stops with a message that names the missing columns. That is the behavior you want: a sheet without the columns you need should stop the load, not be loaded with the wrong row as its header.

Match names with some tolerance. Headers drift: Order No becomes Order No. or Order No with a double space, or changes case. Normalize both sides before comparing, by lowercasing, collapsing whitespace and stripping punctuation:

import re

def norm(s):
    return re.sub(r"[^a-z0-9]+", " ", str(s).lower()).strip()

Use norm on both the expected names and the cells. Keep a mapping from normalized names to the final column names, so the rest of the pipeline uses stable names.

Read from the header to the end of the data

With the header position, read the whole sheet from there:

df = pd.read_excel("report.xlsx", header=header_at, dtype=str, keep_default_na=False)

Now deal with the bottom of the sheet. Reports end with a totals row, a blank row and notes. Cut them off with explicit rules. The simplest is to stop at the first row where every cell is empty:

blank = (df.apply(lambda r: "".join(r.astype(str)).strip(), axis=1) == "")
if blank.any():
    first_blank = blank.idxmax()
    trailing = df.iloc[first_blank:]
    df = df.iloc[:first_blank]
    print(f"cut {len(trailing)} trailing rows")

idxmax on a boolean series returns the index of the first True. Everything from that row on is discarded, and the count is printed. This assumes there is no blank row inside the data. If the data can contain blank rows, use the second rule instead: drop the rows from the first one whose first cell starts with a total label.

first = df.iloc[:, 0].astype(str).str.strip().str.lower()
is_total = first.str.startswith(("total", "subtotal", "grand total"))
df = df[~is_total]

Print what you drop and compare the total with the sheet's own total row: the sum of the kept Amount values should equal the figure on the Total line. If it does, your cut is correct. If the numbers differ, a subtotal is hiding in the data or a data row was dropped (validating row counts after an Excel import).

Many reports also have an explicit footer: "Source: ERP export", "Approved by", an image. These are text in the first column or in a merged cell. Whatever form they take, treat them as not data and make the stop rule visible in the code.

Keep source row numbers correct

The provenance column records the sheet row of each value, so that a bad figure can be traced to its cell. After skipping rows, the dataframe index no longer matches. With a header on sheet row H (one-based) the first data row is H + 1:

header_row_number = header_at + 1          # zero-based index to one-based sheet row
df["source_row"] = df.index + header_row_number + 1

For this sheet, header_at is 4, the header is on sheet row 5, and the first data row is sheet row 6: index 0 gives 0 + 5 + 1 = 6. If you removed blank or total rows from the middle of the frame without resetting the index, the arithmetic still holds, because the index keeps the original positions. If you called reset_index, compute source_row before you do. Test it by looking up one row in Excel.

Merged title cells do not disturb the count, because the title rows are physical rows whether or not their cells are merged. Hidden rows do count: a hidden row is still a row in the sheet, and the numbers follow the sheet and not the display.

Multi-row headers

Some reports use two header rows: a group label above (Q1) and a month name below (Jan, Feb). A single header row cannot hold that. Read both rows and combine them:

raw = pd.read_excel("report.xlsx", header=None, dtype=str, keep_default_na=False)
group = raw.iloc[4].replace("", pd.NA).ffill()        # group labels across merged cells
month = raw.iloc[5]
names = [f"{g} {m}".strip() if pd.notna(g) else m for g, m in zip(group, month)]
df = raw.iloc[6:].copy()
df.columns = names

The forward fill is needed because a merged group label occupies only its first cell, so the cells to the right are empty (merged cells, hidden rows and other things that break imports). The result has columns like Q1 Jan and Q1 Feb, which you can reshape into long form with melt afterwards. Document the rule, because it is the part that breaks if a sender adds a third level.

Asking for a better file

Detecting headers is repair work. If you can, ask for the data on its own sheet or as an Excel Table, which has a defined header and extent, and let the report sheet remain for people (reading Excel tables by name with openpyxl). A stable sheet name and a header in row 1 make the whole function above unnecessary. Where you cannot, keep the detection code small, test it with a sample for each layout you have met, and fail loudly on the one you have not.

When detecting the header row is not worth the code

The layout is fixed and agreed. A system-generated export with an unchanging layout can use a constant header= and a test that fails when the column names differ.

It is a one-off. Deleting the first four rows in Excel and saving is faster than writing a detector.

The title rows hold data. A date range or a unit in the title ("amounts in thousands") is information you may need. Extract it deliberately, as a column or metadata, instead of discarding it.

You have a Table or a named range. Read it directly and skip detection.

The arguments are documented in the read_excel reference, and openpyxl's access to rows in its usage guide. To check a cleaned sample as JSON, use the CSV to JSON converter.