Extracting a Table That Uses Merged Cells Across a Header Row

The table has a header cell reading Q1 2026 stretched across three columns. Your extraction gives you one column called Q1 2026 and two called Unnamed: 3_level_0 and Unnamed: 4_level_0, or three columns where two have an empty name. The values are all present and correct. Only the labels are gone.

What you actually get back

The exact symptom depends on the reader, and the three common ones look different enough that people think they have three different bugs.

• openpyxl. Reading a cell inside a merged range that is not the top-left anchor returns a MergedCell whose value is None. The range itself is available on worksheet.merged_cells.ranges, so nothing is lost — it is just in a different place from where you were looking.
• pandas. With a single header row you get the label once and blanks after it. With a multi-row header — header={[0, 1]} — you get a MultiIndex, upper-level labels are filled forward across the span, and any header cell that was genuinely empty becomes a placeholder of the form Unnamed: N_level_M.
• A PDF or an image. Nothing is filled, because there is no merge to fill from. You get one text run positioned over the middle of a group of columns and no statement anywhere in the file about which columns it belongs to.

Why the label is only in one place

In a spreadsheet, a merge is a display instruction, not a data structure. The sheet XML carries an element of the form <mergeCell ref="B1:D1"/> and the string lives in B1 alone; C1 and D1 are empty cells that happen to be painted over. Unmerging in the application leaves the value in B1 and the others blank, which is exactly what the reader sees.

In HTML the same idea is a colspan attribute, and there the span is at least explicit: the table model in the HTML specification defines how a cell with colspan=3 occupies three slots of the grid, so a correct parser can expand the row into a dense array of slots before it does anything else. If you are parsing HTML tables and your columns drift, the usual cause is skipping that expansion step and zipping header cells against body cells positionally.

In a PDF there is no concept of a cell at all, merged or otherwise. A table is line segments and text runs that a human reads as a grid. The header is a text run with a bounding box, the data columns are text runs with bounding boxes, and the relationship between them has to be reconstructed from coordinates. That is the same class of problem as reading order in a two-column PDF, and it is worth recognising as geometry rather than reaching for a better prompt.

Propagating the span

The operation you want is: for each merged header, find the set of data columns it covers, and write its label onto every one of them. In a spreadsheet that is a lookup, because the range is declared.

from openpyxl import load_workbook

ws = load_workbook("report.xlsx").active

# Expand every merge into a value-per-cell map before reading anything.
filled = {}
for rng in ws.merged_cells.ranges:
    anchor = ws.cell(row=rng.min_row, column=rng.min_col).value
    for row in range(rng.min_row, rng.max_row + 1):
        for col in range(rng.min_col, rng.max_col + 1):
            filled[(row, col)] = anchor

def cell(row, col):
    return filled.get((row, col), ws.cell(row=row, column=col).value)

In a PDF you do not have the range, so you derive it. Establish the data columns first — from ruling lines if the table has them, from the x-extents of the body text if it does not — and represent each as an interval [x0, x1]. Then assign each header run to every column interval it overlaps by more than a small threshold. Deriving columns from body text and not from the header is the important ordering: the body is where the columns actually are.

Composing two header rows into one name

A merged header almost never appears alone. It sits above a second row of sub-labels, and the field you want is the pair: Q1 2026 / Units, Q1 2026 / Value, Q2 2026 / Units. Compose the name from the full path down the header stack rather than from the bottom row, or you get two columns both called Units and a schema that cannot express the difference. This is the same reasoning as naming fields on a document that repeats a role: the disambiguator is part of the field identity, not a suffix bolted on afterwards.

Two composition details save rework. Keep the path as a list rather than a joined string, so a downstream consumer can regroup by the top level without splitting on a separator that turns out to appear inside a label. And keep the original spelling: normalising Q1 2026 to q1_2026 at extraction time means you can no longer show the reviewer the label that is actually printed on the page, which is the one thing a review queue that highlights the source needs.

Where forward-fill is the wrong answer

Forward-filling a header row is the standard trick and it is wrong in four specific situations, all of which occur in ordinary financial and scientific tables.

• A genuinely empty header. The stub column on the left — the one holding row labels — usually has no header. Filling it from the left gives it the label of whatever came before, and filling from the right gives it the first data column’s label. Detect the merge explicitly and fill only inside declared ranges; treat everything else as empty and let it stay empty.
• A centred label narrower than its span. This is the one that catches interval-overlap code in a PDF. A short label such as Q1 centred over three columns can have an x-extent that lies entirely inside the middle column, so overlap assignment gives it to one column and the outer two stay blank. If the table has ruling lines, use the horizontal rule under the header to find the span; if it does not, fall back to the rule that a header run whose centre is not aligned with any single column centre is a span, and grow it symmetrically until it meets a gutter.
• A row-merged cell. Merges run vertically too, and a category name merged down six body rows needs filling downward, not across. The same expansion code handles it, which is why expanding all ranges into a per-cell map before reading is better than special- casing header rows.
• A merge used as a title. A single cell merged across the entire width above the real header is a caption, not a level. Filling it produces a header level that is constant across every column and therefore carries no information. Drop a level whose value is identical for all columns — but record that you dropped it, since it is often the reporting period the rest of the table is silent about.

Once the header is right, the next failure is the one that shows up on page two: columns that do not line up between pages. That page picks up exactly where this one stops.