A Month of New York Complaining

Data analysis: from spreadsheet to script to machine learning

CS 2010 — Computing Fundamentals

2026-11-16

One dataset, five questions, three tools

This lecture is one dataset, taken seriously.

The dataset: every 311 call New York City logged in November 2025 — 304,905 rows, 44 columns, 258 MB of CSV.

The five questions:

  1. What does New York complain about?
  2. Does it depend on where you live?
  3. When during the day does it happen?
  4. How long does the city take to respond?
  5. What is the weirdest thing in this file?

The three tools: pandas and dplyr (a table you can program), an LLM (first-draft engine), and scikit-learn (pattern finding).

Part 1 — Why Scripts, Not Spreadsheets

A spreadsheet is a program you cannot test

A spreadsheet is software. It takes input, applies transformations, produces output. But:

  • The code lives inside the data. Formula and value share a cell, so which is which is invisible.
  • Nothing is testable. No checks, no assertions, no way to ask “does this still add up?”
  • Nothing is replayable. The 47th edit overwrote the 46th, and nobody can say what the file did last Tuesday.

The same job in a script is data in a file, code in a file, and checks in a file — each one readable, diffable, and re-runnable.

Part 2 — The Dataset

311 is the city’s front desk

311 is New York’s non-emergency line. Heat out, pothole, loud neighbor, illegal driveway blocked — you call or tap, and the city logs it.

Every call becomes one row:

  • What: Problem (formerly Complaint Type) — 174 distinct problems
  • When: created, due, closed — down to the second
  • Where: address, zip code, borough, latitude/longitude
  • Who: which agency answers (NYPD, Housing, Sanitation, …)
  • Outcome: status, resolution description

The City publishes it all as open data. We are looking at November 2025.

One month of calls is a file Excel can barely open

304,905 rows × 44 columns

  • 258 MB as CSV — fine for code, painful for a spreadsheet.
  • The full year (nyc-311-2025.csv.xz, 179 MB compressed) has over 3.6 million rows: past Excel’s hard cap of 1,048,576. It cannot even open.
  • 174 problem types, 5 boroughs (+ “Unspecified”), 15 agencies.

You will not eyeball this. From here on, the computer summarizes and you interrogate.

Part 3 — Six Verbs

Six verbs cover almost everything you do with a table

read the file · filter rows · select columns · mutate new columns · group by + summarize · arrange (sort)

That is the whole grammar of today’s lecture. We will practice it on nine real rows from the 311 file — seven columns, picked because they fit on a slide (311-sample.csv ships with the deck).

Created Closed Problem Address Borough Status
11/30 4:27 PM 11/30 6:38 PM Abandoned Vehicle 92 NEWTON STREET Staten Island Closed
11/30 10:18 PM 03/11/2026 7:40 AM For Hire Vehicle Complaint (blank) Unspecified Closed
11/30 11:11 PM (blank) Overgrown Tree/Branches 36-04 31 AVENUE Queens In Progress
11/30 11:29 PM 12/01 8:40 AM Street Condition 95 STREET Queens Closed
11/30 11:51 PM 12/01 1:22 AM Illegal Parking 784 ARGYLE ROAD Brooklyn Closed
11/30 11:55 PM 12/01 12:07 AM Noise - Residential 249 EAST 48 STREET Manhattan Closed
11/30 11:56 PM 12/01 3:41 AM Blocked Driveway 60-52 COOPER AVENUE Queens Closed
11/30 11:59 PM 12/01 3:49 PM HEAT/HOT WATER 249 EAST 37 STREET Brooklyn Closed
11/30 11:59 PM 12/01 8:14 AM Noise - Residential 655 EAST 230 STREET Bronx Closed

read — everything starts by loading the file

Python

import pandas as pd

sample = pd.read_csv("311-sample.csv")
sample.shape        # (9, 7)

R

library(readr); library(dplyr)

sample <- read_csv("311-sample.csv")
# A tibble: 9 × 7

Both give you the same object: a table in memory, with named columns and typed values. Nothing has happened to the data yet — that is the point.

filter — keep the rows you care about

Python

sample[sample["Problem (formerly Complaint Type)"]
       == "Noise - Residential"]
# 2 rows: the two noise complaints

R

sample %>%
  filter(`Problem (formerly Complaint Type)`
         == "Noise - Residential")
# 2 rows: the two noise complaints

A boolean mask selects rows: the comparison builds a true/false column, and the mask keeps the true ones.

And notice the column name: spaces, parentheses, history. Python quotes it, R backticks it — it is never bare.

select — keep the columns you care about

Python

sample[["Created Date", "Borough", "Status"]]

R

sample %>% select(`Created Date`, Borough, Status)

44 columns is too many to think about. Most questions use four or five. Selecting early keeps your output — and your attention — on them.

mutate — derive new columns from old ones

The timestamps are text. To count complaints by hour, first make an hour column — by string surgery, because the format is fixed:

Python

sample.assign(
    hour=sample["Created Date"].str.slice(11, 13).astype(int))
# adds: hour = 4, 22, 23, 23, 23, ...

R

sample %>%
  mutate(hour = as.integer(substr(`Created Date`, 12, 13)))
# adds: hour = 4, 22, 23, 23, 23, ...

mutate never changes the original columns — it appends. New facts about old rows, computed mechanically, so they cannot drift.

group_by + summarize — ask per-group questions

Who answers the phone in this slice?

Python

sample.groupby("Agency").size()

R

sample %>% count(Agency, sort = TRUE)
Agency complaints
NYPD 5
DOT 1
DPR 1
HPD 1
TLC 1

One verb turns nine rows into a fact: in this slice, the police answer the phone.

arrange — sort, then take the top

Python

(sample.groupby("Agency").size()
    .sort_values(ascending=False)
    .head(3))

R

sample %>%
  count(Agency, sort = TRUE) %>%
  head(3)

NYPD answered 5 of the 9 calls; the other four agencies got one each.

Verbs compose. The pipe chains above are the same analysis written twice — which is why a second language is cheap once you can say what you want.

Part 4 — Five Questions About New York

Q1: New York’s #1 complaint is heat — by a hair

Heat/hot water: 47,946. Illegal parking: 47,373. Noise: 43,928.

Python

df["Problem (formerly Complaint Type)"] \
  .value_counts().head(10)

R

df %>%
  count(`Problem (formerly Complaint Type)`,
        sort = TRUE) %>%
  head(10)

Q2: What you complain about depends on where you live

  • Bronx: 44.6% of complaints are noise (citywide: 26.6%) — 22,216 noise calls, more than Brooklyn + Manhattan combined.
  • Brooklyn: illegal parking leads with 18,460.
  • Heat/hot water is top-3 everywhere — November is heating season.

Before Q3: time is text until you say otherwise

Created Date arrives as the string "11/30/2025 11:59:52 PM". Sorted as text, 9 AM comes after 11 PM. You must parse it:

Python

created = pd.to_datetime(
    df["Created Date"],
    format="%m/%d/%Y %I:%M:%S %p")
created.dt.hour.value_counts().sort_index()

R

created <- as.POSIXct(
  df$`Created Date`,
  format = "%m/%d/%Y %I:%M:%S %p")
table(format(created, "%H"))

%I is the 12-hour clock, %p is AM/PM, %m/%d/%Y is US date order. Get one code wrong and every timestamp is wrong without an error message.

Q3: Noise calls come at night; heat calls come at 9 am

Same city, same phone number, two opposite rhythms — once the timestamps are parsed, the pattern is one group_by away.

Q4: Most complaints close in hours; some take weeks

Median across all closed cases: 7.7 hours. But 4,136 cases (1.36%) are still open — no Closed Date at all. Include them and your median is not slow, it is NaN.

Python

closed = pd.to_datetime(df["Closed Date"],
                        format="%m/%d/%Y %I:%M:%S %p")
hours = (closed - created).dt.total_seconds() / 3600
hours.groupby(df["Agency"]).median()

R

closed <- as.POSIXct(df$`Closed Date`,
                     format="%m/%d/%Y %I:%M:%S %p")
hours <- as.numeric(difftime(closed, created,
                             units = "hours"))
tapply(hours, df$Agency, median, na.rm = TRUE)

Q5: One Bronx building called 311 fourteen thousand times

Zip code 10466 looks like the noisiest corner of the city — 17,245 complaints in November. Group by address and the story collapses: 655 East 230 Street accounts for 14,362 of them. Fourteen thousand calls in thirty days is one call every three minutes, around the clock.

Real data arrives with lint in it

Every trap in today’s file, in one place:

Trap Where Size
Open cases with no close time Closed Date 4,136 rows (1.36%)
“Unspecified” borough Borough 196 rows
Missing coordinates Latitude/Longitude ~0.9%
One-address spam Incident Address 14,362 calls
Not-quite-UTF-8 bytes whole file encoding=“latin-1”
AM/PM text timestamps Created Date every row

None of these stopped the analysis. All of them would have silently changed its answers. First thing you do with a new file: look for the lint.

Part 5 — The LLM in the Loop

The LLM writes the first draft; the data writes the truth

The working loop:

  1. Ask for code, naming the file, the columns, and the goal.
  2. Run it. Read the error if it fails.
  3. Verify the result with an independent count — different method, same answer. (Today’s figures were all checked this way: the deck’s numbers are recomputed from the CSV by make check-figs.)

An LLM that is right 95% of the time, verified, beats an expert who is too proud to check — and it is wrong in very predictable ways, which is next.

On this file, LLMs fail in four predictable ways

  1. Hallucinated column names. "Complaint Type" — clean, plausible, wrong. The column is Problem (formerly Complaint Type). Fix: print the column list; never let the LLM guess.
  2. Invented numbers. 47,946 becomes “approximately 48,000” becomes “over 50,000 heat complaints”. Fix: every number on a slide comes from a run, not from the model’s memory.
  3. Silently wrong parsing. Day-first dates that half-succeed; 24-hour assumptions on a 12-hour file. Fix: check the min and max of the parsed column against the raw text.
  4. Silent missing-data defaults. The pandas-versus-R disagreement from Part 3. Fix: know what your tool did with the blanks — ask the LLM “what happened to the NA rows?” and then verify.

Part 6 — Slightly Fancier Tools

With 300k rows, everything is “statistically significant”

Is the Bronx–Brooklyn difference from Q2 real, or noise? A chi-square test of Borough × top-5 problem type:

Python

from scipy.stats import chi2_contingency
tab = pd.crosstab(df["Borough"],
                  df["Problem (formerly Complaint Type)"])
chi2, p, dof, _ = chi2_contingency(tab)
# chi2 = 29109, p ~= 0

R

tab <- table(df$Borough,
             df$`Problem (formerly Complaint Type)`)
chisq.test(tab)
# X-squared = 29109, p-value < 2.2e-16

p ≈ 0. Settled? No. With 165,000 rows, trivial differences are guaranteed “significant”. The useful question is how big: Cramér’s V = 0.21 — a real, modest difference. At this scale, effect size is the headline; p-values are the fine print.

k-means found the boroughs without a map

Give k-means 60,000 complaints as (latitude, longitude) pairs, nothing else, ask for 5 groups:

Python

from sklearn.cluster import KMeans
pts = df[["Latitude", "Longitude"]].dropna() \
        .sample(60_000, random_state=42)
KMeans(5, n_init=10,
       random_state=42).fit_predict(pts)

R

pts <- df[c("Latitude", "Longitude")]
pts <- pts[complete.cases(pts), ]
km <- kmeans(pts, centers = 5)   # base R!

The cluster centers land on the Bronx, Queens, Brooklyn, Manhattan, and Staten Island. Nobody told it about geography.

It is the same six verbs all the way up

  • The six verbs: read, filter, select, mutate, group, arrange — today’s Part 3.
  • Statistics: the same verbs, then one more function call (chisq.test, chi2_contingency).
  • Machine learning: the same verbs to build the table of features, then fit_predict instead of summarise.

You never leave the grammar. The nouns get fancier.