Migrating Zoning Rules from Spreadsheets to YAML

Planning departments almost always start with zoning rules in a spreadsheet, and that spreadsheet becomes a liability the moment the rule engine needs deterministic, reviewable input. This guide walks through reading an xlsx or csv of setback and height rules with pandas, validating every row, and emitting normalized YAML against a documented schema, then round-tripping the result to prove nothing was lost. The payoff is a rule source that reviewers can read, Git can diff, and the rule storage format comparison recommends for human-edited jurisdictions.

Prerequisites

Step-by-step

Step 1: Read the spreadsheet with pandas

Load the source file and immediately normalize column names so downstream code never depends on a planner’s capitalization or stray whitespace. Reading everything as strings first prevents pandas from guessing types and silently mangling zone codes like R-1 or leading-zero identifiers.

import pandas as pd

def read_rules(path: str) -> pd.DataFrame:
    # dtype=str keeps zone codes and IDs intact; parse numbers deliberately later.
    reader = pd.read_excel if path.endswith(".xlsx") else pd.read_csv
    df = reader(path, dtype=str).fillna("")
    # Normalize headers: lowercase, underscores, no surrounding spaces.
    df.columns = [c.strip().lower().replace(" ", "_") for c in df.columns]
    df = df.rename(columns={"zone": "zone_class", "front_setback_ft": "setback_ft"})
    return df

Step 2: Validate rows against the schema

Reject bad data at the boundary rather than letting it reach the engine. Each row must carry a unique rule identifier, a known zone class, and a numeric setback. Convert units here so the emitted YAML is unambiguous — this pipeline standardizes to metres, matching the projected CRS the spatial engine uses for distance operations.

FT_TO_M = 0.3048
REQUIRED = ("rule_id", "zone_class", "setback_ft", "effective_date")

def validate_rows(df: pd.DataFrame) -> list[dict]:
    missing = [c for c in REQUIRED if c not in df.columns]
    if missing:
        raise ValueError(f"Spreadsheet missing columns: {missing}")
    seen, rules = set(), []
    for i, row in df.iterrows():
        rid = row["rule_id"].strip()
        if not rid or rid in seen:
            raise ValueError(f"Row {i}: blank or duplicate rule_id {rid!r}")
        seen.add(rid)
        setback_m = round(float(row["setback_ft"]) * FT_TO_M, 3)  # feet -> metres
        rules.append({
            "rule_id": rid,
            "zone_class": row["zone_class"].strip(),
            "setback_m": setback_m,
            "precedence": int(row.get("precedence") or 0),
            "effective_date": row["effective_date"].strip(),
        })
    return rules

Step 3: Emit normalized YAML with a documented schema

Serialize the validated rules under a single top-level key and pin a schema version so future readers know which contract the file honors. Setting sort_keys=False and default_flow_style=False produces block-style YAML with stable field order, which keeps Git diffs clean when a single threshold changes.

import yaml
from datetime import date

def emit_yaml(rules: list[dict], out_path: str) -> None:
    document = {
        "schema_version": "1.0",
        "units": {"setback_m": "metres", "crs_assumption": "projected metric CRS"},
        "generated": date.today().isoformat(),
        "rules": rules,
    }
    with open(out_path, "w", encoding="utf-8") as fh:
        yaml.safe_dump(document, fh, sort_keys=False,
                       default_flow_style=False, allow_unicode=True)

The resulting document is self-describing:

schema_version: "1.0"
units:
  setback_m: metres
  crs_assumption: projected metric CRS
generated: "2026-07-13"
rules:
  - rule_id: RES-FRONT-01
    zone_class: R-1
    setback_m: 7.62
    precedence: 10
    effective_date: "2025-01-01"

Step 4: Round-trip check the output

Prove the migration is lossless by reloading the YAML with yaml.safe_load and comparing it field by field against the validated in-memory rules. If the reloaded data matches, the file is a faithful representation and safe to commit.

def round_trip_ok(rules: list[dict], out_path: str) -> bool:
    with open(out_path, encoding="utf-8") as fh:
        reloaded = yaml.safe_load(fh)["rules"]
    # Compare on sorted rule_id so ordering differences never cause false failures.
    key = lambda r: r["rule_id"]
    return sorted(rules, key=key) == sorted(reloaded, key=key)

Verification

Run the full pipeline on a small sample and print a summary before trusting a full jurisdiction. Confirm the rule count matches the spreadsheet row count, that no rule_id was dropped, and that the round-trip check passes.

df = read_rules("zoning_rules.xlsx")
rules = validate_rows(df)
emit_yaml(rules, "zoning_rules.yaml")

assert round_trip_ok(rules, "zoning_rules.yaml"), "Round-trip mismatch"
print(f"Migrated {len(rules)} rules; ids unique: {len({r['rule_id'] for r in rules}) == len(rules)}")

A clean run reports the same count you see in the spreadsheet, unique identifiers, and no assertion error. Spot-check two or three converted setbacks by hand to confirm the foot-to-metre conversion landed where you expect.

Common Pitfalls

  • Implicit YAML typing: an unquoted zone code such as NO or ON parses as a boolean, and 1.10 loses its trailing zero. Emit categorical fields as strings and validate types on reload.
  • Mixed units in one column: spreadsheets often blend feet and metres across rows edited by different staff. Standardize the unit during validation and record it in the units block so the value is never ambiguous.
  • Silent duplicate identifiers: two rows sharing a rule_id will overwrite each other downstream. The uniqueness check in Step 2 stops the migration before a collision reaches the engine.

Frequently Asked Questions

Why choose YAML over keeping the rules in a spreadsheet?

A spreadsheet has no reviewable history, no schema enforcement, and no clean way to diff a single amended threshold. YAML in Git gives planners readable text, line-level diffs during council reviews, and inline comments for statutory citations, while still loading into the engine as structured data.

How do I keep the migrated YAML in sync when ordinances change?

Treat the YAML file as the source of truth and version it like code. Pair the migration with a version-control workflow such as the one described in automating zoning code version control with Git, so every amendment is a reviewed commit with a recoverable history.

Should I convert every value to metres during migration?

Convert to a single linear unit that matches the projected CRS your spatial engine uses, and metres is the common choice. Storing one unit removes an entire class of distortion bugs, and recording that unit in the document header makes the file self-documenting for future auditors.

What if the spreadsheet has malformed or empty rows?

Fail loudly at validation rather than emitting a partial file. Blank identifiers, missing required columns, and non-numeric setbacks should raise an error that names the offending row, so a planner can correct the source before the migration proceeds.

Summary

Migrating from spreadsheets to YAML turns a fragile, unversioned artifact into a reviewable, schema-backed rule source. By reading defensively with pandas, validating and unit-normalizing every row, emitting stable block-style YAML, and confirming the result with a round-trip check, you produce a file that both planners and the rule engine can trust. From here, commit the YAML to Git and let the broader rule storage format comparison guide when and whether to graduate to a database.