Parsing Multi Line Headers in Report Style CSV Files

Someone exported a pivot table, or a report from a BI tool, as CSV. The first line is the report title. The second line has years spanning several columns, the third has quarters, and only then do rows of numbers begin. A person reads it perfectly. A parser sees a file with a one-column first line, a header whose first cells are blank and a second header line that has to be combined with the first to mean anything. No library option handles this in general, and the solution is a short piece of code that you can write in ten minutes once you know the shape. This article shows the transformation on a small example. It belongs with the other files that are not quite tables, described in CSV dialects and variants you will run into.

The shape of a report-style export

The sample is sales by region for four quarters across two years:

Sales by region,,,,
,2025,,2026,
Region,Q3,Q4,Q1,Q2
North,100,120,130,140
South,80,90,95,110

Line 1 is a title with four commas after it, because the spreadsheet row was five columns wide. Line 2 is the upper header: 2025 spans the two columns Q3 and Q4, and 2026 spans Q1 and Q2. In the spreadsheet these were merged cells. In the CSV, a merged cell becomes the value in its first cell and empty fields for the rest, so 2025 sits above Q3 and nothing sits above Q4. Line 3 is the lower header, and from line 4 down is the data.

Reading this with pandas' default header=0 gives a column named Sales by region and four columns named Unnamed: 1 to Unnamed: 4, with the real headers as data. Reading with header=[1, 2] gets closer and still leaves the blank cells as Unnamed levels: the columns come back as ('Unnamed: 0_level_0', 'Region'), ('2025', 'Q3'), ('Unnamed: 2_level_0', 'Q4') and so on, a multi-index with holes where the merged cells were. Doing the work yourself is clearer.

Forward fill the merged cells

The key move is to restore the value of each merged cell for every column it covered. Walk the upper header from left to right, remembering the last non-empty value, and use it wherever a cell is blank:

import csv, sys

with open(sys.argv[1], newline="", encoding="utf-8") as f:
    rows = list(csv.reader(f))

title, top, bottom, data = rows[0], rows[1], rows[2], rows[3:]

# Fill the merged cells of the top header to the right
filled, last = [], ""
for cell in top:
    last = cell or last
    filled.append(last)

names = [f"{b}_{t}" if t and b not in ("Region",) else b for t, b in zip(filled, bottom)]
names = [n.lower() for n in names]
print(names)
w = csv.writer(sys.stdout, lineterminator="\n")
w.writerow(names)
w.writerows(data)

['region', 'q3_2025', 'q4_2025', 'q1_2026', 'q2_2026']
region,q3_2025,q4_2025,q1_2026,q2_2026
North,100,120,130,140
South,80,90,95,110

The first element of the top row is empty, so filled starts with an empty string and the first column stays without an upper level. For Region, which has nothing above it, the name is region. For the others the name joins the lower cell and the upper one, giving Q3_2025 and so on, lowercased. The result is a flat CSV that any loader reads, with unique, sortable column names that carry both pieces of information.

The condition b not in ("Region",) is a stand-in for the real rule, which is "a column with no upper level keeps its own name". In this file the first column has an empty upper cell, so t is empty and the condition t and ... already excludes it, and the extra check handles the case where a forward fill would have copied a value into a label column. In a real feed, make the rule structural: the first N columns are labels and are not filled, and the rest are measures that are. Hardcoding a name makes the script correct for one report and wrong for the next.

Decide what the names should be

The join is a naming decision, and you should make it on purpose. Putting the lower level first (q3_2025) sorts by quarter within year only if the year is also first, so for time series you probably want year first (2025_q3), and a name starting with a digit is awkward in SQL, so y2025_q3 or 2025q3 with quoting. Alternatively, do not keep the wide shape at all: unpivot the table into long form, with one row per region, year and quarter, which is how analysis wants it:

SELECT region, col, value FROM wide
UNPIVOT (value FOR col IN (q3_2025, q4_2025, q1_2026, q2_2026))

followed by splitting col on the underscore into a quarter and a year. In DuckDB 2.0 that gave ('North', 'q1_2026', 130) as one of the eight rows. A long table needs no column naming convention and survives a new quarter being added, because the new column becomes new rows. The wide layout with a two-level header is for reading, not for storing.

Variations that break the simple version

Real exports add complications, and each has a fix.

More than two header levels. Loop the forward fill over every upper row and join all levels. The order of levels in the name is a decision; document it.

Blank separator lines. A blank line between the title and the header is a row of empty fields. Skip leading rows until you reach the first row with more than one non-empty cell, or count them in configuration if the layout is fixed. A layout that varies between exports needs a rule for finding the header, such as "the first row whose first cell is Region".

Merged cells in the data region. A region name that spans three rows exports as the name on the first row and blanks below. Forward fill the label columns downward, the same operation along the other axis.

Subtotals and a grand total. Rows labeled Total mix with data rows. Filter them out by label, and use them as a check: the sum of the rows should equal the total row (embedded headers and totals in the middle of a file covers the repeated-header cousin).

Footnotes. Lines of text after the data, starting with an asterisk or the word Source. Stop at the first row that has fewer filled cells than the data, or cut by a marker.

Numbers with formatting. 1,234, 12% and (5) for negative numbers are display forms. Convert per column with an explicit rule after the structure is fixed.

Check the result

After flattening, verify what a person would verify by looking. The number of data rows equals the report's row count. The sum of a column equals the report's total. The names are unique: len(set(names)) == len(names), and the script should fail otherwise. A forward fill that overran, for example copying 2026 into a column that belonged to a different block, shows up as a name that does not fit the pattern, so print the names and read them. Header variants and normalizing column names covers the cleanup of the names themselves.

When the header is not on line one

If you control the export, do not use a report format at all. Export the underlying data as a flat table, one header row and one record per row, and let whoever wants a pivot build it. Most BI tools have a "data export" or "underlying data" option that does this, and a SQL query that produces the long form is more reliable than any parser of a decorated report.

When you cannot, accept that the parser is a conversion script with assumptions, and test it against two or three real exports from different months, since a report that adds a column or a subtotal row changes the layout. Keep the original file with the output, so that a surprise can be traced. The Python module used above is documented in the csv reference, and pandas explains multi-level headers in its read_csv documentation.