Merged two-row headers in a Census XLSX: openpyxl leaves them blank

Checked 2026-09-08 · Numbers out of PDFs and spreadsheets

In the Census new residential construction workbook, the region names sit in merged cells across row 5 and the sub-columns in row 6. openpyxl stores a merged value only in the top-left cell and returns None for every other cell in the range, so reading row 5 gives ('Period', 'United States', None, None, None, 'Northeast', None, 'Midwest'). Unmerge the ranges and copy the value across before you read the header. pandas.read_excel with default arguments is worse: it takes the sheet title as the header and produces twelve columns called Unnamed.

What we saw

Row 5 of the first sheet reads back with holes where the merged spans continue. Source (sheet 'Table 1 - Permits', row 5, columns A to H, 2026-09-08).

('Period', 'United States', None, None, None, 'Northeast', None, 'Midwest')

The merged ranges are explicit in the file and give the real column spans. Source (sheet 'Table 1 - Permits', ws.merged_cells.ranges, 2026-09-08).

{<MergedCellRange A5:A6>, <MergedCellRange J5:K5>, <MergedCellRange A33:A34>, <MergedCellRange F5:G5>, <MergedCellRange H33:I33>, <MergedCellRange F33:G33>, <MergedCellRange L33:M33>, <MergedCellRange B33:E33>, <MergedCellRange J33:K33>, <MergedCellRange H5:I5>, <MergedCellRange L5:M5>, <MergedCellRange B5:E5>}

The sheet holds two stacked tables, not one: the second header band starts at row 33, so a single read of the sheet mixes seasonally adjusted and unadjusted figures. Source (sheet 'Table 1 - Permits', A4 and the row-33 merged ranges, 2026-09-08).

'Table 1a - Seasonally adjusted annual rate' at row 4; merged ranges A33:A34, B33:E33, F33:G33, H33:I33, J33:K33, L33:M33 ; sheet dims A1:N74

pandas.read_excel with defaults produces no usable header. Source (pd.read_excel(path, sheet_name='Table 1 - Permits'), 2026-09-08).

pandas default shape (73, 13) ; columns ['New Privately-Owned Housing Units Authorized in Permit-Issuing Places', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 4', 'Unnamed: 5', 'Unnamed: 6', 'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10', 'Unnamed: 11', 'Unnamed: 12']

After unmerging and filling, the two header rows join into full column names and 20 data rows come out of table 1a. Source (rows 7 to 31 of sheet 'Table 1 - Permits', 2026-09-08).

HEADER: ['Period - Period', 'United States - Total', 'United States - 1 unit', 'United States - 2 to 4 units', 'United States - 5 units  or more', 'Northeast - Total', 'Northeast - 1 unit', 'Midwest - Total', 'Midwest - 1 unit', 'South - Total', 'South - 1 unit', 'West - Total', 'West - 1 unit', ''] ; ROWS: 20 ; ['2025 July', 1400, 884, 55, 461, 130, 55, 222, 123, 762, 526, 286, 180, None]

The command

curl -sS -o newresconst.xlsx -w '%{http_code} %{content_type} %{size_download}\n' https://www.census.gov/construction/nrc/xls/newresconst.xlsx

200 application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 45110

Checked 2026-09-08.

python3 -c "import openpyxl; wb=openpyxl.load_workbook('newresconst.xlsx'); print(wb.sheetnames); ws=wb[wb.sheetnames[0]]; print('dims',ws.dimensions); print('merged',ws.merged_cells.ranges)\nfor r in ws.iter_rows(min_row=1,max_row=12,max_col=8,values_only=True): print(r)"

['Table 1 - Permits', 'Table 2 - Auth Not Started', 'Table 3 - Starts', 'Table 4 - Under Construction', 'Table 5 - Completions']
dims A1:N74
merged {<MergedCellRange A5:A6>, <MergedCellRange J5:K5>, <MergedCellRange A33:A34>, <MergedCellRange F5:G5>, <MergedCellRange H33:I33>, <MergedCellRange F33:G33>, <MergedCellRange L33:M33>, <MergedCellRange B33:E33>, <MergedCellRange J33:K33>, <MergedCellRange H5:I5>, <MergedCellRange L5:M5>, <MergedCellRange B5:E5>}
('New Privately-Owned Housing Units Authorized in Permit-Issuing Places', None, None, None, None, None, None, None)
('(Thousands of Units.  Detail may not add to total because of rounding.)', None, None, None, None, None, None, None)
(None, None, None, None, None, None, None, None)
('Table 1a - Seasonally adjusted annual rate', None, None, None, None, None, None, None)
('Period', 'United States', None, None, None, 'Northeast', None, 'Midwest')
(None, 'Total', '1 unit', '2 to 4 units', '5 units\n or more', 'Total', '1 unit', 'Total')
(None, None, None, None, None, None, None, None)
('2025', None, None, None, None, None, None, None)
('July . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .', 1400, 884, 55, 461, 130, 55, 222)
('August . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .', 1347, 867, 53, 427, 121, 55, 201)
('September . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .', 1444, 888, 58, 498, 148, 57, 204)
('October . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .', 1418, 886, 55, 477, 145, 58, 197)

Checked 2026-09-08.

python3 -c "import pandas as pd; df=pd.read_excel('newresconst.xlsx', sheet_name='Table 1 - Permits'); print(df.shape); print(list(df.columns))"

(73, 13)
['New Privately-Owned Housing Units Authorized in Permit-Issuing Places', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 4', 'Unnamed: 5', 'Unnamed: 6', 'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10', 'Unnamed: 11', 'Unnamed: 12']

Checked 2026-09-08.

python3 xlsxfix.py   # unmerge each range, write the top-left value into every cell of the range, join row 5 and row 6 into one header, then read rows 7 to 31; a row whose second cell is None is a year banner, carry it into the label

HEADER: ['Period - Period', 'United States - Total', 'United States - 1 unit', 'United States - 2 to 4 units', 'United States - 5 units  or more', 'Northeast - Total', 'Northeast - 1 unit', 'Midwest - Total', 'Midwest - 1 unit', 'South - Total', 'South - 1 unit', 'West - Total', 'West - 1 unit', '']
ROWS: 20
['2025 July', 1400, 884, 55, 461, 130, 55, 222, 123, 762, 526, 286, 180, None]
['2025 August', 1347, 867, 53, 427, 121, 55, 201, 125, 710, 515, 315, 172, None]
['2025 September', 1444, 888, 58, 498, 148, 57, 204, 130, 773, 517, 319, 184, None]

Checked 2026-09-08.

what you do	what you get for the header
ws.iter_rows on row 5	('Period', 'United States', None, None, None, 'Northeast', None, 'Midwest')
pd.read_excel defaults	the sheet's title row, then Unnamed: 1 through Unnamed: 12
unmerge, fill, join rows 5 and 6	'United States - Total', 'United States - 1 unit', ... 'West - 1 unit'

Limits

The two tables on a sheet are seasonally adjusted and not seasonally adjusted versions of the same series. Read the whole sheet as one frame and you will silently mix them; the numbers look plausible either way.
Row labels carry trailing dot leaders ('July . . . . . . .'), so a string match on the month name fails unless you strip them.
The merged range A5:A6 makes the first column name come out as 'Period - Period' if you join the two header rows blindly.
The recipe hardcodes rows 5, 6, 7 and 31. Those are this file's layout, not a contract; the sheet is 74 rows and 14 columns today and Census can renumber it.
I did not check the other four sheets, and I did not cross-check any figure against the Census release text.
The last column comes back as an empty header with None values: the sheet is 14 columns wide but only 13 carry data.

Open question

Whether the row offsets are stable month to month, or whether the header band moves when a year rolls over and a banner row is added.

Plain text