What 295,810 inspection rows actually say — once you clean them
NYC DOHMH Restaurant Inspections, analyzed end-to-end: clean → analyze → findings → recommendation, with DataReady dogfooded as step 1. Every number on this page is computed by a committed script — nothing is retyped.
Step 1 — clean first
DataReady (defaults): readiness 73 → 78. Cleared 3,506 placeholder dates, removed 125 exact duplicate rows, converted 60 sentinel tokens to real empties — and refused, out loud, to impute GRADE or decide PII policy. The placeholders it cleared and the structure it exposed become findings 3 and 5 below.
Findings
One row = one violation citation, not an inspection:
31,159 restaurants → 87,892 inspections →
295,685 rows (3.36 rows per inspection).
Every naive COUNT(*) — "Manhattan had 109k inspections" — is violation-weighted
and ~3.4× overstated.
SCORE repeats on every violation row of an inspection, so bad inspections get counted more: row-weighted mean 25.48 vs inspection-weighted 17.83 — a +42.9% bias (higher = worse). That's the difference between "the average inspection is a B" and "borderline C". A dashboard, a pasted-into-ChatGPT summary, or a fine-tuned model would all report the inflated number.
GRADE looks 50.7% broken — until you segment by inspection type: 62% blank on initial cycle inspections vs 4% on re-inspections (99.9% on administrative records, which never carry grades). The blanks are the process, not corruption. Imputing or dropping them would be the actual data-quality disaster — which is why the tool refuses imputation by design.
At the correct granularity (score-bearing inspections, min 500–1,000 per segment):
| Segment | Avg score (worse ↑) | Critical-violation rate |
|---|---|---|
| Indian | 23.9 | — |
| Caribbean | 21.5 | — |
| Latin American | 21.1 | 92% |
| Queens (worst borough) | 19.1 | 88.8% |
| Manhattan (largest volume) | 17.1 | 85.6% |
| Donuts (best cuisine) | 11.0 | 77% |
Inspection volume concentrates in Manhattan; inspection risk concentrates in Queens and a handful of cuisines.
All 3,506 cleared 1900-01-01 dates sit on rows with a blank inspection type: 3,506 establishments — 11.3% of all restaurants — recorded but never yet inspected (Manhattan 1,580 · Brooklyn 853 · Queens 698). A fake date wasn't just dirt; it was an un-inspected-restaurant queue stored where a date should be.
Recommendations
- Reallocate, don't just add: shift initial-inspection capacity toward Queens and the five worst cuisines — per-inspection risk is highest there, while volume-based allocation over-serves Manhattan.
- Burn down the never-inspected queue (3,506 establishments) as its own KPI — today it's invisible because it's encoded as a fake date instead of a status.
- Publish a reporting contract with the data: entity keys, the grade-issuance rule, and "never average across violation rows." Findings 1–3 are silent 40–340% distortions waiting for every analyst and every AI prompt.
What happened to this analysis next
Each finding was generalized into a deterministic insight shape — granularity traps, weighted-average bias, structural missingness, concentration, segment gaps, outliers — that now runs on every file uploaded to DataReady after cleanup ("What your data says"). The engine re-discovers this page's headline findings on its own. Manual analysis as discovery; product as the generalization.