Python Doc & Data Automation

Fix PDF Columns Merged into One DataFrame Column

The extraction returns a DataFrame with one column and every field jammed into it:

>>> frame.head(3)
                                                   0
0   INV-100417 Acme Ltd 15/07/2026 1,204.50 settled
1   INV-100418 Beta Group 16/07/2026 98.00 pending
2   INV-100419 Gamma Partners LLP 16/07/2026 42.00 failed

Splitting on whitespace looks like the obvious fix and immediately breaks: Gamma Partners LLP is three tokens, Acme Ltd is two, so the columns misalign from row three onwards. The information needed to separate the fields is not in the text at all — it is in the positions the text was drawn at.

Root Cause

A PDF has no table structure. A "table" is text drawn at coordinates that a human reads as columns. When camelot runs in stream mode, or pdfplumber extracts text, the library groups characters into lines and then guesses column boundaries from the size of the horizontal gaps. If the gaps between columns are no wider than the gaps between words — common with narrow layouts, condensed fonts or right-aligned numbers — the guess fails and everything lands in one column.

The fix is to stop guessing from gaps and use the x-coordinates directly, which every character in the page carries.

Why gap-based splitting fails and coordinates do not A page fragment shows four column bands defined by x coordinate ranges. Row one has a two word supplier name and row two has a three word supplier name. Splitting the row text on whitespace produces five tokens for one row and six for the other, so the columns shift. Assigning each word to a band by its x coordinate puts the supplier words in the same band regardless of how many there are. Column bands are ranges of x, not counts of words x 40-180 x 180-420 x 420-570 x 570-720 INV-100417 Acme Ltd 15/07/2026 1,204.50 INV-100419 Gamma Partners LLP 16/07/2026 42.00 split on whitespace: 5 tokens vs 6 every later column shifts by one bucket by x: 4 columns, always word count is irrelevant

Minimal Diagnostic

Look at where the words actually are. A histogram of x-positions shows the column bands directly.

# pip install pdfplumber "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd
import pdfplumber

PDF = Path("data/invoices.pdf")

def word_positions(pdf_path: Path, page_number: int = 1) -> pd.DataFrame:
    """Every word with its left edge, so column bands become visible."""
    if not pdf_path.exists():
        raise FileNotFoundError(f"No such file: {pdf_path}")
    try:
        with pdfplumber.open(str(pdf_path)) as pdf:
            words = pdf.pages[page_number - 1].extract_words(x_tolerance=1.5, y_tolerance=3)
    except Exception as exc:
        raise RuntimeError(f"Could not read {pdf_path}: {exc}") from exc
    if not words:
        raise ValueError("No words on the page — this is a scan; OCR it first")
    return pd.DataFrame(words)[["text", "x0", "x1", "top"]]

def column_starts(frame: pd.DataFrame, bin_width: int = 5, min_words: int = 3) -> list[float]:
    """Cluster left edges into likely column start positions."""
    counts = (frame["x0"] // bin_width * bin_width).value_counts().sort_index()
    return [float(x) for x, n in counts.items() if n >= min_words]

if __name__ == "__main__":
    words = word_positions(PDF)
    print(words.head(10))
    print("column starts:", column_starts(words))

column starts: [56.0, 148.0, 336.0, 432.0, 512.0]

Five clusters of left edges means five columns, whatever the whitespace suggests. Those numbers become the boundaries used below.

The Fix: Bucket Words by x-Coordinate

Group words into rows by their vertical position, then assign each word to a column by where its left edge falls.

# pip install pdfplumber "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd
import pdfplumber

def extract_by_columns(pdf_path: Path, boundaries: list[float], headers: list[str],
                       page_number: int = 1, row_tolerance: float = 3.0) -> pd.DataFrame:
    """Rebuild a table by assigning each word to a column band."""
    if len(headers) != len(boundaries):
        raise ValueError(f"{len(headers)} header(s) for {len(boundaries)} column(s)")

    with pdfplumber.open(str(pdf_path)) as pdf:
        words = pdf.pages[page_number - 1].extract_words(x_tolerance=1.5, y_tolerance=3)

    rows: dict[float, list[list[str]]] = {}
    for word in sorted(words, key=lambda w: (w["top"], w["x0"])):
        # Group into a line: reuse an existing row whose top is within tolerance
        key = next((t for t in rows if abs(t - word["top"]) <= row_tolerance), word["top"])
        cells = rows.setdefault(key, [[] for _ in headers])

        # Assign to the last band whose start is at or left of this word
        index = max((i for i, x in enumerate(boundaries) if word["x0"] >= x - 1), default=0)
        cells[index].append(word["text"])

    records = [
        {header: " ".join(parts) for header, parts in zip(headers, cells)}
        for _top, cells in sorted(rows.items())
    ]
    return pd.DataFrame(records)

if __name__ == "__main__":
    frame = extract_by_columns(
        Path("data/invoices.pdf"),
        boundaries=[56.0, 148.0, 336.0, 432.0, 512.0],
        headers=["invoice_no", "supplier", "issued_on", "net_amount", "status"],
    )
    print(frame.head())

The - 1 in the boundary comparison absorbs sub-point rendering jitter, which is enough to push a word one pixel left of its nominal column start and into the previous band. Joining each band's words with a space is what makes multi-word values work: Gamma, Partners and LLP all fall in the supplier band and are rejoined there.

Variant Fix 1: Give camelot Explicit Column Positions

If you are already using camelot in stream mode, pass the boundaries rather than reimplementing the bucketing.

# pip install "camelot-py[cv]" pandas
import camelot

tables = camelot.read_pdf(
    "data/invoices.pdf",
    pages="1",
    flavor="stream",
    columns=["148,336,432,512"],   # x positions of the column SEPARATORS, one string per page
    split_text=True,               # split a word that straddles a boundary
    strip_text="\n",
)
print(tables[0].df.head())

Note the off-by-one in meaning: columns takes the separator positions, not the column starts, so the first start is omitted. Getting that wrong shifts every column by one and produces output that looks structurally right and is entirely misaligned.

Variant Fix 2: Right-Aligned Numeric Columns

Amount columns are usually right-aligned, so their left edges vary with the number's width and cluster poorly. Bucket those on the right edge instead.

# pip install pdfplumber "pandas>=2.2,<3"
def assign_band(word: dict, boundaries: list[float], right_aligned: set[int]) -> int:
    """Left edge for normal columns, right edge for right-aligned ones."""
    for index in range(len(boundaries) - 1, -1, -1):
        edge = word["x1"] if index in right_aligned else word["x0"]
        if edge >= boundaries[index] - 1:
            return index
    return 0

The tell-tale symptom of getting this wrong is a numeric column that is correct for three-digit values and shifts left for six-digit ones, so totals are systematically misassigned — the kind of error that survives review because most rows look fine.

Variant Fix 3: The Layout Changes Between Documents

Hard-coded boundaries break when the template moves. Detect them per document, then sanity-check the count.

# pip install pdfplumber "pandas>=2.2,<3"
from pathlib import Path

def detect_boundaries(pdf_path: Path, expected_columns: int, page_number: int = 1) -> list[float]:
    """Find column starts and fail loudly when the count is not what the schema expects."""
    words = word_positions(pdf_path, page_number)
    starts = column_starts(words, bin_width=5, min_words=max(3, len(words) // 40))
    if len(starts) != expected_columns:
        raise ValueError(
            f"{pdf_path.name}: detected {len(starts)} column(s) at {starts}, "
            f"expected {expected_columns} — template may have changed"
        )
    return starts

Failing rather than proceeding with the wrong count is the right trade. A misdetected layout produces a frame that passes every shape check and contains scrambled values, which is exactly what the schema gate in validating document data with schemas exists to catch — but catching it here, with the document name attached, is cheaper.

From positioned words to a typed frame Words with x and y coordinates are extracted from the page. They are grouped into rows by their vertical position within a tolerance. Each word is then assigned to a column band by its horizontal edge. The resulting records become a DataFrame, which is validated against a schema before anything downstream uses it. extract_words text + x0, x1, top no structure yet group by top tolerance ~3pt gives rows band by x left or right edge gives columns validate schema gate then publish Structure is reconstructed, never read — the PDF never had any Detect the bands per document and fail when the count is unexpected, rather than hard-coding positions that a template change will silently invalidate

Verification

Assert the shape and the plausibility of each column's contents — a misassigned band shows up immediately as a column whose values do not match its type.

# pip install "pandas>=2.2,<3"
import pandas as pd

def assert_columns_sane(frame: pd.DataFrame, numeric: list[str], dated: list[str],
                        min_rows: int = 1) -> None:
    """Fail when a column's contents do not match what that column should hold."""
    assert len(frame.columns) > 1, "frame still has one column — banding did not run"
    assert len(frame) >= min_rows, f"only {len(frame)} row(s) extracted"

    for column in numeric:
        cleaned = frame[column].astype(str).str.replace(r"[£$,\s]", "", regex=True)
        share = pd.to_numeric(cleaned, errors="coerce").notna().mean()
        assert share > 0.9, (
            f"{column}: only {share:.0%} of values are numeric — band boundary is wrong"
        )

    for column in dated:
        share = pd.to_datetime(frame[column], errors="coerce", format="mixed").notna().mean()
        assert share > 0.9, f"{column}: only {share:.0%} of values parse as dates"

    empty = [c for c in frame.columns if frame[c].astype(str).str.strip().eq("").mean() > 0.5]
    assert not empty, f"column(s) mostly empty — too many bands: {empty}"
    print(f"{len(frame):,} row(s) across {len(frame.columns)} column(s) verified")

if __name__ == "__main__":
    assert_columns_sane(frame, numeric=["net_amount"], dated=["issued_on"])

The mostly-empty check catches the opposite error to the one this page opens with: too many boundaries, producing columns that only occasionally receive a word. Together the two assertions bracket the problem from both sides, and both are cheap enough to run on every document in a batch — see extracting PDF data into pandas for where they sit in the wider flow.

Reading column starts off an x-position histogram A histogram of the left edge of every word on the page, binned every five points. Four tall spikes stand at x fifty six, one hundred and forty eight, four hundred and thirty two and five hundred and twelve, each containing dozens of words. The gaps between them are almost empty. Those spikes are the column starts, and reading them off the histogram is more reliable than guessing from whitespace. Word left edges, binned every 5pt 56 148 432 512 Four spikes, four columns — the flat bins between them are stray words, not boundaries

FAQ

Why not just split on two-or-more spaces? Because the gap between columns is not reliably larger than the gap between words, especially in condensed or justified layouts. It works on some documents and fails silently on others, which is worse than failing everywhere.

Where do I get the boundary numbers from? From the document itself, using the x-position histogram in the diagnostic. Detect them per file when templates vary, and assert the expected column count so a change fails loudly.

Does this work for multi-page tables? Yes — run the banding per page and concatenate, taking the header from the first page only. The header-deduplication logic in handle multi-page PDF tables in pandas applies unchanged.

What if a value legitimately spans two columns? That is a merged cell in the original layout. Detect it as a word whose x-range crosses a boundary, and decide deliberately: assign it to the band containing its midpoint, or flag the row for review.

Is camelot lattice mode a better answer? When the table has ruling lines, yes — lattice reconstructs cells from the grid and needs none of this. Coordinate banding is for borderless tables, where lattice finds nothing at all.

Part of Extracting PDF Data into pandas.