Python Doc & Data Automation

Fix Camelot Wrong Column Split in Stream Mode

Camelot's stream flavor finds the borderless price list but gets the columns wrong. The Qty and Unit price values land in one column as 12 4.10. Long descriptions such as A4 copier paper, 80gsm, 5 reams are split across three columns, pushing the prices on those rows one or two cells to the right. The shape reported by tables[0].shape changes from one page to the next, so concatenating pages produces a frame where the same column holds different data on every page.

>>> tables = camelot.read_pdf("in/price-list.pdf", flavor="stream", pages="1-3")
>>> [t.shape for t in tables]
[(48, 5), (51, 7), (47, 6)]

Root Cause

Without ruling lines, stream mode must infer where columns are from the text itself. It groups words into rows by vertical position, then estimates column boundaries from gaps in the horizontal distribution of text across all rows of the table area. Two layouts defeat that estimate. Right-aligned numbers next to left-aligned numbers leave only a small gap between the columns, so camelot sees one column (Qty and Unit price merge). Long text with internal spaces wide enough to look like gaps — justified descriptions, double spaces after commas — creates false boundaries, and because the estimate is computed per table, one long description on a page adds columns for that page only. The inferred grid is a heuristic; when the document's column positions are fixed, the reliable fix is to tell camelot where the boundaries are.

Minimal Diagnostic

Look at what camelot actually inferred. Print the column boundaries it chose on each page and plot the text positions to see where the real gutters are.

# pip install "camelot-py[base]" matplotlib
from pathlib import Path
import camelot

SOURCE = Path("in/price-list.pdf")

def show_columns(pdf_path: Path, pages: str = "1-3") -> None:
    try:
        tables = camelot.read_pdf(str(pdf_path), flavor="stream", pages=pages)
    except Exception as exc:
        raise SystemExit(f"camelot failed on {pdf_path}: {exc}")
    for t in tables:
        edges = [round(c[0], 1) for c in t.cols] + [round(t.cols[-1][1], 1)]
        print(f"page {t.page}: shape {t.shape}, column edges {edges}")
        print("   first row:", t.df.iloc[1].tolist())
    fig = camelot.plot(tables[1], kind="text")           # every text item as a box
    fig.savefig("out/page2-text.png", dpi=110)

if __name__ == "__main__":
    show_columns(SOURCE)

page 1: shape (48, 5), column edges [36.0, 101.2, 318.6, 372.5, 448.0, 559.1]
   first row: ['PA-1001', 'Copier paper A4 80gsm', '12 4.10', '49.20', '']
page 2: shape (51, 7), column edges [36.0, 101.2, 190.3, 244.8, 318.6, 402.1, 448.0, 559.1]
   first row: ['PA-2044', 'A4 copier paper,', '80gsm,', '5 reams', '10 18.90', '189.00', '']

Page 1 has one boundary too few (quantity and price merged, plus an empty trailing column); page 2 added boundaries at 190 and 244 inside the description. The text plot shows the real gutters at roughly x = 318, 360 and 448 on every page.

Inferred columns versus declared columns The left panel shows inferred column edges that merge quantity and unit price into one column on page 1 and split long descriptions into three columns on page 2, giving shapes of 5 and 7 columns. The right panel shows the same pages extracted with declared column boundaries at 101, 318, 360 and 448 points, giving the same five columns on every page with quantity and price separated. Inferred per page p1 edges: 101 318 372 448 -> '12 4.10' merged p2 edges: 101 190 244 318 402 448 -> description in 3 cells shapes: 5, 7, 6 columns columns=['101,318,360,448'] p1: code|desc|qty|price|total p2: code|desc|qty|price|total p3: code|desc|qty|price|total long descriptions stay whole shapes: 5, 5, 5 columns

Fix: Declare Column Boundaries and the Table Area

Pass columns with the x-coordinates of the boundaries between columns (not including the outer edges), together with table_areas so the boundaries apply to a known region. split_text=True splits a text item that crosses a declared boundary. Changed lines carry comments.

# pip install "camelot-py[base]" "pandas>=2.2"
from pathlib import Path
import camelot
import pandas as pd

SOURCE = Path("in/price-list.pdf")
AREA = "36,760,559,60"                       # x1,y1,x2,y2 — top-left and bottom-right, PDF origin bottom-left
COLUMNS = "101,318,360,448"                  # changed: boundaries between the five columns
NAMES = ["code", "description", "qty", "unit_price", "line_total"]

def extract_price_list(pdf_path: Path, pages: str = "1-end") -> pd.DataFrame:
    try:
        tables = camelot.read_pdf(
            str(pdf_path),
            flavor="stream",
            pages=pages,
            table_areas=[AREA],              # changed: one area, repeated for every page
            columns=[COLUMNS],               # changed: one columns string per table area
            split_text=True,                 # changed: split text that straddles a boundary
            row_tol=4,                       # changed: merge wrapped lines of one row
            strip_text="\n",
        )
    except Exception as exc:
        raise RuntimeError(f"camelot failed: {exc}") from exc
    frames = []
    for t in tables:
        df = t.df.copy()
        if df.shape[1] != len(NAMES):
            raise ValueError(f"page {t.page}: {df.shape[1]} columns, expected {len(NAMES)}")
        df.columns = NAMES
        df = df[df["code"].str.match(r"^[A-Z]{2}-\d{4}$", na=False)]   # drop header and footer rows
        frames.append(df.assign(page=int(t.page)))
    return pd.concat(frames, ignore_index=True)

if __name__ == "__main__":
    prices = extract_price_list(SOURCE)
    print(prices.head(), prices.shape)

columns must be a list with one comma-separated string per entry in table_areas, and the numbers are x-coordinates in PDF points. The boundary between right-aligned qty and unit_price belongs in the gutter to the left of the price column's widest value — here 360 — not halfway between column centres, which would cut into long prices. Filtering rows by the code pattern removes headers repeated on each page and totals rows in one step.

Where to place column boundaries The table area spans from x 36 to 559 points. The code column ends before 101, the description column runs to 318, quantity values are right aligned and end before 360, unit prices are right aligned and end before 448, and line totals fill the rest. Boundaries are placed in the empty gutters just left of each column's widest value so long descriptions and wide prices stay in their cells. Price list table area, x 36 to 559 pt 1 1 code (36-101) boundary at 101 2 2 description (101-318) boundary at 318 3 3 qty (318-360) right-aligned 4 4 unit price (360-448) right-aligned 5 5 line total (448-559) last column

Variant Fix 1: Tune Instead of Declaring, for Varying Layouts

When suppliers send price lists with slightly different column positions, hard-coded boundaries break. Tune the inference instead: column_tol controls how close text must be to count as the same column, and edge_tol how far table edges may extend.

# pip install "camelot-py[base]"
import camelot

def tuned_stream(path: str, pages: str = "1-end"):
    return camelot.read_pdf(
        path, flavor="stream", pages=pages,
        column_tol=10,        # larger: fewer spurious columns from spaced descriptions
        edge_tol=500,         # larger: extend the detected table vertically over sparse rows
        row_tol=6,
    )

for tol in (0, 5, 10, 20):
    shapes = [t.shape[1] for t in camelot.read_pdf("in/price-list.pdf", flavor="stream",
                                                    pages="1-3", column_tol=tol)]
    print(f"column_tol={tol:>2}: columns per page {shapes}")

Sweep column_tol on a few representative pages and pick the smallest value that gives a stable column count on all of them. Too large a value merges genuine narrow columns, so check the result as carefully as the default. Where layouts vary more than tolerances can absorb, derive the boundaries per document from header word positions — the same anchoring idea used for regions in extract a PDF table by bounding box coordinates.

Variant Fix 2: Derive Boundaries from the Header Row

Header labels sit above their columns, so their positions give boundaries that follow the layout of each file. Use pdfplumber to find the words, then feed camelot:

# pip install pdfplumber "camelot-py[base]"
import pdfplumber

HEADERS = ["Code", "Description", "Qty", "Unit price", "Total"]

def boundaries_from_header(pdf_path: str, page_index: int = 0) -> str:
    with pdfplumber.open(pdf_path) as pdf:
        words = pdf.pages[page_index].extract_words(keep_blank_chars=True, x_tolerance=2)
    found = {}
    for label in HEADERS:
        match = next((w for w in words if w["text"].strip().lower() == label.lower()), None)
        if match is None:
            raise ValueError(f"header {label!r} not found")
        found[label] = match
    lefts = [found[h]["x0"] for h in HEADERS]
    # boundary just left of each header after the first; right-aligned headers sit over right-aligned values
    return ",".join(f"{x - 4:.0f}" for x in lefts[1:])

cols = boundaries_from_header("in/price-list-supplier-b.pdf")
print(cols)   # e.g. '97,322,355,441'

For right-aligned numeric columns whose values are wider than the header text, the header's left edge can be to the right of the widest value; widen those boundaries by checking the minimum x0 of numeric words below each header before finalising.

Choosing a column strategy The root asks how stable the column positions are. Fixed positions in one template use declared columns with table areas. Positions that vary by supplier but have consistent header labels use boundaries derived from header word positions. Loosely varying layouts without reliable headers use a column_tol sweep. Tables that do have ruling lines should switch to lattice mode instead of stream. How stable are column positions? compare 3-5 sample files fixed template Declared columns plus table_areas varies, same headers From header words pdfplumber positions varies, no headers column_tol sweep stable column count has ruling lines Lattice mode lines define cells

Merging Rows Split by Wrapped Descriptions

Once columns are right, the next visible defect is usually vertical: a description that wraps onto a second line becomes a second row with an empty code and empty numbers. row_tol fixes small gaps, but descriptions that wrap by a full line height need a merge step after extraction. Treat any row without a code as a continuation of the row above:

# pip install "pandas>=2.2"
import pandas as pd

def merge_continuations(frame: pd.DataFrame, key: str = "code", text_col: str = "description") -> pd.DataFrame:
    rows: list[dict] = []
    for record in frame.to_dict("records"):
        is_continuation = not str(record.get(key) or "").strip()
        if is_continuation and rows:
            extra = str(record.get(text_col) or "").strip()
            if extra:
                rows[-1][text_col] = f"{rows[-1][text_col]} {extra}".strip()
            for col, value in record.items():                 # numbers occasionally sit on the second line
                if col not in (key, text_col) and str(value or "").strip() and not str(rows[-1].get(col) or "").strip():
                    rows[-1][col] = value
        else:
            rows.append(dict(record))
    return pd.DataFrame(rows, columns=frame.columns)

Apply the merge before filtering rows by the code pattern — otherwise continuation lines are discarded as non-matching and their words vanish from descriptions. Also check the first row of each page: a description that wraps across a page break starts the next page with a continuation line that has no row above it on that page. Concatenate all pages first, then merge, so the continuation attaches to the last row of the previous page.

Verification

Assert that every page produces the same number of columns, that numeric columns parse, and that quantity times unit price equals the line total — the arithmetic catches any column shift immediately.

# pip install "pandas>=2.2"
import pandas as pd

def to_num(series: pd.Series) -> pd.Series:
    return pd.to_numeric(series.astype("string").str.replace(",", "").str.strip(), errors="coerce")

def verify_prices(frame: pd.DataFrame) -> None:
    qty, price, total = to_num(frame["qty"]), to_num(frame["unit_price"]), to_num(frame["line_total"])
    unparsed = frame[qty.isna() | price.isna() | total.isna()]
    assert unparsed.empty, f"{len(unparsed)} row(s) with non-numeric values, e.g. {unparsed.iloc[0].tolist()}"
    mismatch = frame[(qty * price - total).abs() > 0.01]
    assert mismatch.empty, f"{len(mismatch)} row(s) where qty x price != total, e.g. {mismatch.iloc[0].tolist()}"
    long_desc = frame["description"].str.len().max()
    print(f"{len(frame)} rows across {frame['page'].nunique()} page(s); longest description {long_desc} chars")

A merged qty/unit_price cell fails numeric parsing; a split description shifts numbers and fails the multiplication. Both failures point at the exact row, which is usually the one with the longest description or widest number.

FAQ

Why does lattice mode not have this problem? Lattice uses drawn lines to define cells. If the PDF has ruling lines — even faint ones — try flavor="lattice" first; column inference only happens in stream mode.

Do columns values need the table's outer edges? No. Give only the internal boundaries; the table area provides the outer edges.

Why do wrapped descriptions become separate rows? Each line of a wrapped cell is a separate text row. Increase row_tol, or merge rows whose code cell is empty into the previous row after extraction.

Can I reuse one columns string for many table areas? No — provide one string per area, in the same order as table_areas, even if they are identical.

Part of Extracting Tables from PDFs.