Python Doc & Data Automation

Fix pandera SchemaError Dtype Mismatch

Validation fails before any of the value checks run:

SchemaError: expected series 'net_amount' to have type float64, got object
SchemaError: expected series 'issued_on' to have type datetime64[ns], got object
SchemaError: expected series 'invoice_no' to have type int64, got float64

The third message is the interesting one — nothing in the data is textual, yet the type is wrong. All three come from the same place: pandas decided the column's dtype while reading, and that decision did not match the contract.

Root Cause

pandas infers a dtype per column from the values it sees. One value that will not parse as a number promotes the whole column to object; one missing value in an integer column promotes it to float64, because plain int64 has no way to represent a null.

pandera then compares the actual dtype against the declared one. By default it does not convert anything, so the mismatch is reported as-is and no field checks run — which is why a single bad cell produces an error message that says nothing about that cell.

Three ways a column arrives with the wrong dtype Three columns are read from a file. In the first, a single value of N slash A among numbers promotes the column to object dtype. In the second, one missing value in an otherwise integer column promotes it to float64, because int64 cannot hold a null. In the third, dates in two different text formats mean no single datetime parse succeeds, so the column stays object. In every case pandera reports a type mismatch before any value check runs. Column as read pandera reports 1204.50, 98.00, "N/A" one token blocks inference dtype: object expected float64, got object 100417, 100418, NaN int64 cannot hold a null dtype: float64 expected int64, got float64 "2026-07-15", "15/07/2026" no single format parses both dtype: object expected datetime64, got object The message names the column, never the value — find it before changing the schema

Minimal Diagnostic

The error names a column. Find the values in it that blocked inference.

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

CSV = Path("data/extracted_invoices.csv")

def blockers(csv_path: Path, column: str, target: str = "numeric") -> pd.Series:
    """Values in a column that will not convert to the target type."""
    if not csv_path.exists():
        raise FileNotFoundError(f"No such file: {csv_path}")
    series = pd.read_csv(csv_path, usecols=[column], dtype=str)[column]

    if target == "numeric":
        converted = pd.to_numeric(series, errors="coerce")
    elif target == "datetime":
        converted = pd.to_datetime(series, errors="coerce", format="mixed")
    else:
        raise ValueError(f"Unknown target: {target}")

    failed = series[converted.isna() & series.notna()]
    return failed.value_counts()

if __name__ == "__main__":
    print(blockers(CSV, "net_amount"))
    print(blockers(CSV, "issued_on", target="datetime"))

N/A          14
1,204.50      3
£98.00        1
Name: net_amount, dtype: int64

Three distinct causes in one column: a sentinel, a thousands separator, and a currency symbol. Each needs a different treatment, and none of them is a schema problem.

The Fix: Clean at the Read, Coerce in the Schema

Handle what the reader can handle, then let pandera coerce the rest.

# pip install "pandas>=2.2,<3" "pandera>=0.20,<0.23"
from pathlib import Path
import pandas as pd
import pandera.pandas as pa

SENTINELS = ["N/A", "n/a", "NA", "-", "--", "NULL", "null", "#N/A", ""]

def read_typed(csv_path: Path) -> pd.DataFrame:
    """Read with the sentinels and separators the source actually uses."""
    frame = pd.read_csv(
        csv_path,
        dtype={"invoice_no": str},        # identifiers stay text: no float promotion
        na_values=SENTINELS,              # sentinels become NaN, not strings
        thousands=",",                    # "1,204.50" parses as a number
    )
    if "net_amount" in frame.columns and frame["net_amount"].dtype == object:
        frame["net_amount"] = pd.to_numeric(
            frame["net_amount"].astype(str).str.replace(r"[£$€\s]", "", regex=True),
            errors="coerce",
        )
    if "issued_on" in frame.columns:
        frame["issued_on"] = pd.to_datetime(frame["issued_on"], errors="coerce", format="mixed")
    return frame

class InvoiceSchema(pa.DataFrameModel):
    invoice_no: pa.typing.Series[str] = pa.Field(str_matches=r"^\d{6}$")
    net_amount: pa.typing.Series[float] = pa.Field(ge=0, nullable=True)
    issued_on: pa.typing.Series[pd.Timestamp] = pa.Field(nullable=True)

    class Config:
        coerce = True        # convert where lossless; fail loudly where not
        strict = True

if __name__ == "__main__":
    frame = read_typed(Path("data/extracted_invoices.csv"))
    validated = InvoiceSchema.validate(frame, lazy=True)
    print(validated.dtypes)

coerce=True is the single most useful setting for this error class. With it, a column of numeric strings becomes float64 and validation proceeds; without it, the same column fails on type before any range check runs. Coercion still refuses the impossible — "1O4.5O" fails rather than silently becoming null — which is exactly the boundary you want.

Variant Fix 1: Integers That Became Floats

invoice_no reading as float64 means the column contains a null. Two answers, and the choice matters.

# pip install "pandas>=2.2,<3" "pandera>=0.20,<0.23"
import pandas as pd
import pandera.pandas as pa

# Option A — identifiers are labels, not arithmetic: keep them as strings
frame["invoice_no"] = frame["invoice_no"].astype("string")

# Option B — genuinely numeric and nullable: use the nullable integer dtype
frame["quantity"] = frame["quantity"].astype("Int64")     # capital I

class Schema(pa.DataFrameModel):
    invoice_no: pa.typing.Series[pd.StringDtype] = pa.Field(str_matches=r"^\d{6}$")
    quantity: pa.typing.Series[pd.Int64Dtype] = pa.Field(ge=0, nullable=True)

    class Config:
        coerce = True

Option A is right far more often than people expect. An invoice number, an account code and a postcode are identifiers: leading zeros are meaningful, arithmetic on them is not, and reading them as numbers is what turns 00417 into 417.0 — the same class of loss described in working with Excel dates and number formats.

Variant Fix 2: Dates That Will Not Parse

A mixed-format date column needs parsing in passes rather than one guess.

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

def parse_dates_multi(series: pd.Series, formats: list[str]) -> pd.Series:
    """Apply each format in turn to the values still unparsed."""
    result = pd.Series(pd.NaT, index=series.index, dtype="datetime64[ns]")
    for fmt in formats:
        pending = result.isna() & series.notna()
        if not pending.any():
            break
        result.loc[pending] = pd.to_datetime(series[pending], format=fmt, errors="coerce")
    unparsed = series[result.isna() & series.notna()]
    if len(unparsed):
        print(f"{len(unparsed)} date(s) matched no format, e.g. {unparsed.unique()[:3]}")
    return result

if __name__ == "__main__":
    frame["issued_on"] = parse_dates_multi(frame["issued_on"],
                                           ["%Y-%m-%d", "%d/%m/%Y", "%d-%b-%Y"])

Reporting the leftovers rather than silently coercing them to NaT is the difference between a fix and a cover-up: a batch where 30% of dates match no format is a template change, not a data-quality blip.

Variant Fix 3: Timezone-Aware Against Naive

A column parsed with utc=True is datetime64[ns, UTC], which does not match a schema declaring datetime64[ns]:

SchemaError: expected series 'issued_on' to have type datetime64[ns], got datetime64[ns, UTC]

Pick one convention for the whole pipeline and enforce it in the schema:

# pip install "pandas>=2.2,<3" "pandera>=0.20,<0.23"
import pandas as pd
import pandera.pandas as pa

# Convention: everything is stored as UTC-aware
frame["issued_on"] = pd.to_datetime(frame["issued_on"], utc=True, errors="coerce")

class Schema(pa.DataFrameModel):
    issued_on: pa.typing.Series[pd.DatetimeTZDtype] = pa.Field(dtype_kwargs={"unit": "ns", "tz": "UTC"})

Mixing the two is what produces comparisons that raise mid-pipeline, long after validation passed.

Where to fix a dtype problem Three stages in order. At read time, declare sentinels, thousands separators and identifier columns as strings, which removes most causes. In the schema, enable coercion so remaining safe conversions happen and unsafe ones fail. Only then do the value checks run, on data whose types are already guaranteed. A note warns that widening the schema type to object to make the error go away disables every check on that column. 1. At the read na_values, thousands ids as str 2. In the schema coerce = True safe conversions only 3. Value checks run ranges, patterns, uniqueness types already guaranteed Do not widen the schema type to object the error disappears and so does every check that column was meant to enforce

Verification

Assert the dtypes after reading and after validating, and check that coercion did not quietly null out live data.

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

EXPECTED = {"invoice_no": "string", "net_amount": "float64", "issued_on": "datetime64[ns]"}

def assert_dtypes_and_coverage(frame: pd.DataFrame, expected: dict[str, str],
                               max_null_pct: float = 5.0) -> None:
    """Fail on a wrong dtype, or on coercion that removed too many values."""
    for column, want in expected.items():
        assert column in frame.columns, f"missing column: {column}"
        got = str(frame[column].dtype)
        assert got == want, f"{column}: dtype is {got}, expected {want}"

    for column in expected:
        null_pct = frame[column].isna().mean() * 100
        assert null_pct <= max_null_pct, (
            f"{column}: {null_pct:.1f}% null after parsing — coercion is eating real values"
        )
    print(f"{len(expected)} column dtype(s) and coverage verified")

if __name__ == "__main__":
    frame = read_typed(Path("data/extracted_invoices.csv"))
    assert_dtypes_and_coverage(frame, EXPECTED)
    InvoiceSchema.validate(frame, lazy=True)

The coverage half is what stops the classic over-correction: adding so many entries to na_values that genuine values become null, so the dtype check passes while the data quietly empties. Run both assertions in CI against a fixture file, and pair the schema with the reject handling in quarantine invalid rows in a pipeline.

Nullability decides the dtype Four dtypes against whether they can hold a missing value. Plain int64 cannot, so one null promotes the column to float64. Nullable Int64 with a capital I can, and stays integer. float64 can hold NaN natively. The pandas string dtype can hold pd.NA and is the right home for identifiers, which should never be numeric in the first place. Can this dtype hold a missing value? int64 no — one null promotes the column to float64 Int64 (capital I) yes — stays integer, holds pd.NA string yes — the right home for identifiers and codes Declare the nullable form whenever a numeric column is allowed to be empty

FAQ

Should I set coerce=True globally or per field? Globally in Config for extracted data, where almost everything arrives as text. Per field when one column has a deliberate type you do not want converted.

Why does the error appear on a column that looks fine? Because one value out of a million is enough. The diagnostic prints the offenders with counts, which is faster than scanning the file.

Does coerce change my data? It converts representation, not meaning: "1204.50" to 1204.50, "2026-07-15" to a timestamp. Anything it cannot convert losslessly raises rather than becoming null.

Can I declare object and check the values instead? You can, and you lose the guarantee that downstream arithmetic works. Keep the real dtype and fix the read; object should be a deliberate choice for genuinely free-text columns only.

Why did the dtype differ between two runs of the same job? Different input files, or chunked reading with per-chunk inference. Declaring dtypes at the read removes both sources of variation — see fix pandas DtypeWarning mixed types.

Where the Dtype Should Be Decided

There are three places a column's type can be established, and picking one deliberately removes most of these errors permanently.

At the reader is the earliest and usually the best: dtype= and na_values= on read_csv or read_excel mean the frame is correctly typed the moment it exists, and any value that cannot conform fails with the file name and row still in scope. The cost is that the reader call grows a dictionary that has to be maintained alongside the source format.

In the schema is the next best, using coerce=True. It keeps the type declaration in one place with the range and pattern checks, which is easier to review as a whole. The cost is that the failure happens one step later, so the error message names the column rather than the file.

In ad-hoc code between the two is the worst option, and the most common. Scattered astype and to_numeric calls drift apart, and the type a column ends up with depends on which branch ran. If you find yourself adding a third conversion for the same column, that is the signal to move the decision into the reader and delete the others.

Part of Validating Document Data with Schemas.