Extracting Line Items from Change Order Tables with Python

A change order is priced in its table, not its cover sheet. The scope narrative explains why the work changed, but the money lives in a grid of rows — description, quantity, unit, unit cost, extended amount — and that grid is where automation either produces a defensible cost delta or silently loses a line. This page solves one precise problem: how to turn a tabular change-order body from a PDF or spreadsheet into a list of typed, Decimal-priced line-item rows, correctly reassembling merged cells and multi-line descriptions, dropping header and subtotal rows, and routing every ambiguous row by confidence instead of guessing. It is the row-level companion to the document-wide extraction covered in field extraction techniques: that layer resolves the change order’s identity and top-line totals, while this layer decomposes the priced grid underneath it. The rows it emits feed change order schema validation at the boundary and, once each line carries a cost code, become the join keys that WBS mapping strategies post against a budgeted node. It targets Python builders inside an automated document ingestion pipeline who need line-item precision under real-world table variance from Procore exports, AIA continuation sheets, and subcontractor takeoffs.

Each grid row is assembled, classified, priced in Decimal, and reconciled against quantity times unit cost before the site-canonical confidence bands decide whether it posts, waits for an estimator, or is quarantined.

Key Rules and Specification

A table extractor is judged on what it refuses to guess. The following rules govern every row before it becomes a typed record:

Assemble before you classify. PDF and spreadsheet grids carry merged cells (a cost code that spans several rows) and wrapped text (a description broken across two visual lines). Fill each empty cell from the last populated value in its column, and fold a row whose only populated cell is the description into the row above it, before any row is classified.
Every row has a type. Classify each assembled row as header, line_item, subtotal, or note. Only line_item rows are priced; header and note rows are dropped or attached, and subtotal rows are kept for reconciliation but never posted as work.
Money is Decimal, always. quantity, unit_cost, and extended are Decimal, never float. Parse regional formats ($1,850.00 and 1.850,00) at the coercion boundary and quantize to two decimal places so a rollup never inherits binary-float drift.
Unit of measure is a closed set. The unit field is a Literal of construction units (LS, EA, SF, SY, CY, LF, HR, TON, GAL) — an unrecognized token is inferred as lump-sum and costs confidence, rather than entering the ledger as free text.
Reconcile the extended amount. A line item’s extended must equal quantity × unit_cost within one cent; a larger gap is a transcription or OCR error, not rounding, and the row is rejected at construction time.
Confidence bands drive routing. Scores are site-canonical: 0.92 and above auto-routes, 0.75 to 0.92 flags for human review, and below 0.75 quarantines the row. Every inferred field (a defaulted unit, a recomputed total) subtracts from the base OCR confidence rather than being silently accepted.

Row type	Signal	Action	Priced?
`line_item`	Money in a cell, no subtotal keyword	Coerce, reconcile, route	Yes
`subtotal`	“subtotal” / “total” in description	Keep for reconciliation	No
`note`	Text only, no money	Attach to prior line	No
`header`	Empty or column-label row	Drop	No

Production Code Example

The contract below models one priced row. Construction constants — the MasterFormat cost code and the unit vocabulary — are regex- or Literal-constrained, and a model_validator reconciles the extended total so an arithmetic error raises at construction time rather than corrupting a change-order sum. Decimal handling follows the standard Python decimal module.

from __future__ import annotations

import logging
import re
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
from typing import Literal, Optional

from pydantic import BaseModel, ConfigDict, Field, field_validator, model_validator

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)-8s | %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S",
)
logger = logging.getLogger("co_line_items")

# Canonical MasterFormat cost code, e.g. "03 30 00" (cast-in-place concrete).
COST_CODE_RE = re.compile(r"^\d{2} \d{2} \d{2}$")

RowType = Literal["header", "line_item", "subtotal", "note"]
UnitOfMeasure = Literal["LS", "EA", "SF", "SY", "CY", "LF", "HR", "TON", "GAL"]
RouteState = Literal["auto_route", "human_review", "quarantine"]

AUTO_ROUTE = Decimal("0.92")    # site-canonical: >= 0.92 posts automatically
HUMAN_REVIEW = Decimal("0.75")  # 0.75-0.92 holds for an estimator to confirm
CENTS = Decimal("0.01")
# Extended totals may drift a cent from rounding; a larger gap is a real error.
RECONCILE_TOLERANCE = Decimal("0.01")


class ChangeOrderLineItem(BaseModel):
    """One validated row from a change-order table, priced in Decimal."""

    model_config = ConfigDict(frozen=True)

    row_index: int = Field(ge=0)
    description: str = Field(min_length=3, max_length=400)
    quantity: Decimal = Field(gt=0)
    unit: UnitOfMeasure
    unit_cost: Decimal = Field(ge=0, decimal_places=2)
    extended: Decimal = Field(ge=0, decimal_places=2)
    cost_code: Optional[str] = None
    confidence: Decimal = Field(ge=0, le=1)

    @field_validator("cost_code")
    @classmethod
    def _canonical_cost_code(cls, v: Optional[str]) -> Optional[str]:
        if v is not None and not COST_CODE_RE.match(v):
            raise ValueError(f"cost_code must be MasterFormat XX XX XX: {v!r}")
        return v

    @model_validator(mode="after")
    def _extended_reconciles(self) -> "ChangeOrderLineItem":
        expected = (self.quantity * self.unit_cost).quantize(CENTS, rounding=ROUND_HALF_UP)
        if abs(expected - self.extended) > RECONCILE_TOLERANCE:
            raise ValueError(f"extended {self.extended} != quantity*unit_cost {expected}")
        return self

    @property
    def route_state(self) -> RouteState:
        if self.confidence >= AUTO_ROUTE:
            return "auto_route"
        if self.confidence >= HUMAN_REVIEW:
            return "human_review"
        return "quarantine"

Row assembly runs first: it fills merged cells down each column and folds a wrapped-description row into the line above it. Classification then reads each assembled row’s own signals — money presence and subtotal keywords — so header and note rows never reach the pricing path.

Cell = Optional[str]
Grid = list[list[Cell]]

_SUBTOTAL_RE = re.compile(r"\b(sub\s*total|total)\b", re.IGNORECASE)
_MONEY_RE = re.compile(r"[-+]?\d[\d,.]*\.\d{2}")


def _to_decimal(raw: str) -> Decimal:
    """Parse '$1,850.00' or European '1.850,00' money into a 2 dp Decimal."""
    s = raw.strip().lstrip("$€£").replace(" ", "")
    if "," in s and s.rfind(",") > s.rfind("."):   # European 1.850,00 form
        s = s.replace(".", "").replace(",", ".")
    else:
        s = s.replace(",", "")
    return Decimal(s).quantize(CENTS, rounding=ROUND_HALF_UP)


def assemble_rows(grid: Grid) -> list[dict[str, str]]:
    """Fill merged cells downward and fold wrapped description lines upward.
    A None cell inherits the last seen value in its column; a row whose only
    populated cell is the description is treated as a wrap continuation."""
    header = [(h or "").strip().lower().replace(" ", "_") for h in grid[0]]
    desc_col = header.index("description") if "description" in header else 0
    last = ["" for _ in header]
    rows: list[dict[str, str]] = []
    for cells in grid[1:]:
        filled = [(c.strip() if c is not None else last[i]) for i, c in enumerate(cells)]
        for i, c in enumerate(cells):
            if c is not None and c.strip():
                last[i] = c.strip()
        populated = [i for i, c in enumerate(cells) if c and c.strip()]
        if rows and populated == [desc_col]:
            rows[-1]["description"] += " " + filled[desc_col].strip()
            continue
        rows.append({header[i] or f"col{i}": filled[i] for i in range(len(header))})
    return rows


def classify_row(row: dict[str, str]) -> RowType:
    joined = " ".join(v for v in row.values() if v).strip()
    if not any(_MONEY_RE.search(v or "") for v in row.values()):
        return "note" if joined else "header"
    if _SUBTOTAL_RE.search(row.get("description", "")):
        return "subtotal"
    return "line_item"

Coercion is where confidence is earned or lost: a defaulted unit or a recomputed total each subtract a fixed penalty from the base OCR confidence, so the routing decision reflects real uncertainty. The batch driver isolates each row and emits an audit line for anything that does not auto-route, following the Python logging HOWTO.

def parse_line_item(index: int, row: dict[str, str], base_confidence: Decimal) -> ChangeOrderLineItem:
    """Coerce one classified line_item row, lowering confidence per inferred field."""
    penalty = Decimal("0")
    unit_raw = (row.get("unit") or "").strip().upper()
    if unit_raw not in UnitOfMeasure.__args__:   # type: ignore[attr-defined]
        unit_raw = "LS"                           # inferred lump-sum default
        penalty += Decimal("0.10")

    qty = _to_decimal(row.get("quantity") or "1")
    unit_cost = _to_decimal(row.get("unit_cost") or row.get("rate") or "0")
    extended_raw = row.get("extended") or row.get("amount")
    if extended_raw:
        extended = _to_decimal(extended_raw)
    else:                                         # recompute a missing total
        extended = (qty * unit_cost).quantize(CENTS, rounding=ROUND_HALF_UP)
        penalty += Decimal("0.08")

    confidence = max(Decimal("0"), base_confidence - penalty)
    return ChangeOrderLineItem(
        row_index=index, description=row.get("description", "").strip(),
        quantity=qty, unit=unit_raw, unit_cost=unit_cost, extended=extended,
        cost_code=(row.get("cost_code") or "").strip() or None, confidence=confidence,
    )


def extract_line_items(grid: Grid, ocr_confidence: Decimal) -> list[ChangeOrderLineItem]:
    """Assemble, classify, and price every line_item row with per-row isolation."""
    items: list[ChangeOrderLineItem] = []
    for i, row in enumerate(assemble_rows(grid)):
        if classify_row(row) != "line_item":
            continue
        try:
            item = parse_line_item(i, row, ocr_confidence)
        except (ValueError, InvalidOperation) as exc:
            logger.warning("row %d quarantined at parse: %s", i, exc)
            continue
        items.append(item)
        if item.route_state != "auto_route":
            logger.info("AUDIT | %s", item.model_dump_json())
    return items


if __name__ == "__main__":
    table: Grid = [
        ["Description", "Cost Code", "Quantity", "Unit", "Unit Cost", "Extended"],
        ["Additional rebar at grade beams", "03 20 00", "4.5", "TON", "1,850.00", "8,325.00"],
        ["per RFI-114 revised detail", None, None, None, None, None],
        ["Concrete pump mobilization", "03 30 00", "1", "LS", "2,400.00", "2,400.00"],
        ["Subtotal", None, None, None, None, "10,725.00"],
    ]
    for it in extract_line_items(table, ocr_confidence=Decimal("0.97")):
        print(f"row {it.row_index}: {it.description[:34]:<34} {it.extended} {it.route_state}")

Common Mistakes and Gotchas

Classifying rows before merged cells are filled. A subtotal row often leaves the description column blank and only populates the amount, while a merged cost-code cell leaves lower rows visually empty. If you classify raw cells, the subtotal masquerades as a line item and the merged-cell rows drop their code. Assemble first — fill down, fold wrapped text — and only then read each row’s signals. The order is the correctness property, not a convenience.

Summing the extended column without excluding subtotals. The tempting one-liner totals every money cell in the extended column, which double-counts because a subtotal row is the sum of the rows above it. Keep subtotal rows out of the priced set and use them the other way around — as an independent check that your line-item sum reconciles to the document’s own total. A mismatch there is exactly the signal you want before a change order posts.

Trusting the printed extended amount over the arithmetic. OCR routinely misreads a digit in a dense number column, so a printed extended of 8,325.00 may not equal quantity × unit_cost. Recompute and compare within a one-cent tolerance; when they disagree, lower confidence and route the row to review rather than posting the printed figure. Reconciliation catches the transcription errors that pattern validation alone cannot, and it hands mismatches to error handling protocols instead of the ledger.

Where This Fits in the Pipeline

This extractor is the row-level stage of the broader field extraction techniques subsystem inside automated document ingestion. Upstream, a document only reaches it after OCR and layout parsing have produced a grid with a per-page confidence; downstream, the typed rows clear change order schema validation before any of them move a budget total, and each row’s cost code becomes the join key that WBS mapping strategies resolves to a budgeted node. Because a single change order can carry dozens of line items and bid-period bursts arrive in waves, the extraction itself runs under the async batching workflows queue so rows are processed in bounded batches rather than blocking on one large document. The confidence bands used here are the same 0.92 and 0.75 thresholds applied across every routing decision on the site, keeping a row’s fate predictable from grid to ledger.

Frequently Asked Questions

How do you tell a subtotal row from a line item?

By signal, not position. After assembly, a row is a subtotal when its description matches a “subtotal” or “total” keyword; it is a note when it carries text but no money; it is a header when it is empty or holds column labels; otherwise it is a line item. Only line items are priced and routed, so the document’s own subtotal can be used as an independent reconciliation check rather than being posted as work.

Why recompute the extended amount instead of trusting the printed value?

Because OCR and manual transcription both drop or transpose digits in dense number columns. Recomputing quantity × unit_cost and comparing against the printed extended within a one-cent tolerance catches those errors at construction time. When they disagree the row’s confidence is lowered and it routes to review, so a misread figure never posts silently against a budget.

How are merged cells and wrapped descriptions handled?

Row assembly runs before classification. Each empty cell inherits the last populated value in its column, which reconstructs a merged cost code that spans several rows. A row whose only populated cell is the description is treated as a continuation and folded into the line above it, so a two-line scope note becomes one complete description rather than an orphan row.

Why is the unit of measure a Literal rather than a free string?

Downstream cost analysis groups and converts by unit, so an unrecognized token like each versus EA would fragment a rollup. Constraining unit to a closed Literal means an unknown value is inferred as lump-sum and lowers the row’s confidence, routing it to review instead of letting a typo enter the ledger as its own phantom unit.

What happens to a row that fails reconciliation or coercion?

It never posts. A ValidationError from the reconciliation check or an InvalidOperation during money coercion is caught per row, logged, and the row is skipped rather than aborting the whole document. Compliant rows in the same table proceed, and the failed row surfaces through the audit log for manual triage.

← Back to Field Extraction Techniques