Préparer une table sans fuite pour les demandes de réparation d'une ville

Ce que vous saurez faire

Transformez une exportation réelle de 315 424 demandes de réparation de rues en une table de modélisation fiable. Vous repérez les problèmes discrets de l’exportation et décidez ce qu’un modèle peut savoir à l’arrivée d’une demande. Ensuite, vous livrez la table avec des vérifications, des tests et une trace de chaque étape.

Étude de casPièce de portfolio

Jeu de données

NYC 311 Service Requests, Department of Transportation, janvier 2024 à juin 2025

Licence

NYC Open Data : aucune restriction d'utilisation

Durée

Environ 2 h 30

S’exécute dans

VS Code, Python 3.12+

Compétences utilisées

• Profiler une exportation réelle en SQL
• Nettoyer avec une quarantaine
• Moment de la cible et maturité des étiquettes
• Caractéristiques historiques avec un cadre de fenêtre
• Séparation temporelle
• Règles de validation avec des tests
• Provenance et livraison en une commande

Télécharger le projet de départ (.zip)Projet terminé (.zip)

Décompressez le projet de départ, ouvrez le dossier dans VS Code et suivez les étapes ci-dessous. Elles commencent par un nouvel environnement virtuel.

Le projet de départ contient des scripts prêts à utiliser qui ne font que déplacer les données. Vous écrivez les parties qui demandent des décisions : requêtes, nettoyage, cible, caractéristiques, séparation et contrat. La page fournit chaque ligne de ce code et explique sa raison d’être. Le projet terminé présente le résultat, si vous voulez comparer votre travail ou sauter une étape.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 150 minutes

Ce que vous devez savoir

Cette étude de cas utilise tout le cours. Ces leçons sont les plus importantes :

• Les requêtes paramétrées et un manifeste d’extraction, dans Une extraction reproductible.
• Les fonctions de fenêtre et les intervalles de dates semi-ouverts, dans Le temps et le classement.
• Un profil avant toute modification, puis l’exclusion, la correction ou l’imputation avec une quarantaine, dans Profiler avant de modifier et Traiter les valeurs désordonnées.
• Le moment de la prédiction, le moment de la cible et les caractéristiques provenant uniquement du passé, dans Caractéristiques et moment de la cible.
• Une séparation temporelle, dans Choisir une séparation.
• Un contrat vérifié par validate.py, une fiche de jeu de données et une commande qui exécute tout, dans Les règles de validation, Suivre la provenance des données et Livrer un jeu de données réutilisable.
• Les tests exécutés par pytest, dans Des tests ciblés du cours Python pour l’ingénierie de l’IA en pratique.

Ce qu’il vous faut sur votre ordinateur

• Python 3.12 ou une version ultérieure, et VS Code avec l’extension Python, comme dans Installer et vérifier Python du cours Python pour l’ingénierie de l’IA en pratique.
• Environ 1 Go d’espace disque libre : les données et leurs copies occupent environ 530 Mo, et les paquets environ 300 Mo.
• Une connexion Internet pour la préparation (les paquets et un fichier de données de 17 Mo).

Testé sur macOS avec Python 3.14.6 (SQLite 3.50.4), pandas 3.0.6, scikit-learn 1.9.1 et pytest 9.1.1. Le test a suivi les étapes de cette page dans un nouvel environnement virtuel. Les commandes Windows ne sont pas testées.

La situation

Lucía Ortiz assure la liaison avec le 311 au Department of Transportation (DOT) de New York. Les résidents signalent les problèmes de rue au 311, le service téléphonique municipal : nid-de-poule, lampadaire éteint, feu de circulation brisé, trottoir fissuré. Chaque signalement est une demande de service. Le DOT reçoit la demande, une équipe ou une personne responsable de l’inspection s’en occupe, puis quelqu’un la ferme.

La plupart des demandes se ferment rapidement, mais certaines restent ouvertes pendant des semaines. Elles entraînent de nouveaux appels au 311 et des questions des membres du conseil municipal. L’équipe de Lucía utilise sa propre règle simple : une demande encore ouverte après 14 jours est en retard. Elle veut savoir, dès l’arrivée d’une demande, quelles nouvelles demandes seront probablement en retard. Son équipe pourra alors les examiner tôt et donner aux résidents un délai réaliste.

Lucía demande à Sam Okafor, un ingénieur de données, de préparer les données pour ce modèle. Hana Weiss, une scientifique des données, entraînera le modèle plus tard. Hana résume sa demande à Sam : « Donne-moi une table fiable. Une ligne par demande, seulement les faits connus à son arrivée, et la réponse : en retard ou non. »

Chaque erreur dans les données a un coût. Si la table contient un fait du futur sans le signaler, le modèle de Hana paraît excellent en test, mais échoue en utilisation réelle. Si la table supprime ou dédouble des demandes sans garder de trace, personne ne peut vérifier les nombres plus tard. Lucía, Sam et Hana sont des personnages inventés pour cette étude de cas. Les données sont réelles et publiques.

Ce que vous allez créer

Vous créez le projet de Sam, repair-requests : un pipeline de petits scripts. Il transforme l’exportation municipale en une table de modélisation avec une cible, late_14d, séparée en données d’entraînement, de validation et de test. Un contrat de 13 règles vérifie les fichiers livrés, et des tests vérifient les règles. Une commande exécute tout et consigne une somme de contrôle de chaque fichier de sortie. Une fiche de jeu de données explique d’où viennent les données et ce qu’elles ont subi.

Le pipeline produit aussi un deuxième livrable, service_requests.db : les demandes propres dans une base SQLite, pour l’équipe du tableau de bord municipal. L’étude de cas Créer une API de données sur les demandes de service municipal du cours Créer et déployer votre première API crée une API sur une base de données qui suit les mêmes règles.

Le diagramme montre le parcours complet. Vous le construisez de gauche à droite dans cette étude de cas.

Le pipeline repair-requests, avec le nombre de lignes après chaque étape. Chaque étape consigne ce qu'elle a fait.

Description textuelle du diagramme

Le pipeline compte six étapes après la base de données source.

1. extract.py sélectionne les demandes dans nyc311.db (315 424 lignes) et écrit un manifeste.
2. clean.py retire 392 soumissions répétées et déplace en quarantaine 1 459 demandes fermées avant leur création. Il reste 313 573 lignes propres.
3. build_db.py écrit les lignes propres dans service_requests.db pour les autres équipes.
4. build_features.py ajoute la cible et les caractéristiques. Il exclut 17 319 demandes sans 30 jours d’historique et 9 621 demandes dont les 14 jours n’étaient pas écoulés au moment de l’instantané. Il reste 286 633 lignes, dont 13,7 % sont en retard.
5. split.py les sépare selon le temps : entraînement 186 339, validation 53 156, test 47 138.
6. validate.py vérifie 13 règles. Le jeu de données est livré avec MANIFEST.json et DATASET.md seulement si toutes les règles sont respectées.

Préparer le projet

Le projet s’exécute sur votre ordinateur, dans VS Code, dans son propre environnement virtuel. C’est un dossier .venv qui contient les paquets de ce projet seulement. Si les environnements virtuels sont nouveaux pour vous, lisez d’abord Isoler les dépendances du cours Python pour l’ingénierie de l’IA en pratique.

Attention

Si vous avez déjà un dossier ~/projects/repair-requests, renommez-le ou supprimez-le d’abord. Les étapes suivantes en créent un nouveau.

1. Téléchargez le projet de départ avec le bouton Download the starter project (.zip) (télécharger le projet de départ) en haut de cette page. Votre navigateur enregistre starter.zip, habituellement dans votre dossier de téléchargements.

2. Décompressez-le dans votre dossier de projets. Le fichier zip contient un dossier, repair-requests. Safari peut le décompresser pour vous : le dossier de téléchargements contient alors un dossier repair-requests, sans fichier zip. Déplacez plutôt ce dossier dans ~/projects.

   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. Dans VS Code, sélectionnez File › Open Folder (fichier › ouvrir un dossier) et ouvrez ~/projects/repair-requests. Si VS Code demande si vous faites confiance aux auteurs des fichiers, sélectionnez Yes (oui).

4. Ouvrez un terminal dans VS Code avec Terminal › New Terminal (terminal › nouveau terminal). Il s’ouvre dans le dossier du projet.

5. Créez l’environnement virtuel et activez-le. L’invite commence alors par (.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

   Si Windows indique que l’exécution des scripts est désactivée, exécutez Set-ExecutionPolicy -Scope CurrentUser RemoteSigned une fois, puis activez l’environnement à nouveau.

6. Indiquez à VS Code d’utiliser le même Python. Appuyez sur Ctrl+Shift+P (Cmd+Shift+P sur macOS), saisissez Python: Select Interpreter (Python : sélectionner l’interpréteur), puis sélectionnez l’entrée qui affiche .venv.

7. Installez les trois paquets aux versions indiquées dans requirements.txt. À partir d’ici, la commande est python sur tous les systèmes, car l’environnement est actif.

   Terminal

   python -m pip install -r requirements.txt

   Vers la fin, une ligne commence par Successfully installed (installation réussie). Elle liste pandas 3.0.6, pytest 9.1.1 et scikit-learn 1.9.1 avec les paquets nécessaires. Un message [notice] (avis) sur une nouvelle version de pip peut suivre; vous pouvez l’ignorer.

8. Téléchargez les données et créez la base de données source :

   Terminal

   python get_data.py

   Sortie

   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.

   Si le téléchargement échoue avec CERTIFICATE_VERIFY_FAILED (échec de vérification du certificat) sur macOS, le script essaie plutôt curl. Si cela échoue aussi, ouvrez Applications › Python 3.x et exécutez Install Certificates.command (installer les certificats), puis relancez le script. Si vous avez téléchargé vous-même le fichier de données, exécutez python get_data.py --from suivi du chemin de ce fichier.

9. Exécutez la première requête, fournie dans le projet de départ :

   Terminal

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

   Sortie

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

Ce que cela nous apprend. La préparation fonctionne : le fichier de données est arrivé complet (sa somme de contrôle correspond), et nyc311.db contient 315 424 lignes. Chaque ligne possède son propre unique_key, donc la clé est unique. Les demandes ont été créées du 1er janvier 2024 au 30 juin 2025, soit 18 mois. Les heures sont du texte au format 2024-01-01T00:30:02.000, avec un T entre la date et l’heure.

Le projet de départ contient ces fichiers. Ceux marqués « à écrire » sont actuellement de courts fichiers provisoires; chaque section ci-dessous en remplace un.

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

Les données

Les données proviennent de NYC Open Data, le portail municipal de données ouvertes : le jeu de données 311 Service Requests from 2020 to Present. Il contient toutes les demandes au 311 de la ville depuis 2020, pour tous les organismes. Cette étude de cas utilise les demandes au DOT créées du 1er janvier 2024 au 30 juin 2025.

Source, version et conditions

« 311 Service Requests from 2020 to Present », NYC Open Data, jeu de données erm2-nwe9, publié par la Ville de New York (311). Version : récupérée le 7 octobre 2026 par l’API municipale avec agency='DOT' AND created_date >= '2024-01-01T00:00:00' AND created_date < '2025-07-01T00:00:00'. Modifications : Nextia Learning a conservé 20 des 44 colonnes (sans adresses) et enregistré le résultat dans un fichier CSV compressé. Les valeurs sont inchangées. Conditions : NYC Open Data n’impose aucune restriction d’utilisation (NYC Administrative Code § 23-502(d)). La Ville demande d’indiquer la source, la version et toute modification, comme le fait cette note.

Pourquoi un fichier figé? La Ville met ce jeu de données à jour chaque jour : des demandes ferment, des statuts changent, des textes sont réécrits. Si vous récupériez les données aujourd’hui, vos nombres différeraient de ceux de cette page. Vous ne pourriez alors pas distinguer une erreur d’un changement dans la source. Le cours conserve donc un fichier figé, nyc311_dot_2024-01_2025-06.csv.gz, avec une somme de contrôle SHA-256, que get_data.py vérifie. Pour récupérer plutôt la version actuelle de la même sélection, exécutez python get_data.py --fresh (cela prend quelques minutes). Une nouvelle récupération a donné les mêmes 315 424 lignes avec les mêmes valeurs; une récupération ultérieure ne le fera pas.

Le dictionnaire de données

Voici les colonnes utilisées dans l’étude de cas. La colonne Saisie est la plus importante ici : elle indique quand la valeur est renseignée. Les descriptions proviennent du portail; les heures sont à l’heure locale de New York.

Colonne	Signification	Saisie
unique_key	L’identifiant de la demande	à la création
created_date	Le moment de création de la demande	à la création
complaint_type, descriptor	Le problème et son détail, par exemple Street Condition (état de la rue) et Pothole (nid-de-poule)	à la création
location_type, borough, community_board, council_district, incident_zip, latitude, longitude	L’emplacement du problème	à la création
open_data_channel_type	Le canal d’arrivée au 311 : ONLINE (en ligne), PHONE (téléphone), MOBILE (mobile), OTHER (autre) ou UNKNOWN (inconnu)	à la création
status	Closed (fermée), Pending (en attente), Assigned (attribuée) et autres : le statut le jour de la récupération	plus tard
closed_date	Le moment où l’organisme a fermé la demande	plus tard
resolution_description	« La dernière action de l’organisme »; elle « peut décrire les prochaines étapes ou des étapes futures »	plus tard
resolution_action_updated_date	Le moment de la dernière mise à jour de la demande par l’organisme	plus tard
due_date	Vide dans chaque ligne de cette sélection	—

Premières vérifications

Tout profil commence par les valeurs manquantes. COUNT(column) compte seulement les valeurs qui ne sont pas NULL. Donc, COUNT(*) - COUNT(column) donne le nombre de valeurs manquantes. Enregistrez cette requête sous queries/02-missing-values.sql dans VS Code, puis exécutez-la :

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

Sortie

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

Ce que cela nous apprend. 6 083 demandes n’ont pas d’heure de fermeture : elles étaient encore ouvertes le jour de la récupération. 46 841 demandes (14,9 %) n’ont pas de coordonnées. Toute caractéristique concernant « le même endroit » sera donc manquante pour elles. due_date est vide dans chaque ligne, donc elle n’apporte aucune information, et l’extraction l’exclut. Les autres valeurs manquantes se trouvent dans des colonnes dont la cible n’a pas besoin.

Ensuite, Sam examine l’objet des demandes. Enregistrez et exécutez 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;

Sortie

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

Ce que cela nous apprend. Quatre types représentent 83,5 % des demandes : état des rues (surtout des nids-de-poule), feux de circulation, lampadaires et trottoirs. Ce sont des travaux très différents, réalisés par des équipes différentes. Réparer un nid-de-poule et réparer un lampadaire ne prennent pas le même temps. Le type de problème sera donc important pour la cible.

Définir le problème

Avant tout nettoyage, Sam note ce que fera le modèle de Hana, en quatre décisions. Chacune détermine ce qui entre dans la table.

L’unité. Une ligne représente une demande de service.

Le moment de la prédiction. Le modèle répond à la création d’une demande. Tout ce qu’il utilise doit exister à ce moment. Cela comprend le type de problème, le lieu, le canal, l’heure et ce qui s’est passé avant la demande.

La cible. Sam la rédige avec Lucía, mot à mot :

late_14d = 1 si la demande n’a pas été fermée dans les 14 jours (14 × 24 heures) après created_date. Une demande sans heure de fermeture compte comme en retard.

L’instantané. Les données vont jusqu’au 30 juin 2025. Sam construit la table comme l’équipe l’aurait fait le 1 juillet 2025 à 0 h, juste après la dernière demande. Ce moment est l’instantané : le moment de l’exportation, selon les mots du cours. Un modèle construit ce jour-là pourrait être testé sur les mois suivants.

La vie d’une demande

L’exportation contient toute la vie de chaque demande, pas seulement son début. Regardez la demande 63774786, un nid-de-poule dans Queens :

Moment	Ce qui se passe	Colonnes remplies	Connu à la création?
2025-01-15 09:11:14	Quelqu’un signale un nid-de-poule.	created_date, complaint_type = Street Condition (état de la rue), descriptor = Pothole (nid-de-poule), borough = QUEENS, coordonnées, canal UNKNOWN (inconnu)	Oui
2025-01-16 14:43:00	Un inspecteur transmet la demande à une autre unité.	resolution_description = « The Department of Transportation inspected this complaint and referred it to the Arterial Division for further action. » (Le service des transports a inspecté cette plainte et l’a transmise à la division des artères pour la suite.), resolution_action_updated_date	Non
2026-10-07 (l’extraction)	Elle est encore ouverte, 21 mois plus tard.	status = Pending (en attente), closed_date vide	Non

À 9 h 11 min 14 s le 15 janvier, un modèle voit seulement la première ligne. L’exportation, extraite 21 mois plus tard, sait que la demande a été transmise et qu’elle est encore en attente. Ces deux faits indiquent directement la réponse : en retard. C’est une fuite de données : la table contient de l’information que le modèle n’aurait pas au moment de la prédiction. status, closed_date, resolution_description et resolution_action_updated_date sont écrits après la création et ne servent donc jamais de variables.

Une cible a besoin de 14 jours pour devenir définitive

Au moment de l’instantané, le 1 juillet 2025, regardez une demande du 25 juin. Seulement six jours se sont écoulés. Si elle est encore ouverte, sa cible n’est pas 1 : elle est inconnue. La demande pourrait encore être fermée avant la fin des 14 jours. Une cible est définitive seulement pour les demandes créées au moins 14 jours avant l’instantané :

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

Cette règle s’appelle la maturité des étiquettes : la cible doit avoir eu tout le temps prévu pour se réaliser. Le fichier figé permet de mesurer ce qui arrive sans cette règle, car il a été extrait 15 mois plus tard. On compte 9 621 demandes propres créées après le 17 juin 2025 à 0 h. Au moment de l’instantané, 2 978 étaient encore ouvertes. Si Sam les avait marquées comme en retard, le taux de retard des demandes récentes aurait été de 31,0 %. Finalement, 1 779 des 2 978 demandes (59,7 %) ont été fermées dans leurs 14 jours. Le vrai taux de retard de ces mêmes demandes était de 12,5 %. L’étiquette prématurée aurait plus que doublé ce taux. Les 9 621 demandes restent donc hors de la table de modélisation, et l’étape de construction les compte.

Sam vérifie aussi un détail. Le fichier connaît des heures de fermeture après l’instantané. Est-ce une fuite dans la cible? Pour une demande créée avant le 17 juin, non. Ses 14 jours se terminent avant l’instantané, donc une fermeture après l’instantané est en retard dans tous les cas. La cible est la même qu’au 1 juillet 2025.

Profiler l’exportation

Un profil révèle les problèmes avant toute modification. Le cours les appelle des erreurs discrètes : aucune ne déclenche d’erreur. Écrivez chaque requête dans queries/, exécutez-la avec python q.py et lisez le résultat avant de prendre une décision.

Des heures de fermeture impossibles

julianday() convertit un moment en nombre de jours. La différence entre deux moments donne donc une durée en jours. Enregistrez et exécutez 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;

Sortie

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

1 row

Ce que cela nous apprend. On compte 1 482 demandes fermées avant leur création. C’est impossible, et rien ne permet de connaître la bonne heure de fermeture. Ces lignes ne peuvent donc pas avoir une cible correcte. On compte 11 162 demandes fermées dans la même seconde que leur création. Environ la moitié indiquent dans leur texte de résolution qu’elles sont le double d’une demande antérieure. Elles ont été fermées immédiatement. C’est un résultat réel (à temps), pas une erreur. Enfin, 1 445 demandes sont restées ouvertes plus d’un an : elles sont réelles et en retard dans tous les cas.

Comparer le statut et l’heure de fermeture

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;

Sortie

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

Ce que cela nous apprend. Presque toutes les 1 482 heures de fermeture impossibles concernent des demandes Pending (1 464). Un décompte par type montre que 1 458 concernent des lampadaires. L’heure de fermeture de ces lignes signifie donc probablement autre chose; les données ne disent pas quoi. De plus, 508 demandes Assigned (assignées) ont une heure de fermeture, et une demande Closed (fermée) n’en a pas. Le statut correspond au jour de l’extraction et peut changer après une fermeture. Sam décide que seul closed_date définit la cible. Les lignes impossibles vont en quarantaine : un fichier distinct, avec une raison, destiné à quelqu’un qui peut les vérifier.

La même demande, envoyée deux fois

Le cours repérait les doublons en cherchant des lignes identiques dans toutes les colonnes. Ici, chaque ligne a sa propre clé unique_key. Sam cherche des lignes identiques dans toutes les autres colonnes, à la seconde près :

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;

Sortie

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

1 row

Ce que cela nous apprend. On trouve 273 groupes de lignes identiques dans les 19 colonnes autres que la clé. Ensemble, ils contiennent 392 lignes supplémentaires. Un groupe compte neuf demandes pour un nid-de-poule aux mêmes coordonnées, dans la même seconde. Neuf personnes n’ont pas signalé le même nid-de-poule dans la même seconde; un signalement a été enregistré plusieurs fois. Chaque copie supplémentaire compterait comme une demande distincte, dans le taux cible et dans les variables d’historique. Sam garde la première ligne de chaque groupe (la plus petite clé) et supprime les autres, en les comptant.

Des valeurs qui signifient « non enregistré »

Les valeurs manquantes ne sont pas toutes NULL. Certaines colonnes contiennent plutôt un mot :

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]';

Sortie

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

Ce que cela nous apprend. UNKNOWN est le canal le plus fréquent : 182 215 demandes, soit 57,8 % du total. Un WHERE channel IS NULL n’en trouverait aucune. Un décompte des canaux présenterait UNKNOWN comme un vrai canal, au même titre que PHONE (téléphone). L’étape de nettoyage transforme chaque valeur de remplacement en NULL, pour donner une seule forme à « non enregistré ». Plus tard, la table de modélisation transforme NULL en catégorie unknown (inconnu). Pour le canal, « non enregistré » a son propre sens, comme le montre l’exploration.

Des heures communes à trop de demandes

Les demandes arrivent toute la journée, donc aucune seconde précise ne devrait être très fréquente. Sam compte les heures les plus fréquentes, à la seconde près :

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;

Sortie

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

Ce que cela nous apprend. On compte 1 908 demandes créées exactement à 7 h et 751 exactement à 22 h 10. L’heure suivante la plus fréquente en compte 278. Ce sont des heures de saisie par lot. Les demandes ont été saisies en groupe, avec une seule heure pour toutes, plutôt qu’au moment du signalement par un résident. La plupart des lignes de 7 h concernent des nids-de-poule avec le canal UNKNOWN. Une variable « heure de création » les interpréterait comme des demandes du matin. Sam garde l’heure, mais ajoute un indicateur, batch_time, pour que le modèle puisse distinguer les deux cas.

Du texte lu avec le mauvais encodage

Les textes de résolution proviennent d’une courte liste de messages standard. Deux d’entre eux commencent de la même façon. Sam regroupe les lignes par texte complet et affiche une partie de chacun :

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;

Sortie

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

2 rows

Ce que cela nous apprend. Le premier texte compte six caractères de plus et affiche â là où devrait se trouver une apostrophe. C’est du texte mal encodé. La ville a enregistré le texte en UTF-8, puis une étape l’a lu en Latin-1. En UTF-8, l’apostrophe typographique ’ occupe trois octets. Lus en Latin-1, ces trois octets deviennent trois caractères : â et deux caractères de contrôle. Votre terminal peut les masquer ou les afficher comme des carrés ou des points d’interrogation. Le texte contient trois apostrophes, donc il gagne six caractères. La nouvelle extraction montrait le même texte : l’erreur vient donc de la source, pas du téléchargement. Cette colonne est une fuite, donc le modèle ne la voit jamais, mais des personnes la lisent. L’étape de nettoyage la corrige et compte les corrections.

Les décisions tirées du profil

Sam note les décisions avant d’écrire le code de nettoyage, pour que chacune ait une raison et un décompte. Il supprime seulement les copies erronées ou les lignes impossibles à corriger. Tout le reste est conservé ou corrigé, et consigné.

Problème	Lignes	Décision
Envois répétés	392 lignes supplémentaires	Supprimer; garder la plus petite clé de chaque groupe
Fermeture avant la création	1 482	Mettre en quarantaine avec la raison closed_before_created
Fermeture dans la même seconde	11 162	Garder : un résultat réel (à temps)
Fermeture après plus d’un an; encore ouverte	1 445 et 6 083	Garder : en retard
Statut incompatible avec l’heure de fermeture	518	Garder; closed_date définit la cible
Unspecified (non précisé), UNKNOWN, code postal NA (non disponible)	voir plus haut	Transformer en NULL; unknown dans la table de modélisation
Heures de saisie par lot à 7 h et 22 h 10	2 659	Garder; ajouter l’indicateur batch_time
Texte de résolution mal encodé	96 528 (96 516 pour un texte, 12 pour deux autres)	Corriger; compter les corrections
Aucune coordonnée	46 841	Garder; la variable d’historique manque pour ces lignes

Explorer ce qu’exige la décision

L’exploration répond ici à trois questions pour le modèle de Hana. À quel point le taux de retard varie-t-il? Change-t-il avec le temps? Les valeurs inhabituelles portent-elles de l’information? Chaque requête attribue d’abord la cible aux demandes dans une expression de table commune (CTE), avec les mêmes règles que le pipeline. Elle garde seulement les demandes créées avant le 17 juin 2025, sans heure de fermeture impossible.

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;

Sortie

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

Ce que cela nous apprend. Le taux de retard varie presque du simple au décuple selon le type de problème. Il est de 4,3 % pour les feux de circulation et de 38,4 % pour les bordures de trottoir. Un modèle qui connaît seulement le type classe déjà bien les demandes; toute autre variable doit apporter un gain. Pour Lucía, c’est aussi un premier fait utile. Plus d’une demande sur trois concernant les bordures est en retard, contre moins d’une sur vingt pour les feux de circulation.

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;

Sortie

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

Ce que cela nous apprend. Le taux de retard suit les saisons dans les deux années. Il va de 14 % à 16 % d’avril à août, et de 10 % à 13 % de décembre à mars. Juin 2025 compte moins de demandes, car seuls les 16 premiers jours ont une cible définitive. Cela compte pour la séparation. On entraîne un modèle sur le passé pour l’utiliser dans le futur, donc le test doit aussi se situer dans le futur. Les mois présents dans chaque partie changent le taux que voit le modèle.

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;

Sortie

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

Ce que cela nous apprend. « Non enregistré » n’est pas aléatoire ici. Les demandes avec le canal UNKNOWN sont en retard dans 10,8 % des cas, contre 25,9 % pour l’application mobile. Remplacer le canal inconnu par la vraie valeur la plus fréquente, ou supprimer ces lignes, effacerait une différence réelle. De plus, les nids-de-poule créés exactement à 7 h sont presque jamais en retard (0,4 %, contre 6,6 %). L’indicateur d’heure de saisie par lot mérite d’être gardé.

Extraire la sélection

L’étape d’extraction sélectionne les lignes avec une requête paramétrée, comme dans Une extraction reproductible. Elle donne aussi des noms simples aux colonnes et change le format des dates et heures. Elle passe de 2024-01-01T00:30:02.000 à 2024-01-01 00:30:02, que pandas lit avec un seul format. Écrivez 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;

Le script scripts/extract.py est prêt dans les fichiers de départ. Avant d’exécuter votre requête, il vérifie la source : les colonnes nécessaires et le format de chaque date et heure. Un autre format, par exemple sans le T, ferait comparer le mauvais texte à created_date >= :created_from. Le filtre serait alors incorrect, sans déclencher d’erreur. Exécutez-le :

Terminal

python scripts/extract.py

Sortie

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

Ce que cela nous apprend. Tout le fichier fait partie de la sélection, car le fichier figé a été extrait avec les mêmes dates. Le manifeste, data/extract/manifest.json, consigne la source provenant de la table source écrite par get_data.py. Il consigne aussi les paramètres, la somme de contrôle SHA-256 de la requête et les décomptes. Si quelqu’un modifie la requête, sa somme de contrôle change, et le manifeste le montre.

Nettoyer les demandes

Remplacez le contenu provisoire dans scripts/clean.py par les cinq parties ci-dessous, dans l’ordre. La règle de Traiter les valeurs désordonnées s’applique : chaque ligne d’entrée finit à un seul endroit, et chaque changement est compté.

La partie 1 charge l’extraction avec des types explicites. keep_default_na=False avec na_values=[""] rend seulement les champs vides manquants. Un texte comme NA reste donc du texte jusqu’à ce qu’une règle décide de son traitement.

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

La partie 2 supprime les envois répétés. duplicated(subset=…, keep="first") marque chaque ligne qui répète une ligne précédente dans toutes les colonnes sauf la clé. Comme les lignes sont d’abord triées par clé, la plus petite clé reste.

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())

La partie 3 déplace en quarantaine les demandes fermées avant leur création, avec la raison. Une comparaison avec un closed_at manquant donne faux, donc les demandes ouvertes restent.

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

La partie 4 transforme les valeurs de remplacement en valeurs manquantes et corrige le texte. La correction travaille à rebours. Elle convertit le texte en octets selon l’encodage utilisé par l’étape erronée (Latin-1), puis lit ces octets en UTF-8. Pour un texte déjà correct, une des deux étapes échoue, et le texte reste tel quel. La nouvelle colonne resolution_repaired consigne les lignes modifiées.

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

La partie 5 exécute les étapes, vérifie que les décomptes concordent et écrit la table propre, la quarantaine et les décomptes.

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()

Exécutez le script :

Terminal

python scripts/clean.py

Sortie

{
  "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
}

Ce que cela nous apprend. Il y avait 315 424 lignes en entrée. On a supprimé 392 doublons, placé 1 459 lignes en quarantaine et obtenu 313 573 lignes propres. La somme des trois nombres correspond à l’entrée, et le script le vérifie avec assert. La quarantaine compte 1 459 lignes, pas 1 482, car 23 lignes avec une fermeture impossible étaient aussi des doublons. La première règle les a supprimées. L’ordre des règles change les décomptes, ce qui est une raison de les noter. Les décomptes des valeurs de remplacement sont un peu plus bas que dans le profil pour la même raison.

Une base de données pour les autres équipes

L’équipe du tableau de bord de Lucía ne veut pas de table de modélisation. Elle veut chaque demande propre, avec son statut et son heure de fermeture. Le format doit permettre à un tableau de bord ou à une API de faire des requêtes rapides. C’est un produit différent de la table de Hana, avec d’autres règles. Ici, status et closed_at sont des faits utiles, pas des fuites, car personne ne prédit quoi que ce soit.

scripts/build_db.py est prêt dans les fichiers de départ. Il écrit data/clean/service_requests.db avec une table, service_requests, et une table meta contenant la source et les règles. Lisez son schéma au début du fichier. Trois choix méritent chacun une phrase :

• Aucun texte libre ni adresse. La table contient le lieu (arrondissement, conseil communautaire, code postal, coordonnées), mais pas le texte de résolution. Le fichier figé ne contient aucune adresse municipale.
• NULL signifie « non enregistré ». borough, channel, community_board et incident_zip valent NULL là où la source contenait une valeur de remplacement. Les contraintes CHECK autorisent seulement les vraies valeurs. Une requête pour channel = 'UNKNOWN' ne renvoie rien, donc personne ne compte « inconnu » comme un canal par erreur.
• days_to_close est stocké. Il vaut closed_at − created_at en jours. Le tableau de bord ne le recalcule donc pas à chaque requête. Deux contraintes CHECK assurent sa cohérence avec 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"

Sortie

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

Ce que cela nous apprend. La base de données contient chaque demande propre, soit 313 573 lignes. Dans les deux années, environ 86 % à 87 % ont été fermées dans les 14 jours. Cela concorde avec le taux de retard de 13,7 % de la table de modélisation. Les deux produits concordent, ce qui permet de vérifier à peu de frais qu’aucun n’a perdu de lignes. La base de données est volumineuse (103 Mo), car elle contient quatre index. Ils accélèrent les filtres par date, arrondissement, type ou statut.

Construire la cible et les caractéristiques

Passons à la table de modélisation : une ligne par demande, la cible et les caractéristiques connues à la création. La caractéristique la plus délicate est l’historique du lieu.

Un historique limité au passé : un cadre de fenêtre

Un nid-de-poule signalé deux fois la semaine dernière n’est probablement pas nouveau. Une demande antérieure pour le même problème au même endroit est un fait connu à la création. Elle peut changer le délai de fermeture de la nouvelle demande. Sam appelle cette caractéristique prior_same_place_30d. « Même endroit » signifie le même type de problème aux mêmes coordonnées arrondies à 4 décimales (environ 10 mètres).

Le compte doit inclure seulement les demandes créées avant celle-ci. Regardez quatre demandes pour un nid-de-poule au même endroit :

Demande	Création	Demandes comptées	prior_same_place_30d
A	1 janvier à 10 h 00 min 00 s	aucune	0
B	20 janvier à 9 h 00 min 00 s	A (environ 19 jours avant)	1
C	15 février à 8 h 00 min 00 s	B (environ 26 jours avant); A précède C d’environ 45 jours, hors des 30 jours	1
D	15 février à 8 h 00 min 00 s	B; pas C, qui a la même seconde	1

C et D ont été créées à la même seconde, comme dans un lot. Aucune n’existait avant l’autre, donc aucune ne compte l’autre. Une fonction de fenêtre de la leçon Le temps et le classement calcule cela avec un cadre. Ce sont les lignes de la partition que la fonction examine par rapport à la ligne courante. RANGE BETWEEN 2592000 PRECEDING AND 1 PRECEDING signifie « les lignes datant de 30 jours (2 592 000 secondes) à 1 seconde avant cette ligne ». unixepoch() convertit une heure en secondes, pour que le cadre puisse compter en secondes. Écrivez 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;

Un cadre se terminant à CURRENT ROW (ligne courante) compterait la demande elle-même et les autres demandes de son lot. Ces demandes ne sont pas dans le passé, donc la caractéristique introduirait une petite fuite. unixepoch() nécessite SQLite 3.38 ou une version ultérieure. Python 3.12 et les versions ultérieures de python.org incluent une version plus récente de SQLite. Pour vérifier la vôtre, exécutez python -c "import sqlite3; print(sqlite3.sqlite_version)".

Le script

Remplacez le texte provisoire dans scripts/build_features.py par les quatre parties ci-dessous. La partie 1 conserve les décisions sous forme de constantes. Elles définissent l’instantané, les 14 jours, le premier jour avec un historique complet, les heures des lots et les colonnes interdites comme caractéristiques.

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"]

La partie 2 définit la cible, exactement comme Sam l’a écrite avec Lucía. Une demande respecte le délai seulement si elle a une heure de fermeture au plus 14 jours après sa création.

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

La partie 3 crée les caractéristiques. Les catégories reçoivent unknown (inconnu) pour une valeur manquante, comme le suggérait l’exploration. La caractéristique d’historique exécute votre fichier SQL sur une copie SQLite en mémoire de la table nettoyée. Elle associe ensuite les comptes aux lignes. Les demandes sans coordonnées n’ont pas de compte : la valeur est manquante, et non 0. Un 0 affirmerait que personne n’a rien signalé à cet endroit.

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")

La partie 4 assemble le tout. Le script calcule les caractéristiques sur toutes les lignes nettoyées, donc janvier 2024 sert d’historique pour février. Ensuite, deux filtres retirent les lignes qui ne permettent pas une comparaison équitable dans la table. Les demandes antérieures au 1 février 2024 ont moins de 30 jours d’historique dans le fichier. Les demandes après le 17 juin 2025 n’ont pas de cible finale.

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

Sortie

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

Ce que cela nous apprend. Le script a reçu 313 573 lignes nettoyées. Parmi elles, 9 621 sont trop récentes pour une cible finale et 17 319 précèdent l’historique complet. Il reste donc 286 633 lignes. Parmi elles, 39 145 sont en retard : 13,7 %, près du taux observé dans l’exploration. Les deux groupes retirés sont comptés, donc le lecteur peut additionner les nombres.

Vérifier une valeur à la main

La demande 63774786, le nid-de-poule de Queens, a prior_same_place_30d = 2 dans data/model/requests_model.csv. Vérifiez cette valeur avec une requête qui n’utilise pas la fonction de fenêtre. Elle s’exécute sur nyc311.db, la table brute, donc les heures contiennent encore le 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;

Sortie

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

Ce que cela nous apprend. Deux demandes antérieures concernent un nid-de-poule au même endroit, toutes deux le 2 janvier. La valeur 2 est donc correcte. La deuxième a été fermée à la seconde même de sa création : elle a été marquée comme doublon de la première. La première est encore en attente, comme 63774786. Le même nid-de-poule a été signalé trois fois en deux semaines. La caractéristique d’historique le représente sans aucun fait venant du futur. Le script de calculs du projet terminé a aussi vérifié 3 000 lignes aléatoires de cette façon, en Python seul : aucune différence.

Séparer selon le temps

L’exploration a montré un taux de retard saisonnier, et le modèle de Hana servira pour de futures demandes. La séparation se fait donc selon le temps, comme dans Choisir une séparation. Entraînez sur 2024 et validez sur le premier trimestre de 2025. La période d’avril à la mi-juin 2025 forme les données de test. Hana les utilise une seule fois, à la fin de son travail. Remplacez le texte provisoire dans 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

Sortie

{
  "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"
    }
  }
}

Ce que cela nous apprend. Les trois parties se suivent dans le temps, sans chevauchement. Leurs taux de retard diffèrent : 13,9 % pour l’entraînement (onze mois), 11,1 % pour la validation (hiver) et 15,7 % pour le test (printemps). C’est l’effet saisonnier observé dans l’exploration, pas une erreur. Cela indique deux choses à Hana avant tout entraînement. Un modèle ajusté sur les données de validation hivernales verra plus de demandes en retard que prévu dans le test. Et une seule période de validation ne suffit pas pour juger un modèle : elle doit le vérifier sur plusieurs saisons.

Pourquoi pas une séparation aléatoire?

Une séparation aléatoire placerait des demandes de mai 2025 dans l’entraînement et des demandes de janvier 2025 dans le test. Le modèle apprendrait du futur de son propre test. Les saisons se mélangeraient, donc le test ressemblerait à tous les autres mois. Le score serait plus élevé que ce que le modèle mérite.

Écrire le contrat et valider

La table est prête, mais Hana ne peut pas voir comment elle a été produite. Un contrat est une promesse écrite sur les fichiers livrés : leurs colonnes, types, valeurs permises et plages de valeurs. Comme dans Les règles de validation, les règles sont des données, et un script les vérifie. Ici, le contrat a son propre fichier, scripts/contract.py. Ainsi, modifier une règle ne demande jamais de modifier le code de vérification. scripts/validate.py est prêt dans les fichiers de départ; il lit le contrat et contient une fonction par règle.

D’abord, exécutez les tests fournis avec les fichiers de départ. Ils testent les règles sur une petite table de 12 lignes. pytest trouve les fonctions de test dans tests/ et les exécute chacune. C’est la méthode de Des tests ciblés dans le cours Python pour l’ingénierie de l’IA en pratique.

Terminal

python -m pytest

Les tests ne s’exécutent pas. pytest signale une erreur pendant leur chargement et, vers la fin, affiche :

Sortie (fin)

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 !!!!!!!!!!!!!!!!!!!!

C’est normal : validate.py lit ses règles dans le contrat, qui contient encore du texte provisoire. Remplacez le texte provisoire dans 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)

Deux règles sont propres à ces données. NEVER liste les cinq colonnes connues seulement après la création; la vérification échoue si la table en contient une. MISSING_EXACTLY_WHEN précise que prior_same_place_30d manque exactement dans les lignes sans coordonnées. Un compte manquant dans une ligne avec coordonnées indiquerait une défaillance du code des caractéristiques. Exécutez les tests de nouveau :

Terminal

python -m pytest

Sortie

============================= 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 ==============================

Ce que cela nous apprend. Les 14 tests réussissent; la deuxième ligne et le dossier reflètent votre propre système. Le premier test vérifie que la petite table respecte le contrat. Chacun des 13 autres enfreint une règle et vérifie que la bonne règle échoue avec le bon message. Par exemple : une colonne avec fuite, une clé répétée, une demande du 20 juin 2025 ou un compte sans lieu. Une règle qui n’échoue jamais n’est pas une vérification, et ces tests prouvent que chacune peut échouer. Vérifiez maintenant les vrais fichiers :

Terminal

python scripts/validate.py

Sortie

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.

Ce que cela nous apprend. Les fichiers livrés respectent les 13 règles. La règle du taux de retard (5 % à 30 %) est volontairement large. Elle ne vérifie pas que le taux est « correct ». Elle détecte une étape défectueuse, comme une cible qui vaut 1 pour chaque ligne.

Erreur courante

La colonne qui améliore le score

Symptôme : un collègue entraîne rapidement un modèle sur la table et se montre déçu. Il ajoute ensuite status par une jointure avec la table nettoyée, « parce que c’est déjà dans les données ». Le score bondit. Il enregistre la table avec la nouvelle colonne et demande à Sam de la livrer.

Cause : status est l’état au jour de l’extraction, 15 mois ou plus après la création. Pending (en attente) signifie presque toujours « en retard ». Le modèle apprend à lire la réponse, pas à la prédire.

Diagnostic : validate.py bloque la livraison. Avec status ajouté à requests_model.csv, sa sortie commence ainsi :

Sortie

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)

et se termine par 1 of 8 rules failed. Fix the step that made the data, then run it again. (Une des huit règles a échoué. Corrigez l’étape qui a produit les données, puis relancez-la.) (Les règles de séparation ne s’exécutent pas tant que la table échoue.)

Correction : retirez la colonne de l’étape qui l’a ajoutée et exécutez le pipeline de nouveau. L’équipe consigne ces décisions dans la liste NEVER du contrat. Un nouveau collègue découvre ainsi la règle avant de rencontrer le problème.

Conséquence dans le travail en IA : une colonne avec fuite donne au modèle un score qu’il n’atteindra jamais en utilisation réelle. La prochaine section mesure l’ampleur de ce faux gain sur ces données.

Tout exécuter avec une seule commande

Chaque script fonctionne seul. Mais un jeu de données est réutilisable seulement si une commande le recrée à partir de la source. C’est le principe de Livrer un jeu de données réutilisable. run_pipeline.py est prêt dans les fichiers de départ. Il supprime les anciennes sorties, exécute les six étapes dans l’ordre et s’arrête au premier échec. À la fin, il écrit data/model/MANIFEST.json avec une somme de contrôle SHA-256 de chaque sortie. Exécutez-le deux fois :

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

Ce que cela nous apprend. Les deux exécutions se terminent avec la même empreinte, une somme de contrôle regroupant les 12 fichiers de sortie. Même source, même code, mêmes octets : le pipeline est reproductible, y compris la base de données SQLite. Sur votre ordinateur, l’empreinte reste identique avec les mêmes versions de Python, SQLite et des paquets sur macOS ou Linux. SQLite écrit sa version dans service_requests.db. Une autre version de SQLite donne donc une autre empreinte, tandis que les fichiers CSV restent identiques. Sur Windows, pandas écrit d’autres fins de ligne; comparez donc deux exécutions sur le même ordinateur. Une exécution prend de 15 à 30 secondes.

Preuve : l’effet d’une fuite sur un score

La modélisation est le sujet de Apprentissage automatique : du problème à un modèle fiable. Mais une mesure a sa place ici, car elle montre ce que le contrat protège. scripts/leak_check.py est prêt dans les fichiers de départ. Il entraîne le même modèle simple quatre fois sur les données d’entraînement et évalue les données de validation chaque fois. Le modèle est une régression logistique sur des catégories à encodage unitaire, dans un pipeline scikit-learn. Le score est l’aire sous la courbe ROC (ROC AUC) : 0,5 correspond au hasard. Un score de 1,0 classe parfaitement les demandes en retard au-dessus de celles qui respectent le délai.

Terminal

python scripts/leak_check.py

Sortie

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

Ce que cela nous apprend. Le type de problème seul donne 0,812, le signal fort observé dans l’exploration. Toutes les autres caractéristiques sans fuite ajoutent ensemble un petit gain, pour atteindre 0,816. Une seule colonne avec fuite ajoute environ 0,05 : plus de dix fois le gain de toutes les autres caractéristiques de Sam. C’est exactement pourquoi les fuites sont tentantes : elles améliorent le nombre, et personne ne reçoit d’erreur. En utilisation réelle, le jour où une demande arrive, status et le texte de résolution n’existent pas encore. Le score de 0,863 est donc trompeur. Présentez ces nombres comme une preuve de l’utilité du contrat, pas comme un résultat de modèle. Le travail de Hana dans le cours Apprentissage automatique : du problème à un modèle fiable part du score sans fuite de 0,816. Il part aussi de la référence de base de 0,812 que tout modèle doit dépasser.

Documenter le jeu de données

La dernière partie de la livraison est la fiche du jeu de données, DATASET.md, comme dans Suivre la provenance des données. Les fichiers de départ contiennent un modèle avec les titres des sections. Remplissez chaque section à partir des fichiers écrits par le pipeline; vous n’avez aucun nombre à retenir.

Section	Origine des faits
Description	data/model/split_manifest.json : les fichiers, leurs lignes et leurs taux de retard
Provenance	La note sur la source dans cette page; data/extract/manifest.json; la table source dans nyc311.db
Ordre des transformations	reports/cleaning.json, data/model/build_summary.json, split_manifest.json, reports/validation.json
Colonnes	scripts/contract.py, et la liste NEVER avec la raison
Versions des outils	la section run (exécution) de MANIFEST.json
Sommes de contrôle des sorties	la section outputs (sorties) de MANIFEST.json
Limites connues	le profil, l’exploration et la séparation

Décrivez aussi service_requests.db et son schéma pour l’équipe du tableau de bord. Le fichier DATASET.md du projet terminé est un exemple complet; comparez le vôtre avec lui quand vous avez terminé.

Recommandations

Sam envoie une courte note à Lucía et Hana avec la livraison.

Pour Hana (le modèle) :

• Utilisez la séparation livrée telle quelle : train pour l’entraînement, valid pour les choix, test une seule fois à la fin. Présentez le résultat du test à côté du résultat de validation, car la période de test a un taux de retard supérieur.
• Partez de la référence de base « type de problème seulement » (ROC AUC de 0,812 sur valid). Un modèle qui ne la dépasse pas clairement ne justifie pas sa complexité supplémentaire.
• N’ajoutez jamais status, closed_at ou les textes de résolution, même depuis une autre table. validate.py les bloque dans cette table; dans un nouveau projet, copiez d’abord la liste NEVER.

Pour Lucía (la décision) :

• Le type de problème en dit déjà beaucoup. Les bordures, panneaux manquants, parcomètres et lampadaires sont en retard bien plus souvent que les feux de circulation et nids-de-poule. Son équipe peut utiliser cette table dès aujourd’hui, avant qu’un modèle existe.
• Posez trois questions à l’équipe des données de la ville. Que signifie l’heure de fermeture des demandes sur les lampadaires mises en quarantaine? Quel processus crée des demandes à 7 h 00 min 00 s et à 22 h 10 min 00 s? Pourquoi la plupart des demandes ont-elles le canal UNKNOWN (inconnu)?
• Actualisez le jeu de données chaque mois : une nouvelle extraction, un nouvel instantané, le même pipeline. Conservez le MANIFEST.json de chaque mois. Si le taux de retard sort de la plage de 5 % à 30 %, ou qu’une colonne est renommée, le pipeline bloque la livraison.

Pour l’équipe du tableau de bord : utilisez service_requests.db pour les comptes et les heures de fermeture. Utilisez la médiane de days_to_close, pas la moyenne. Les 1 444 demandes nettoyées qui ont pris plus d’un an à fermer tirent la moyenne vers le haut.

Limites

• La cible concerne le dossier, pas la rue. Une demande ferme lorsqu’elle est un doublon, qu’un inspecteur ne trouve rien ou qu’un autre organisme est responsable. late_14d indique seulement si le dossier a été fermé dans le délai.
• Une ville, 18 mois, un organisme. Les saisons apparaissent deux fois, mais un changement de processus municipal peut rendre le passé peu utile comme guide. Vérifiez le taux de retard de chaque nouveau mois.
• Beaucoup de demandes ont peu de contexte. Parmi les demandes, 57,8 % n’ont pas de canal, et 14,8 % des lignes nettoyées n’ont pas de coordonnées. Le modèle en sait moins sur elles.
• La caractéristique d’historique est simple. « Même endroit » est un point arrondi. Deux signalements de part et d’autre d’une rue peuvent donc ne pas correspondre. Un problème sur une longue route s’étend sur plusieurs points.
• Des heures sans fuseau horaire. La source donne l’heure locale de New York sans fuseau. Une durée qui traverse le passage à l’heure avancée ou le retour à l’heure normale est décalée d’une heure. Cela peut changer la cible seulement pour une demande fermée à moins d’une heure de sa limite de 14 jours.
• Ce qu’il ne faut pas conclure. Un modèle entraîné sur cette table classe les demandes selon leur risque de retard. Il n’en explique pas la cause et ne doit pas servir à classer les quartiers ou les personnes qui signalent les problèmes.

Faites-en un projet de portfolio

Ce projet montre une partie du travail en IA que beaucoup de portfolios omettent : des données fiables pour un modèle, preuves à l’appui. Mettez-le dans un dépôt public.

Quoi mettre dans le dépôt : tout le dossier du projet, sauf les données, la base de données et .venv. Le .gitignore du projet de départ les exclut déjà. Il conserve les petits fichiers JSON dans data/model/, pour que les lecteurs puissent suivre la provenance des données. Incluez reports/, DATASET.md, les tests et les requêtes. Les données sont publiques; votre README explique comment les obtenir avec get_data.py.

Un modèle de README : remplacez le README.md de départ par un README de portfolio, dans cet ordre :

1. La question, en une phrase, avec le taux de retard : « À sa création, une demande sera-t-elle encore ouverte 14 jours plus tard? Environ 13,7 % le sont. »
2. Données et licence : la source, la date d’extraction, les conditions de NYC Open Data et un lien vers DATASET.md.
3. Approche : le profil et ses constats, puis les décisions de nettoyage avec les décomptes. Ensuite, la cible et la maturité des étiquettes, la caractéristique historique tirée des lignes antérieures, la séparation temporelle et le contrat avec ses tests.
4. Résultats : le tableau de séparation et la vérification des fuites avec son message.
5. Comment exécuter : les commandes de préparation (l’environnement virtuel, pip install et get_data.py), puis run_pipeline.py.
6. Limites.

Le README.md du projet terminé suit cet ordre; utilisez-le comme modèle, avec vos propres mots.

Comment le décrire en entrevue, en deux phrases : « J’ai transformé 315 000 vraies demandes de réparation de rues de NYC en une table de modélisation sans fuite, avec des caractéristiques tirées uniquement de faits connus à l’arrivée de chaque demande. Un pipeline exécuté en une commande la produit avec 13 règles testées et des sommes de contrôle, et une vérification des fuites montre qu’une colonne interdite ferait passer le score de 0,816 à un faux 0,863. »

Allez plus loin :

• Une nouvelle caractéristique, avec prudence : le nombre de demandes du même type encore ouvertes dans le même arrondissement au moment de la création. Il faut évaluer « encore ouvertes » à ce moment précis : une demande compte seulement si elle existait déjà et n’était pas encore fermée. Écrivez le calcul et ajoutez un test pour vérifier qu’une fermeture ultérieure ne change jamais la valeur.
• Une vérification des entrées pour une nouvelle extraction : exécutez python get_data.py --fresh, puis le pipeline. Notez ce qui a changé dans les décomptes, et pourquoi.
• Un deuxième instantané : construisez la table au 1er avril 2025, avec avril 2025 comme période de test. Comparez les décomptes avec ceux du premier instantané.
• Entraînez le modèle dans les études de cas du cours Apprentissage automatique : du problème à un modèle fiable avec cette table. Présentez son résultat à côté de la référence de base.

Vérification des connaissances

Vérifiez votre compréhension

Dans l'instantané, 2 978 demandes récentes étaient encore ouvertes. Un collègue dit : « Elles sont ouvertes, donc en retard. Gardez-les avec late_14d = 1. » Que se passe-t-il, et quelle est la bonne approche?

Afficher la réponse

Leurs 14 jours n’étaient pas écoulés au moment de l’instantané. « Ouverte » signifie donc « pas encore connu », et non « en retard ». Le fichier figé montre le coût : 1 779 des 2 978 demandes (59,7 %) ont finalement fermé dans leurs 14 jours. Avec cette étiquette prématurée, le taux de retard des demandes récentes serait de 31,0 % plutôt que de 12,5 %. Un modèle apprendrait que les nouvelles demandes sont en retard bien plus souvent qu’elles ne le sont vraiment. Excluez toutes les demandes créées après le 17 juin 2025 à 0 h et comptez-les, comme le fait build_features.py.

Vérifiez votre compréhension

Lesquels peuvent servir de caractéristiques pour late_14d : complaint_type, status, le nombre de demandes au même endroit dans les 15 jours avant et après, created_hour, resolution_description, batch_time? Expliquez pourquoi chacun des éléments exclus ne convient pas.

Afficher la réponse

complaint_type, created_hour et batch_time peuvent servir de caractéristiques : ils existent à la création de la demande. status représente l’état au jour de l’extraction, après la vie de la demande. L’organisme rédige resolution_description lorsqu’il intervient sur la demande. Le décompte sur les 15 jours avant et après utilise des demandes qui n’existent pas encore à la création. Remplacez-le par les 30 jours avant, avec un cadre de fenêtre qui se termine une seconde avant la demande, comme prior_same_place_30d.

Vérifiez votre compréhension

Les données de validation ont un taux de retard de 11,1 %, et les données de test, de 15,7 %. Est-ce le signe d'un bogue dans split.py? Qu'est-ce que cela signifie pour le modèle?

Afficher la réponse

Non. L’exploration a montré un taux de retard saisonnier : de 10 % à 13 % de décembre à mars, de 14 % à 16 % d’avril à août. La validation couvre janvier à mars, et le test, avril à la mi-juin. Hana fait ses choix sur les données de validation d’hiver. Son modèle rencontre donc plus de demandes en retard dans le test que dans la validation. Il faut donc vérifier le modèle sur plusieurs saisons et présenter le résultat de test à côté du résultat de validation.

Récapitulatif

• Vous avez profilé une exportation réelle en SQL et découvert ses problèmes discrets. Ils comprennent 392 soumissions répétées, 1 482 heures de fermeture impossibles, UNKNOWN (inconnu) dans 57,8 % des canaux, des heures de traitement par lot et du texte mal encodé.
• Vous avez nettoyé les données avec des décisions chiffrées : supprimer les copies, isoler les valeurs impossibles à corriger, remplacer les valeurs de substitution par des valeurs manquantes, réparer le texte et conserver le reste. Vous avez obtenu 313 573 lignes propres, et chaque ligne d’entrée a été prise en compte.
• Vous avez défini la cible au moment de la création, late_14d, et exclu les colonnes écrites plus tard. Seules les demandes dont les 14 jours étaient écoulés au moment de l’instantané ont reçu une cible. Étiquetées trop tôt, les demandes récentes auraient semblé en retard dans 31,0 % des cas plutôt que dans 12,5 %.
• Vous avez construit une caractéristique historique à partir des lignes antérieures seulement, avec un cadre de fenêtre se terminant une seconde avant chaque demande.
• Vous avez séparé les données selon le temps, rédigé un contrat de 13 règles et testé chaque règle. Le pipeline a produit la même empreinte deux fois. Une vérification des fuites a mesuré le faux gain d’une fuite : de 0,816 à 0,863.
• Vous avez livré deux produits avec un seul pipeline : une table de modélisation pour un scientifique des données et une base de données propre pour une équipe de tableaux de bord.

La prochaine étude de cas passe des demandes aux vélos. Vous repérez des défauts d’exportation et des déplacements par fourgonnette dans de vrais trajets de vélopartage. Vous construisez une cible par station à partir des jours antérieurs seulement.

Leçon suivante : Planifier le rééquilibrage des vélos pendant la nuit