Python Doc & Data Automation

Fix: pandera Column Not in DataFrame Error

The schema names a column, the DataFrame displays it, and validation fails anyway:

SchemaError: column 'due_date' not in dataframe. Columns in dataframe: ['Reference ', 'Customer', 'Quantity', 'Due Date', 'Email']

Printing df.columns shows the column. Copying its name into the schema still fails. And when the name is finally right, validation stops at the first error, so fixing twelve problems takes twelve runs.

Root Cause

pandera matches column names exactly, and the names in an extracted or uploaded DataFrame are rarely what they appear to be. 'Due Date' is not 'due_date'; 'Reference ' has a trailing space that no display shows; a column read from a merged Excel header may be 'Unnamed: 3'; and a MultiIndex from a two-row header makes each name a tuple, so nothing matches a plain string. The error message helpfully prints the actual columns — and because Python's list repr shows the quotes, the trailing space is visible there even though it is invisible everywhere else. The second half of the problem is separate: validate() raises on the first failure by default, and lazy=True is what collects them all.

Why a column that is there does not match If the error message shows the name with surrounding whitespace, the heading has a leading or trailing space and needs stripping. If the name differs only by case or by spaces instead of underscores, the headings need normalising to the schema's convention. If the names print as tuples, the file had a two row header and produced a MultiIndex that must be flattened. If the name is genuinely absent, the schema should mark that column optional or the extraction is reading the wrong sheet or header row. The column looks present but does not match read the error's column list carefully quotes show a space Strip the headings invisible whitespace case or separator differs Normalise names one convention names are tuples Flatten the MultiIndex two-row header genuinely absent Mark optional or fix input wrong sheet or header row

Minimal Diagnostic

Print the column names in a way that makes invisible differences visible.

# pip install pandas
import unicodedata
import pandas as pd

def column_report(df: pd.DataFrame, expected: list[str]) -> None:
    print(f"{len(df.columns)} column(s); index type: {type(df.columns).__name__}")
    for column in df.columns:
        if isinstance(column, tuple):
            print(f"  TUPLE {column!r} — MultiIndex header")
            continue
        text = str(column)
        odd = [f"U+{ord(ch):04X}" for ch in text
               if ch.isspace() and ch != " " or unicodedata.category(ch) == "Cf"]
        print(f"  {text!r:<26} len={len(text)} "
              f"stripped={text.strip()!r}{' ODD: ' + ','.join(odd) if odd else ''}")
    actual = {str(c).strip().lower().replace(" ", "_") for c in df.columns}
    for name in expected:
        status = "ok" if name in df.columns else (
            "NEEDS NORMALISING" if name in actual else "GENUINELY MISSING")
        print(f"  schema wants {name!r}: {status}")

if __name__ == "__main__":
    df = pd.read_excel("uploads/records.xlsx")
    column_report(df, ["reference", "customer", "quantity", "due_date", "email"])

5 column(s); index type: Index
  'Reference '               len=10 stripped='Reference'
  'Customer'                 len=8 stripped='Customer'
  'Quantity'                 len=8 stripped='Quantity'
  'Due Date'                 len=8 stripped='Due Date'
  'Email[U+200B]'             len=6 stripped='Email[U+200B]' ODD: U+200B
  schema wants 'reference': NEEDS NORMALISING
  schema wants 'due_date': NEEDS NORMALISING
  schema wants 'email': GENUINELY MISSING

Three different failures at once: a trailing space, a case-and-separator mismatch, and a zero-width space pasted into a heading — which strip() does not remove.

Fix: Normalise Headings, Then Validate Lazily

Put the normalisation before the schema and let pandera report everything in one pass.

# pip install pandera pandas
import re
import unicodedata
import pandas as pd
import pandera.pandas as pa
from pandera.typing import Series

def normalise_columns(df: pd.DataFrame) -> pd.DataFrame:
    out = df.copy()
    if isinstance(out.columns, pd.MultiIndex):
        out.columns = ["_".join(str(part) for part in column if str(part) and
                                not str(part).startswith("Unnamed"))
                       for column in out.columns]                     # changed: flatten first
    names = []
    for column in out.columns:
        text = unicodedata.normalize("NFKC", str(column))
        text = "".join(ch for ch in text if unicodedata.category(ch) != "Cf")   # changed: drop U+200B
        names.append(re.sub(r"[^a-z0-9]+", "_", text.strip().lower()).strip("_"))
    out.columns = names
    return out

SCHEMA = pa.DataFrameSchema(
    {
        "reference": pa.Column(str, pa.Check.str_matches(r"^[A-Z]-\d{4}$"), nullable=False),
        "customer": pa.Column(str, pa.Check.str_length(2, 120)),
        "quantity": pa.Column(int, pa.Check.in_range(1, 10_000), coerce=True),
        "due_date": pa.Column("datetime64[ns]", coerce=True),
        "email": pa.Column(str, pa.Check.str_matches(r"^[^@\s]+@[^@\s]+\.[^@\s]+$"),
                           nullable=True, required=False),            # changed: optional column
    },
    strict="filter",                                                  # changed: drop extra columns
    unique=["reference"],
    name="records upload",
)

def validate(df: pd.DataFrame) -> tuple[pd.DataFrame | None, pd.DataFrame | None]:
    clean = normalise_columns(df)
    try:
        return SCHEMA.validate(clean, lazy=True), None                # changed: collect every failure
    except pa.errors.SchemaErrors as errors:
        return None, errors.failure_cases

if __name__ == "__main__":
    frame, failures = validate(pd.read_excel("uploads/records.xlsx"))
    if failures is not None:
        summary = failures.groupby(["column", "check"]).size().reset_index(name="rows")
        print(summary.to_string(index=False))
        print(failures[["index", "column", "check", "failure_case"]].head(10).to_string(index=False))

   column           check  rows
 due_date  coerce_dtype  5
 quantity  in_range(1, 10000)  2
reference  str_matches('^[A-Z]-\d{4}$')  3
 index    column      check              failure_case
    12  quantity      in_range(1, 10000)  0
    26 reference      str_matches(...)    a-0041
    87  due_date      coerce_dtype        end of month

Three settings do the work. lazy=True returns every failure as a DataFrame rather than raising on the first, so one run produces the whole list. coerce=True converts a column to the declared type before checking it, which is what turns a column of numeric strings into integers rather than failing. And strict="filter" drops columns the schema does not mention instead of rejecting the frame — the right behaviour for an upload whose template may carry extra notes columns.

Schema options and what each changes Lazy validation collects every failure instead of raising on the first and should almost always be on. Coerce converts to the declared dtype before checking, which suits data read from files. Required set to false allows a column to be absent. Nullable allows missing values within a present column. Strict set to true rejects unexpected columns while filter drops them. Unique enforces a key across rows. Choosing these deliberately is most of schema design. Option Effect When to use lazy=True all failures at once almost always coerce=True convert then check data from files required=False column may be absent optional fields nullable=True values may be missing not the same as optional strict=True reject extra columns fixed templates strict='filter' drop extra columns tolerant uploads unique=[...] enforce a key reference columns

Variant Fix 1: Required Versus Nullable

These are routinely confused, and the confusion produces a schema that passes files it should reject:

# pip install pandera
import pandera.pandas as pa

COLUMNS = {
    # must exist, must be populated
    "reference": pa.Column(str, nullable=False, required=True),
    # must exist, may be blank in some rows
    "notes": pa.Column(str, nullable=True, required=True),
    # may be absent entirely; if present, must be populated
    "vat_number": pa.Column(str, nullable=False, required=False),
}

required is about the column, nullable about the values in it. A schema that marks a critical field required=False accepts a file missing that field entirely and reports nothing — the failure then surfaces downstream as a KeyError a long way from the upload. When in doubt, make columns required and values non-nullable, and relax deliberately.

Variant Fix 2: Checks That Span Columns

Row-level relationships need a wide check registered on the schema rather than on one column:

# pip install pandera pandas
import pandas as pd
import pandera.pandas as pa

def due_after_issued(df: pd.DataFrame) -> pd.Series:
    return df["due_date"] >= df["issued_date"]

def total_matches_lines(df: pd.DataFrame) -> pd.Series:
    return (df["quantity"] * df["unit_price"] - df["line_total"]).abs() < 0.01

SCHEMA_WITH_RULES = pa.DataFrameSchema(
    COLUMNS,
    checks=[
        pa.Check(due_after_issued, name="due_date_not_before_issued_date",
                 error="due date is earlier than the issue date"),
        pa.Check(total_matches_lines, name="line_total_matches_quantity_times_price",
                 error="line total does not match quantity times unit price"),
    ],
)

A wide check returning a boolean Series reports the failing row indices, which is what makes it actionable — a check returning a single boolean tells you the file is wrong without saying where. Naming each check and giving it an error message matters too: the default message quotes the function's source, which is not something to show an uploader.

Validating a frame so the report is usable Read the frame with types preserved. Flatten any MultiIndex header and normalise the headings to the schema's naming convention. Validate with lazy set to true so every failure is collected. Group the resulting failure cases by column and check to see the shape of the problem rather than a list of thousands of rows. Decide whether to accept, reject or hold based on the rates. Report back using the uploader's original column names and spreadsheet row numbers. Read the frame types preserved Flatten and normalise headings to one convention Validate lazily collect every failure Group the failures by column and check Decide acceptance by rate, not count Report back their names, their rows

Reading the Failure Cases Frame

SchemaErrors.failure_cases is a DataFrame, and treating it as one is what makes a large validation tractable. It carries a row per failure with the column, the check that failed, the offending value and the index in the validated frame.

Grouping by column and check turns forty thousand failures into six lines that say what is actually wrong — usually one column with a dtype problem and one check that is stricter than the data. Sorting by that count tells you which single fix recovers the most rows, which is the question anyone triaging an upload is really asking.

Two details save confusion. The index column is the position in the frame as validated, so mapping it back to a spreadsheet row means adding two for the header, plus however many rows were dropped before validation. And a coerce_dtype failure lists the value that could not be converted, which is usually more informative than the check that would have run afterwards — a column failing coercion never reaches its range or pattern checks at all.

Where Normalisation Belongs

It is tempting to put the heading normalisation inside the validation function, as the fix here does for brevity. On a pipeline handling more than one source, it belongs one step earlier, as part of reading the file.

The reason is that every consumer of the frame needs the normalised names, not just the validator. Code that reads df["due_date"] after validation will break on an unvalidated frame from the same reader, and a second reader added later will produce a frame with different conventions that validates and then fails downstream. Making normalisation part of the read function means there is exactly one shape of frame in the system, and the schema describes that shape rather than repairing it.

Keeping a mapping from normalised name back to the original heading is worth the extra dictionary. Error reports need the uploader's own words, and the reverse mapping is the only place that information survives once the columns are renamed.

Verification

Test the schema against the shapes real files arrive in.

# pip install pandera pandas pytest
import pandas as pd
import pandera.pandas as pa
import pytest

GOOD = pd.DataFrame({
    "Reference ": ["A-0041", "A-0042"],
    "Customer": ["Northgate Ltd", "Bevan & Co"],
    "Quantity": ["12", "3"],                      # numeric strings, as Excel often supplies
    "Due Date": ["2026-10-01", "2026-10-15"],
})

def test_normalising_headings_makes_it_validate() -> None:
    frame, failures = validate(GOOD)
    assert failures is None, failures
    assert list(frame.columns) == ["reference", "customer", "quantity", "due_date"]
    assert frame["quantity"].dtype.kind == "i", "coerce did not convert the numeric strings"

def test_optional_column_may_be_absent() -> None:
    frame, failures = validate(GOOD.drop(columns=[]))
    assert failures is None and "email" not in frame.columns

def test_every_failure_is_collected() -> None:
    bad = GOOD.copy()
    bad.loc[0, "Reference "] = "nope"
    bad.loc[1, "Quantity"] = "0"
    _, failures = validate(bad)
    assert failures is not None
    assert set(failures["column"]) == {"reference", "quantity"}, \
        f"only reported {set(failures['column'])} — is lazy=True set?"

def test_duplicate_reference_is_rejected() -> None:
    duplicated = pd.concat([GOOD, GOOD.head(1)], ignore_index=True)
    _, failures = validate(duplicated)
    assert failures is not None and "reference" in set(failures["column"])

The lazy-validation test is the one to keep. It fails the moment someone removes lazy=True while debugging and forgets to restore it — a change that makes every subsequent upload take as many round trips as it has distinct problems.

FAQ

Why does coerce=True not fix my date column? It converts what it can and reports what it cannot. A value like end of month fails coercion; parse those before validating.

Should I normalise headings in the schema instead? No — a schema with 'Due Date ' in it encodes one file's quirk. Normalise first and keep the schema clean.

Can pandera validate the index as well? Yes, with pa.Index(...) in the schema — useful when the index is a meaningful key rather than a row number.

pandera or Pydantic? pandera for column-level checks on a whole frame; Pydantic for per-row objects with rich error messages. See validate spreadsheet uploads with Pydantic.

Part of Validating Document Data with Schemas.