Python Doc & Data Automation

Fix: PDF Numbers Parsed as Strings in pandas

The table extracted cleanly, the values look right, and then arithmetic fails:

df = camelot.read_pdf("in/statement.pdf")[0].df
df["Amount"].sum()

'1,234.5670.00(89.20)£45.00 1,000.00'

Instead of a total, the column concatenated. df.dtypes shows object for every column, and pd.to_numeric(df["Amount"]) raises ValueError: Unable to parse string "1,234.56" at position 0.

Root Cause

PDF extraction returns text, not values. A PDF has no type information at all — a number in a table cell is a sequence of glyphs positioned on the page, so every extraction library hands back strings and pandas infers object dtype for the column. That would be easy to fix if the strings were plain digits, but financial PDFs carry presentation with them: thousands separators, currency symbols, parenthesised negatives for credits, trailing minus signs, percent signs, and — the one that defeats most cleaning code — non-breaking or thin spaces used as digit group separators. A single unconvertible value in a column of ten thousand keeps the whole column as strings, so the failure is all-or-nothing.

What a number looks like coming out of a PDF A value with a thousands comma fails a plain float conversion. A parenthesised value means a negative in accounting notation and fails. A value with a currency symbol fails. A trailing minus sign fails. A value using a non-breaking space as a group separator fails and is invisible in a terminal. A European format with a comma as the decimal separator converts to the wrong number rather than failing, which is worse. Only a bare decimal converts correctly. Extracted text Means Plain conversion 1,234.56 1234.56 ValueError (89.20) 89.20 ValueError £45.00 45.00 ValueError 1234.56- 1234.56 ValueError 1 234,56 1234.56 ValueError 1.234,56 1234.56 wrong: 1.234 1234.56 1234.56 works

Minimal Diagnostic

Find out exactly which values block the conversion, and what characters they contain.

# pip install pandas
import unicodedata
import pandas as pd

def numeric_blockers(df: pd.DataFrame, column: str, limit: int = 10) -> None:
    series = df[column].astype("string")
    converted = pd.to_numeric(series, errors="coerce")
    bad = series[converted.isna() & series.notna() & (series.str.strip() != "")]
    print(f"{column!r}: {len(series)} value(s), {len(bad)} unconvertible")
    for value in bad.drop_duplicates().head(limit):
        codes = " ".join(f"U+{ord(ch):04X}:{unicodedata.name(ch, '?')[:18]}"
                         for ch in value if not ch.isdigit() and ch != ".")
        print(f"  {value!r:<18} {codes}")

if __name__ == "__main__":
    df = pd.read_csv("out/extracted.csv", dtype="string")
    for column in df.columns:
        numeric_blockers(df, column)

'Amount': 412 value(s), 391 unconvertible
  '1,234.56'         U+002C:COMMA
  '(89.20)'          U+0028:LEFT PARENTHESIS U+0029:RIGHT PARENTHESIS
  '£45.00'           U+00A3:POUND SIGN
  '1 234.56'         U+00A0:NO-BREAK SPACE
  '-'                U+002D:HYPHEN-MINUS
  ''

The codepoint dump is the part that pays off. A no-break space and a hyphen used as "nil" are invisible in a terminal, and both are common in PDFs generated by accounting software.

Fix: Normalise Then Convert, Column by Column

Strip presentation, decide what missing means, and convert with a strict check that nothing was lost.

# pip install pandas
import re
import unicodedata
import pandas as pd

CURRENCY = "\N{POUND SIGN}\N{EURO SIGN}\N{DOLLAR SIGN}\N{YEN SIGN}"
NIL_TOKENS = {"", "-", "\N{EN DASH}", "\N{EM DASH}", "n/a", "na", "nil", "none", "."}

def clean_numeric(series: pd.Series, decimal: str = ".") -> pd.Series:
    text = series.astype("string")
    text = text.map(lambda v: unicodedata.normalize("NFKC", v) if pd.notna(v) else v)  # changed: NBSP -> space
    text = text.str.strip()
    text = text.mask(text.str.lower().isin(NIL_TOKENS))                    # changed: nil markers -> NA
    negative = text.str.match(r"^\(.*\)$", na=False) | text.str.endswith("-", na=False)
    text = text.str.replace(rf"[{CURRENCY}%()\s]", "", regex=True)         # changed: drop presentation
    text = text.str.rstrip("-")
    if decimal == ",":
        text = text.str.replace(".", "", regex=False).str.replace(",", ".", regex=False)
    else:
        text = text.str.replace(",", "", regex=False)                      # changed: thousands separator
    numbers = pd.to_numeric(text, errors="coerce")
    numbers = numbers.mask(negative, -numbers.abs())                       # changed: accounting negatives
    lost = numbers.isna() & series.astype("string").str.strip().notna() & \
           ~series.astype("string").str.strip().str.lower().isin(NIL_TOKENS)
    if lost.any():
        examples = series[lost].drop_duplicates().head(5).tolist()
        raise ValueError(f"{int(lost.sum())} value(s) could not be converted, e.g. {examples}")
    return numbers

def clean_frame(df: pd.DataFrame, numeric: list[str], decimal: str = ".") -> pd.DataFrame:
    out = df.copy()
    for column in numeric:
        out[column] = clean_numeric(out[column], decimal=decimal)
    return out

if __name__ == "__main__":
    raw = pd.read_csv("out/extracted.csv", dtype="string")
    clean = clean_frame(raw, numeric=["Amount", "VAT", "Total"])
    print(clean.dtypes)
    print(f"total: {clean['Amount'].sum():,.2f}")

Amount    float64
VAT       float64
Total     float64
dtype: object
total: 148,302.71

unicodedata.normalize("NFKC", ...) does more work than it looks. It turns a no-break space into an ordinary one, a full-width digit into an ASCII digit, and the various Unicode minus signs into a hyphen — three separate bugs handled by one call. Raising on unconvertible values rather than leaving them as NaN is the important decision: a silently coerced column sums to a number that is wrong by however much was dropped, and nothing about the output says so.

Turning extracted text into numbers you can trust Extract the table as text with every column typed as string. Normalise Unicode so no-break spaces and full-width digits become ordinary characters. Map nil markers such as a lone dash to missing, deliberately. Detect accounting negatives from parentheses or a trailing minus before stripping those characters. Strip currency symbols, separators and percent signs. Convert to numeric, raising rather than coercing on failure. Finally reconcile the column total against a figure printed on the document. Extract as text every column a string Normalise Unicode NFKC folds NBSP and more Map nil markers dash and n/a to missing Detect the sign before stripping brackets Strip presentation symbols, separators Convert, raise on loss no silent coercion Reconcile the total against the printed figure

Variant Fix 1: Deciding Which Separator Is Which

A file of unknown origin may use either convention, and guessing wrong changes the value by a factor of a thousand without any error:

# pip install pandas
import re
import pandas as pd

def detect_decimal(series: pd.Series) -> str:
    sample = series.dropna().astype(str).head(200)
    comma_decimal = sample.str.contains(r"\d,\d{2}\b", regex=True).sum()
    dot_decimal = sample.str.contains(r"\d\.\d{2}\b", regex=True).sum()
    both = sample.str.contains(r"\d[.,]\d{3}[.,]\d{2}", regex=True).sum()
    if both:
        return "," if sample.str.contains(r"\d\.\d{3},\d{2}").sum() else "."
    if comma_decimal and not dot_decimal:
        return ","
    if dot_decimal and not comma_decimal:
        return "."
    raise ValueError("ambiguous decimal separator; pass it explicitly")

Raising on ambiguity beats defaulting. A column of whole pounds with thousands separators — 1,234 and 5,678 — is genuinely ambiguous, and the right answer comes from knowing the document's origin, not from a heuristic. Pass the separator explicitly whenever the source is known.

Variant Fix 2: Dates and Percentages in the Same Table

Numbers are rarely the only typed column. Handle each kind explicitly rather than letting pandas infer:

# pip install pandas
import pandas as pd

def clean_percent(series: pd.Series) -> pd.Series:
    text = series.astype("string").str.strip().str.rstrip("%")
    return pd.to_numeric(text, errors="coerce") / 100

def clean_date(series: pd.Series, formats: list[str]) -> pd.Series:
    text = series.astype("string").str.strip()
    result = pd.Series(pd.NaT, index=series.index, dtype="datetime64[ns]")
    for fmt in formats:                                         # try each, never infer
        pending = result.isna() & text.notna()
        if not pending.any():
            break
        result.loc[pending] = pd.to_datetime(text[pending], format=fmt, errors="coerce")
    return result

Listing the formats explicitly avoids the classic failure where 03/04/2026 parses as 3 April in one run and 4 March in the next, because pandas inferred a different convention from a different sample. If both formats appear in one column, the document itself is ambiguous and the extraction needs a column of origin, not a cleverer parser.

Coercing versus raising on bad values Converting with errors set to coerce turns every unparseable value into missing, so the column becomes numeric and the total is quietly short by whatever was dropped. Converting with a loss check compares the count of missing values before and after and raises with examples, so the job stops and the cleaning rule gets extended. The first produces a plausible wrong answer, the second produces a fixable failure. errors='coerce' 391 values -> NaN column is float64 total: 8,142.30 no warning anywhere raise on loss ValueError with examples cleaning rule extended total: 148,302.71 failure is visible

Reconciling Against the Document

Cleaning code that runs without error can still be wrong. A PDF statement usually prints its own total, and comparing against it is the only check that tests the whole chain — extraction, row detection and conversion together.

# pip install pandas pdfplumber
import re
import unicodedata
from decimal import Decimal
from pathlib import Path
import pdfplumber

TOTAL = re.compile(r"(?:total|balance)\D{0,20}([\d, ]+\.\d{2})", re.IGNORECASE)

def printed_total(pdf_path: Path) -> Decimal | None:
    with pdfplumber.open(pdf_path) as pdf:
        text = "\n".join(page.extract_text() or "" for page in pdf.pages)
    text = unicodedata.normalize("NFKC", text)        # no-break spaces become ordinary ones
    matches = TOTAL.findall(text)
    if not matches:
        return None
    return Decimal(matches[-1].replace(",", "").replace(" ", ""))

A mismatch does not always mean the numbers are wrong — a total may exclude VAT, or the statement may carry a balance forward. What it means is that something needs explaining before the figures are used, which is exactly the state a finance team wants an automated extraction to be in. Where the document prints no total, a row count against a printed item count serves the same purpose.

Keeping the Raw Text Alongside the Numbers

Once a column has been cleaned there is no way back to what the PDF actually said, and that is the first thing anyone asks when a figure looks wrong. Keeping both costs one column:

# pip install pandas
import pandas as pd

def with_raw(df: pd.DataFrame, numeric: list[str], decimal: str = ".") -> pd.DataFrame:
    out = df.copy()
    for column in numeric:
        out[f"{column}_raw"] = df[column].astype("string")     # exactly as extracted
        out[column] = clean_numeric(df[column], decimal=decimal)
    return out

The raw columns cost little in a Parquet file, since repeated presentation strings compress well, and they turn a dispute about a total into a two-minute query. They also make the cleaning rules testable against real data: any row where the cleaned value and the raw text disagree about the sign, or where the raw text contains a character the rules do not handle, can be found with a filter rather than by re-running the whole extraction.

Verification

Assert the dtypes, the absence of silent loss, and the reconciliation in one place.

# pip install pandas
from decimal import Decimal
import pandas as pd

def verify_numeric(df: pd.DataFrame, numeric: list[str], expected_total: Decimal | None = None,
                   column: str = "Amount", tolerance: Decimal = Decimal("0.02")) -> None:
    for name in numeric:
        assert pd.api.types.is_numeric_dtype(df[name]), f"{name!r} is {df[name].dtype}, not numeric"
        assert not df[name].isna().all(), f"{name!r} is entirely missing after cleaning"
    blank_rate = df[column].isna().mean()
    assert blank_rate < 0.05, f"{blank_rate:.1%} of {column!r} is missing — cleaning likely dropped values"
    if expected_total is not None:
        actual = Decimal(str(round(float(df[column].sum()), 2)))
        difference = abs(actual - expected_total)
        assert difference <= tolerance, \
            f"{column!r} sums to {actual}, document prints {expected_total} (off by {difference})"
    print(f"{len(df)} row(s), {len(numeric)} numeric column(s), total {df[column].sum():,.2f} reconciled")

if __name__ == "__main__":
    clean = pd.read_parquet("out/clean.parquet")
    verify_numeric(clean, ["Amount", "VAT", "Total"], expected_total=Decimal("148302.71"))

The missing-rate assertion catches the case the strict conversion cannot: values that were legitimately mapped to nil by an over-broad rule. Five percent is a starting point — for a statement where blanks are genuinely rare, one percent is a better tripwire.

FAQ

Why does df.sum() concatenate instead of adding? The column is object dtype holding strings, and + on strings concatenates. The dtype check in verification catches this.

Should I use Decimal instead of float? For money that will be stored or reconciled, yes — convert after cleaning with df[col].map(Decimal). Floats are fine for aggregation and charts.

What about numbers split across two columns by the extractor? That is a table-detection problem, not a type problem — see extracting tables from PDFs.

How do I keep leading zeros on reference numbers? Do not convert them. Reference numbers are identifiers, not quantities; leave them as string dtype.

Part of Extracting PDF Data into pandas.