Three posts in, your toolkit is Python scripts, an intake pipeline, and a database. Then December arrives with a renamed column, a second file to combine, and a request that now repeats every Monday. Time to meet pandas — the tool built for exactly this week.
Assumes: Posts 1–3. One new install: pip install pandas. Everything else is concepts you already own.
Monday, 8:40 AM. Maria: "I need the December numbers every Monday morning from now on. Same per-agency averages, plus complaints per capita by borough."
You open the December export. Four columns: borough, category, created_date, status. No agency. No close date. No duration.
You: "Maria — I can do per-capita by borough from this file, but the per-agency averages need agency and close-date columns that aren't here. Want me to build the per-capita report now and flag the missing columns to Tom?"
Maria: "Yes. Per-capita first, and put the data gap in writing."
Per capita needs population data — a second file, combined with the first. And Tom, attaching the December export: "Same format as November... well, they renamed a column. It's category now. Same thing."
It's never the same thing. But this time you're ready: the rename gets handled once, at the boundary, in code that reruns. And the missing agency columns go into the email, not into a guessed metric.
Before you code: clarify the ask
You: "Per capita by borough — where does the population data come from, and which month counts as December?"
Maria: "Dev has a borough population file. December 1st through 7th is the first week — start there, we'll expand."
You: "And the renamed column — category means complaint_type, confirmed?"
Tom: "Yes. Just that one."
Input: december_export.csv (Dec 1–7), borough_population.csv (Dev's file)
Output: weekly_report.py — rerunnable, producing complaints per 100k residents by borough for an explicit reporting period
Explicitly out of scope: per-agency averages (the December file has no agency or duration columns — flagged to Tom in writing)
Deadline: Mondays, 9 AM, forever
"Forever" is the most important word in that box. A one-off answer can be a notebook scribble. A Monday-morning answer must be a script — which is the entire point of this post.
The minimal concept
Four ideas, and you're dangerous:
A DataFrame is a table you can program. Post 3's requests table, but in memory: rows, named columns, types. If you can picture a spreadsheet, you can picture a DataFrame — except every transformation is code, which means it's repeatable.
A Series is a column. One labeled column — pandas tracks a dtype for it, which is why checking types before transforming data matters. And it has superpowers: df["borough"].str.strip() cleans twelve thousand cells in one expression.
Inspect before you touch. head(), info(), describe() — the pandas equivalent of Post 2's "print the keys and look." You don't reshape data you haven't seen.
The verbs are few: filter, group, merge, reshape. Filter rows with a boolean mask. groupby buckets and aggregates (Post 1's Counter, Post 3's GROUP BY — same question, third tool). merge joins tables on a key (Post 3's JOIN). pivot_table turns long data into a grid. Chain them, and the pipeline reads top to bottom.
Fix it together: inspect first
import pandas as pd
dec = pd.read_csv("december_export.csv")
dec.info()
print(dec.head(3))
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 12 entries, 0 to 11
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 borough 12 non-null object
1 category 12 non-null object
2 created_date 12 non-null object
3 status 12 non-null object
borough category created_date status
0 Queens Noise 2026-12-01 closed
1 Queens Noise 2026-12-02 closed
2 Queens Sanitation 2026-12-03 open
Twelve rows, four columns, no missing values — and there it is, category, the renamed column Tom warned about. describe() adds one more useful glance:
print(dec.describe(include="object").T)
count unique top freq
borough 12 5 Queens 4
category 12 4 Noise 5
created_date 12 7 2026-12-02 3
status 12 2 closed 10
Five boroughs, four categories, ten of twelve already closed. You now know the shape of the data before transforming it — thirty seconds that prevent thirty minutes. One caveat: these checks describe the sample; they don't prove validity. Twelve non-null boroughs doesn't tell you whether one of them says 'Qeens' — that's what the boundary validation below is for.
Clean at the boundary
The rename gets handled once, explicitly, where the data enters — Post 2's boundary rule, now in pandas:
dec = dec.rename(columns={"category": "complaint_type"}) # the December rename, handled once
dec["borough"] = dec["borough"].str.strip().str.title() # canonical: "queens" → "Queens" on both sides
dec["complaint_type"] = dec["complaint_type"].str.strip().str.lower()
dec["created_date"] = pd.to_datetime(dec["created_date"], format="%Y-%m-%d")
print(dec.dtypes)
borough object
complaint_type object
created_date datetime64[ns]
status object
dtype: object
Notice the order: rename first (so every later line uses the canonical name), normalize text, then fix types. Dates become real dates — datetime64 — instead of strings that merely look like dates. When January's file renames the column again, exactly one line changes, and it changes here.
Group, merge, reshape
The count-by-borough question, third telling:
dec.groupby("borough").size().sort_values(ascending=False)
borough
Queens 4
Brooklyn 3
Bronx 2
Manhattan 2
Staten Island 1
dtype: int64
Now the new question — per capita. Merge the counts with Dev's population file, then compute:
pop = pd.read_csv("borough_population.csv")
pop["borough"] = pop["borough"].str.strip() # both sides of a join need compatible keys
per_cap = (dec.groupby("borough").size().reset_index(name="complaints")
.merge(pop, on="borough", how="left")
.assign(per_100k=lambda d: (d.complaints / d.population * 100_000).round(2))
.sort_values("per_100k", ascending=False))
print(per_cap.to_string(index=False))
borough complaints population per_100k
Staten Island 1 496000 0.20
Queens 4 2306000 0.17
Bronx 2 1424000 0.14
Manhattan 2 1629000 0.12
Brooklyn 3 2590000 0.12
Read that twice. In this small sample, Staten Island has the lowest raw count but the highest complaints-per-100k rate. Same records, different denominator, different ranking. Raw counts weren't wrong — they answered a different question than the one Maria asked. This is the moment per-capita thinking earns its keep, and it's the insight she'll remember from the meeting.
Two deliberate choices in that chain. how="left" preserves every borough on the complaints side — because we're asking a question about the complaints data, the complaints side is the set we must not lose. A missing population match then shows up as NaN: an error to investigate, not a row silently dropped (an inner merge would have made unmatched boroughs simply vanish — Post 3's JOIN lesson, one argument later). And the population file's borough column gets the same str.strip() treatment as the complaints side: both sides of a join need compatible, canonical keys, or the merge "succeeds" while matching nothing.
Try it: break the merge on purpose
Open borough_population.csv and change Queens to Qeens. Re-run the script. It should fail with ValueError: boroughs missing population: ['Queens'] — the indicator=True check caught the mismatch before a single wrong number was computed.
Now ask yourself: what would have happened without that check? The merge would have "succeeded" — Queens' population would be NaN, its per-capita rate would be NaN, and it would silently sort to the bottom of the report. No error. A technically successful merge producing a semantically wrong report. That's the difference the indicator check makes, and it's the same engineering judgment from Posts 1–3: don't trust a result you haven't verified.
Change it back when you're done. The exercise takes thirty seconds; the instinct lasts a career.
And the ops review wants a grid — boroughs down the side, complaint types across the top. That's a reshape:
dec.pivot_table(index="borough", columns="complaint_type",
values="created_date", aggfunc="count", fill_value=0)
complaint_type heat noise parking sanitation
borough
Bronx 1 1 0 0
Brooklyn 0 1 0 2
Manhattan 0 1 1 0
Queens 1 2 0 1
Staten Island 0 0 0 1
pivot_table is groupby with the results folded into a matrix. Same data, meeting-shaped. (For a simple count grid like this, pd.crosstab(dec["borough"], dec["complaint_type"]) is the more direct tool — same result, less typing.)
Break it: pandas' favorite traps
| The trap | What happens | The fix |
|---|---|---|
| Chained assignment | df[df.status=="closed"]["created_date"] = ... may write to a temporary copy instead of the real DataFrame — the change silently never lands, with no error. (Depending on your pandas version and copy-on-write settings, you may or may not see a warning.) | One step with .loc: df.loc[df.status=="closed", "created_date"] = ... |
| Merge key mismatch | "Queens" vs "queens" across the two files — the merge "succeeds" and the population column fills with NaN. No error. | Normalize keys at the boundary (Post 2's rule, again). Diagnose with merge(..., indicator=True) and inspect anything not "both". |
NaN comparisons | NaN != NaN, so df[df.borough == "Queens"] silently drops the rows with missing boroughs. | Check df["borough"].isna().sum() before filtering; filter missingness explicitly. |
| dtype guessing | read_csv infers types: a code column that's mostly digits loads as int64 and leading zeros vanish — or one stray "N/A" flips a numeric column to object. | Declare what you know: dtype={"zip": str}. (Milestone 2's "ZIP codes are unreliable" interrupt starts here.) |
groupby drops NaN keys | Twelve rows in, eleven counted — the row with a missing borough vanishes from every group with no warning. | Reconcile: grouped total must equal len(df). Investigate the gap with isna() before you explain it away. |
Production-minded habits: the Monday-morning script
Everything above becomes weekly_report.py — because the deliverable was never "the December answer," it was "every Monday's answer":
"""Weekly per-capita complaints report.
Scope note: Maria asked for per-agency averages AND per-capita by borough.
The December export has no agency or duration columns, so per-agency
averages are out of scope for this script — that's a data gap to raise
with Tom, not a metric to invent. This script delivers the per-capita
report, filtered to an explicit reporting period.
"""
import argparse
from pathlib import Path
import pandas as pd
RENAME = {"category": "complaint_type"} # known renames, at the boundary
EXPECTED = {"borough", "complaint_type", "created_date", "status"}
KNOWN_BOROUGHS = {"Queens", "Brooklyn", "Bronx", "Manhattan", "Staten Island"}
KNOWN_STATUS = {"open", "closed"}
DATE_FORMAT = "%Y-%m-%d" # explicit: reject anything that isn't ISO YYYY-MM-DD
def load_month(path, start, end):
"""Load and validate one reporting period. Half-open interval:
start <= created_date < end."""
df = pd.read_csv(path, dtype=str) # don't let pandas guess dtypes
df = df.rename(columns=RENAME)
missing = EXPECTED - set(df.columns)
if missing:
raise ValueError(f"missing expected columns: {sorted(missing)}")
# Canonical keys on both sides before any join
df["borough"] = df["borough"].str.strip().str.title()
df["complaint_type"] = df["complaint_type"].str.strip().str.lower()
# Explicit date format: invalid dates become NaT, then fail loudly
df["created_date"] = pd.to_datetime(df["created_date"], format=DATE_FORMAT,
errors="coerce")
bad_dates = df["created_date"].isna().sum()
if bad_dates:
raise ValueError(f"{bad_dates} rows have invalid created_date "
f"(expected {DATE_FORMAT})")
# Boundary validation: known boroughs, known statuses
unknown_boroughs = set(df["borough"].dropna().unique()) - KNOWN_BOROUGHS
if unknown_boroughs:
raise ValueError(f"unknown boroughs: {sorted(unknown_boroughs)}")
unknown_status = set(df["status"].dropna().unique()) - KNOWN_STATUS
if unknown_status:
raise ValueError(f"unknown status values: {sorted(unknown_status)}")
# Reporting-period filter: half-open interval
in_period = df[(df["created_date"] >= start) & (df["created_date"] < end)]
dropped = len(df) - len(in_period)
if dropped:
print(f"note: {dropped} rows outside {start.date()}–{end.date()} excluded")
return in_period.reset_index(drop=True)
def load_population(path):
pop = pd.read_csv(path, dtype={"borough": str, "population": str})
pop["borough"] = pop["borough"].str.strip().str.title()
missing = {"borough", "population"} - set(pop.columns)
if missing:
raise ValueError(f"population file missing columns: {sorted(missing)}")
pop["population"] = pd.to_numeric(pop["population"], errors="coerce")
if pop["population"].isna().any() or (pop["population"] <= 0).any():
raise ValueError("population must be present and positive for every borough")
return pop[["borough", "population"]]
def per_capita(complaints, population):
counts = complaints.groupby("borough", dropna=False).size().reset_index(name="complaints")
# NaN boroughs would silently vanish from the merge — catch them first
if counts["borough"].isna().any():
raise ValueError(f"{counts['borough'].isna().sum()} rows have missing borough")
merged = counts.merge(population, on="borough", how="left",
validate="many_to_one", indicator=True)
orphans = merged[merged["_merge"] != "both"]
if not orphans.empty:
raise ValueError(f"boroughs missing population: {orphans['borough'].tolist()}")
if merged["complaints"].sum() != len(complaints):
raise ValueError("reconciliation failed: grouped != input rows")
report = (merged.assign(per_100k=lambda d: (d.complaints / d.population * 100_000).round(2))
.sort_values("per_100k", ascending=False)
.drop(columns=["_merge"]))
return report
def main():
ap = argparse.ArgumentParser(description="Weekly per-capita complaints report")
ap.add_argument("csv", help="complaints export CSV")
ap.add_argument("--start", required=True, help="period start (YYYY-MM-DD, inclusive)")
ap.add_argument("--end", required=True, help="period end (YYYY-MM-DD, exclusive)")
ap.add_argument("--population", default="borough_population.csv")
args = ap.parse_args()
start = pd.to_datetime(args.start, format=DATE_FORMAT)
end = pd.to_datetime(args.end, format=DATE_FORMAT)
complaints = load_month(args.csv, start, end)
population = load_population(args.population)
report = per_capita(complaints, population)
# Timestamped output: period + run time. Reruns never overwrite.
stamp = pd.Timestamp.now().strftime("%Y%m%d-%H%M%S")
out = Path(f"report_{start.date()}_{end.date()}_{stamp}.csv")
report.to_csv(out, index=False)
print(f"wrote {out}: {len(report)} boroughs from {len(complaints)} complaints")
print(report.to_string(index=False))
if __name__ == "__main__":
main()
$ python weekly_report.py december_export.csv --start 2026-12-01 --end 2026-12-08
wrote report_2026-12-01_2026-12-08_20261012-141013.csv: 5 boroughs from 12 complaints
borough complaints population per_100k
Staten Island 1 496000 0.20
Queens 4 2306000 0.17
Bronx 2 1424000 0.14
Manhattan 2 1629000 0.12
Brooklyn 3 2590000 0.12
Reproducibility: the two sample files below contain all 12 records and all 5 borough populations. Save them as december_export.csv and borough_population.csv, run the command above, and you should get exactly this output.
# december_export.csv — all 12 records
borough,category,created_date,status
Queens,Noise,2026-12-01,closed
Queens,Noise,2026-12-02,closed
Queens,Sanitation,2026-12-03,open
Brooklyn,Noise,2026-12-01,closed
Brooklyn,Sanitation,2026-12-02,closed
Brooklyn,Sanitation,2026-12-04,closed
Bronx,Noise,2026-12-02,closed
Bronx,Heat,2026-12-05,closed
Manhattan,Noise,2026-12-03,closed
Manhattan,Parking,2026-12-06,open
Queens,Heat,2026-12-07,closed
Staten Island,Sanitation,2026-12-07,closed
# borough_population.csv — 2024 estimates, Dev's file
borough,population,source_year
Queens,2306000,2024
Brooklyn,2590000,2024
Bronx,1424000,2024
Manhattan,1629000,2024
Staten Island,496000,2024
Five habits, all familiar by now: the rename map handles known variants at the boundary, and the EXPECTED-columns check fails loudly on anything unknown — a missing column raises ValueError naming exactly which columns are absent (Post 2's EXPECTED_COLUMNS, grown up). validate="many_to_one" plus indicator=True is Post 3's orphan check in one argument — the merge proves every borough matched. The reconciliation check compares grouped output against input rows with a message a human can act on — Post 3's principle made executable, and Post 1's rule: fail with a message, not a bare assert. The denominator gets validated too: matching a population row is not the same as proving its value is usable. And the source files are never modified; the report gets a fresh timestamped name.
One line deserves its own moment: validate="many_to_one". Your counts have one row per borough; the population table should have exactly one population per borough. If Dev's file accidentally contains Queens twice — 2,306,000 and 2,310,000 — a normal merge would silently multiply rows and produce plausible wrong numbers. validate="many_to_one" refuses. Don't let tools confidently produce plausible wrong numbers — the recurring theme of this entire stage.
Explain it to the customer
"Maria — December week one, per 100k residents: Staten Island 0.20 (1 complaint), Queens 0.17 (4), Bronx 0.14 (2), Manhattan 0.12 (2), Brooklyn 0.12 (3). The headline for the review: Staten Island looks quietest by raw count but highest per capita — worth a sentence before someone cites the raw number. Two caveats: this is a 12-row excerpt, so treat magnitudes as direction, not precision; and the December file renamed complaint_type to category, which the script handles at load. Same script runs every Monday — send me January's file whenever it lands."
"Same script runs every Monday" is doing the real work in that paragraph — with one honest caveat: the script is rerunnable, not yet scheduled. Nobody's cron job runs it at 9 AM; you (or someone) still types the command. True scheduling — the pipeline that wakes up, pulls, cleans, and reports without human hands — arrives in Milestone 2. What you've built today is the prerequisite: a script deterministic enough to deserve a schedule.
Must know
- Inspect first:
head(),info(),describe()before you transform anything - Clean at the boundary: rename, validate columns, strip, lowercase, fix dtypes — once, in code, rerunnable
groupby+ aggregation;mergeon keys;pivot_tablefor grids- Boolean masks and
.locfor filtering and assignment — never chained assignment - Reconcile: grouped totals must equal input rows; merges must match
"both"
Useful later
- Vectorized string/datetime operations for richer cleaning — and
.apply()only when a transformation genuinely doesn't fit a built-in vectorized operation read_excelwithdtype=str— the 2007 spreadsheet, finally tamed- Chunked reading (
chunksize=) for files bigger than memory — Post 2's streaming lesson, revisited
Don't memorize this
- The full method list —
groupby,merge,pivot_table,assigncover nearly everything SettingWithCopyWarninginternals — remember the rule (.loc, one step), not the mechanism- Pivot syntax details — look them up; remember that reshaping exists
Where this lands in CityOps
weekly_report.py is the first CityOps artifact with a schedule: every Monday, 9 AM, Maria gets her numbers without anyone touching a spreadsheet. In Milestone 2 it grows up into the pipeline's cleaning stage — pandas between the intake and the database, doing exactly what it did here: normalize at the boundary, merge, aggregate, reconcile, write the report. The rename map is the seed of the schema-drift handling the milestone demands.
Post 3's principle was don't trust a metric you can't reconcile. Post 4's: if you can't rerun it, you didn't clean it.
Field check
- After merging, two boroughs show
NaNin the population column. What happened, and what's the one expression that lists exactly which rows failed to match? df[df.status == "closed"]["created_date"] = ...triggersSettingWithCopyWarning. What's the actual risk, and what's the fix?- Tom's January file renames the column again — your script dies with
KeyError. How do you make the boundary resilient without guessing? - Maria says "just do it by hand in Excel this once." When is the hack the right call, and when must it be code?
- Your grouped totals sum to 11 but the file has 12 rows. Where did the row go, and what check catches this on every run?
What good answers look like
1. A key mismatch between the two files — case, whitespace, or a spelling variant ("Queens" vs "queens"). The merge "succeeded" because pandas doesn't know your keys are supposed to match; it just couldn't. List the failures with merged[merged["_merge"] != "both"] (after merge(..., indicator=True)), or merged[merged["population"].isna()]. Fix it at the boundary — normalize both sides before merging, the same rule as Post 2. 2. You may be writing into a temporary copy, so the assignment silently never lands in the real DataFrame — the most dangerous kind of bug, because the code after it reads stale data with no error. Fix it with a single .loc step: df.loc[df.status == "closed", "created_date"] = .... 3. Keep an explicit rename map of known variants at the boundary (RENAME = {"category": "complaint_type"}), then validate the resulting columns against the expected set — a missing column raises ValueError naming exactly which columns are absent. A loud, specific failure you investigate beats a guessed mapping that silently corrupts the report. That's Post 2's contract instinct, one lesson later. 4. The hack is right exactly once, when the question will never be asked again and the cost of being wrong is low. The moment Maria says "every Monday," the economics flip: an hour of scripting buys back every future Monday plus every future mistake the manual version would have made. "Every Monday" is the tripwire — hear those words and reach for code. 5. groupby silently drops NaN keys — the row with a missing borough was counted by nothing. Catch it every run with the reconciliation check: grouped total must equal len(df). When they disagree, df["borough"].isna().sum() tells you how many rows fell through the crack, and the quarantine habit from Post 2 tells you what to do with them.
Comments
Post a Comment