CAM Reconciliation

Parsing Complex CAM Invoices with Tabula and Pandas

Some vendor statements defeat a pure text extractor: a national HVAC provider bills a mall across a bordered grid, then nests a second sub-table of per-suite filter changes inside a merged cell, and closes with a right-aligned subtotal block that shares no column boundaries with the line items above it. When the coordinate-aware PDF Invoice Extraction with Python and pdfplumber stage returns misaligned rows on these ruled, multi-table layouts, tabula-py plus pandas is the right tool — Tabula reconstructs the grid from the invoice’s own borders, and pandas gives you a typed, columnar surface to clean before the numbers reach the general ledger. This page is a focused implementation recipe within the Automated Invoice Parsing & Data Ingestion pipeline for exactly that scenario.

Hybrid Tabula and pandas extraction pipeline Six stages in a horizontal pipeline. A complex invoice of ruled, nested tables feeds Tabula (lattice or stream mode), which yields a typed pandas DataFrame; that is normalized for headers and wrapped rows, then money cells are coerced to exact Decimal while subtotal rows are excluded, and the clean records are mapped to GL codes for the recoverable pool. Complex invoice ruled, nested tables Tabula lattice / stream pandas typed DataFrame Normalize headers, wrapped rows To Decimal drop subtotals GL mapping recoverable pool Hybrid Tabula and pandas extraction pipeline

Context & When to Use This Approach

Reach for Tabula and pandas when the invoice has drawn table borders and pdfplumber’s text-based column inference has already failed you. The concrete triggers on a CAM portfolio are consistent:

  • Ruled, bordered expense grids. Tabula’s lattice mode reads the vertical and horizontal rules a vendor draws around each cell, so it recovers structure that whitespace heuristics miss. This is the common case for utility and property-management statements generated by enterprise billing systems.
  • Nested or multi-row line items. A single logical charge (“Q3 Chiller PM — Suites 100–140”) wraps across two or three physical rows, or embeds a sub-table of covered units. pandas lets you collapse those fragments back into one record.
  • Right-aligned subtotal and gross-up blocks that break column alignment near the page footer and must be excluded from the recoverable line-item set before they double-count into the CAM pool.
  • Merged header cells where one label spans several money columns (for example a “Labor / Materials / Tax” super-header), which pandas can flatten into unambiguous field names.

If the vendor sends a photographed or scanned statement with no text layer, Tabula cannot help — that document belongs on the OCR path described in the parent extraction stage. And if the invoice is a clean single-table PDF, stay with pdfplumber; Tabula’s Java dependency is only worth carrying when the layout genuinely demands lattice reconstruction. For statements that merely span many pages rather than nesting tables, the streaming approach in handling multi-page commercial invoices in Python is the better fit.

Lattice vs stream detection on a complex CAM invoice region Two panels. Left, lattice mode: a bordered expense table (Description, Qty, Amount) whose drawn vertical and horizontal rules Tabula snaps into a cell grid, marked by dots at every line intersection. One row is a merged Description cell containing a nested per-suite filter sub-table that multiple_tables keeps separate, and the bottom CAM Total row is a right-aligned summary that the subtotal filter drops rather than sums. Right, stream mode: the same data with no drawn rules; each word sits in a bounding box, and dashed vertical lines mark the column separators inferred from the whitespace gaps between those boxes. A wrapped continuation row carries a blank amount and is coalesced into the charge above it. Lattice mode snaps to drawn borders Stream mode infers columns from gaps Description Qty Amount Chiller PM 4 500.00 Filter changes 3 180.00 per-suite filters Suite 1001 Suite 1201 Suite 1401 CAM Total 680.00 EXCLUDED dots = ruled intersections snapped into a cell grid merged cell holds a nested sub-table, kept separate right-aligned “CAM Total” row is dropped, not summed gap→split gap→split Description Qty Amount Q3 Chiller PM — 4 500.00 Suites 100–140 (blank) coalesced ↑ Landscaping 2 90.00 no drawn rules: columns come from whitespace gaps dashed lines = inferred column separators continuation row (blank amount) merged into the row above Same invoice region, two detectors lattice=True when borders are drawn stream=True when the grid is faked or blank A single wide, jumbled column from lattice is the signal to fall back to stream.

Step-by-Step Implementation

The pipeline below turns one complex PDF into a list of clean, typed records. Every monetary value is carried as decimal.Decimal — never float — because a single half-cent of binary rounding drift compounds across hundreds of line items and breaks the pro-rata math downstream.

Step 1 — Install the toolchain and confirm the Java runtime. tabula-py is a thin wrapper that shells out to a bundled Java .jar, so a JRE (11+) must be on the path. Extraction of a rotated or oddly cropped grid fails silently if Java is missing, so probe it explicitly at startup rather than mid-batch.

from __future__ import annotations

import shutil
import subprocess


def assert_java_available() -> None:
    """Fail fast if the JRE that Tabula shells out to is absent."""
    if shutil.which("java") is None:
        raise RuntimeError("Tabula requires a Java runtime (JRE 11+) on PATH")
    # `java -version` writes to stderr; a non-zero exit means a broken JRE.
    subprocess.run(["java", "-version"], capture_output=True, check=True)

Step 2 — Extract every table with the lattice reader. Point Tabula at the ruled grid. lattice=True uses the drawn borders; pages="all" sweeps a multi-page statement; multiple_tables=True returns one DataFrame per detected table so a nested sub-table does not silently merge into its parent.

from decimal import Decimal

import pandas as pd
import tabula


def extract_tables(pdf_path: str) -> list[pd.DataFrame]:
    """Return one DataFrame per table region Tabula finds in the invoice."""
    tables: list[pd.DataFrame] = tabula.read_pdf(
        pdf_path,
        pages="all",
        lattice=True,          # reconstruct cells from the invoice's ruled borders
        multiple_tables=True,
        pandas_options={"dtype": str},  # keep raw strings; we coerce money ourselves
    )
    return [t for t in tables if not t.empty]

Step 3 — Fall back to the stream reader for borderless regions. If lattice returns a single wide, jumbled column (the signature of a table with no drawn rules), re-run that page with stream=True, which infers columns from the horizontal gaps between words.

def extract_with_fallback(pdf_path: str) -> list[pd.DataFrame]:
    tables = extract_tables(pdf_path)
    looks_unruled = any(frame.shape[1] <= 1 for frame in tables)
    if not tables or looks_unruled:
        tables = tabula.read_pdf(
            pdf_path, pages="all", stream=True,
            multiple_tables=True, pandas_options={"dtype": str},
        )
    return [t for t in tables if not t.empty]

Step 4 — Normalize headers and flatten merged super-headers. Vendor grids arrive with ragged whitespace, Unnamed: N placeholder columns from merged cells, and inconsistent casing. Collapse them to stable field names your schema expects.

import re

CANONICAL = {
    "description": "expense_description",
    "desc": "expense_description",
    "qty": "quantity",
    "unit price": "unit_cost",
    "unit cost": "unit_cost",
    "amount": "line_total",
    "total": "line_total",
}


def normalize_headers(frame: pd.DataFrame) -> pd.DataFrame:
    """Lower-case, de-whitespace, and remap vendor headers to canonical fields."""
    cleaned: list[str] = []
    for col in frame.columns:
        key = re.sub(r"\s+", " ", str(col)).strip().lower()
        cleaned.append(CANONICAL.get(key, key.replace(" ", "_")))
    frame = frame.copy()
    frame.columns = cleaned
    # Drop the empty spacer columns Tabula emits for merged/blank cells.
    return frame.loc[:, [c for c in frame.columns if not c.startswith("unnamed")]]

Step 5 — Reunite wrapped line items. A description that wraps onto the next physical row leaves a fragment with a blank line_total. Forward-fill the identifying columns and concatenate the continuation text so each charge becomes one row.

def coalesce_wrapped_rows(frame: pd.DataFrame) -> pd.DataFrame:
    """Merge continuation rows (blank money column) into the charge above them."""
    frame = frame.reset_index(drop=True)
    keep: list[int] = []
    for i, row in frame.iterrows():
        total_blank = not str(row.get("line_total", "")).strip()
        if total_blank and keep:
            prev = keep[-1]
            frame.at[prev, "expense_description"] = (
                f"{frame.at[prev, 'expense_description']} "
                f"{str(row.get('expense_description', '')).strip()}"
            ).strip()
        else:
            keep.append(i)
    return frame.loc[keep].reset_index(drop=True)

Step 6 — Drop subtotal noise and coerce money to Decimal. Strip currency symbols, thousands separators, and footnote markers, then quarantine the summary rows (“Subtotal”, “CAM Total”, “Tax”) that must not enter the recoverable pool. Convert through str → Decimal so no float ever touches a dollar amount.

from decimal import Decimal, InvalidOperation

SUMMARY_MARKERS = ("subtotal", "cam total", "grand total", "tax", "balance due")


def to_decimal(raw: str) -> Decimal:
    """Parse a money cell exactly; strip $ , and trailing footnote symbols."""
    cleaned = re.sub(r"[^\d.\-]", "", str(raw))
    try:
        return Decimal(cleaned)
    except (InvalidOperation, ValueError) as exc:
        raise ValueError(f"unparseable money cell: {raw!r}") from exc


def finalize(frame: pd.DataFrame) -> list[dict[str, object]]:
    """Emit clean line-item records; summary rows are excluded, not summed."""
    records: list[dict[str, object]] = []
    for _, row in frame.iterrows():
        desc = str(row.get("expense_description", "")).strip()
        if any(marker in desc.lower() for marker in SUMMARY_MARKERS):
            continue  # subtotal / gross-up rows never enter the CAM pool
        records.append({
            "expense_description": desc,
            "quantity": int(re.sub(r"[^\d]", "", str(row.get("quantity", "0"))) or 0),
            "unit_cost": to_decimal(row.get("unit_cost", "0")),
            "line_total": to_decimal(row.get("line_total", "0")),
        })
    return records

The list of dictionaries finalize returns is deliberately shaped, not yet trusted — cross-field typing, required-field enforcement, and quarantine disposition belong to schema validation for parsed expense data, and the standardized buckets each expense_description resolves to are assigned by GL code mapping for CAM expenses.

Gotchas & Known Limitations

  • Java is a hard dependency. tabula-py is not pure Python; a container without a JRE fails at first call. Pin the runtime in your image and probe it with the Step 1 guard.
  • Lattice needs real rules, not shading. Vendors who fake a grid with alternating cell background colors (no drawn lines) will yield garbage under lattice=True. Detect the single-wide-column signature and fall back to stream=True.
  • multiple_tables=True can over-segment. A page break mid-table produces two DataFrames with the same schema; concatenate adjacent frames whose columns match before normalizing.
  • Never let pandas infer money dtypes. Reading with dtype=str and converting through Decimal yourself is mandatory — read_pdf defaults will parse 1,234.50 into a lossy float64.
  • Rotated pages return empty. Tabula does not auto-deskew; detect a page rotation flag upstream and pass the page through a rotation-normalizing step first.
  • Subtotal rows masquerade as line items. A right-aligned “CAM Total” row often carries a value in line_total and nothing else; the SUMMARY_MARKERS filter is what stops it from double-counting into recoverable expenses.
  • Merged header cells leak Unnamed: N columns. Always run normalize_headers before any positional column access, or an off-by-one shift silently misfiles unit_cost as quantity.

Verification

Because these records feed lease-level allocation, correctness is checked arithmetically, not by eyeballing the DataFrame. The load-bearing invariant is that each cleaned line reconciles internally, and that the sum of line items matches the invoice’s own stated CAM total to the cent.

from decimal import Decimal


def verify_records(records: list[dict[str, object]],
                   stated_cam_total: Decimal) -> None:
    """Assert per-line and invoice-level arithmetic before GL posting."""
    running = Decimal("0")
    for rec in records:
        expected = rec["unit_cost"] * Decimal(rec["quantity"])
        # Per-line cross-check catches a misread digit or a shifted column.
        assert rec["line_total"] == expected, (
            f"line mismatch: {rec['expense_description']!r} "
            f"{rec['line_total']} != {expected}"
        )
        running += rec["line_total"]
    # Invoice-level cross-check against the vendor's own printed subtotal.
    assert running == stated_cam_total, f"pool drift: {running} != {stated_cam_total}"


records = [
    {"expense_description": "Chiller PM", "quantity": 4,
     "unit_cost": Decimal("125.00"), "line_total": Decimal("500.00")},
]
verify_records(records, stated_cam_total=Decimal("500.00"))

Two assertions do the real work. The per-line check (unit_cost * quantity == line_total) catches a misread digit or a column that shifted because a merged header was not flattened. The invoice-level check confirms your extracted, subtotal-filtered set reconstructs the vendor’s printed CAM total exactly — if it drifts, a summary row slipped through the filter or a wrapped line was double-counted. Only records that clear both assertions should be handed to the validation gate; anything that fails routes to manual review with the source page number attached, preserving the audit trail a CAM reconciliation must reproduce on demand.

Once these records validate, they flow back into the PDF Invoice Extraction with Python and pdfplumber stage’s canonical output, and from there into pro-rata allocation where each tenant’s share of the reconciled pool is computed.