Data Wrangling with pandas

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.

Before you code: clarify the ask

Input: december_export.csv (Dec 1–7 excerpt), borough_population.csv (Dev’s file)
Output: weekly_report.py — rerunnable, producing complaints per 100k residents by borough
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()
dec["complaint_type"] = dec["complaint_type"].str.strip().str.lower()
dec["created_date"] = pd.to_datetime(dec["created_date"])
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.

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.

Break it: pandas’ favorite traps

The trap	What it does	The fix
Chained assignment	df[df.status=="closed"]["created_date"] = ... raises SettingWithCopyWarning — you may be writing to a throwaway copy, so the change silently never lands.	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-safe: the Monday-morning script

Everything above becomes weekly_report.py — because the deliverable was never “the December answer,” it was “every Monday’s answer”:

import sys
from pathlib import Path
import pandas as pd

RENAME = {"category": "complaint_type"}  # known renames, at the boundary
EXPECTED = {"borough", "complaint_type", "created_date", "status"}

def load_month(path):
    df = pd.read_csv(path)
    df = df.rename(columns=RENAME)
    missing = EXPECTED - set(df.columns)
    if missing:
        raise ValueError(f"missing expected columns: {sorted(missing)}")
    df["borough"] = df["borough"].str.strip()
    df["complaint_type"] = df["complaint_type"].str.strip().str.lower()
    df["created_date"] = pd.to_datetime(df["created_date"])
    return df

def load_population(path):
    population = pd.read_csv(path)
    population["borough"] = population["borough"].str.strip()
    return population

def per_capita(complaints, population):
    counts = complaints.groupby("borough").size().reset_index(name="complaints")
    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()}")
    grouped_total = merged["complaints"].sum()
    if grouped_total != len(complaints):
        raise ValueError(
            f"reconciliation failed: {len(complaints)} input rows, "
            f"{grouped_total} grouped rows"
        )
    merged = merged.assign(population=pd.to_numeric(merged["population"], errors="coerce"))
    if merged["population"].isna().any():
        raise ValueError("population contains missing or non-numeric values")
    if (merged["population"] <= 0).any():
        raise ValueError("population must be positive")
    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

if __name__ == "__main__":
    month = sys.argv[1]                      # "december"
    complaints = load_month(f"{month}_export.csv")
    population = load_population("borough_population.csv")
    report = per_capita(complaints, population)
    out = Path(f"report_{month}.csv")
    report.to_csv(out, index=False)          # source files never modified
    print(f"wrote {out}: {len(report)} boroughs from {len(complaints)} complaints")

$ python weekly_report.py december
wrote report_december.csv: 5 boroughs from 12 complaints

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. You’ve converted a recurring request into a solved problem, in writing, with the caveats attached.

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.