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 dfStep 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 rulesStep 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
NOorONparses as a boolean, and1.10loses 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
unitsblock so the value is never ambiguous. - Silent duplicate identifiers: two rows sharing a
rule_idwill 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.
What the Spreadsheet Was Hiding
Every zoning spreadsheet that has been maintained for a few years contains information that is not in any cell. Migration is the moment that information either gets captured or gets lost, and it is worth going looking for it deliberately.
Formatting as data is the commonest form: a cell shaded yellow means “pending council approval”, a strikethrough means superseded, a bold row is a heading rather than a rule. None of it survives a CSV export, and all of it was load-bearing. Read the workbook with a library that exposes formatting, list the distinct styles, and ask the maintainer what each one meant before anything is converted.
Comments and notes carry the reasoning — why this district’s side setback is unusual, which council decision produced it, what the exception in practice is. These map naturally onto YAML comments and citations and are often the single most valuable thing recovered by the migration.
Formulas encode relationships that a values-only export flattens into constants. A cell computing a rear setback as a percentage of lot depth is a rule; the number it currently displays is one evaluation of that rule. Migrating the number silently converts a proportional standard into a fixed one.
Implicit ordering matters where rows were meant to be read top to bottom, with later rows understood as exceptions to earlier ones. That precedence has to become explicit ranks, because nothing else preserves it.
Reconciling the Migration
A migration is only trustworthy if the new store produces the same answers as the old one for every case anybody has looked at. Fortunately the spreadsheet gives you a ready-made oracle.
Export the sheet’s own evaluation — the district-by-district table of thresholds it currently yields — and generate the same table from the YAML. They should match cell for cell. Where they do not, the discrepancy is either a migration error or a defect in the sheet that had gone unnoticed, and both are worth resolving before the old file is retired. In practice a migration of any size turns up a handful of the latter: a row that two districts both point at, a threshold changed in one place and not its duplicate, a formula referencing a moved cell.
Then run the parcel-level check. Evaluate a corpus of parcels under the old logic and the new rule pack and diff the verdicts. Zero differences is the expected result; a small number of differences that all trace back to a sheet defect is an acceptable and well-documented outcome. A large number means something structural is wrong and the migration is not ready.
Retiring the Spreadsheet
The migration is not finished when the YAML is correct; it is finished when the spreadsheet is no longer edited. Leaving both alive guarantees divergence, usually within weeks, and the pipeline will be reading the one that stopped being updated.
A clean cutover has three parts: the spreadsheet is made read-only and marked superseded with the date and the location of its replacement; the people who maintained it are shown the pull-request path for making a change and have made one successfully; and a check runs to confirm nobody has produced a new copy. That last point is not cynicism — it is the natural response to being told the file they rely on is now read-only, and the answer is to make the new path genuinely easier rather than to police the old one.
Keep the final spreadsheet as an archived artefact rather than deleting it. It is the provenance for every rule in the new store, and the first question about an unusual threshold is often answered by looking at the row it came from.
Frequently Asked Questions
Should the migration preserve the spreadsheet’s structure?
No. Spreadsheet layout is optimised for a screen and a mouse, and reproducing it in YAML carries over its compromises — merged cells becoming nested keys, column-per-district becoming repetition. Migrate to the schema the evaluator wants, and let the reconciliation prove the values survived.
How do we handle rules that were never in the sheet?
They exist, and the migration is a good time to find them: undocumented conventions applied by whoever ran the reviews. Ask the reviewers what they check that is not in the file. Some of it is regulation that was never written down, and some is discretionary judgement that should stay with a person.
What if the sheet has thousands of rows?
Then it almost certainly has structure — one block per district or per rule family — and should become one file per block rather than one enormous file. Convert a block at a time, reconciling each before moving on, so a mistake is contained.
Can the conversion be automated end to end?
The mechanical part can, and should be scripted so it is repeatable while the source is still changing. The judgement parts — what a colour meant, whether a formula was a rule, which rows are exceptions to which — cannot, and trying to infer them is how a migration produces a rule set that is plausible and wrong.
Related
Part of: Rule storage formats: JSON, YAML and databases
- Automating zoning code version control with Git — the workflow the sheet’s maintainers move to.
- Spatial threshold configuration — the fields each migrated record needs.
- Writing golden-file tests for zoning rule packs — locking the reconciliation in as a test.
- Translating ordinance text into machine-readable predicates — for the rules the sheet never contained.
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.