Part 7 · Scientific Computing & Data Science · Graded project
Project 7: City Data EDA Report
Turn a messy export of a city's 311 service requests into a cleaned dataset, honest statistics, clear charts, and a Markdown report a city manager can read. Every cleaning decision is documented and every number is reproducible, because the tests check the exact values.
- Load raw data with explicit dtypes instead of letting pandas guess
- Design cleaning rules as small, ordered, logged steps whose effects can be counted and tested
- Engineer features from timestamps and detect outliers relative to each group
- Quantify uncertainty with a bootstrap confidence interval and compare groups with a nonparametric test
- Produce publication-quality charts headlessly with matplotlib's
FigureAPI - Assemble a reproducible pipeline that writes a Markdown report, verified by 60+ tests
The scenario
Every large city runs a 311 line: the non-emergency number (and app) where residents report noise, missing heat, potholes, broken street lights, and illegal parking. Each report becomes a service request with an ID, a creation time, a closing time once resolved, a category, and a borough.
The deputy mayor's office wants a monthly exploratory data analysis (EDA) report: what people
complain about, where, how the volume is trending, how long the city takes to resolve requests, and whether one
borough is being served more slowly than another. Last month an analyst built it by hand in a spreadsheet; nobody
can reproduce the numbers, and the borough totals did not add up because BROOKLYN,
Bklyn, and Brooklyn were counted separately.
Your job is to build cityeda, a small Python package that runs the whole analysis from the raw
export to report.md plus charts, with a cleaning log that explains every row that was dropped or
changed. The raw export is genuinely messy:
- borough names in many spellings (
BRONX,The Bronx,BX), and some blank; - categories with random casing and stray spaces (
STREET LIGHT); - creation dates that are not dates at all (
not a date,31/02/2025,N/A); - closing dates of
unknown, and some requests that "close" before they were opened; - a few requests that took thousands of hours, and request IDs that appear twice.
Because the real export cannot be shipped with the course, the package includes a deterministic
synthetic generator, generate_requests(n, seed), that produces a realistic CSV with exactly
known amounts of each kind of mess. That is what lets the tests check your cleaning counts and statistics
exactly.
Here is a small sample in the same format. Explore it before you write any code:
Learning goals and feature spec
0. cityeda.generate (provided)
generate_requests(n=2000, seed=311) -> str returns CSV text with n + n // 50
rows; write_requests(path, n, seed) writes it. mess_counts(n) tells you exactly how
many rows of each kind of mess were injected. You do not change this module.
1. cityeda.load
RAW_DTYPES maps every column to an explicit dtype: text as "str" (dates stay text
for now), channel and status as "category".
load_requests(path) raises ValueError("... missing columns ...") if a column is
absent and returns the columns in COLUMNS order.
2. cityeda.clean
normalize_borough, normalize_category, and
clean_requests(raw) -> (frame, log) applying seven rules in order, each recorded as a
CleaningStep(rule, description, rows_affected). The input is never modified.
3. cityeda.features
iqr_outliers(values, k=3.0) (Tukey fences) and add_features(df) adding
resolution_hours, ordered weekday, hour, week (the
Monday), and is_outlier computed within each category.
4. cityeda.analysis
requests_by (counts, largest first, ties alphabetical), resolution_summary
(named aggregations), bootstrap_median_ci (seeded), weekly_trend (weekly counts and a
rolling mean), and compare_boroughs (Mann-Whitney U).
5. cityeda.charts, report, pipeline
Three PNG charts via matplotlib.figure.Figure; markdown_table,
render_report, write_report; run_pipeline(csv, out_dir) and a
main() CLI that logs with structlog.
The seven cleaning rules
| # | Rule name (in the log) | What it does | rows_affected counts |
|---|---|---|---|
| 1 | drop_duplicate_ids | keep the first row for each request_id | rows removed |
| 2 | normalize_category | collapse whitespace and title-case | rows whose value changed |
| 3 | normalize_borough | map spellings to Manhattan, Brooklyn, Queens, Bronx, Staten Island | non-blank rows whose value changed |
| 4 | fill_missing_borough | blank boroughs become "Unknown" | blank rows |
| 5 | drop_invalid_created | drop rows whose created_at does not parse with the exact format | rows removed |
| 6 | null_invalid_closed | unparseable closed_at becomes missing (NaT) | values nulled |
| 7 | null_closed_before_created | closed_at earlier than created_at becomes missing | values nulled |
Architecture
The pipeline is a straight line of pure functions, each taking a DataFrame and returning a new one (or a result object). That makes every stage testable on its own and the whole run deterministic.
flowchart LR G[generate.py
generate_requests] -->|CSV| L[load.py
load_requests] L -->|raw frame| C[clean.py
clean_requests] C -->|clean frame + log| F[features.py
add_features] F --> A[analysis.py
counts, CI, trend, test] F --> V[charts.py
PNG files] A --> R[report.py
render_report] V --> R R -->|report.md| O[(out_dir)] P[pipeline.py
run_pipeline / main] -.orchestrates.-> L
work/p07_eda_report/
cityeda/
__init__.py
generate.py # provided: the synthetic data generator
load.py # milestone 1
clean.py # milestone 2
features.py # milestone 3
analysis.py # milestone 4
charts.py # milestone 5
report.py # milestone 5
pipeline.py # milestone 5 (main() is provided)Setup and commands
pixi run projects # list every project and whether you started it
pixi run start p07 # copy the starter into work/p07_eda_report/
pixi run grade p07 # run all 67 grading tests against your work/ copy
pixi run grade p07 -- -k clean # grade one milestone (any pytest -k expression)
cd work/p07_eda_report && pixi run python -m cityeda.pipeline --out reportsThe last command generates reports/requests.csv (2,000 requests, seed 311), runs your pipeline,
and writes reports/report.md and reports/charts/*.png. Open the report in any Markdown
viewer. Everything you need (pandas, NumPy, SciPy, matplotlib, structlog) is already in the course's Pixi
environment.
Milestones
Milestone 0: Meet the data
Generate a dataset and explore it in a REPL (pixi run python from inside
work/p07_eda_report) before writing any cleaning code. Read generate.py: it documents
every kind of mess and how many rows of each it injects (mess_counts). Profile the raw columns with
value_counts(dropna=False), isna().sum(), and
duplicated().sum(), and note what you find. The generator tests already pass.
- I generated
requests.csvand inspected it withhead,info, andvalue_counts - I listed every borough spelling and every invalid date pattern in the raw data
pixi run grade p07 -- -k cityeda_generatepasses (7 tests)
Milestone 1: Load with explicit dtypes
Fill in RAW_DTYPES and load_requests. Read the header first with
pd.read_csv(path, nrows=0) to check for missing columns and raise a clear
ValueError; then read with usecols and dtype. Leave dates as text: if
read_csv parses them, the bad ones vanish silently and you cannot count them.
- Columns come back in
COLUMNSorder with string dtypes for text channelandstatusare categorical- Blank fields load as missing values
- A file with missing columns raises
ValueErrormentioning "missing columns" pixi run grade p07 -- -k cityeda_loadpasses (6 tests)
Milestone 2: Clean, and log every rule
Implement the normalizers first; they are plain functions on one value, easy to test in isolation. Then build
clean_requests as a sequence of steps, appending a CleaningStep after each. Use
df.assign(...), boolean masks, and Series.mask(...); under pandas 3.0 Copy-on-Write,
chained assignment like df["closed_at"][mask] = pd.NaT silently does nothing. Parse dates with an
explicit format= and errors="coerce", then count the NaTs you created.
normalize_boroughhandles casing, extra spaces, aliases, "The ...", blanks, andNaN- The log lists all seven rules in order with honest counts
- Counts match
mess_counts(n): duplicates, blanks, bad created, bad closed, closed-before-created - The cleaned frame is sorted by
created_atwith a fresh index and the input is unchanged pixi run grade p07 -- -k cityeda_cleanpasses (24 tests including parametrized cases)
Milestone 3: Feature engineering
Subtracting two datetime columns gives a timedelta column; .dt.total_seconds() / 3600 turns it
into hours (missing closing times stay NaN). weekday should be an
ordered categorical so charts and group-bys list Monday to Sunday, not alphabetically. For outliers,
a single global fence would flag every legitimately slow street-light repair, so compute the fences per
category with groupby(...).transform(iqr_outliers).
resolution_hoursis non-negative orNaNweekdayis ordered Monday..Sunday;houris 0-23;weekis a Monday- Every injected extreme (over 1,500 hours) is flagged, and fewer than 5% of rows are
pixi run grade p07 -- -k cityeda_featurespasses (7 tests)
Milestone 4: Analysis with uncertainty
Counts first: requests_by(df, "category") must order ties alphabetically so reports are stable.
Then the statistics from M54: a percentile bootstrap for the
median resolution time (resolution times are skewed, so the median is the honest "typical" value and there is
no simple formula for its CI), a weekly trend with resample("W-MON", label="left", closed="left")
and a 4-week rolling mean, and a Mann-Whitney U test comparing two boroughs. Why not a t-test?
The data are heavily right-skewed with extreme values; a rank-based test is robust to that.
- On the seed-311 dataset: 1,980 cleaned rows, Noise is top with 503, Bronx 366 vs Manhattan 362 requests
- Median resolution is about 25.2 hours from 1,806 closed requests, with a deterministic bootstrap CI
- The weekly trend has 26 weeks starting 2025-01-06 and sums to the row count
- The borough comparison matches
scipy.stats.mannwhitneyuand finds the Bronx slower pixi run grade p07 -- -k cityeda_analysispasses (13 tests)
Milestone 5: Charts, report, and pipeline
Build charts with from matplotlib.figure import Figure: fig = Figure(),
ax = fig.subplots(), ..., fig.savefig(path). No pyplot means no GUI backend and no
global state, so it works in tests and CI. Apply M53: sorted
horizontal bars, a gray weekly line with a colored rolling mean, colorblind-safe colors, titles that say what
the chart shows. Then render Markdown: headings, a bullet summary, the cleaning log as a table, and image links
relative to the report (charts/by_category.png). Finally wire everything in
run_pipeline.
- All four PNGs exist, start with the PNG signature, and live in
out_dir/charts/ report.mdhas every required section, the cleaning table, the computed numbers, and all four images- The borough comparison is interpreted in words, including what the p-value does not mean
- Running the pipeline twice on the same CSV produces byte-identical reports
pixi run grade p07passes all 67 tests
Rubric
| Criterion | Weight | Excellent | Needs work |
|---|---|---|---|
| Correctness | 35% | All 67 tests pass; counts and statistics match the fixed-seed values | Cleaning counts are off, or statistics differ from the expected values |
| Data cleaning judgment | 20% | Every rule is justified, logged, and preserves information where possible (null a duration rather than drop a request) | Rows disappear silently; rules depend on order by accident |
| Statistical honesty | 15% | Skew-aware summaries (median, percentiles), uncertainty reported, test chosen for the data, p-value interpreted correctly | Means of skewed data without context; "significant" presented as "important" |
| Communication | 15% | Charts are accessible and labelled; the report reads well for a non-technical audience | Default-styled charts, unlabeled axes, a report that is only numbers |
| Code quality | 15% | Pure functions, no chained assignment, typed and documented, ruff-clean, deterministic | Mutates inputs, uses pyplot global state, unseeded randomness |
pixi run grade p07
pixi run ruff check work/p07_eda_report
pixi run ruff format --check work/p07_eda_reportHints
My duplicate count is right but the borough counts are off by one
Duplicates are exact copies, so a blank-borough row can be duplicated. Remove duplicates first (rule 1), then count blanks; the log counts are defined on the frame as it is at each step.
How do I count rows a normalizer "changed"?
Compute the new Series, compare with the old one (new != old), and sum the mask. For boroughs,
exclude blank rows from that count (& ~missing) because they belong to rule 4.
pd.to_datetime accepts "2025-13-40 25:00:00" or warns about formats
Always pass format=TIMESTAMP_FORMAT and errors="coerce". With an explicit format,
pandas rejects impossible dates instead of guessing, and does not emit format-inference warnings.
My closed_before_created rule changes nothing
That is the Copy-on-Write trap: df["closed_at"][backwards] = pd.NaT modifies a temporary copy.
Write df = df.assign(closed_at=df["closed_at"].mask(backwards)), or use
df.loc[backwards, "closed_at"] = pd.NaT on a frame you own.
My weekly trend has 27 rows or starts on a Sunday
The default weekly rule "W" means weeks ending Sunday, labelled by the Sunday. Use
resample("W-MON", label="left", closed="left") so each bin starts on a Monday and is labelled by
it.
My bootstrap interval changes every run
Create the generator inside the function from the seed argument
(np.random.default_rng(seed)) and draw all resamples at once with
rng.choice(data, size=(n_boot, data.size), replace=True), so the sequence of draws is fixed.
Tests about chart files fail with "no such file or directory"
Create the parent folder before saving: path.parent.mkdir(parents=True, exist_ok=True).
Stretch goals
- Time zones: localize timestamps to
America/New_York, store UTC, and handle the March daylight-saving gap (nonexistent="shift_forward"). - Heatmap: requests by weekday and hour with a sequential colormap (
pivot_table+ax.imshow). - All pairs: compare every pair of boroughs and apply a Holm correction; report which differences survive.
- Effect size: add the Hodges-Lehmann estimate (median of pairwise differences) or a bootstrap CI for the difference in medians next to the p-value.
- Polars port: re-implement
clean_requestswith the Polars lazy API (M52) and assert both versions agree on 200,000 rows; compare timings. - HTML report: render the Markdown to a single self-contained HTML file with embedded images.