Inspiration
The real question underneath is simple and a little uncomfortable: when someone calls the city about rats, does the city show up? We wanted to answer that by ZIP code, so any New Yorker could check their own block.
What it does
It builds a Service Gap Index for every NYC ZIP code. Instead of ranking neighbourhoods by raw complaint counts (which just measures who bothers to call 311), it compares two things the city already records: how much rodent evidence inspectors actually find in restaurants, versus how much residents report to 311. Where inspectors keep finding rats but residents rarely call, that's a service gap - a neighbourhood the city can see has a problem but isn't hearing about. You can type in any ZIP and get an honest answer, or ask questions in plain English through a Genie space.
How we built it
A medallion pipeline on Databricks (Free Edition), catalog nyc_rats: Bronze — raw CSVs loaded as-is from a volume, with lineage columns. Silver — cleaned, ZIP-standardized tables with every column described, plus a one-row-per-restaurant table so counts are honest. Gold — the ZIP index, a borough summary, monthly trends, and cuisine rates. The index standardises each signal (z-scores), so evidence and complaints can be compared on the same scale. On top of Gold sits a Genie space for natural-language questions and an AI/BI dashboard with a ZIP filter.
Challenges we ran into
✅The counting trap: the inspections file is one row per violation, not per restaurant — 158k rows but only 26,114 restaurants. ✅ A silent CSV bug: the bronze load wasn't honouring quotes, so ~98k descriptions with a comma inside shifted a column, dropping "Not Applicable" into the numeric score. Caught it by opening the raw file in Excel and comparing — the fix was RFC-4180 quote/escape settings
Accomplishments that we're proud of
The finding holds up: the loudest ZIPs (much of Manhattan and brownstone Brooklyn) aren't the rattiest — the biggest gaps are in Queens, where evidence is high but reporting is low. And the data is clean enough that Genie answers live questions correctly instead of guessing.
What we learned
Most of the value was in the boring part - making the data trustworthy. Clean tables and good column descriptions are what make natural-language querying actually work.
What's next for NYC Rodent Complaint Analysis
✅ Add per-capita normalisation (Census population) alongside the per-restaurant view. ✅ Bring in more 311 complaint types to generalise the "does the city show up?" measure beyond rats. ✅ Automate refreshes so the index stays current, and parameterise environments for a real dev/prod split.