Normalizing merged and multi-header tables from inspection reports

A raw table pulled out of a tower inspection PDF rarely lands as a clean grid. Vendors stack two or three header rows to describe a single column — “Torque Spec” over “Min” and “Max” — and merge cells vertically so a component category like “Flange” is printed once and left blank for every bolt underneath it. Feed that grid straight into a dataframe and you get columns named None or Unnamed: 3, and rows where half the fields are silently empty. This page is the practical fix inside Table Extraction Strategies: a small, runnable module that detects where the header band ends, joins the stacked header cells into one composite name per column, forward-fills the merged row labels back in, maps the result to a fixed set of canonical field names, and validates and seals the output before anything downstream ever sees it.

Prerequisites & Context #

This page assumes you already have a raw table as a list of rows — a List[List[str]] grid — sitting in memory. That grid is typically the direct output of a table extraction call inside PDFplumber Extraction Workflows, where table.extract() returns each row exactly as the PDF’s ruling lines define it, spanned cells included as repeated blanks. If you have not yet decided which library produces that grid, pdfplumber vs Camelot for structural report tables compares the two on exactly this point — how each one represents a merged cell in its output — because the normalization approach below only works if blanks, not None, mark a spanned cell.

You will also need:

• Python 3.10+ and nothing beyond the standard library — this stage of the pipeline is pure data transformation, so it has no PDF-parsing dependency of its own.
• A stable canonical field vocabulary for the table type you are normalizing. This example uses a bolt-torque specification table, mapping composite headers to structure_type, component_id, torque_min, torque_max, torque_value, and torque_unit — the same field names other stages of the Automated Structural Report Parsing & Document Ingestion pipeline expect to see, regardless of which vendor template the numbers originally came from.
• A representative sample grid per vendor template. Header band depth and merge patterns vary enough between vendors that you should print the raw grid for a new template before assuming the detection heuristic below will find the header band correctly on the first try.

Step-by-Step Implementation #

Each step corrects one specific way a multi-row, merged-cell table breaks a naive row-by-row read.

Step 1 — Detect the multi-row header band. Scan rows from the top and stop at the first row that contains a genuinely numeric cell. Specification and label rows are text; the moment a row carries a real measurement, the header band is over. This avoids hardcoding “the header is always two rows,” which breaks the instant a vendor adds or drops a sub-header line.

Step 2 — Join stacked header cells into a composite column name. A spanning label like “Torque Spec” is only printed in the first cell of its span; every other cell under that span is blank in the raw grid. Forward-fill each header row horizontally first so the span label repeats across every column it covers, then read down each column across the header rows and join the non-repeating tokens — "Torque Spec" over "Min" becomes torque spec min. That composite string is your temporary column key.

Step 3 — Forward-fill merged row labels. Below the header band, a vertically merged cell like a component category leaves every row after the first one blank in that column. Walk the data rows top to bottom and, for each column, carry the last non-empty value down into any blank cell. Miss this step and every bolt after the first one in a component group silently loses its category.

Step 4 — Map composite headers to canonical fields. Look each composite header string up in a fixed dictionary — "component bolt id" to component_id, "measured value" to torque_value, and so on. If a composite header has no entry, raise a custom TableNormalizationError immediately rather than emitting a record with a made-up or missing field; a table you cannot fully map is a table whose vendor template changed and needs a human to look at it.

Step 5 — Validate and seal. With every row now keyed by canonical field name, check that the measured value actually falls inside its own min/max spec (again raising TableNormalizationError on a violation), then serialize the normalized rows with sorted keys and hash them with hashlib.sha256. The digest is what proves, later, that the flattened table an auditor is looking at is the same one the pipeline produced from the original PDF grid.

The diagram below traces one raw grid through all five steps.

Figure: a merged, multi-row header grid flattened to one canonical header row with the blank category cell forward-filled.

Complete Runnable Example #

The module below implements all five steps against a realistic two-row-header bolt-torque grid, with stdlib structured logging, a custom TableNormalizationError, and a SHA-256 audit hash over the normalized set.

import hashlib
import json
import logging
from dataclasses import asdict, dataclass
from typing import Dict, List

logging.basicConfig(level=logging.INFO, format="%(asctime)s | %(levelname)s | %(message)s")
logger = logging.getLogger("table_normalizer")

CANONICAL_FIELDS: Dict[str, str] = {
    "component category": "structure_type",
    "component bolt id": "component_id",
    "torque spec min": "torque_min",
    "torque spec max": "torque_max",
    "measured value": "torque_value",
    "measured unit": "torque_unit",
}


class TableNormalizationError(Exception):
    """Raised when a header band cannot be mapped or a value fails validation."""


@dataclass
class NormalizedRow:
    site_id: str
    fields: Dict[str, str]


def detect_header_band(grid: List[List[str]]) -> int:
    for i, row in enumerate(grid):
        if any(c.strip().replace(".", "", 1).isdigit() for c in row if c):
            return i
    raise TableNormalizationError("no numeric data row found; grid may be header-only")


def join_header_rows(header_rows: List[List[str]]) -> List[str]:
    width = max(len(r) for r in header_rows)
    filled = []
    for row in header_rows:
        padded, last, out = row + [""] * (width - len(row)), "", []
        for cell in padded:
            last = cell.strip() if cell and cell.strip() else last
            out.append(last)
        filled.append(out)
    composite = []
    for col in range(width):
        tokens, seen = [], set()
        for row in filled:
            if row[col] and row[col] not in seen:
                tokens.append(row[col])
                seen.add(row[col])
        composite.append(" ".join(tokens).lower())
    return composite


def forward_fill_rows(data_rows: List[List[str]]) -> List[List[str]]:
    width = len(data_rows[0])
    last_values, filled = [""] * width, []
    for row in data_rows:
        out = []
        for col in range(width):
            cell = row[col].strip() if col < len(row) and row[col] else ""
            last_values[col] = cell or last_values[col]
            out.append(last_values[col])
        filled.append(out)
    return filled


def map_and_validate(site_id: str, headers: List[str], row: List[str]) -> Dict[str, str]:
    fields = {}
    for header, value in zip(headers, row):
        canonical = CANONICAL_FIELDS.get(header)
        if canonical is None:
            raise TableNormalizationError(f"{site_id}: unmapped composite header '{header}'")
        fields[canonical] = value
    vmin, vmax, value = float(fields["torque_min"]), float(fields["torque_max"]), float(fields["torque_value"])
    if not vmin <= value <= vmax:
        raise TableNormalizationError(f"{site_id}: torque {value} outside spec [{vmin}, {vmax}]")
    return fields


def normalize_table(site_id: str, grid: List[List[str]]) -> List[NormalizedRow]:
    band_end = detect_header_band(grid)
    headers = join_header_rows(grid[:band_end])
    records = []
    for row in forward_fill_rows(grid[band_end:]):
        fields = map_and_validate(site_id, headers, row)
        records.append(NormalizedRow(site_id, fields))
        logger.info("NORMALIZED | %s | %s", site_id, fields)
    return records


def audit_hash(records: List[NormalizedRow]) -> str:
    canonical = json.dumps([asdict(r) for r in records], sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()


if __name__ == "__main__":
    raw_grid = [
        ["Component", "", "Torque Spec", "", "Measured", ""],
        ["Category", "Bolt ID", "Min", "Max", "Value", "Unit"],
        ["Flange", "Bolt-1", "100", "200", "150", "ft-lb"],
        ["", "Bolt-2", "100", "200", "148", "ft-lb"],
        ["Guy Wire", "Anchor-A", "150", "300", "220", "ft-lb"],
        ["", "Anchor-B", "150", "300", "215", "ft-lb"],
    ]
    recs = normalize_table("TWR-8842", raw_grid)
    digest = audit_hash(recs)
    logger.info("AUDIT | TWR-8842 | %d records | %s", len(recs), digest[:12])
    print(f"TWR-8842: {len(recs)} normalized rows | audit={digest[:12]}")

Verification & Expected Output #

Running the module against the TWR-8842 sample grid produces one structured log line per normalized row, then a sealing audit line:

2026-04-22 09:12:03 | INFO | NORMALIZED | TWR-8842 | {'structure_type': 'Flange', 'component_id': 'Bolt-1', 'torque_min': '100', 'torque_max': '200', 'torque_value': '150', 'torque_unit': 'ft-lb'}
2026-04-22 09:12:03 | INFO | NORMALIZED | TWR-8842 | {'structure_type': 'Flange', 'component_id': 'Bolt-2', 'torque_min': '100', 'torque_max': '200', 'torque_value': '148', 'torque_unit': 'ft-lb'}
2026-04-22 09:12:03 | INFO | NORMALIZED | TWR-8842 | {'structure_type': 'Guy Wire', 'component_id': 'Anchor-A', 'torque_min': '150', 'torque_max': '300', 'torque_value': '220', 'torque_unit': 'ft-lb'}
2026-04-22 09:12:03 | INFO | NORMALIZED | TWR-8842 | {'structure_type': 'Guy Wire', 'component_id': 'Anchor-B', 'torque_min': '150', 'torque_max': '300', 'torque_value': '215', 'torque_unit': 'ft-lb'}
2026-04-22 09:12:03 | INFO | AUDIT | TWR-8842 | 4 records | edc99b61d977
TWR-8842: 4 normalized rows | audit=edc99b61d977

Note that structure_type reads Flange on the Bolt-2 row even though the raw grid’s cell there was empty — that is forward_fill_rows doing its job. To assert this behaviour in a test, check the invariant directly: assert all(r.fields["structure_type"] for r in recs) confirms no row was left with a blank category after normalization. If detect_header_band raises TableNormalizationError: no numeric data row found, the grid you passed in is header-only or the wrong slice of the table; if map_and_validate raises unmapped composite header, print join_header_rows(grid[:band_end]) on its own and compare the composite strings against your CANONICAL_FIELDS keys — a mismatched space or an extra stacked label is almost always the cause.

Gotchas & Edge Cases #

• Three-row headers, not two. Some vendors add a units row below the min/max row — “Min” / “Max” over “(ft-lb)” repeated under both. detect_header_band still finds the boundary correctly because it only looks for the first numeric cell, but join_header_rows will fold the repeated unit token into every composite name unless you dedupe by exact string match, as the example does with the seen set — otherwise torque spec min ft-lb and torque spec max ft-lb collide on nothing useful and clutter the mapping dictionary for no benefit.
• A blank that means “not applicable,” not “merged.” Forward-filling assumes every blank cell in a data row is a vertically merged continuation of the value above it. That is true for the component category column, but if the same table has an optional “Notes” column where blank genuinely means no note was recorded, blindly forward-filling that column copies a stale note onto rows that never had one. Scope forward_fill_rows to the columns you know are merged — typically the leftmost grouping columns — rather than applying it to every column in the grid.
• A composite header that maps to nothing this table has. When a vendor’s template swaps in a field your CANONICAL_FIELDS dictionary has never seen — a new “Torque Spec Revision” sub-header, say — map_and_validate raises TableNormalizationError on the very first row, which is the correct outcome: better to halt the batch and add one dictionary entry than to silently drop a column your downstream code depended on.

FAQ #

Related #

• Up to the parent workflow: Table Extraction Strategies
• Sibling task: pdfplumber vs Camelot for structural report tables
• The full architecture: Automated Structural Report Parsing & Document Ingestion