Prepare a leak-free table for city repair requests

What you will be able to do

Turn a real city export of 315,424 street-repair requests into a modelling table that a model can trust. You find the export’s quiet problems and decide what a model may know when a request arrives. Then you deliver the table with checks, tests and a record of every step.

Case studyPortfolio piece

Dataset

NYC 311 Service Requests, Department of Transportation, January 2024 to June 2025

Licence

NYC Open Data: no restrictions on use

Time

About 2.5 hours

Runs in

VS Code, Python 3.12+

Skills used

• Profile a real export in SQL
• Clean with a quarantine
• Target timing and label maturity
• History features with a window frame
• Time split
• Validation rules with tests
• Lineage and one-command delivery

Download the starter project (.zip)Finished project (.zip)

Unzip the starter, open the folder in VS Code, and follow the steps below. They start with a new virtual environment.

The starter project has the scripts that only move data around, ready to use. You write the parts that need decisions: the queries, the cleaning, the target and the features, the split and the contract. The page gives every line of that code, with the reason for it. The finished project is the result, if you want to compare your work or skip a step.

Before you startWhat to know and to have readyAbout 150 minutes

What you need to know

This case study uses the whole course. These lessons matter most:

• Parameterised queries and an extraction manifest, from Reproducible extraction.
• Window functions and half-open date ranges, from Time and ranking.
• A profile before any change, and exclude, correct or impute with a quarantine, from Profile before changing and Handle messy values.
• Prediction time, target timing and features from the past only, from Features and target timing.
• A split by time, from Choose a split.
• A contract checked by validate.py, a dataset card and one command that runs everything, from Validation rules, Track lineage and Deliver a reusable dataset.
• Tests that pytest runs, from Focused tests in Python for Practical AI Engineering.

What you need on your computer

• Python 3.12 or later, and VS Code with the Python extension, as in Install and verify Python in the Python course.
• About 1 GB of free disk space: the data and its copies use about 530 MB, and the packages about 300 MB.
• An internet connection for the setup (the packages and a 17 MB data file).

Tested on macOS with Python 3.14.6 (SQLite 3.50.4), pandas 3.0.6, scikit-learn 1.9.1 and pytest 9.1.1. The test followed the steps on this page from a new virtual environment. The Windows commands are not tested.

The situation

Lucía Ortiz is a 311 liaison at New York City’s Department of Transportation (DOT). Residents report street problems to 311, the city’s service line: a pothole, a dark street light, a broken traffic signal, a cracked sidewalk. Each report is a service request. It goes to DOT, a crew or an inspector deals with it, and someone closes the request.

Most requests close quickly, but some stay open for weeks. Those are the ones that bring calls back to 311 and questions from council members. Lucía’s team uses a simple rule of its own: a request that is still open after 14 days is late. She wants to know, on the day a request arrives, which new requests will probably be late. Then her team can look at them early and give residents an honest time.

Lucía asks Sam Okafor, a data engineer, to prepare the data for that model. Hana Weiss, a data scientist, will train the model later. Hana’s request to Sam is short: “Give me one table that I can trust. One row per request, only facts from the moment it arrived, and the answer: late or not.”

Each mistake in the data has a cost. If the table hides a fact from the future, Hana’s model looks excellent in testing and fails in real use. If the table drops or doubles requests without a record, nobody can check the numbers later. Lucía, Sam and Hana are invented for this case study. The data is real and public.

What you will build

You build Sam’s project, repair-requests: a pipeline of small scripts. It turns the city’s export into a modelling table with a target, late_14d, split into train, validation and test sets. A contract with 13 rules checks the delivered files, and tests check the rules. One command runs everything and records a checksum of every output. A dataset card explains where the data came from and what happened to it.

The pipeline also writes a second product, service_requests.db: the clean requests as a SQLite database, for the city’s dashboard team. The case study Build a data API for city service requests in Build and Deploy Your First API builds an API on a database with the same rules.

The diagram shows the whole path. You build it from left to right in this case study.

The repair-requests pipeline, with the row count after each step. Every step writes a record of what it did.

Text description of the diagram

The pipeline has six steps after the source database.

1. extract.py selects the requests from nyc311.db (315,424 rows) and writes a manifest.
2. clean.py removes 392 repeated submissions and moves 1,459 requests that closed before they were created to a quarantine file. 313,573 clean rows remain.
3. build_db.py writes the clean rows to service_requests.db for other teams.
4. build_features.py adds the target and the features. It leaves out 17,319 requests without 30 days of history and 9,621 requests whose 14 days were not over at the snapshot. 286,633 rows remain, 13.7% of them late.
5. split.py divides them by time: train 186,339, valid 53,156, test 47,138.
6. validate.py checks 13 rules. Only if all pass, the dataset is delivered with MANIFEST.json and DATASET.md.

Set up the project

The project runs on your computer, in VS Code, in its own virtual environment: a folder .venv with the packages for this project only. If virtual environments are new to you, read Isolate dependencies in the Python course first.

Caution

If you already have a folder ~/projects/repair-requests, rename it or delete it first. The steps below make a new one.

1. Download the starter project with the button Download the starter project (.zip) at the top of this page. Your browser saves starter.zip, usually in your Downloads folder.

2. Unzip it into your projects folder. The zip has one folder, repair-requests. Safari may unzip it for you: then Downloads has a folder repair-requests and no zip, and you move that folder into ~/projects instead.

   macOS

   Terminal

   mkdir -p ~/projects
   cd ~/projects
   unzip ~/Downloads/starter.zip

   Windows

   PowerShell

   New-Item -ItemType Directory -Force -Path ~/projects
   Expand-Archive -Path ~/Downloads/starter.zip -DestinationPath ~/projects

   Linux

   Terminal

   mkdir -p ~/projects
   cd ~/projects
   unzip ~/Downloads/starter.zip

3. In VS Code, select File › Open Folder and open ~/projects/repair-requests. If VS Code asks whether you trust the authors of the files, select Yes.

4. Open a terminal in VS Code with Terminal › New Terminal. It opens in the project folder.

5. Make the virtual environment and activate it. The prompt then starts with (.venv).

   macOS

   Terminal

   python3 -m venv .venv
   source .venv/bin/activate

   Windows

   PowerShell

   python -m venv .venv
   .venv\Scripts\Activate.ps1

   Linux

   Terminal

   python3 -m venv .venv
   source .venv/bin/activate

   If Windows says that running scripts is disabled, run Set-ExecutionPolicy -Scope CurrentUser RemoteSigned once, and activate again.

6. Tell VS Code to use the same Python. Press Ctrl+Shift+P (Cmd+Shift+P on macOS), type Python: Select Interpreter, and select the entry that shows .venv.

7. Install the three packages at the versions in requirements.txt. From here on, the command is python on every system, because the environment is active.

   Terminal

   python -m pip install -r requirements.txt

   Near the end, a line starts with Successfully installed and lists pandas 3.0.6, pytest 9.1.1 and scikit-learn 1.9.1 with the packages they need. A [notice] about a new version of pip may follow; you can ignore it.

8. Download the data and build the source database:

   Terminal

   python get_data.py

   Output

   Downloading nyc311_dot_2024-01_2025-06.csv.gz (17 MB) ...
   Checked nyc311_dot_2024-01_2025-06.csv.gz: SHA-256 OK
   Built nyc311.db. Check: requests 315,424 rows.

   If the download fails with CERTIFICATE_VERIFY_FAILED on macOS, the script tries curl instead. If that also fails, open Applications › Python 3.x and run Install Certificates.command, then run the script again. If you downloaded the data file yourself, run python get_data.py --from followed by the path of that file.

9. Run the first query, which the starter gives you:

   Terminal

   python q.py queries/01-first-look.sql

   Output

   row_count  distinct_requests  first_created            last_created
   ---------  -----------------  -----------------------  -----------------------
   315424     315424             2024-01-01T00:30:02.000  2025-06-30T23:50:45.000
   
   1 row

What this tells us. The setup works: the data file arrived complete (its checksum matches), and nyc311.db has 315,424 rows. Each row has its own unique_key, so the key is unique. The requests were created from 1 January 2024 to 30 June 2025, 18 months. The times are text in the form 2024-01-01T00:30:02.000, with a T between the date and the time.

The starter has these files. The ones marked “you write” are short placeholders now; each section below replaces one of them.

repair-requests/
  get_data.py            downloads the frozen file and builds nyc311.db
  q.py                   runs a .sql file and prints a table (as in the course)
  run_pipeline.py        runs every step in order and writes a manifest
  requirements.txt       pandas, scikit-learn, pytest, at exact versions
  queries/               01-first-look.sql; you write the others
  scripts/
    extract.py           ready: runs your extraction query, writes a manifest
    clean.py             you write
    build_db.py          ready: writes service_requests.db
    build_features.py    you write
    split.py             you write
    contract.py          you write
    validate.py          ready: checks the contract
    leak_check.py        ready: shows what leaks do to a score
  tests/test_validate.py tests of the validation rules
  DATASET.md             a template for the dataset card
  README.md, LICENSE     the code is MIT-licensed

The data

The data comes from NYC Open Data, the city’s open data portal: the dataset 311 Service Requests from 2020 to Present. It has every 311 request in the city since 2020, for every agency. This case study uses the requests for DOT that were created from 1 January 2024 to 30 June 2025.

Source, version and terms

“311 Service Requests from 2020 to Present”, NYC Open Data, dataset erm2-nwe9, published by the City of New York (311). Version: pulled on 2026-10-07 through the city’s API with agency='DOT' AND created_date >= '2024-01-01T00:00:00' AND created_date < '2025-07-01T00:00:00'. Changes: Nextia Learning kept 20 of the 44 columns (no street addresses) and saved the result as a compressed CSV file, with the values unchanged. Terms: NYC Open Data has no restrictions on use (NYC Administrative Code § 23-502(d)). The city asks users to state the source, the version and any changes, as this note does.

Why a frozen file? The city updates this dataset every day: requests close, statuses change, texts are rewritten. If you pulled the data today, your numbers would differ from the ones on this page. Then you could not tell a mistake from a change in the source. So the course keeps a frozen file, nyc311_dot_2024-01_2025-06.csv.gz, with a SHA-256 checksum, and get_data.py checks it. To pull today’s version of the same selection instead, run python get_data.py --fresh (it takes a few minutes). A fresh pull gave the same 315,424 rows with the same values; a later pull will not.

The data dictionary

These are the columns that the case study uses. The column Written is the most important one for this case study: it says when the value is filled in. The descriptions come from the portal; the times are New York local time.

Column	What it means	Written
unique_key	The request’s ID	at creation
created_date	When the request was created	at creation
complaint_type, descriptor	The problem and its detail, for example Street Condition and Pothole	at creation
location_type, borough, community_board, council_district, incident_zip, latitude, longitude	Where the problem is	at creation
open_data_channel_type	How the request reached 311: ONLINE, PHONE, MOBILE, OTHER or UNKNOWN	at creation
status	Closed, Pending, Assigned and others: the status on the day of the pull	later
closed_date	When the agency closed the request	later
resolution_description	“The last action taken by the agency”; it “may describe next or future steps”	later
resolution_action_updated_date	When the agency last updated the request	later
due_date	Empty in every row of this selection	—

First checks

Every profile starts with missing values. COUNT(column) counts only the values that are not NULL, so COUNT(*) - COUNT(column) is the number of missing values. Save this query as queries/02-missing-values.sql in VS Code, then run it:

queries/02-missing-values.sql

-- How many rows have no value in each column that matters for the decision?
-- COUNT(column) counts only the values that are not NULL.
SELECT
    COUNT(*)                           AS row_count,
    COUNT(*) - COUNT(closed_date)      AS no_closed_date,
    COUNT(*) - COUNT(descriptor)       AS no_descriptor,
    COUNT(*) - COUNT(location_type)    AS no_location_type,
    COUNT(*) - COUNT(incident_zip)     AS no_zip,
    COUNT(*) - COUNT(latitude)         AS no_coordinates,
    COUNT(*) - COUNT(council_district) AS no_council_district,
    COUNT(*) - COUNT(due_date)         AS no_due_date
FROM requests;

Terminal

python q.py queries/02-missing-values.sql

Output

row_count  no_closed_date  no_descriptor  no_location_type  no_zip  no_coordinates  no_council_district  no_due_date
---------  --------------  -------------  ----------------  ------  --------------  -------------------  -----------
315424     6083            15             178585            14735   46841           50520                315424

1 row

What this tells us. 6,083 requests have no closing time: they were still open on the day of the pull. 46,841 requests (14.9%) have no coordinates, so any feature about “the same place” will be missing for them. due_date is empty in every row, so it carries no information, and the extraction leaves it out. The other gaps are in columns that the target does not need.

Next, Sam looks at what the requests are about. Save and run queries/03-complaint-types.sql:

queries/03-complaint-types.sql

-- The most common types of problem, with their share of all rows.
SELECT
    complaint_type,
    COUNT(*)                                                 AS requests,
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM requests), 1) AS pct
FROM requests
GROUP BY complaint_type
ORDER BY requests DESC
LIMIT 8;

Output

complaint_type            requests  pct
------------------------  --------  ----
Street Condition          110641    35.1
Traffic Signal Condition  69641     22.1
Street Light Condition    45797     14.5
Sidewalk Condition        37218     11.8
Curb Condition            10952     3.5
Outdoor Dining            8328      2.6
Street Sign - Damaged     7330      2.3
Street Sign - Dangling    5611      1.8

8 rows

What this tells us. Four types make up 83.5% of the requests: street conditions (mostly potholes), traffic signals, street lights and sidewalks. These are very different jobs, done by different crews. A pothole crew and a street-light repair do not take the same time, so the type of problem will matter for the target.

Frame the problem

Before any cleaning, Sam writes down what Hana’s model will do, in four decisions. Each one decides what goes into the table.

The unit. One row is one service request.

The prediction time. The model answers when a request is created. Everything it uses must exist at that moment. That includes the type of problem, the place, the channel, the time of day, and what happened before the request.

The target. Sam writes it with Lucía, word by word:

late_14d = 1 if the request was not closed within 14 days (14 × 24 hours) after created_date. A request with no closing time counts as late.

The snapshot. The data runs to 30 June 2025. Sam builds the table as the team would have built it on 1 July 2025 at 00:00, the moment after the last request. This moment is the snapshot: the time of the export, in the course’s words. A model built that day could be tested on the months that followed.

The life of one request

The export holds the whole life of each request, not only its start. Look at request 63774786, a pothole in Queens:

Time	What happens	Columns it fills in	Known at creation?
2025-01-15 09:11:14	Someone reports a pothole.	created_date, complaint_type = Street Condition, descriptor = Pothole, borough = QUEENS, coordinates, channel UNKNOWN	Yes
2025-01-16 14:43:00	An inspector sends it to another unit.	resolution_description = “The Department of Transportation inspected this complaint and referred it to the Arterial Division for further action.”, resolution_action_updated_date	No
2026-10-07 (the pull)	It is still open, 21 months later.	status = Pending, closed_date empty	No

At 09:11:14 on 15 January, a model sees only the first row. The export, pulled 21 months later, knows that the request was sent to another unit and is still pending. Both facts point straight at the answer, late. That is leakage: information in the table that the model would not have at prediction time. status, closed_date, resolution_description and resolution_action_updated_date are written after creation, so they are never features.

A target needs 14 days to become final

On the snapshot, 1 July 2025, look at a request from 25 June. It has had only six days. If it is still open, its target is not 1: it is unknown, because it may still close before its 14 days end. A target is final only for requests created at least 14 days before the snapshot:

snapshot − 14 days = 2025-07-01 00:00 − 14 days = 2025-06-17 00:00

This rule is called label maturity: a target must have had its full time to happen. The frozen file lets us measure what happens without it, because it was pulled 15 months later. 9,621 clean requests were created after 17 June 2025 00:00. On the snapshot, 2,978 of them were still open. If Sam had marked those as late, the late rate of the recent requests would be 31.0%. In the end, 1,779 of the 2,978 (59.7%) closed within their 14 days, and the true late rate of the same requests was 12.5%. The early label would have more than doubled it. So the 9,621 requests stay out of the modelling table, and the build step counts them.

Sam also checks one detail. The file knows closing times after the snapshot. Does that leak into the target? For a request created before 17 June, no. Its 14 days end before the snapshot, so a closing after the snapshot is late either way. The target is the same as on 1 July 2025.

Profile the export

A profile finds the problems before anything changes. The course calls them quiet mistakes: none of them raises an error. Write each query in queries/, run it with python q.py, and read the result before you decide anything.

Closing times that cannot be right

julianday() turns a time into a number of days, so the difference of two times is a duration in days. Save and run queries/04-closing-times.sql:

queries/04-closing-times.sql

-- Four checks on the closing time. julianday() turns a time into a number
-- of days, so a difference of two times is a duration in days.
SELECT
    SUM(closed_date IS NULL)                                     AS still_open,
    SUM(closed_date < created_date)                              AS closed_before_created,
    SUM(closed_date = created_date)                              AS closed_in_same_second,
    SUM(julianday(closed_date) - julianday(created_date) > 365)  AS open_over_a_year
FROM requests;

Output

still_open  closed_before_created  closed_in_same_second  open_over_a_year
----------  ---------------------  ---------------------  ----------------
6083        1482                   11162                  1445

1 row

What this tells us. 1,482 requests were closed before they were created. That is impossible, and there is no way to know the right closing time, so these rows cannot get a correct target. 11,162 requests were closed in the same second as they were created. About half of them say in their resolution text that they are a duplicate of an earlier request; they were closed at once. That is a real outcome (on time), not an error. 1,445 requests stayed open for more than a year: real, and late either way.

Status against closing time

queries/05-status-vs-closed.sql

-- Does the status agree with the closing time?
-- COUNT(CASE WHEN ... THEN 1 END) counts the rows where the condition is true.
SELECT
    status,
    COUNT(*)                                                AS requests,
    COUNT(CASE WHEN closed_date >= created_date THEN 1 END) AS closed_after_created,
    COUNT(CASE WHEN closed_date <  created_date THEN 1 END) AS closed_before_created,
    COUNT(CASE WHEN closed_date IS NULL THEN 1 END)         AS no_closed_date
FROM requests
GROUP BY status
ORDER BY requests DESC;

Output

status       requests  closed_after_created  closed_before_created  no_closed_date
-----------  --------  --------------------  ---------------------  --------------
Closed       307361    307342                18                     1
Pending      5832      9                     1464                   4359
Assigned     2212      508                   0                      1704
Open         11        0                     0                      11
In Progress  6         0                     0                      6
Unspecified  2         0                     0                      2

6 rows

What this tells us. Almost all of the 1,482 impossible closing times belong to Pending requests (1,464). A count by type shows that 1,458 of them are street-light requests. So the closing time of these rows probably means something else; the data does not say what. Also, 508 Assigned requests have a closing time, and one Closed request has none. The status is the status on the day of the pull, and it can change after a closing. Sam decides that closed_date alone defines the target, and that the impossible rows go to quarantine: a separate file, with a reason, for someone who can check them.

The same request, sent twice

The course found duplicates as rows that were equal in every column. Here, every row has its own unique_key. Sam looks for rows that are equal in every other column, down to the second:

queries/06-repeated-submissions.sql

-- Groups of rows that are equal in every column except unique_key.
-- GROUP BY puts rows with equal values (NULLs too) in one group.
WITH groups AS (
    SELECT COUNT(*) AS copies
    FROM requests
    GROUP BY created_date, closed_date, agency, complaint_type, descriptor, location_type,
             incident_zip, address_type, city, status, due_date, resolution_description,
             resolution_action_updated_date, community_board, council_district, borough,
             open_data_channel_type, latitude, longitude
    HAVING COUNT(*) > 1
)
SELECT
    COUNT(*)                AS groups_found,
    SUM(copies)             AS rows_in_groups,
    SUM(copies) - COUNT(*)  AS extra_rows
FROM groups;

Output

groups_found  rows_in_groups  extra_rows
------------  --------------  ----------
273           665             392

1 row

What this tells us. 273 groups of rows are identical in all 19 columns except the key, and together they have 392 extra rows. One group has nine pothole requests at the same coordinates in the same second. Nine people did not report the same pothole in the same second; one report was saved more than once. Each extra copy would count as its own request, in the target rate and in the history features. Sam keeps the first row of each group (the lowest key) and removes the others, with a count.

Values that mean “not recorded”

Not every missing value is NULL. Some columns write a word instead:

queries/07-placeholders.sql

-- Values that mean "not recorded", although they are not NULL.
SELECT 'borough = Unspecified' AS placeholder, COUNT(*) AS row_count
FROM requests WHERE borough = 'Unspecified'
UNION ALL
SELECT 'community_board contains Unspecified', COUNT(*)
FROM requests WHERE community_board LIKE '%Unspecified%'
UNION ALL
SELECT 'open_data_channel_type = UNKNOWN', COUNT(*)
FROM requests WHERE open_data_channel_type = 'UNKNOWN'
UNION ALL
SELECT 'incident_zip is not five digits', COUNT(*)
FROM requests WHERE incident_zip NOT GLOB '[0-9][0-9][0-9][0-9][0-9]';

Output

placeholder                           row_count
------------------------------------  ---------
borough = Unspecified                 1728
community_board contains Unspecified  22003
open_data_channel_type = UNKNOWN      182215
incident_zip is not five digits       1

4 rows

What this tells us. UNKNOWN is the most common channel: 182,215 requests, 57.8% of all. A WHERE channel IS NULL would find none of them, and a count of channels would show UNKNOWN as if it were a real channel, like PHONE. The cleaning step turns each placeholder into NULL, so that “not recorded” has one form. Later, the modelling table turns NULL into the category unknown. For channel, “not recorded” has a meaning of its own, as the exploration shows.

Times that too many requests share

Requests arrive all day, so no single second should be very common. Sam counts the most common times of day, to the second:

queries/08-busy-times.sql

-- The times of day (to the second) that the most requests share.
SELECT
    substr(created_date, 12, 8) AS time_of_day,
    COUNT(*)                    AS requests
FROM requests
GROUP BY time_of_day
ORDER BY requests DESC
LIMIT 5;

Output

time_of_day  requests
-----------  --------
07:00:00     1908
22:10:00     751
14:19:00     278
14:20:00     276
14:14:00     268

5 rows

What this tells us. 1,908 requests were created at exactly 07:00:00, and 751 at exactly 22:10:00. The next most common time has 278. These are batch times: requests entered in a group, with one time for all, not the moment a resident reported a problem. Most of the 07:00:00 rows are potholes with channel UNKNOWN. A feature “hour of creation” would read them as morning requests. Sam keeps the hour, but adds a flag, batch_time, so the model can tell the two apart.

Text that was read with the wrong encoding

The resolution texts come from a short list of standard messages. Two of them start the same way. Sam groups by the full text and shows a part of each:

queries/09-text-encoding.sql

-- The resolution texts that start with the same 50 characters, with a
-- part of each text, its length, and how often it appears.
SELECT
    substr(resolution_description, 75, 20) AS part_of_text,
    length(resolution_description)         AS characters,
    COUNT(*)                               AS requests
FROM requests
WHERE resolution_description LIKE 'Service Request status for this request is available%'
GROUP BY resolution_description
ORDER BY requests DESC;

Output

part_of_text          characters  requests
--------------------  ----------  --------
Transportationâs w  147         96516
Transportation's web  141         18918

2 rows

What this tells us. The first text is six characters longer and shows â where an apostrophe should be. This is mis-encoded text: the city saved the text as UTF-8, and a later step read it as Latin-1. In UTF-8, the right quote mark ’ is three bytes. Read as Latin-1, the three bytes become three characters: â and two control characters. Your terminal may show them as nothing, as boxes or as question marks. The text has three quote marks, so it grows by six characters. The fresh pull showed the same text, so the error is in the source, not in the download. This column is a leak, so the model never sees it, but people read it. The cleaning step repairs it and counts the repairs.

What the profile decided

Sam writes the decisions down before writing the cleaning code, so that each one has a reason and a count. Rows are only removed when they are wrong copies or cannot be corrected; everything else is kept or corrected, and recorded.

Problem	Rows	Decision
Repeated submissions	392 extra rows	Remove; keep the lowest key of each group
Closed before created	1,482	Quarantine with the reason closed_before_created
Closed in the same second	11,162	Keep: a real outcome (on time)
Closed after more than a year; still open	1,445 and 6,083	Keep: late
Status disagrees with closing time	518	Keep; closed_date defines the target
Unspecified, UNKNOWN, ZIP NA	see above	Turn into NULL; unknown in the modelling table
Batch times 07:00:00 and 22:10:00	2,659	Keep; add the flag batch_time
Mis-encoded resolution text	96,528 (96,516 of one text, 12 of two others)	Repair; count the repairs
No coordinates	46,841	Keep; the history feature is missing for them

Explore what the decision needs

Exploration here answers three questions for Hana’s model. How much does the late rate vary? Does it change over time? Do the odd values carry information? Each query labels the requests with the target first, in a CTE, with the same rules as the pipeline: only requests created before 17 June 2025, and no impossible closing times.

queries/10-late-by-type.sql

-- The share of late requests for each type of problem.
-- Late = not closed within 14 days of creation (still open counts as late).
-- Only requests created at least 14 days before the snapshot (2025-07-01),
-- and not closed before they were created.
WITH labelled AS (
    SELECT
        complaint_type,
        CASE WHEN closed_date IS NOT NULL
              AND julianday(closed_date) - julianday(created_date) <= 14
             THEN 0 ELSE 1 END AS late
    FROM requests
    WHERE created_date < '2025-06-17'
      AND (closed_date IS NULL OR closed_date >= created_date)
)
SELECT
    complaint_type,
    COUNT(*)                   AS requests,
    ROUND(100.0 * AVG(late), 1) AS pct_late
FROM labelled
GROUP BY complaint_type
HAVING COUNT(*) >= 4000
ORDER BY pct_late DESC;

Output

complaint_type            requests  pct_late
------------------------  --------  --------
Curb Condition            10692     38.4
Street Sign - Missing     4987      28.0
Broken Parking Meter      4078      28.0
Street Light Condition    43211     26.2
Outdoor Dining            8138      24.5
Sidewalk Condition        36209     22.8
Street Sign - Damaged     7107      20.6
Highway Condition         4789      16.7
Street Sign - Dangling    5507      7.5
Street Condition          107438    6.2
Traffic Signal Condition  66637     4.3

11 rows

What this tells us. The type of problem changes the late rate almost tenfold: 4.3% for traffic signals, 38.4% for curbs. A model that knows only the type already ranks requests well, and any other feature must add to that. For Lucía, this is also a first useful fact. More than one curb request in three is late, but fewer than one traffic-signal request in twenty.

queries/11-late-by-month.sql

-- The share of late requests by month of creation (same rules as 10).
WITH labelled AS (
    SELECT
        substr(created_date, 1, 7) AS month,
        CASE WHEN closed_date IS NOT NULL
              AND julianday(closed_date) - julianday(created_date) <= 14
             THEN 0 ELSE 1 END AS late
    FROM requests
    WHERE created_date < '2025-06-17'
      AND (closed_date IS NULL OR closed_date >= created_date)
)
SELECT
    month,
    COUNT(*)                   AS requests,
    ROUND(100.0 * AVG(late), 1) AS pct_late
FROM labelled
GROUP BY month
ORDER BY month;

Output

month    requests  pct_late
-------  --------  --------
2024-01  17333     12.9
2024-02  16005     11.9
2024-03  19858     12.8
2024-04  19864     16.4
2024-05  17590     15.9
2024-06  17242     16.4
2024-07  16870     13.8
2024-08  17247     15.9
2024-09  15989     12.6
2024-10  15572     12.4
2024-11  14247     12.5
2024-12  16069     10.4
2025-01  16493     10.3
2025-02  17701     11.2
2025-03  19004     11.8
2025-04  18077     14.9
2025-05  19125     16.4
2025-06  10011     15.8

18 rows

What this tells us. The late rate follows the seasons: 14% to 16% from April to August, 10% to 13% from December to March, in both years. June 2025 has fewer requests because only the first 16 days have a final target. This matters for the split. A model is trained on the past and used in the future, so the test must also be in the future, and the months in each part change the rate that the model sees.

queries/12-late-by-channel.sql

-- The share of late requests by channel, and for potholes created at
-- 07:00:00 exactly compared with other potholes (same rules as 10).
WITH labelled AS (
    SELECT
        open_data_channel_type AS channel,
        descriptor,
        substr(created_date, 12, 8) = '07:00:00' AS at_seven,
        CASE WHEN closed_date IS NOT NULL
              AND julianday(closed_date) - julianday(created_date) <= 14
             THEN 0 ELSE 1 END AS late
    FROM requests
    WHERE created_date < '2025-06-17'
      AND (closed_date IS NULL OR closed_date >= created_date)
)
SELECT 'channel ' || channel AS rows_, COUNT(*) AS requests, ROUND(100.0 * AVG(late), 1) AS pct_late
FROM labelled
GROUP BY channel
UNION ALL
SELECT CASE WHEN at_seven THEN 'pothole at 07:00:00' ELSE 'pothole at other times' END,
       COUNT(*), ROUND(100.0 * AVG(late), 1)
FROM labelled
WHERE descriptor = 'Pothole'
GROUP BY at_seven;

Output

rows_                   requests  pct_late
----------------------  --------  --------
channel MOBILE          15027     25.9
channel ONLINE          57868     16.7
channel OTHER           4         0.0
channel PHONE           56671     15.7
channel UNKNOWN         174727    10.8
pothole at other times  59282     6.6
pothole at 07:00:00     1680      0.4

7 rows

What this tells us. “Not recorded” is not random here. Requests with channel UNKNOWN are late 10.8% of the time, against 25.9% for the mobile app. If the cleaning filled the unknown channel with the most common real value, or dropped those rows, it would erase a real difference. And potholes created at exactly 07:00:00 are almost never late (0.4%, against 6.6%). The batch-time flag is worth keeping.

Extract the selection

The extraction step selects the rows with a parameterised query, as in Reproducible extraction. It also renames the columns to plain names and changes the time format from 2024-01-01T00:30:02.000 to 2024-01-01 00:30:02, which pandas reads with one format. Write queries/extract_requests.sql:

queries/extract_requests.sql

-- One row per service request in the selection, with plain column names.
-- Times become 'YYYY-MM-DD HH:MM:SS' (New York local time, as published).
-- Nothing is removed or corrected here: the cleaning step does that and
-- counts it. Left out: agency (always DOT), city (repeats the borough),
-- due_date (always empty) and address_type (not used).
SELECT
    CAST(unique_key AS INTEGER)                              AS request_id,
    replace(substr(created_date, 1, 19), 'T', ' ')           AS created_at,
    replace(substr(closed_date, 1, 19), 'T', ' ')            AS closed_at,
    status,
    complaint_type,
    descriptor,
    location_type,
    borough,
    community_board,
    council_district,
    incident_zip,
    open_data_channel_type                                   AS channel,
    latitude,
    longitude,
    resolution_description,
    replace(substr(resolution_action_updated_date, 1, 19), 'T', ' ') AS resolution_updated_at
FROM requests
WHERE agency = 'DOT'
  AND created_date >= :created_from
  AND created_date <  :created_before
ORDER BY request_id;

The script scripts/extract.py is ready in the starter. Before it runs your query, it checks the source: the columns that the query needs, and the form of every time. A time in another form, for example without the T, would make created_date >= :created_from compare the wrong text, and the filter would be wrong without an error. Run it:

Terminal

python scripts/extract.py

Output

PASS  the source has the expected columns and time format
data/extract/requests.csv: 315,424 rows
Not selected: 0 rows

What this tells us. The whole file is in the selection, because the frozen file was pulled with the same dates. The manifest, data/extract/manifest.json, records the source (from the source table that get_data.py wrote), the parameters, the SHA-256 checksum of the query, and the counts. If someone changes the query, its checksum changes, and the manifest shows it.

Clean the requests

Replace the placeholder in scripts/clean.py with the five parts below, in order. The rule from Handle messy values holds: every input row ends in exactly one place, and every change is counted.

Part 1 loads the extract with explicit types. keep_default_na=False with na_values=[""] makes only an empty field missing, so a text such as NA stays text until a rule decides about it.

scripts/clean.py

"""Clean the extracted requests. Every input row ends in exactly one place:
the clean table, the quarantine table, or the count of removed repeated
submissions. Each correction is counted in reports/cleaning.json."""

import json
from pathlib import Path

import pandas as pd

IN = Path("data/extract/requests.csv")
OUT = Path("data/clean")
REPORTS = Path("reports")
TIME_FORMAT = "%Y-%m-%d %H:%M:%S"
TYPES = {
    "request_id": "Int64", "status": "string", "complaint_type": "string", "descriptor": "string",
    "location_type": "string", "borough": "string", "community_board": "string",
    "council_district": "string", "incident_zip": "string", "channel": "string",
    "latitude": "float64", "longitude": "float64", "resolution_description": "string",
}
TIMES = ["created_at", "closed_at", "resolution_updated_at"]


def load():
    # float_precision="round_trip" reads each coordinate exactly as written.
    # The faster default can change its last digit.
    df = pd.read_csv(IN, dtype=TYPES, keep_default_na=False, na_values=[""], float_precision="round_trip")
    for column in TIMES:
        df[column] = pd.to_datetime(df[column], format=TIME_FORMAT)
    return df

Part 2 removes the repeated submissions. duplicated(subset=…, keep="first") marks every row that repeats an earlier one in all columns except the key. Because the rows are sorted by key first, the lowest key stays.

scripts/clean.py

def remove_repeated_submissions(df):
    """Rows that are equal in every column except request_id are one request
    sent more than once. Keep the first one (the lowest request_id)."""
    other_columns = [c for c in df.columns if c != "request_id"]
    df = df.sort_values("request_id")
    repeated = df.duplicated(subset=other_columns, keep="first")
    return df[~repeated], int(repeated.sum())

Part 3 moves the requests that closed before they were created to quarantine, with the reason. A comparison with a missing closed_at is false, so open requests stay.

scripts/clean.py

def quarantine_rows(df):
    """Rows that we cannot correct with confidence go to quarantine, with a reason."""
    reason = pd.Series(pd.NA, index=df.index, dtype="string")
    reason[df["closed_at"] < df["created_at"]] = "closed_before_created"
    quarantine = df[reason.notna()].assign(reject_reason=reason[reason.notna()])
    return df[reason.isna()], quarantine

Part 4 turns the placeholders into missing values and repairs the text. The repair works backwards: it turns the text into bytes the way the wrong step read them (Latin-1), and reads those bytes as UTF-8. For a text that is already right, one of the two steps fails, and the text stays as it was. The new column resolution_repaired records which rows changed.

scripts/clean.py

def standardise(df, counts):
    """One way to write 'not recorded': a missing value (NULL in SQL)."""
    df = df.copy()
    placeholders = {
        "borough": df["borough"] == "Unspecified",
        "community_board": df["community_board"].str.contains("Unspecified", na=False),
        "channel": df["channel"] == "UNKNOWN",
        "incident_zip": ~df["incident_zip"].str.fullmatch(r"\d{5}", na=True),
    }
    for column, mask in placeholders.items():
        df.loc[mask, column] = pd.NA
        counts[f"{column}_placeholder_to_missing"] = int(mask.sum())

    # Some texts were saved as UTF-8 and read back as Latin-1, so a
    # right quote (’) shows as three characters (â, \x80, \x99).
    repaired = df["resolution_description"].map(repair_text, na_action="ignore")
    changed = repaired.ne(df["resolution_description"]).fillna(False)
    df["resolution_description"] = repaired
    df["resolution_repaired"] = changed
    counts["resolution_text_repaired"] = int(changed.sum())
    return df


def repair_text(text):
    """Undo UTF-8 read as Latin-1. A text that is already right stays the same:
    either the first step fails, or the second one does."""
    try:
        return text.encode("latin-1").decode("utf-8")
    except (UnicodeEncodeError, UnicodeDecodeError):
        return text

Part 5 runs the steps, checks that the counts add up, and writes the clean table, the quarantine and the counts.

scripts/clean.py

def main():
    raw = load()
    counts = {"input_rows": len(raw)}
    df, counts["repeated_submissions_removed"] = remove_repeated_submissions(raw)
    df, quarantine = quarantine_rows(df)
    counts["quarantined"] = len(quarantine)
    df = standardise(df, counts)
    counts["clean_rows"] = len(df)
    assert counts["clean_rows"] + counts["quarantined"] + counts["repeated_submissions_removed"] == len(raw)
    assert df["request_id"].is_unique

    OUT.mkdir(parents=True, exist_ok=True)
    REPORTS.mkdir(exist_ok=True)
    df.to_csv(OUT / "requests.csv", index=False, date_format=TIME_FORMAT)
    quarantine.to_csv(OUT / "quarantine.csv", index=False, date_format=TIME_FORMAT)
    (REPORTS / "cleaning.json").write_text(json.dumps(counts, indent=2) + "\n", encoding="utf-8")
    print(json.dumps(counts, indent=2))


if __name__ == "__main__":
    main()

Run it:

Terminal

python scripts/clean.py

Output

{
  "input_rows": 315424,
  "repeated_submissions_removed": 392,
  "quarantined": 1459,
  "borough_placeholder_to_missing": 1721,
  "community_board_placeholder_to_missing": 21765,
  "channel_placeholder_to_missing": 180366,
  "incident_zip_placeholder_to_missing": 1,
  "resolution_text_repaired": 95560,
  "clean_rows": 313573
}

What this tells us. 315,424 rows came in: 392 were removed as repeats, 1,459 went to quarantine, and 313,573 are clean. The three numbers add up to the input, and the script checks that with assert. The quarantine has 1,459 rows, not 1,482, because 23 rows with an impossible closing time were also repeats: the first rule removed them. The order of the rules changes the counts, which is one reason to write them down. The placeholder counts are a little lower than in the profile for the same reason.

A database for other teams

Lucía’s dashboard team does not want a modelling table. They want every clean request, with its status and closing time, in a form that a dashboard or an API can query fast. That is a different product from Hana’s table, with different rules: here, status and closed_at are useful facts, not leaks, because nobody predicts anything.

scripts/build_db.py is ready in the starter. It writes data/clean/service_requests.db with one table, service_requests, and a meta table with the source and the rules. Read its schema at the top of the file. Three choices in it are worth a sentence each:

• No free text and no addresses. The table has the place (borough, community board, ZIP code, coordinates), but not the resolution text. The frozen file has no street addresses at all.
• NULL means “not recorded”. borough, channel, community_board and incident_zip are NULL where the source had a placeholder, and CHECK constraints allow only the real values. A query for channel = 'UNKNOWN' returns nothing, so nobody counts “unknown” as a channel by mistake.
• days_to_close is stored. It is closed_at − created_at in days, so a dashboard does not compute it again in every query, and two CHECK constraints keep it consistent with closed_at.

Terminal

python scripts/build_db.py
python q.py --db data/clean/service_requests.db "SELECT substr(created_at, 1, 4) AS year, COUNT(*) AS requests, ROUND(AVG(days_to_close IS NOT NULL AND days_to_close <= 14), 3) AS share_closed_in_14_days FROM service_requests GROUP BY year"

Output

data/clean/service_requests.db: service_requests 313,573 rows, meta 13 rows, 103 MB

year  requests  share_closed_in_14_days
----  --------  -----------------------
2024  203658    0.862
2025  109915    0.868

2 rows

What this tells us. The database has every clean request, 313,573 rows. In both years, about 86% to 87% closed within 14 days, close to the 13.7% late rate of the modelling table. The two products agree, which is a cheap check that neither lost rows. The database is large (103 MB) because it has four indexes, which make filters by date, borough, type or status fast.

Build the target and the features

Now the modelling table: one row per request, the target, and the features that exist at creation time. The trickiest feature is the history of the place.

History from the past only: a window frame

A pothole that people reported twice last week is probably not new. An earlier request about the same problem at the same place is a fact known at creation time. It may change how fast the new request closes. Sam calls the feature prior_same_place_30d. “Same place” means the same type of problem at the same coordinates rounded to 4 decimals (about 10 metres).

The count must use only requests created before this one. Look at four pothole requests at one place:

Request	Created	Counted requests	prior_same_place_30d
A	1 Jan 10:00:00	none	0
B	20 Jan 09:00:00	A (about 19 days before)	1
C	15 Feb 08:00:00	B (about 26 days before); A is about 45 days before, outside the 30 days	1
D	15 Feb 08:00:00	B; not C, which has the same second	1

C and D were created in the same second, like a batch. Neither existed before the other, so neither counts the other. A window function from Time and ranking computes this with a frame: the rows of the partition that the function looks at, relative to the current row. RANGE BETWEEN 2592000 PRECEDING AND 1 PRECEDING means “the rows whose time is from 30 days (2,592,000 seconds) to 1 second before this row”. unixepoch() turns a time into seconds, so the frame can count in seconds. Write queries/prior_same_place.sql:

queries/prior_same_place.sql

-- For each request with a location: how many requests about the same
-- problem (complaint type and descriptor) were created at the same place
-- in the 30 days BEFORE it. Same place = latitude and longitude rounded
-- to 4 decimals (about 10 metres).
-- The frame ends 1 second before the request, so the request itself and
-- requests created in the same second do not count: at creation time,
-- nobody knows them yet. 2592000 seconds = 30 days.
SELECT
    request_id,
    COUNT(*) OVER (
        PARTITION BY complaint_type, descriptor, ROUND(latitude, 4), ROUND(longitude, 4)
        ORDER BY unixepoch(created_at)
        RANGE BETWEEN 2592000 PRECEDING AND 1 PRECEDING
    ) AS prior_same_place_30d
FROM requests
WHERE latitude IS NOT NULL;

A frame that ended at CURRENT ROW would count the request itself and the other requests of its batch. Those requests are not in the past, so the feature would leak a little. unixepoch() needs SQLite 3.38 or later. Python 3.12 and later from python.org include a newer SQLite; to check yours, run python -c "import sqlite3; print(sqlite3.sqlite_version)".

The script

Replace the placeholder in scripts/build_features.py with the four parts below. Part 1 holds the decisions as constants. They are the snapshot, the 14 days, the first day with a full history, the batch times, and the columns that must never be features.

scripts/build_features.py

"""Build the modelling table: one row per request, with only the facts known
when the request is created, and the target, late_14d."""

import json
import sqlite3
from pathlib import Path

import pandas as pd

IN = Path("data/clean/requests.csv")
OUT = Path("data/model")
PRIOR_QUERY = Path("queries/prior_same_place.sql")
TIME_FORMAT = "%Y-%m-%d %H:%M:%S"
SNAPSHOT = pd.Timestamp("2025-07-01 00:00:00")  # the end of the selection
WINDOW = pd.Timedelta(days=14)
HISTORY_FROM = pd.Timestamp("2024-02-01")  # the first day with 30 days of history before it
BATCH_TIMES = {"07:00:00", "22:10:00"}  # times that many requests share (see the profile)

# Filled in after the request is created. They describe its future, so
# they must never be features.
AFTER_CREATION = ["status", "closed_at", "resolution_description", "resolution_updated_at"]
CATEGORIES = ["complaint_type", "descriptor", "location_type", "borough", "channel"]
FEATURES = CATEGORIES + ["created_hour", "created_weekday", "batch_time", "has_location",
                         "prior_same_place_30d"]

Part 2 is the target, exactly as Sam wrote it with Lucía. A request is on time only if it has a closing time and that time is at most 14 days after creation.

scripts/build_features.py

def add_target(df):
    """late_14d = 1 if the request was not closed within 14 days of its creation.
    A request that is still open counts as late."""
    on_time = df["closed_at"].notna() & (df["closed_at"] <= df["created_at"] + WINDOW)
    df["late_14d"] = (~on_time).astype(int)
    return df

Part 3 makes the features. The categories get unknown for a missing value, as the exploration suggested. The history feature runs your SQL file on an in-memory SQLite copy of the clean table, then maps the counts back to the rows. Requests without coordinates get no count: missing, not 0, because 0 would claim that nobody reported anything there.

scripts/build_features.py

def add_features(df):
    for column in CATEGORIES:
        df[column] = df[column].fillna("unknown")
    df["created_hour"] = df["created_at"].dt.hour
    df["created_weekday"] = df["created_at"].dt.weekday  # 0 = Monday
    df["batch_time"] = df["created_at"].dt.strftime("%H:%M:%S").isin(BATCH_TIMES).astype(int)
    df["has_location"] = df["latitude"].notna().astype(int)
    df["prior_same_place_30d"] = prior_same_place(df)
    return df


def prior_same_place(df):
    """Earlier requests about the same problem at the same place, from the SQL
    in queries/prior_same_place.sql. Missing when the request has no location."""
    con = sqlite3.connect(":memory:")
    columns = ["request_id", "created_at", "complaint_type", "descriptor", "latitude", "longitude"]
    df[columns].to_sql("requests", con, index=False)
    prior = pd.read_sql(PRIOR_QUERY.read_text(encoding="utf-8"), con).set_index("request_id")
    con.close()
    return df["request_id"].map(prior["prior_same_place_30d"]).astype("Int64")

Part 4 puts it together. The features are computed on all clean rows, so January 2024 serves as history for February. Then two filters remove the rows that cannot have a fair row in the table. Requests before 1 February 2024 have less than 30 days of history in the file. Requests after 17 June 2025 have no final target.

scripts/build_features.py

def main():
    df = pd.read_csv(IN, dtype={"request_id": "Int64", "council_district": "string", "incident_zip": "string"},
                     parse_dates=["created_at", "closed_at"], date_format=TIME_FORMAT,
                     keep_default_na=False, na_values=[""], float_precision="round_trip")
    counts = {"clean_rows": len(df)}
    df = add_target(df)
    # Features come from all clean rows: January 2024 is history for February.
    df = add_features(df)

    # A request needs 14 days before the snapshot for a final target, and
    # 30 days of earlier data for a complete history.
    too_recent = df["created_at"] > SNAPSHOT - WINDOW
    too_early = df["created_at"] < HISTORY_FROM
    counts["too_recent_for_target"] = int(too_recent.sum())
    counts["before_history_from"] = int(too_early.sum())
    df = df[~too_recent & ~too_early].sort_values(["created_at", "request_id"])

    table = df[["request_id", "created_at"] + FEATURES + ["late_14d"]]
    OUT.mkdir(parents=True, exist_ok=True)
    table.to_csv(OUT / "requests_model.csv", index=False, date_format=TIME_FORMAT)
    counts.update({"rows": len(table), "late": int(table["late_14d"].sum()),
                   "late_rate": round(table["late_14d"].mean(), 3)})
    (OUT / "build_summary.json").write_text(json.dumps(counts, indent=2) + "\n", encoding="utf-8")
    print(json.dumps(counts, indent=2))


if __name__ == "__main__":
    main()

Terminal

python scripts/build_features.py

Output

{
  "clean_rows": 313573,
  "too_recent_for_target": 9621,
  "before_history_from": 17319,
  "rows": 286633,
  "late": 39145,
  "late_rate": 0.137
}

What this tells us. 313,573 clean rows came in. 9,621 are too recent for a final target and 17,319 come before the history is complete, so 286,633 rows remain. 39,145 of them are late: 13.7%, close to the rate in the exploration. Both removed groups are counted, so a reader can add the numbers up.

Check one value by hand

Request 63774786, the Queens pothole, has prior_same_place_30d = 2 in data/model/requests_model.csv. Check it with a query that does not use the window function. It runs on nyc311.db, the raw table, so the times still have the T:

queries/13-check-prior.sql

-- Check prior_same_place_30d for request 63774786 (created 2025-01-15 09:11:14)
-- by hand: the pothole requests at the same rounded place in the 30 days before.
-- It runs on nyc311.db, so the times still have the T and the coordinates are text.
WITH target AS (
    SELECT ROUND(CAST(latitude AS REAL), 4)  AS lat,
           ROUND(CAST(longitude AS REAL), 4) AS lon
    FROM requests
    WHERE unique_key = '63774786'
)
SELECT r.unique_key, r.created_date, r.closed_date, r.status
FROM requests AS r, target AS t
WHERE r.complaint_type = 'Street Condition'
  AND r.descriptor = 'Pothole'
  AND ROUND(CAST(r.latitude AS REAL), 4) = t.lat
  AND ROUND(CAST(r.longitude AS REAL), 4) = t.lon
  AND r.created_date >= '2024-12-16T09:11:14'
  AND r.created_date <  '2025-01-15T09:11:14'
ORDER BY r.created_date;

Output

unique_key  created_date             closed_date              status
----------  -----------------------  -----------------------  -------
63601369    2025-01-02T18:48:22.000  NULL                     Pending
63601493    2025-01-02T18:50:30.000  2025-01-02T18:50:30.000  Closed

2 rows

What this tells us. Two earlier pothole requests at the same place, both on 2 January, so the value 2 is right. The second one closed in the same second as it was created: it was marked as a duplicate of the first. The first is still pending, like 63774786. The same pothole was reported three times in two weeks, and the history feature captures that without any fact from the future. The finished project’s numbers script also checked 3,000 random rows this way, in plain Python: no differences.

Split by time

The exploration showed a seasonal late rate, and Hana’s model will be used on future requests. So the split is by time, as in Choose a split. Train on 2024 and validate on the first quarter of 2025. April to mid-June 2025 is the test set, which Hana uses once, at the end of her work. Replace the placeholder in scripts/split.py:

scripts/split.py

"""Split the modelling table by time: train on the past, check on the future."""

import json
from pathlib import Path

import pandas as pd

IN = Path("data/model/requests_model.csv")
OUT = Path("data/model")
VALID_FROM = pd.Timestamp("2025-01-01")
TEST_FROM = pd.Timestamp("2025-04-01")
TIME_FORMAT = "%Y-%m-%d %H:%M:%S"


def main():
    # Int64 keeps a missing count missing; without it, 3 would become 3.0.
    df = pd.read_csv(IN, parse_dates=["created_at"], date_format=TIME_FORMAT,
                     dtype={"prior_same_place_30d": "Int64"}, keep_default_na=False, na_values=[""])
    df["split"] = "train"
    df.loc[df["created_at"] >= VALID_FROM, "split"] = "valid"
    df.loc[df["created_at"] >= TEST_FROM, "split"] = "test"
    manifest = {"rule": f"train < {VALID_FROM.date()} <= valid < {TEST_FROM.date()} <= test", "parts": {}}
    for name in ["train", "valid", "test"]:
        part = df[df["split"] == name].drop(columns="split")
        part.to_csv(OUT / f"{name}.csv", index=False, date_format=TIME_FORMAT)
        manifest["parts"][name] = {
            "rows": len(part), "late": int(part["late_14d"].sum()),
            "late_rate": round(part["late_14d"].mean(), 3),
            "first_created": str(part["created_at"].min()), "last_created": str(part["created_at"].max()),
        }
    (OUT / "split_manifest.json").write_text(json.dumps(manifest, indent=2) + "\n", encoding="utf-8")
    print(json.dumps(manifest, indent=2))


if __name__ == "__main__":
    main()

Terminal

python scripts/split.py

Output

{
  "rule": "train < 2025-01-01 <= valid < 2025-04-01 <= test",
  "parts": {
    "train": {
      "rows": 186339,
      "late": 25821,
      "late_rate": 0.139,
      "first_created": "2024-02-01 00:02:00",
      "last_created": "2024-12-31 23:56:00"
    },
    "valid": {
      "rows": 53156,
      "late": 5911,
      "late_rate": 0.111,
      "first_created": "2025-01-01 00:02:00",
      "last_created": "2025-03-31 23:56:00"
    },
    "test": {
      "rows": 47138,
      "late": 7413,
      "late_rate": 0.157,
      "first_created": "2025-04-01 00:00:00",
      "last_created": "2025-06-16 23:47:00"
    }
  }
}

What this tells us. The three parts follow each other in time with no overlap. Their late rates differ: 13.9% in train (eleven months), 11.1% in valid (winter) and 15.7% in test (spring). That is the season from the exploration, not an error. It tells Hana two things before she trains anything. A model tuned on the winter validation set will see more late requests in the test than it expects. And one validation period is not enough to judge a model: she must check it on more than one season.

Why not a random split?

A random split would put requests from May 2025 in train and requests from January 2025 in test. The model would learn from the future of its own test, and the seasons would mix, so the test would look like every other month. The score would be higher than the model deserves.

Write the contract and validate

The table is ready, but Hana cannot see how it was made. A contract is a written promise about the delivered files: their columns, types, allowed values and ranges. As in Validation rules, the rules are data, and a script checks them. Here, the contract has its own file, scripts/contract.py, so that changing a rule never means changing the checking code. scripts/validate.py is ready in the starter; it reads the contract and has one function per rule.

First, run the tests that come with the starter. They test the rules on a tiny table of 12 rows. pytest finds the test functions in tests/ and runs each one, as in Focused tests in the Python course.

Terminal

python -m pytest

The tests do not run. pytest reports one error while it loads them, and near the end it says:

Output (end)

E   NotImplementedError: scripts/contract.py is not written yet. See "Write the contract and validate" in the case study.
=========================== short test summary info ============================
ERROR tests/test_validate.py - NotImplementedError: scripts/contract.py is no...
!!!!!!!!!!!!!!!!!!!! Interrupted: 1 error during collection !!!!!!!!!!!!!!!!!!!!

That is expected: validate.py reads its rules from the contract, and the contract is still a placeholder. Replace the placeholder in scripts/contract.py:

scripts/contract.py

"""The contract of the delivered dataset: the rules that the person who trains
the model can rely on in data/model/. scripts/validate.py checks each rule.
To change a rule, change it here."""

import pandas as pd

KEY = "request_id"
TIME = "created_at"
TARGET = "late_14d"
SPLITS = ["train", "valid", "test"]

# Every column, its type, and whether a value is required.
COLUMNS = {
    "request_id": ("integer", True),
    "created_at": ("datetime", True),
    "complaint_type": ("text", True),
    "descriptor": ("text", True),
    "location_type": ("text", True),
    "borough": ("text", True),
    "channel": ("text", True),
    "created_hour": ("integer", True),
    "created_weekday": ("integer", True),
    "batch_time": ("integer", True),
    "has_location": ("integer", True),
    "prior_same_place_30d": ("integer", False),  # no location, no count
    "late_14d": ("integer", True),               # the target
}

# Known only after the request is created: never in the dataset.
NEVER = ["status", "closed_at", "days_to_close", "resolution_description", "resolution_updated_at"]

ALLOWED = {
    "borough": {"BRONX", "BROOKLYN", "MANHATTAN", "QUEENS", "STATEN ISLAND", "unknown"},
    "channel": {"ONLINE", "PHONE", "MOBILE", "OTHER", "unknown"},
    "batch_time": {0, 1},
    "has_location": {0, 1},
    "late_14d": {0, 1},
}

RANGES = {  # inclusive; None means no limit
    "created_hour": (0, 23),
    "created_weekday": (0, 6),
    "prior_same_place_30d": (0, None),
    # From the first day with full history to the snapshot minus 14 days.
    "created_at": (pd.Timestamp("2024-02-01"), pd.Timestamp("2025-06-17 00:00:00")),
}

# A value that must be missing exactly when another column has a given value.
MISSING_EXACTLY_WHEN = {"prior_same_place_30d": ("has_location", 0)}

# The share of late requests, in the whole table and in each split.
LATE_RATE = (0.05, 0.30)

Two rules are specific to this data. NEVER lists the five columns that are known only after creation; the check fails if any of them is in the table. MISSING_EXACTLY_WHEN says that prior_same_place_30d is missing in exactly the rows without coordinates: a missing count in a row with coordinates would mean that the feature code broke. Run the tests again:

Terminal

python -m pytest

Output

============================= test session starts ==============================
platform darwin -- Python 3.14.6, pytest-9.1.1, pluggy-1.6.0
rootdir: /Users/you/projects/repair-requests
configfile: pytest.ini
testpaths: tests
collected 14 items

tests/test_validate.py ..............                                    [100%]

============================== 14 passed in 0.25s ==============================

What this tells us. All 14 tests pass; the second line and the folder show your own system. The first test checks that the tiny table keeps the contract; each of the other 13 breaks one rule, for example a leaky column, a repeated key, a request from 20 June 2025, or a count without a location, and checks that the right rule fails with the right message. A rule that never fails is not a check, and these tests prove that each one can. Now check the real files:

Terminal

python scripts/validate.py

Output

Checking data/model against the contract
PASS  required columns are present
PASS  no column that is known only after creation
PASS  every value has its column's type
PASS  no missing values in required columns
PASS  request_id is unique
PASS  categories use allowed values only
PASS  numbers and dates are in their allowed ranges
PASS  missing values only where the contract allows them
PASS  every split has rows
PASS  together, the splits are exactly the modelling table
PASS  no request is in two splits
PASS  the splits are in time order: train, then valid, then test
PASS  late rate between 5% and 30%
All 13 rules passed.

What this tells us. The delivered files keep all 13 rules. The late rate rule (5% to 30%) is wide on purpose. It does not check that the rate is “right”; it catches a broken step, such as a target that is 1 for every row.

Common mistake

The column that makes the score better

Symptom: a colleague trains a quick model on the table and is disappointed. Then he joins status from the clean table, “because it is in the data anyway”, and the score jumps. He saves the table with the new column and asks Sam to deliver it.

Cause: status is the status on the day of the pull, 15 months or more after creation. Pending almost always means “late”. The model learns to read the answer, not to predict it.

Diagnosis: validate.py stops the delivery. With status added to requests_model.csv, its output starts like this:

Output

Checking data/model against the contract
PASS  required columns are present
FAIL  no column that is known only after creation
      status must not be in the dataset (known only after creation)

and ends with 1 of 8 rules failed. Fix the step that made the data, then run it again. (The split rules do not run while the table fails.)

Fix: remove the column from the step that added it and run the pipeline again. The contract’s NEVER list is the place where the team writes such decisions down, so a new colleague meets the rule before he meets the problem.

Consequence in AI work: a leaky column gives a model a score that it will never reach in real use. The next section measures how large that false gain is on this data.

Run it all with one command

Each script works alone. But a dataset is only reusable if one command makes it again from the source, as in Deliver a reusable dataset. run_pipeline.py is ready in the starter. It deletes the old outputs, runs the six steps in order and stops at the first failure. At the end, it writes data/model/MANIFEST.json with a SHA-256 checksum of each output. Run it twice:

Terminal

python run_pipeline.py
python run_pipeline.py

Output (each run)

1/6 extract: ok
2/6 clean: ok
3/6 build_db: ok
4/6 build_features: ok
5/6 split: ok
6/6 validate: ok
Done: 286,633 rows (train 186,339, valid 53,156, test 47,138).
Wrote data/model/MANIFEST.json. Outputs fingerprint: e833003d8eddcf489566d48244501929a997464d122949c060e62b2c69f127d1

What this tells us. Both runs end with the same fingerprint, one checksum of all 12 output files together. Same source, same code, same bytes: the pipeline is repeatable, the SQLite database included. On your computer, the fingerprint is the same if you use the same versions of Python, SQLite and the packages on macOS or Linux. SQLite writes its version into service_requests.db, so another SQLite version gives another fingerprint, while the CSV files stay the same. On Windows, pandas writes other line endings, so compare two runs on the same computer instead. A run takes 15 to 30 seconds.

Evidence: what a leak does to a score

Modelling is the subject of Machine Learning: From Problem to Reliable Model, but one measurement belongs here, because it shows what the contract protects. scripts/leak_check.py is ready in the starter. It trains the same simple model four times on the train set and scores the validation set each time. The model is a logistic regression on one-hot categories, in a scikit-learn pipeline. The score is ROC AUC: 0.5 is guessing, and 1.0 is a perfect ranking of late requests above on-time ones.

Terminal

python scripts/leak_check.py

Output

train 186,339 rows, valid 53,156 rows

features                                             ROC AUC on valid
type of problem only (complaint_type, descriptor)    0.812
honest features (the delivered dataset)              0.816
honest + status (leak)                               0.863
honest + resolution_description (leak)               0.865

What this tells us. The type of problem alone gives 0.812, the strong signal from the exploration. All the other honest features together add a little, to 0.816. One leaky column adds about 0.05: more than ten times what all of Sam’s other features add. That is exactly why leaks are tempting: they make the number better, and nobody gets an error. In real use, on the day a request arrives, status and the resolution text do not exist yet, so the 0.863 is false. Present these numbers as evidence for the contract, not as a model result. Hana’s work in the machine learning course starts from the honest 0.816, and from the 0.812 baseline that any model must beat.

Document the dataset

The last part of the delivery is the dataset card, DATASET.md, as in Track lineage. The starter has a template with the section headings. Fill in each section from the records that the pipeline wrote; you do not need to remember any number.

Section	Where the facts come from
What it is	data/model/split_manifest.json: the files, their rows and late rates
Provenance	The source note on this page; data/extract/manifest.json; the source table in nyc311.db
Transformation order	reports/cleaning.json, data/model/build_summary.json, split_manifest.json, reports/validation.json
Columns	scripts/contract.py, and the NEVER list with the reason
Tool versions	the run section of MANIFEST.json
Output checksums	the outputs section of MANIFEST.json
Known limitations	the profile, the exploration and the split

Also describe service_requests.db and its schema, for the dashboard team. The finished project’s DATASET.md is a complete example; compare yours with it when you are done.

Recommendations

Sam sends Lucía and Hana a short note with the delivery.

For Hana (the model):

• Use the delivered split as it is: train for training, valid for choices, test once at the end. Report the test result next to the validation result, because the test period has a higher late rate.
• Start from the baseline “type of problem only” (ROC AUC 0.812 on valid). A model that does not clearly beat it is not worth its extra parts.
• Never add status, closed_at or the resolution texts, even from another table. validate.py stops them in this table; in a new project, copy the NEVER list first.

For Lucía (the decision):

• The type of problem already tells a lot. Curbs, missing signs, parking meters and street lights are late far more often than traffic signals and potholes. Her team can use that table today, before any model exists.
• Ask the city’s data team three questions. What does the closing time of the quarantined street-light requests mean? Which process creates requests at 07:00:00 and 22:10:00? Why do most requests have the channel UNKNOWN?
• Refresh the dataset every month: a fresh pull, a new snapshot, the same pipeline. Keep each month’s MANIFEST.json. If the late rate leaves 5% to 30%, or a column is renamed, the pipeline stops before it delivers.

For the dashboard team: use service_requests.db for counts and closing times. Use the median of days_to_close, not the mean: 1,444 clean requests that took more than a year to close pull the mean up.

Limits

• The target is about the record, not the street. A request closes when it is a duplicate, when an inspector finds nothing, or when another agency is responsible. late_14d says only whether the record closed in time.
• One city, 18 months, one agency. The seasons appear twice, but a change in the city’s process can make the past a poor guide. Check the late rate of every new month.
• Many requests have little context. 57.8% have no channel and 14.8% of the clean rows have no coordinates. The model knows less about them.
• The history feature is simple. “Same place” is a rounded point, so two reports on opposite sides of a street may not match. A long road problem spreads over many points.
• Times without a time zone. The source gives New York local time without a zone. A duration that crosses a change to or from daylight saving time is off by one hour. That can change the target only for a request closed within an hour of its 14-day limit.
• What not to conclude. A model trained on this table ranks requests by their risk of being late. It does not say why, and it must not be used to rank neighbourhoods or the people who report problems.

Make it a portfolio piece

This project shows the part of AI work that many portfolios skip: data that a model can trust, with the evidence. Put it in a public repository.

What to put in the repository: everything in the project folder except the data, the database and .venv. The starter’s .gitignore already leaves them out. It keeps the small JSON records in data/model/, so readers can see the lineage. Include reports/, DATASET.md, the tests and the queries. The data itself is public; your README tells readers how to get it with get_data.py.

A README template: replace the starter’s README.md with a portfolio README in this order:

1. The question, in one sentence, with the late rate: “When a request is created, will it still be open 14 days later? About 13.7% are.”
2. Data and licence: the source, the pull date, the terms of NYC Open Data, and a link to DATASET.md.
3. Approach: the profile and what it found, and the cleaning decisions with counts. Then the target and label maturity, the history feature from earlier rows, the time split, and the contract with its tests.
4. Results: the split table, and the leak check with its message.
5. How to run: the setup commands (the virtual environment, pip install and get_data.py), then run_pipeline.py.
6. Limits.

The finished project’s README.md follows this order; use it as a model, in your own words.

How to describe it in an interview, in two sentences: “I turned 315,000 real NYC street-repair requests into a leak-free modelling table, with features only from facts that existed when each request arrived. A one-command pipeline with 13 tested rules and checksums delivers it, and a leak check shows that one forbidden column would raise the score from 0.816 to a false 0.863.”

Go further:

• A new feature, with care: the number of requests of the same type that were still open in the same borough at creation time. “Still open” must be judged at that moment: a request counts only if it was created before and not yet closed then. Write it, and add a test that a later closing never changes the value.
• An input check for a fresh pull: run python get_data.py --fresh and then the pipeline. Record what changed in the counts, and why.
• A second snapshot: build the table as on 2025-04-01 with test as April 2025, and compare the counts with the first one.
• Train the model in the machine learning course’s case studies with this table, and report it next to the baseline.

Knowledge check

Check your understanding

On the snapshot, 2,978 recent requests were still open. A colleague says: 'They are open, so they are late. Keep them with late_14d = 1.' What happens, and what is right?

Show the answer

Their 14 days were not over at the snapshot, so “open” means “not known yet”, not “late”. The frozen file shows the cost: 1,779 of the 2,978 (59.7%) closed within their 14 days in the end. With the early label, the late rate of the recent requests would be 31.0% instead of 12.5%. A model would learn that new requests are late far more often than they are. Leave out every request created after 2025-06-17 00:00, and count them, as build_features.py does.

Check your understanding

Which of these can be features for late_14d: complaint_type, status, the number of requests at the same place in the 15 days before and the 15 days after, created_hour, resolution_description, batch_time? Give the reason for each one that cannot.

Show the answer

complaint_type, created_hour and batch_time can be features: they exist when the request is created. status is the status on the day of the pull, after the request’s life. resolution_description is written when the agency acts on the request. The count over 15 days before and after uses requests that do not exist yet at creation; change it to the 30 days before, with a frame that ends one second before the request, as prior_same_place_30d does.

Check your understanding

The validation set has a late rate of 11.1% and the test set 15.7%. Is that a sign of a bug in split.py? What does it mean for the model?

Show the answer

No. The exploration showed a seasonal late rate: 10% to 13% from December to March, 14% to 16% from April to August. Validation is January to March, test is April to mid-June. Hana makes her choices on the winter validation set, so her model meets more late requests in the test than it saw there. So the model must be checked on more than one season, and the test result reported next to the validation result.

Recap

• You profiled a real export in SQL and found its quiet problems. They include 392 repeated submissions, 1,482 impossible closing times, UNKNOWN in 57.8% of the channels, batch times, and mis-encoded text.
• You cleaned with counted decisions: remove the copies, quarantine what cannot be corrected, turn placeholders into missing values, repair the text, and keep the rest. 313,573 clean rows, and every input row accounted for.
• You framed the target at creation time, late_14d, and kept out the columns written later. Only requests whose 14 days were over at the snapshot got a target. Labelled too early, the recent requests would have looked late 31.0% of the time instead of 12.5%.
• You built a history feature from earlier rows only, with a window frame that ends one second before each request.
• You split by time, wrote a contract with 13 rules and tested each rule. The pipeline ran twice with the same fingerprint, and a leak check measured the false gain of a leak: 0.816 to 0.863.
• You delivered two products from one pipeline: a modelling table for a data scientist and a clean database for a dashboard team.

The next case study moves from requests to bicycles. You find export defects and van moves in real bike-share trips, and build a station target from earlier days only.

Next: Plan overnight bike rebalancing