Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Validate Excel Data with pandera Schemas

Declare a schema for a spreadsheet and enforce it: column types, ranges, regex checks, uniqueness, cross-column rules, lazy validation and a readable error report.

Spreadsheets arrive imperfect: a blank in a required column, a negative quantity, a date typed as text, a duplicate reference. Catching those with scattered assert statements works until there are five of them and someone needs to know which rows failed. pandera replaces that with a declared schema — types, ranges, patterns, uniqueness — validated in one call that reports every violation at once. This guide builds a schema for a real import, produces an error report a colleague can act on, and wires it into an ingest job. It belongs to Validating Excel Data with Python.

A schema sitting between the sheet and the pipeline The workbook is read into a frame, validated against a declared schema, and either continues into the pipeline or produces a report naming every failing row and rule. One gate, every rule, one report orders.xlsx from a colleague DataFrameSchema types + checks valid: continue, types coerced invalid: every failing row and rule as a frame you can export

Prerequisites

Bash
pip install pandera pandas openpyxl

Declare the schema

A schema is a dictionary of columns, each with a type and any number of checks:

Python
"""schema.py — what a valid orders sheet looks like."""
import pandera.pandas as pa
from pandera import Check, Column, DataFrameSchema

ORDERS = DataFrameSchema(
    {
        "order_id": Column(str, Check.str_matches(r"^\d{5}$"), unique=True),
        "ordered": Column("datetime64[ns]", Check.le(pa.Timestamp("2030-01-01"))),
        "region": Column(str, Check.isin(["North", "South", "East", "West"])),
        "quantity": Column(int, Check.gt(0)),
        "unit_price": Column(float, Check.in_range(0, 10_000)),
        "note": Column(str, nullable=True),
    },
    strict=False,        # extra columns allowed; set True to reject them
    coerce=True,         # cast to the declared types during validation
)

Each argument earns its place. unique=True catches the duplicate reference that would double-count a sale. Check.isin catches the region typed as "Norht". nullable=True says a blank note is fine, which by contrast means every other column must be populated. And coerce=True makes validation do the type conversion too, so the frame that comes out is correctly typed rather than merely approved.

Validate and read the report

Python
import pandas as pd
import pandera.errors

from schema import ORDERS

df = pd.read_excel("orders.xlsx", dtype={"order_id": "string"}, parse_dates=["ordered"])

try:
    clean = ORDERS.validate(df, lazy=True)
    print(f"{len(clean):,} rows validated")
except pandera.errors.SchemaErrors as exc:
    failures = exc.failure_cases
    failures["excel_row"] = failures["index"].astype("Int64") + 2   # header offset
    print(failures[["excel_row", "column", "check", "failure_case"]].to_string(index=False))

lazy=True is the argument that changes the experience. Without it, validation stops at the first failing check; with it, every rule runs and failure_cases holds one row per violation — the column, the rule that failed, the offending value and its position. Adding two to the index converts a zero-based DataFrame position into the row number the reader will see in Excel.

Text
 excel_row     column                    check failure_case
        14   quantity                greater_than           -3
        27     region     isin(['North', ...])        Norht
        41   order_id                  unique         00417

Send the failures back as a workbook

A list in a log helps you; a spreadsheet helps the person who has to fix it:

Python
import pandas as pd

def write_error_report(failures: pd.DataFrame, path: str) -> None:
    report = failures.assign(excel_row=failures["index"].astype("Int64") + 2)[
        ["excel_row", "column", "check", "failure_case"]
    ].rename(columns={
        "excel_row": "Row", "column": "Column",
        "check": "Rule broken", "failure_case": "Value found",
    })
    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        report.to_excel(writer, index=False, sheet_name="Problems")
        ws = writer.sheets["Problems"]
        ws.freeze_panes(1, 0)
        ws.autofilter(0, 0, len(report), len(report.columns) - 1)
        ws.set_column(0, 0, 8)
        ws.set_column(1, 3, 26)

Emailing that back to the sender closes the loop without anybody reading a stack trace — the delivery step is in Emailing Excel Reports with smtplib, and highlighting the offending cells in the original file is covered in Highlight invalid cells in Excel with Python.

Eager validation versus lazy validation Eager validation stops at the first failure, so problems are discovered one per round trip, while lazy validation reports every violation in a single pass. lazy=False lazy=True run 1: quantity is negative — stop run 2: region misspelled — stop run 3: duplicate id — stop one run 3 violations, with rows and rules one message back to the sender

Add rules that span columns

Some rules are about the row, not the cell. A schema-level check receives the whole frame:

Python
from pandera import Check, DataFrameSchema

ORDERS_WITH_ROW_RULES = ORDERS.add_checks([
    Check(lambda df: df["shipped"].isna() | (df["shipped"] >= df["ordered"]),
          error="shipped date is before ordered date"),
    Check(lambda df: (df["quantity"] * df["unit_price"] - df["total"]).abs() < 0.01,
          error="total does not equal quantity x unit price"),
])

The second check is the one that catches manual edits: somebody overtyped a total in the spreadsheet and the arithmetic no longer holds. Giving each check an error string means the report says what is wrong in words rather than showing a lambda.

Reuse the schema as documentation and as a test

A schema is also the clearest description of the file format, so it belongs in version control next to the ingest code and in the test suite:

Python
"""tests/test_schema.py"""
import pandas as pd
import pytest
import pandera.errors

from schema import ORDERS

def test_good_sheet_validates():
    df = pd.read_excel("tests/fixtures/orders_good.xlsx", parse_dates=["ordered"])
    ORDERS.validate(df, lazy=True)

def test_bad_sheet_reports_every_problem():
    df = pd.read_excel("tests/fixtures/orders_bad.xlsx", parse_dates=["ordered"])
    with pytest.raises(pandera.errors.SchemaErrors) as excinfo:
        ORDERS.validate(df, lazy=True)
    assert len(excinfo.value.failure_cases) == 3

When the upstream format changes, the schema and its fixtures change together, and the diff shows exactly what was renegotiated.

Wire it into the job

Validation belongs immediately after the read and before anything computes:

Python
import sys

import pandas as pd
import pandera.errors

from schema import ORDERS

def load_orders(path: str) -> pd.DataFrame:
    df = pd.read_excel(path, dtype={"order_id": "string"}, parse_dates=["ordered"])
    try:
        return ORDERS.validate(df, lazy=True)
    except pandera.errors.SchemaErrors as exc:
        write_error_report(exc.failure_cases, "orders_problems.xlsx")
        print(f"{len(exc.failure_cases)} problem(s) in {path}", file=sys.stderr)
        raise SystemExit(2)

Exiting non-zero matters: a scheduled job that validated its input and then carried on regardless has only added a log line, not a safeguard. Fail the run, keep the report, and let the retry happen after the file is fixed.

Decide what a failure should do

Not every violation deserves the same response, and the schema is a good place to make that explicit. Three tiers cover most imports:

Three severities of validation failure A structural failure stops the job, a row-level failure quarantines the offending rows and continues, and a warning is recorded for review without affecting the run. What each kind of problem should cost structural a column is missing a type cannot coerce stop the job row-level a negative quantity an unknown region quarantine those rows advisory an empty note field an unusual outlier record and continue

The middle column is the one worth implementing deliberately, because "quarantine the bad rows and process the rest" is what most operations teams actually want. pandera makes it a two-line split once you have the failure cases:

Python
bad_rows = set(exc.failure_cases["index"].dropna().astype(int))
usable = df.drop(index=bad_rows)
quarantined = df.loc[sorted(bad_rows)]
print(f"processing {len(usable):,} rows, quarantining {len(quarantined):,}")

Write the quarantined rows out beside the error report so the sender can correct and resubmit just those, rather than the whole file.

Generate the first draft of a schema

Writing a schema for a forty-column export by hand is tedious, and pandera will infer one from a known-good file to start from:

Python
import pandas as pd
import pandera.pandas as pa

good = pd.read_excel("orders_known_good.xlsx", parse_dates=["ordered"])
inferred = pa.infer_schema(good)
print(inferred.to_script())      # a Python module you can edit and commit

Treat the output as a draft, not an answer. Inference sees the ranges that happen to exist in one file, so it will propose a maximum quantity of 47 simply because that was the largest value present. Keep the column names and types, replace the invented bounds with the real business rules, and commit the edited version.

Common pitfalls and gotchas

  • Forgetting lazy=True. Eager validation makes fixing a sheet an iterative guessing game.
  • Off-by-two row numbers. A DataFrame index is zero-based and the sheet has a header, so add two before showing a row number to a person.
  • Over-strict schemas. strict=True rejects extra columns, which is right for a controlled feed and wrong for a file people also use for their own notes.
  • Validating after transforming. Validate the raw import; a rule that runs after cleaning tells you about your code, not about the file.
  • Silent coercion surprises. coerce=True will turn "5" into 5; that is usually welcome, but be deliberate about identifier columns.

Performance and scale notes

Validation is vectorised, so cost scales with rows in the same way a groupby does — negligible next to the Excel parse for a report-sized file. The exceptions are unique=True on a very wide string column and custom lambdas that fall back to element-wise evaluation; write checks as vectorised expressions over the frame wherever possible. For genuinely large imports, validate after converting to Parquet rather than on every Excel read, so the parse happens once — the conversion is covered in Convert Excel files to Parquet with Python.

Conclusion

A schema turns "check the spreadsheet" into an executable, reviewable definition of what the spreadsheet must contain. Declare types and checks once, validate with lazy=True so every problem surfaces together, translate the failures into Excel row numbers, and hand the sender a workbook naming what to fix. Then fail the job on invalid input, so bad data never becomes a plausible-looking report.

Frequently asked questions

Why use pandera instead of a few assert statements? A schema is declarative, reusable and self-documenting, and lazy validation reports every failure at once instead of stopping at the first. Assertions are fine for one script; a schema is what you want when several jobs read the same file shape.

Does pandera change my data? Only if you ask it to. With coerce=True it casts columns to the declared types as part of validation; otherwise it checks and reports without modifying anything.

How do I report which spreadsheet rows failed? Catch SchemaErrors and read its failure_cases frame. Add two to the index to get the Excel row number, since a DataFrame is zero-based and the sheet has a header row.

Can it validate that two columns agree? Yes. A schema-level check receives the whole frame, so a rule like "end date is not before start date" is a single lambda.

Does it work with Polars? Yes, recent pandera versions support Polars alongside pandas, with the same schema vocabulary — useful if the read side has already moved.