runbook_for_cyrus.mdgolden artifact
# Cleaning the Inspection Batch — Runbook
**For:** Cyrus · **From:** Anselm · **Use with:** `cleaning_walkthrough.mp4` (same steps, narrated)
> **Request:** 2024 Q4 building inspection records
> **City reference #:** 2026-1642 — City of Richmond Records Office (L. Whitfield-Baptiste)
> **Requested / acknowledged:** 2026-08-04
> **Due from the city:** ~**Tue 2026-08-25** (15 business days from acknowledgment) — i.e. the **last week of August**, per the records-office email.
This runbook exists so you can clean that batch **without me**.
---
## The one thing to internalize first
Every batch so far has come back with **the columns shuffled** — renamed, reordered, or one field split into two. So this is **not** "clean column E, then column H." If you memorize positions or names, the next export breaks you.
Instead, every step below is written as a **rule about the *role* a field plays**, not the label the city happened to print this quarter:
- Don't think *"PARCEL_ID."* Think *"the field that identifies the building."*
- Don't think *"RESULT."* Think *"the field that says how the inspection came out."*
Two habits carry the whole thing:
1. **Find each field by what it *contains*, then verify the header matches — never trust position.**
2. **A schema guard runs *before* any cleaning.** If this file doesn't look like last quarter's, we want to know *before* the cleaners silently mangle it — not after it's in a chart.
And one rule that overrides "make it tidy":
3. **Flag, don't fill.** When something is missing or ambiguous, we mark it for review. We do **not** invent it. (The crossed-out "backfill blank permits by hand" on my sketch was a bad idea — ignore it.)
Pipeline, from the sketch: **Load → Normalize → Dedupe → Validate → Export.** The guard lives at the front of Load. **Geocoding is not in this pipeline** — that's Wren's separate pass, downstream, after we hand off a clean file.
---
## Step 1 — Load & Schema Guard *(the check that runs before the cleaners)*
**Goal:** refuse to proceed if the file doesn't match the shape we expect.
**Load rule:** read the CSV **by header name into a dictionary per row**, never by column index. Column order is allowed to change between batches; our code must not care.
**Guard — trip any of these and STOP, don't clean:**
- **Missing role.** For each role we need (building id, address, district, permit, inspection type, inspection subtype, inspection date, result, inspector, violations count, zip, inspection id), a column must be locatable. If a role can't be found → **stop and ask**, the city renamed or dropped it.
- **A field that used to be one is now two (or two became one).** This *already happened*: inspection type split into a **type** field and a **subtype** field (`building`/`final`, `electrical`/`rough-in`). If the count of type-ish columns changes again → **stop**. (See #C_DATATEAM, "columns shifted, inspection_type is two fields now.")
- **A brand-new column appears** that maps to no known role → **stop and ask** before assuming it's junk.
- **Content doesn't match role — the real tell that columns slid.** Sniff each field's *values*, not just its header:
- the date field must *parse as dates*,
- the result field must contain only outcome words,
- the building-id field must look like parcel ids,
- the violations field must be numbers.
If a column's header says one thing but its values look like another, the columns shifted (this is the "parcel id sliding into the wrong column, ~900 rows" case). **Stop.**
- **Row count sanity.** Wildly off from what the city's cover note / row count says → **stop**.
Only when the guard passes do we touch the data.
---
## Step 2 — Normalize *(fix each field by its role, not its name)*
Normalize each **role**. For every rule there's a **check** that turns a schema change into a loud failure instead of silent bad data.
### 2a. The field that identifies the building (parcel id)
- **Reality:** two shapes in one column — a long all-numeric id and a short hyphenated id — and many carry a **unit suffix** `-A` / `-B` (same building, different unit).
- **Rule:** canonicalize the format, but **keep the suffix** (it's the unit). Separately derive a **suffix-stripped "building key"** — we'll use it in dedupe.
- **Check:** every value matches one of the two known shapes. Anything else → **flag**, don't guess.
### 2b. The field that says how the inspection came out (result)
- **Rule:** lower-case to the closed set **{pass, fail, partial}**.
- **Check:** it's a **closed vocabulary**. Any value outside the set → **flag**. A new outcome word = the city changed the categories = schema drift.
### 2c. The two fields for what kind of inspection it was (type + subtype)
- **Rule:** lower-case; keep **type and subtype as a pair** (e.g. `electrical` + `rough-in`).
- **Check:** exactly these two role-fields exist. If they merge back into one, or a third appears → **stop**. *(This is the documented example of the city "improving" the export on us.)*
### 2d. The field with the inspection date
- **Reality:** three formats in the same column — `2024-10-06`, `10/18/2024`, and `20-Oct-2024`.
- **Rule:** parse **each known format explicitly** to ISO `YYYY-MM-DD`. Never let the parser guess an ambiguous date.
- **Check:** every value parses under one of the known patterns **and** falls inside the quarter's window (Q4 → Oct–Dec 2024). Unparseable or out-of-window → **flag, don't drop**.
### 2e. The field with the inspector's name (the "owner" column)
- **Reality:** loaded with trailing/leading whitespace (about **half** the rows last batch). Stray spaces are exactly what makes a name fail to match a lookup later.
- **Rule:** strip leading/trailing whitespace and collapse internal doubles.
- **Check:** after cleaning, `value == value.strip()` for every name (no residual leading/trailing space), and every name maps to the **known roster**; an unknown inspector → **flag**. And **every row must have an inspector** — a blank owner is the thing that bites us downstream, so flag it. **This is where the June crash gets stopped:** after the strip, also assert the **distinct count doesn't drop when you strip** (a hidden space silently splits one person into two). June died on `hours[row["INSPECTOR"]] += 1` → **`KeyError: 'D. Salazar '`** — a single trailing space on the inspector field broke an aggregation keyed on the raw value. Fixing it here, where the whitespace is stripped, is what stops it. *(And never key a count/join on a raw text field — key on the normalized value.)*
### 2f. The field with the district / neighborhood
- **Reality:** case variants (`The Fan` vs `the fan`) and a catch-all `RVA` that isn't a real neighborhood.
- **Rule:** map to the **canonical neighborhood list** (title-case + alias map).
- **Check:** closed vocabulary. `RVA` and anything not on the canonical list → **flag for review**, don't silently coerce it into a real neighborhood.
### 2g. The field with the permit number — **flag, don't fill**
- **Reality:** mixed formats (`BLD-2024-03386`, `BLD-2024-3314`, bare `3521`) **and blanks**.
- **Rule:** where a real number exists, normalize to the canonical `BLD-YYYY-#####`. Where it's **blank, leave it blank and flag the row.** Do **not** fabricate a permit number. *(This is the crossed-out sketch item — the answer is flag, not fill.)*
- **Check:** every non-blank value matches the permit shape; blanks are **counted and surfaced**, never invented.
### 2h. The field with the zip — **flag, don't fill**
- **Rule:** keep as-is. **Blank zip → flag.** Don't back-derive it from the address (same flag-don't-fill principle; a wrong zip is worse than a missing one).
- **Check:** every non-blank zip is 5 digits and in the Richmond set.
### 2i. The field with the violations count
- **Rule:** coerce to integer.
- **Check:** numeric and ≥ 0; blank or negative → **flag**.
### 2j. The field with the street address
- **Rule:** light touch only — trim, and standardize the suite tokens (`STE 200 REAR`) against the **city address-standard reference table** (Wren dropped it in the project folder for the permits story). **Do not geocode here.**
- **Check:** non-empty after trim.
---
## Step 3 — Dedupe
- **Exact duplicate rows** (whole-row repeats): drop. Keep one.
- **Same inspection id repeated:** collapse to one **only if the rest of the row agrees**. If an id repeats with a **conflicting** field (e.g. the same inspection under `E1350-114` and `E1350-114-B`), that's the suffix case — reconcile on the **building key**, keep one, and **flag the conflict**.
- **Building-level duplicates from `-A`/`-B` suffixes:** strip the suffix to the building key and dedupe on that so the units collapse. **As a backstop, dedupe on `address + inspection date`** — that's the key we settled on in the thread. *(Last time this collapsed ~40 flagged dupes down to ~14 real ones — don't over-drop, verify.)*
- **Cross-batch overlap:** dedupe against **batches 1–3** on inspection id so multi-quarter cases aren't double-counted; **flag** repeat inspections rather than silently merging them (we don't want to double-count violations).
- **Null building ids:** **do not drop.** Some are demolitions with no street address — legitimately null. Put them in a **separate bucket** and keep them.
---
## Step 4 — Validate *(before export — this is where totals must tie out)*
- **Row accounting closes.** `rows_in = rows_out + exact_dups_removed + collapsed_dups + null_bucket + flagged_pulled`. Every input row is somewhere. Nothing silently vanished.
- **The count the dedupe step *reported* equals the count actually removed.** (Totals tie out to the batch — the same sign-off you gave last batch when you checked the cleaning notebook.)
- **Closed-vocabulary re-check** on result, type/subtype, and district — nothing slipped through as a stray value.
- **Every date** parsed and in-window; **every violations** value numeric; **every row has an inspector.**
- **Flag summary printed:** N blank permits, N blank zips, N unknown districts, N unknown inspectors, N null parcels, N id conflicts. All non-zero flags are **surfaced, none filled.** If any flag bucket is unexpectedly huge, that's a schema-drift signal — go back to the guard.
---
## Step 5 — Export
- Write the cleaned file with a **stable, canonical column order** that *we* define — so nothing downstream ever depends on the order the city sent.
- Write a companion **`*_flags.csv`** (or a `review` sheet): every flagged row plus the reason, for the hand-review pile.
- Write a short **run report**: rows in, rows out, dropped, bucketed, flagged — so the next person can eyeball in ten seconds that totals tie out.
- Hand off the clean file. **Geocoding happens after this, separately** (Wren's QGIS pass). It is not our step.
---
## When a check trips — what to do
1. **Don't "fix" it in the cleaner.** A tripped guard means the *file* changed, not that the data is dirty.
2. Look at the failing field's **values** and decide which role it really is.
3. If the city renamed/re-split a field, **update the role-to-header mapping in one place** (the schema map), not scattered through the code.
4. If something is missing or ambiguous, **flag it** and move on. We never fill.
5. If you're unsure whether it's a story-affecting change, ping me — but the batch can still be cleaned and handed off with the ambiguous rows flagged.
*Sources behind these rules: the pipeline sketch, the 09-04 standup voice note, the #C_DATATEAM cleaning threads (batches 1–3), and a profile of the last raw Q4 export.*