This guide is part of Attribute Mapping & Schema Harmonization, within the broader Automated Vector & Raster Cleaning Workflows reference.

Coercing Mixed Attribute Types Across Sources

Twelve municipalities publishing the same attribute will publish it in six different types, and the pipeline has to produce one. The difficulty is not the casting — it is deciding what to do with the values that will not cast, and doing it the same way every night.

Why Blanket Coercion Fails

  • Sentinels survive the cast. -9999 becomes a valid integer and poisons every mean computed afterwards.
  • Identifiers lose information. A parcel code of 00471 becomes 471 and stops joining to anything.
  • One bad value fails the batch. A strict cast raises on the first offender and tells you nothing about the other 4 000.
  • Silent coercion hides drift. A column that quietly becomes a string because one row was text is a schema change nobody was told about.

Version and Environment Compatibility

Component Version Note
pandas >=2.2 Nullable dtypes: Int64, Float64, string, boolean
pyarrow >=14 Backing store for nullable types; Parquet round-trip
GeoPandas >=1.0 Attribute frame alongside the geometry column 
pip install "pandas>=2.2" "pyarrow>=14" "geopandas>=1.0"

Order Matters: Sentinels First

Why sentinel handling precedes the cast The same column processed in two orders. Casting first turns the sentinel minus 9999 into a legitimate integer, so the mean of the column is dragged far below any real value. Normalising sentinels to null first leaves the column with a genuine missing value, which every aggregate skips, and the resulting mean matches the real data. cast first · the sentinel becomes data "-9999" -9999 (int64) mean drops from 148 to −312 min is now the sentinel normalise first · the sentinel becomes null "-9999" <NA> (Int64) mean stays 148 null count is reportable

The coerce_schema Recipe

from __future__ import annotations

import logging
from dataclasses import dataclass, field

import pandas as pd

logger = logging.getLogger(__name__)

GLOBAL_SENTINELS = ("", " ", "NULL", "null", "N/A", "NA", "n/a", "-", "--", "unknown")


@dataclass(frozen=True)
class ColumnRule:
    dtype: str                       # "Int64", "Float64", "string", "boolean", "datetime64[ns]"
    sentinels: tuple = ()            # column-specific extras, e.g. (-9999, -1)
    required: bool = False


SCHEMA: dict[str, ColumnRule] = {
    "parcel_id": ColumnRule("string", required=True),          # a label, never a number
    "zone_code": ColumnRule("string", required=True),
    "area_sqm": ColumnRule("Float64", sentinels=(-9999, -1)),
    "built_year": ColumnRule("Int64", sentinels=(0, 9999)),
    "is_protected": ColumnRule("boolean"),
    "surveyed_at": ColumnRule("datetime64[ns]", sentinels=("0000-00-00",)),
}


def coerce_schema(df: pd.DataFrame, schema: dict[str, ColumnRule] = SCHEMA,
                  ) -> tuple[pd.DataFrame, pd.DataFrame]:
    """Coerce a frame to the target schema, returning (typed, failures)."""
    out = df.copy()
    failures: list[dict] = []

    for column, rule in schema.items():
        if column not in out.columns:
            if rule.required:
                raise ValueError(f"required column {column!r} missing from the batch")
            out[column] = pd.Series(pd.NA, index=out.index, dtype=rule.dtype)
            continue

        series = out[column]
        # 1. Sentinels become NA before any cast is attempted.
        blanked = series.replace(list(GLOBAL_SENTINELS) + list(rule.sentinels), pd.NA)

        # 2. Cast, capturing rather than raising on the values that will not convert.
        if rule.dtype.startswith(("Int", "Float")):
            converted = pd.to_numeric(blanked, errors="coerce")
        elif rule.dtype.startswith("datetime"):
            converted = pd.to_datetime(blanked, errors="coerce", utc=False)
        else:
            converted = blanked

        newly_null = converted.isna() & blanked.notna()
        for index in out.index[newly_null]:
            failures.append({
                "row_index": index,
                "column": column,
                "value": blanked.loc[index],
                "target_dtype": rule.dtype,
            })

        out[column] = converted.astype(rule.dtype)

    failure_frame = pd.DataFrame(failures)
    if not failure_frame.empty:
        by_column = failure_frame.groupby("column").size().to_dict()
        logger.warning("coercion failures by column: %s", by_column)
    return out, failure_frame

Key Implementation Notes

  • Sentinels are replaced before the cast, per column. -1 means missing in a survey-year column and is a legitimate value in an elevation delta, so the list belongs on the rule rather than in a global.
  • errors="coerce" captures rather than raises. One unparseable value becomes a null and a failure row; the other 4 000 values still convert, and the batch still completes.
  • Failures are diffed against the pre-cast nulls. Only values that were present and became null count as failures — otherwise every genuine missing value would be reported.
  • Nullable dtypes throughout. Int64 rather than int64 means a missing integer is representable without silently promoting the column to float.
  • Identifiers are declared as strings in the schema. This is the single most valuable line in the table and the one most often omitted.
  • A missing required column raises immediately. That is a structural failure, not a per-row one, and continuing produces a batch that validates against a schema it does not meet.
Reading the failure rate per column Coercion failure rates across four columns of one batch. Three columns fail on a fraction of a percent of rows, which indicates individual bad values worth quarantining. The fourth fails on forty percent, which is not a data problem but a wrong target type — the column holds ranges rather than numbers and should be a string. coercion failure rate by column · one batch area_sqm → Float64 0.1% · quarantine the rows built_year → Int64 0.3% · quarantine the rows surveyed_at → datetime 0.05% · quarantine the rows frontage_m → Float64 40% · the target type is wrong Values like "12-15" and "approx 9" are not bad numbers; they are text, and the schema should say so.

Booleans and Dates, the Two Worst Offenders

Boolean columns arrive as Y/N, yes/no, T/F, 1/0, true/false and occasionally -1/0, sometimes several within one dataset. A generic cast maps almost none of them correctly, and bool("N") is True, which is the worst possible failure because it is silent and inverted. Map booleans through an explicit dictionary per source and treat an unmapped value as a coercion failure rather than as False.

The boolean encodings one pipeline actually receives Four sources encoding the same protected-status flag four different ways, plus the result a generic Python truthiness cast gives for each. Every non-empty string evaluates as true, so the N, no and F values all become true, which is silently inverted rather than merely wrong. source encoding bool(value) gives correct answer "Y" / "N" True / True True / False "yes" / "no" True / True True / False "T" / "F" True / True True / False 1 / 0 True / False the only one that works

Dates are worse because they are ambiguous rather than merely varied. 03/04/2026 is two different days depending on the publisher’s locale, and no amount of parsing sophistication resolves it from the value alone. Where a source’s convention is known, state the format explicitly and parse strictly; where it is not, the only correct move is to ask, because a pipeline that guesses will guess consistently and be wrong for half the year.

Troubleshooting Coercion

Symptom Likely cause Fix
Statistics dragged far from reality Sentinel cast to a number Normalise sentinels before casting
Identifiers stop joining Numeric cast dropped leading zeros Declare identifier columns as string
Column silently became object One text value in a numeric column Coerce with capture; inspect the failure rows
Every value in a column fails Wrong target type for the column’s real content Change the rule, not the data
All booleans are True Truthiness of a non-empty string Map booleans explicitly per source
Dates off by months Ambiguous day/month order Parse with an explicit format per source

Integration Note

Coercion runs in the harmonization stage, after column names are mapped and before validation — the ordering set out in attribute mapping and schema harmonization. The failure frame joins to the row identifiers and goes to the quarantine path in quarantining invalid features to a dead-letter table, and the per-column failure rate belongs in the metrics as a leading indicator of upstream schema drift.

Units, the Coercion Nobody Writes Down

Type coercion converts "12.4" to 12.4. It says nothing about whether that number is metres, feet or square metres, and mixing units across sources is a harder failure to detect than mixing types because the result is always numerically valid.

Three habits keep it visible. Put the unit in the column name — area_sqm, frontage_m, elevation_ft_asl — so that a mismatch is apparent at the point of use rather than in a schema document. Record the source’s declared unit per column in the mapping registry, next to the alias, so that a conversion factor is data rather than a magic number in code. And assert on plausible ranges after conversion: a residential parcel of 40 000 square metres is possible and a parcel of 40 000 square feet is more likely, and a range check catches the confusion that a type check cannot.

Where a source publishes units inconsistently between releases — which happens more often than it should — the range assertion is the only thing standing between the pipeline and a silent factor-of-ten error in every downstream figure.