Python Doc & Data Automation

Combine Tables from Many PDFs into One DataFrame

Four hundred monthly statements, one table each, one combined dataset. The direct approach produces something unusable:

frames = [camelot.read_pdf(p)[0].df for p in Path("in").glob("*.pdf")]
combined = pd.concat(frames, ignore_index=True)

The result has 8,412 rows where the files hold about 7,900, columns named 0 through 5, header text appearing as data every few hundred rows, and — because two files used a slightly different layout — a scattering of rows where the amount is in the description column.

Root Cause

Each PDF is extracted independently and nothing enforces that the results agree. Three specific things go wrong. Header rows come back as data, because table extractors return the header as row zero unless told otherwise, so concatenating four hundred frames adds four hundred header rows to the dataset. Column identity is positional: pd.concat aligns on column labels, and when those labels are integers from the extractor, a file whose table has an extra leading column silently shifts every value one place. And once combined, there is no column saying which file or page a row came from, so a wrong figure cannot be traced back to the document that produced it. All three are fixed before the concat, not after.

Combining many extractions safely List the input files in a deterministic order. Extract each one independently, promoting its first row to a header and normalising the column names. Check the resulting columns against an expected schema, quarantining any file that does not match rather than concatenating it. Add provenance columns naming the source file, page and row. Concatenate the aligned frames. Reconcile the row count and column totals against the per-file figures recorded during extraction. List files, sorted deterministic order Extract per file independently Promote the header row zero is not data Check the schema quarantine mismatches Tag provenance file, page, row Concat and reconcile counts must agree

Minimal Diagnostic

Before combining anything, find out how many distinct table shapes the corpus contains.

# pip install pandas camelot-py
from collections import Counter
from pathlib import Path
import camelot
import pandas as pd

IN = Path("in")

def shape_survey(limit: int | None = None) -> None:
    shapes, failures = Counter(), []
    files = sorted(IN.glob("*.pdf"))[:limit]
    for pdf in files:
        try:
            tables = camelot.read_pdf(str(pdf), pages="all", flavor="lattice")
        except Exception as error:
            failures.append((pdf.name, f"{type(error).__name__}: {error}"))
            continue
        if not tables:
            failures.append((pdf.name, "no tables detected"))
            continue
        for table in tables:
            header = tuple(str(value).strip().lower() for value in table.df.iloc[0])
            shapes[header] += 1
    print(f"{len(files)} file(s), {len(shapes)} distinct header shape(s), {len(failures)} failure(s)")
    for header, count in shapes.most_common():
        print(f"  {count:>4}x {header}")
    for name, reason in failures[:5]:
        print(f"  FAIL {name}: {reason}")

if __name__ == "__main__":
    shape_survey(limit=50)

50 file(s), 3 distinct header shape(s), 2 failure(s)
   41x ('date', 'description', 'debit', 'credit', 'balance')
     5x ('date', 'ref', 'description', 'debit', 'credit', 'balance')
     2x ('', 'date', 'description', 'debit', 'credit', 'balance')
  FAIL 2024-03.pdf: no tables detected
  FAIL 2024-11.pdf: PdfReadError: file has not been decrypted

Three shapes, not one. The five-column majority, a six-column variant with a reference, and two files with a blank leading column — which is what shifts every value one place.

Fix: Align Each File to a Declared Schema

Extract per file, promote the header, map to canonical names, and refuse anything that does not fit.

# pip install pandas camelot-py
from dataclasses import dataclass, field
from pathlib import Path
import re
import camelot
import pandas as pd

CANONICAL = ["date", "reference", "description", "debit", "credit", "balance"]
REQUIRED = {"date", "description", "balance"}
ALIASES = {"ref": "reference", "details": "description", "dr": "debit", "cr": "credit",
           "value": "balance", "transaction_date": "date"}

def normalise(name: str) -> str:
    slug = re.sub(r"[^a-z0-9]+", "_", str(name).strip().lower()).strip("_")
    return ALIASES.get(slug, slug)

@dataclass
class Extraction:
    frames: list[pd.DataFrame] = field(default_factory=list)
    quarantined: list[tuple[str, str]] = field(default_factory=list)

def extract_one(pdf: Path) -> list[pd.DataFrame]:
    out = []
    for page_index, table in enumerate(camelot.read_pdf(str(pdf), pages="all", flavor="lattice")):
        df = table.df.copy()
        df.columns = [normalise(value) for value in df.iloc[0]]       # changed: promote the header
        df = df.iloc[1:].reset_index(drop=True)
        df = df.loc[:, [c for c in df.columns if c]]                  # changed: drop blank-named columns
        df = df.loc[:, ~df.columns.duplicated()]
        df["source_file"] = pdf.name                                  # changed: provenance
        df["source_page"] = table.page
        df["source_row"] = df.index + 2
        out.append(df)
    return out

def collect(indir: Path) -> Extraction:
    result = Extraction()
    for pdf in sorted(indir.glob("*.pdf")):                           # changed: deterministic order
        try:
            frames = extract_one(pdf)
        except Exception as error:
            result.quarantined.append((pdf.name, f"{type(error).__name__}: {error}"))
            continue
        if not frames:
            result.quarantined.append((pdf.name, "no tables detected"))
            continue
        for df in frames:
            missing = REQUIRED - set(df.columns)
            if missing:
                result.quarantined.append((pdf.name, f"missing columns {sorted(missing)}"))
                continue
            for column in CANONICAL:
                if column not in df.columns:
                    df[column] = pd.NA                                # changed: fill optional columns
            result.frames.append(df[CANONICAL + ["source_file", "source_page", "source_row"]])
    return result

if __name__ == "__main__":
    extraction = collect(Path("in"))
    combined = pd.concat(extraction.frames, ignore_index=True) if extraction.frames else pd.DataFrame()
    print(f"{len(extraction.frames)} table(s) combined into {len(combined)} row(s); "
          f"{len(extraction.quarantined)} quarantined")
    for name, reason in extraction.quarantined[:10]:
        print(f"  QUARANTINE {name}: {reason}")

Selecting df[CANONICAL + [...]] before appending is what makes the concat safe: every frame has identical columns in identical order, so no alignment surprise is possible. Adding missing optional columns as pd.NA rather than dropping files that lack them keeps the five-column majority and the six-column variant in one dataset, with the reference simply absent where the document never had one.

Concatenating raw frames versus aligned frames Concatenating raw extractor output gives integer column names, four hundred header rows mixed into the data, silently shifted values where a file had an extra column, and no way to trace a row to its source. Concatenating frames aligned to a declared schema gives named columns, no header rows, files that do not fit set aside with a reason, and every row carrying its source file, page and row number. raw concat columns named 0..5 400 header rows as data values shifted in 2 files no provenance schema-aligned concat canonical column names headers promoted, not data odd layouts quarantined file, page, row on each row

Variant Fix 1: Memory on Large Corpora

Holding four hundred frames in a list before concatenating peaks at roughly twice the final size. For a corpus that does not fit, write each file's result as it is produced:

# pip install pandas pyarrow
from pathlib import Path
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

def stream_to_parquet(indir: Path, out: Path, schema: pa.Schema | None = None) -> int:
    writer, rows = None, 0
    try:
        for pdf in sorted(indir.glob("*.pdf")):
            for df in extract_one(pdf):
                table = pa.Table.from_pandas(df[CANONICAL + ["source_file", "source_page"]],
                                             schema=schema, preserve_index=False)
                if writer is None:
                    writer = pq.ParquetWriter(out, table.schema)
                    schema = table.schema                  # first file fixes the schema
                writer.write_table(table)
                rows += len(df)
    finally:
        if writer is not None:
            writer.close()
    return rows

Fixing the schema from the first file turns a later mismatch into a pyarrow error naming the offending column, which is a better outcome than a frame that concatenates and quietly changes a column's dtype from integer to object. Reading the result back with pd.read_parquet and a column subset then costs a fraction of the memory the combined frame would.

Variant Fix 2: Multi-Page Tables and Repeated Headers

A statement spanning six pages repeats its header on each one, and the extractor returns each page as a separate table:

# pip install pandas
import pandas as pd

def drop_repeated_headers(df: pd.DataFrame, header_column: str = "date") -> pd.DataFrame:
    looks_like_header = df[header_column].astype("string").str.strip().str.lower().isin(
        {"date", "transaction date", "value date"})
    return df.loc[~looks_like_header].reset_index(drop=True)

def join_continuation_rows(df: pd.DataFrame, key: str = "date", text: str = "description") -> pd.DataFrame:
    out, buffer = [], None
    for row in df.to_dict("records"):
        if not str(row.get(key) or "").strip() and buffer is not None:
            buffer[text] = f"{buffer[text]} {row[text]}".strip()    # continuation of the previous row
            continue
        if buffer is not None:
            out.append(buffer)
        buffer = row
    if buffer is not None:
        out.append(buffer)
    return pd.DataFrame(out)

Continuation rows — a long description wrapping onto a second line with every other cell blank — are the other reason row counts come out too high. Joining them back needs a key column that is always populated on a real row; a date or reference works, a description does not.

Row-count symptoms and their causes More rows than expected usually means header rows kept as data or wrapped description lines counted separately. Fewer rows means tables the extractor did not detect on some pages. Values in the wrong column means a file with an extra leading column concatenated by position. Mixed dtypes means one file's numeric column arrived as text. Rows that cannot be traced mean no provenance columns were added. Each has a specific correction applied before concatenating. Symptom Cause Fix Too many rows headers kept as data promote row zero Slightly too many wrapped description lines join continuations Too few rows undetected tables quarantine, do not ignore Values one column off extra leading column align to a schema Mixed dtypes one file arrived as text clean per file Untraceable row no provenance file, page, row columns

Why Provenance Columns Earn Their Keep

Three extra columns look like clutter until the first question arrives: someone in finance says a figure is wrong, and the only information is the value and roughly which month. With source_file, source_page and source_row, that becomes a one-line filter returning the document and the row on the page. Without them, it becomes an afternoon.

They also make the dataset re-buildable incrementally. Knowing which files are already represented means a nightly job can process only what is new, and a file that turns out to have been extracted wrongly can be removed from the combined set and reprocessed on its own — combined[combined.source_file != bad] followed by a concat of the corrected frame. Neither operation is possible on a dataset whose rows have forgotten where they came from.

Keep the columns as string dtype rather than categorical while building. Categoricals with four hundred categories save little and complicate the concat, since frames with different category sets fall back to object dtype anyway.

Verification

Reconcile the combined dataset against the per-file extraction counts before using it.

# pip install pandas
import pandas as pd

def verify_combined(combined: pd.DataFrame, per_file_rows: dict[str, int],
                    quarantined: list[tuple[str, str]], expected_files: int) -> None:
    for column in ("source_file", "source_page", "source_row"):
        assert column in combined.columns, f"missing provenance column {column!r}"
    assert combined["source_file"].nunique() + len(quarantined) == expected_files, \
        f"{combined['source_file'].nunique()} file(s) present + {len(quarantined)} quarantined " \
        f"!= {expected_files} input(s)"
    counted = combined.groupby("source_file").size().to_dict()
    mismatched = {name: (counted.get(name, 0), expected)
                  for name, expected in per_file_rows.items() if counted.get(name, 0) != expected}
    assert not mismatched, f"row counts differ for {list(mismatched)[:5]}: {list(mismatched.values())[:5]}"
    header_leak = combined["description"].astype("string").str.strip().str.lower().eq("description").sum()
    assert header_leak == 0, f"{header_leak} header row(s) leaked into the data"
    duplicated = combined.duplicated(subset=["source_file", "source_page", "source_row"]).sum()
    assert duplicated == 0, f"{duplicated} duplicated provenance key(s) — a file was extracted twice"
    print(f"{len(combined)} row(s) from {combined['source_file'].nunique()} file(s), "
          f"{len(quarantined)} quarantined, reconciled")

The duplicated-provenance assertion catches a mistake that is otherwise invisible: a glob matching both in/*.pdf and in/archive/*.pdf where the archive holds copies, doubling part of the dataset. Counting files present plus files quarantined against the input count is what proves nothing was silently skipped — the failure mode that makes a dataset look complete while missing a month.

FAQ

Should I use pd.concat or append in a loop?pd.concat once on a list. Appending in a loop copies the frame each time and is quadratic on large corpora.

How do I handle files with genuinely different tables? Keep them as separate datasets. Forcing unrelated schemas together produces a frame that is mostly missing values.

Where should cleaning happen — per file or after combining? Per file, so a file whose numbers fail to convert is quarantined with its name attached. See numbers parsed as strings.

Can I parallelise the extraction? Yes — extraction is CPU-bound and independent per file, so a process pool helps. Keep the ordering deterministic by sorting the results afterwards.

Part of Extracting PDF Data into pandas.