Normalizing zoning attributes across different city schemas

You are merging zoning layers from eleven municipalities into a single regional fabric, and every city names its primary zoning field differently — ZONING_CD in one county shapefile, zoning_classification_primary in an open-data REST feed, ZONE in a third. Worse, the values collide too: R-1, RES-1, SF-1, and Residential Single Family all mean the same low-density residential class, and your valuation model treats them as four distinct categories. This page answers one narrow question: how do you map heterogeneous municipal zoning schemas onto a single canonical taxonomy deterministically, so a field rename or a new code variant never silently corrupts downstream analytics? It is a concrete application of attribute normalization, focused on the exact moment two cities disagree about what to call the same thing. The goal is a crosswalk that resolves field aliases, normalizes raw code strings, quarantines anything ambiguous, and emits an audit manifest you can defend in a due-diligence review.

Diagnosis: identifying where schema heterogeneity breaks the pipeline jump to heading

Schema drift rarely fails loudly. It surfaces as three distinct symptoms, and you want to recognize all three before writing any mapping code.

1. Hard KeyError on a renamed field. A pipeline that ingested ZONING_CD last quarter dies when the same dataset migrates to an open-data portal and renames the column. The trace points at the access, not the cause:

   Copy

   Traceback (most recent call last):
     File "/opt/pipeline/zoning_ingest.py", line 142, in _map_attributes
       zoning_code = row["ZONING_CLASSIFICATION"]
   KeyError: 'ZONING_CLASSIFICATION'
   

2. Silent value collapse. No exception fires, but gdf["zone"].nunique() jumps from the expected ~40 canonical classes to 180-plus because R-1, R1, r-1 , and Residential, Single-Family were never unified. The corruption only appears later as inflated category counts in a feasibility model.

3. Type coercion drift. Density indicators arrive as "4.5" strings in one feed and floats in another; a naive concat makes the column object dtype, and a later numeric filter (gdf[gdf.max_far > 4]) silently returns zero rows instead of raising.

Profile the divergence before refactoring so you can measure the win. Read each source’s headers and the cardinality of the candidate zoning field, then diff them:

import geopandas as gpd
from pathlib import Path

for src in Path("feeds").glob("*.gpkg"):
    gdf = gpd.read_file(src, engine="pyogrio", rows=5000)  # sample, not full load
    zone_cols = [c for c in gdf.columns if "zon" in c.lower() or "land_use" in c.lower()]
    print(f"{src.name:30} candidates={zone_cols}")

If the candidate column name differs across sources — or there are two plausible columns in one file — you have a field-alias problem and a value-normalization problem, and they must be solved in that order.

Architecture: a two-layer crosswalk jump to heading

Keep field resolution and value resolution as separate, composable layers. Conflating them is what makes municipal pipelines brittle.

• Field-alias layer. Maps each source’s column names to a canonical internal key (zoning_code, max_far, effective_date). Driven by an explicit per-city alias table, with a heuristic fallback that scans headers when no exact alias matches.
• Value-normalization layer. Cleans the raw code string, extracts structured tokens with regex, and resolves them against a versioned ontology shared with zoning taxonomy mapping. Anything unmatched is tagged REVIEW_REQUIRED, never force-defaulted.

Both layers stay stateless so the pass is recoverable and idempotent — the same input always yields the same canonical output and the same manifest. Upstream, this crosswalk consumes whatever the ingestion layer hands it, whether that came from a clean OGC API or from PDF & HTML scraping pipelines for municipalities that publish ordinances as documents.

Step-by-step implementation jump to heading

Each step isolates exactly one concern. Compose them into the orchestrator at the end.

Step 1 — Define the canonical schema and alias table jump to heading

Pin the target field names and their accepted source aliases in a version-controlled mapping. Keep it data, not code, so a new municipality is a config change, not a deploy.

CANONICAL_FIELDS = ("zoning_code", "max_far", "effective_date")

# Per-canonical-key list of known source column names (lowercased).
FIELD_ALIASES = {
    "zoning_code": ["zoning_cd", "zoning_classification_primary", "zone", "zone_class"],
    "max_far": ["far", "max_far", "floor_area_ratio", "density"],
    "effective_date": ["eff_date", "adopted", "ordinance_date"],
}

# Substrings used only when no explicit alias matches.
FIELD_HEURISTICS = {
    "zoning_code": ("zon", "land_use", "overlay"),
    "max_far": ("far", "density", "intensity"),
    "effective_date": ("date", "adopt", "effect"),
}

Step 2 — Resolve field names with an explicit-then-heuristic fallback jump to heading

Try the alias table first; only fall back to substring scanning when it misses, and log the drift so a renamed column gets a permanent alias entry later.

import logging

logger = logging.getLogger("zoning_normalize")

def resolve_field(columns, canonical_key):
    """Map a canonical key to an actual source column, or None."""
    lower = {c.lower(): c for c in columns}

    for alias in FIELD_ALIASES[canonical_key]:
        if alias in lower:
            return lower[alias]

    # Fallback: first header containing a heuristic substring.
    for col_lower, original in lower.items():
        if any(token in col_lower for token in FIELD_HEURISTICS[canonical_key]):
            logger.warning(
                "Alias miss for %s; heuristic matched column %r. Add to FIELD_ALIASES.",
                canonical_key, original,
            )
            return original

    return None

Step 3 — Sanitize and extract structured tokens jump to heading

Strip superficial variance, then pull out the base zone prefix and any numeric density indicator with a compiled regex so identical classes converge before lookup.

import re

ZONE_PATTERN = re.compile(r"^(?P<base>[A-Z]+)[-_ ]?(?P<num>\d+(?:\.\d+)?)?")

def sanitize_code(raw: str) -> str:
    """Collapse whitespace, case, and delimiter variance to a stable token."""
    return raw.strip().upper().replace("-", "_").replace(" ", "_")

def extract_tokens(clean: str):
    """Return (base_prefix, numeric_indicator) e.g. ('R', '1') for 'R_1'."""
    match = ZONE_PATTERN.match(clean.replace("_", ""))
    if not match:
        return clean, None
    return match.group("base"), match.group("num")

Step 4 — Resolve against a versioned ontology with strict type casting jump to heading

Map the cleaned token to the canonical class through a bidirectional lookup, and cast numeric attributes explicitly so a "4.5" string never poisons the column dtype. Unmatched codes are tagged, not guessed.

import math

# Canonical ontology: cleaned source token -> canonical class.
ZONE_ONTOLOGY = {
    "R_1": "RES_SF_LOW", "R1": "RES_SF_LOW", "RES_1": "RES_SF_LOW",
    "SF_1": "RES_SF_LOW", "SFR": "RES_SF_LOW",
    "C_1": "COMM_NEIGHBORHOOD", "NC": "COMM_NEIGHBORHOOD",
}

def to_canonical(raw_code: str, raw_far) -> dict:
    """Resolve one record's zoning code and FAR to canonical, typed values."""
    clean = sanitize_code(raw_code)
    canonical = ZONE_ONTOLOGY.get(clean)

    if canonical is None:
        base, _num = extract_tokens(clean)        # second-chance match on prefix
        canonical = ZONE_ONTOLOGY.get(base, "REVIEW_REQUIRED")

    try:
        far = float(raw_far)
        if math.isnan(far):
            far = None
    except (TypeError, ValueError):
        far = None  # never coerce a bad value into a misleading default

    return {
        "zoning_code": canonical,
        "max_far": far,
        "status": "mapped" if canonical != "REVIEW_REQUIRED" else "REVIEW_REQUIRED",
    }

Step 5 — Apply across the frame and emit a manifest jump to heading

Run the resolver over a normalized GeoDataFrame and capture a per-source compliance manifest the moment normalization completes. This manifest is exactly what feeds schema validation & data quality checks downstream.

import hashlib

def normalize_layer(gdf, source_name: str) -> tuple:
    """Return (canonical_gdf, manifest) for one municipal source."""
    code_col = resolve_field(gdf.columns, "zoning_code")
    far_col = resolve_field(gdf.columns, "max_far")
    if code_col is None:
        raise KeyError(f"No zoning code column resolvable in {source_name}")

    records = [
        to_canonical(row[code_col], row.get(far_col) if far_col else None)
        for _, row in gdf.iterrows()
    ]
    gdf["zoning_code"] = [r["zoning_code"] for r in records]
    gdf["max_far"] = [r["max_far"] for r in records]
    gdf["norm_status"] = [r["status"] for r in records]

    review = int((gdf["norm_status"] == "REVIEW_REQUIRED").sum())
    manifest = {
        "source": source_name,
        "resolved_code_column": code_col,
        "records_total": len(gdf),
        "records_mapped": len(gdf) - review,
        "records_review_required": review,
        "checksum": hashlib.sha256(gdf.to_json().encode()).hexdigest()[:16],
    }
    return gdf, manifest

Verification & testing jump to heading

Confirm the crosswalk actually unified the schemas rather than hiding the divergence.

• Cardinality collapsed to the canonical set. After normalization, gdf["zoning_code"].nunique() should equal your ontology size plus at most one (REVIEW_REQUIRED). A higher count means a raw variant slipped through sanitize_code.
• No silent value loss. Assert records_mapped + records_review_required == records_total in every manifest. A shortfall means a row raised inside the resolver and was dropped.
• Dtype is correct. assert gdf["max_far"].dtype == "float64" — if it is object, a string value escaped the cast in Step 4.
• Round-trip determinism. Re-running the same source must reproduce an identical manifest checksum. Drift means a non-deterministic row order; sort before normalizing.
• Review rate is bounded. Track records_review_required / records_total per source. A sudden spike for one city is the signature of an unannounced schema change — exactly the drift you want flagged, not absorbed.

A quick reference for how raw municipal variants resolve:

Failure recovery jump to heading

When a source fails to normalize cleanly mid-run, keep the rest of the pipeline moving and stay reproducible.

• Quarantine, do not force-default. Records tagged REVIEW_REQUIRED write to a review_required.parquet partition with their original code string and source name, so a human or a later ontology update can reclassify them without re-ingesting the whole feed.
• Halt only the failing source. A KeyError from an unresolvable zoning column aborts that one municipality’s layer and emits a status: failed manifest entry; sibling cities continue. Never let one renamed column abort the regional merge.
• Add the alias, replay the one source. When the heuristic fallback logs a new column name, add it to FIELD_ALIASES and re-run only that source — cheap, because the manifest already records exactly which feed drifted.
• Geometry repair is a separate concern. This crosswalk handles attributes only; pair it with per-chunk topology repair and projection from CRS alignment strategies before committing, and run heavy regional merges through async batch processing so a slow portal never blocks the normalization workers.

Frequently asked questions jump to heading

Related jump to heading

• Parent topic: Attribute Normalization Rules
• Section overview: Automated Feed Ingestion & GIS Data Parsing
• Zoning Taxonomy Mapping — the shared ontology this crosswalk resolves against
• Schema Validation & Data Quality Checks — consuming the normalization manifest downstream
• PDF & HTML Scraping Pipelines — upstream extraction for document-only municipalities
• Error Handling & Retry Logic — quarantine, replay, and dead-letter patterns
• GIS Export Sync Workflows — emitting the canonical layer downstream