Planifier le rééquilibrage des vélos pendant la nuit

Ce que vous saurez faire

Transformez un mois de trajets réels à vélo en libre-service en une table pour une équipe de nuit. Elle répond à une question : quelles 20 stations doivent recevoir des vélos avant l’heure de pointe de demain matin? Vous trouverez les défauts de l’exportation et compterez chaque ligne mise de côté. Ensuite, vous testerez un classement simple contre « comme hier » sur une semaine gardée à part.

Étude de casPièce de portfolio

Jeu de données

Bike Share Toronto Ridership Data, juin 2025 (Ville de Toronto)

Licence

Licence du gouvernement ouvert – Toronto

Durée

Environ 2 h 30

S’exécute dans

Colab, Kaggle ou VS Code

Compétences utilisées

• Profiler une exportation réelle
• Mettre en quarantaine avec des raisons
• LAG par partition
• Une grille complète pour les fonctions de fenêtre
• Caractéristiques des jours précédents seulement
• Séparation temporelle et un seul test final
• Les règles de validation

Ouvrir dans ColabOuvrir dans KaggleVoir sur GitHub

Les carnets sont en anglais. Colab et Kaggle ouvrent votre propre copie. Pour utiliser VS Code, téléchargez le carnet depuis GitHub. Vous êtes bloqué ? Consultez le carnet de solution.

Le carnet (notebook) Jupyter exécute chaque étape de cette page, avec le même code et les mêmes résultats. Il contient aussi quatre tâches avec des vérifications. Ses cellules de préparation installent pandas et téléchargent les trajets (28 Mo), sans configuration locale. Son exécution prend environ une minute.

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 fonctions de fenêtre avec PARTITION BY, LAG et ROW_NUMBER, les égalités et les intervalles de dates semi-ouverts, dans Le temps et le classement.
• Le profilage avant toute modification, dans Profiler avant de modifier, et la quarantaine avec une raison, dans Traiter les valeurs désordonnées.
• Le moment de la prédiction et les caractéristiques issues du passé seulement, dans Caractéristiques et moment de la cible.
• Les séparations temporelles, dans Choisir une séparation, et les règles exprimées par des requêtes qui ne doivent retourner aucune ligne, dans Les règles de validation.

Ce qu’il vous faut sur votre ordinateur (seulement pour l’exécution locale; le carnet n’en a pas besoin)

• Python 3.12 ou une version ultérieure avec pandas 3.0.6, comme dans Transformer avec Python, et matplotlib pour les graphiques. L’environnement virtuel du projet ticket-data convient; cette étude de cas ne modifie pas ses fichiers.
• Le carnet, téléchargé à partir du lien ci-dessus, dans VS Code ou JupyterLab. Sa cellule de préparation crée un dossier bike-rebalancing et y télécharge les données. Pour recommencer, supprimez ce dossier.

Testé sur macOS avec Python 3.14.6 (SQLite 3.50.4) et pandas 3.0.6, dans Jupyter après un redémarrage complet. Windows et Linux n’ont pas été testés.

La situation

Leila Haddad dirige les opérations d’un réseau municipal de vélos en libre-service. Les matins de travail, les gens vont de leur domicile à leur bureau à vélo. Les stations près des domiciles se vident, et celles près des bureaux se remplissent. Une personne qui trouve une station vide marche ou prend l’autobus, et risque de ne plus essayer les vélos.

Chaque soir à 22 h, Leila envoie donc une équipe avec des fourgonnettes. L’équipe prend des vélos aux stations pleines et les dépose aux stations qui se videront le matin. C’est le rééquilibrage. L’équipe peut réapprovisionner environ 20 stations par nuit, sur plus de 900. La décision consiste à choisir ces 20 stations.

Aujourd’hui, Leila choisit « les stations qui se sont vidées ce matin ». Elle demande à son analyste, Tomasz Nowak : « Pouvez-vous me donner une meilleure liste chaque soir? Et pouvez-vous prouver qu’elle est meilleure? »

Les trajets de cette étude de cas sont réels : un mois de Bike Share Toronto. Leila, Tomasz et l’équipe de fourgonnettes sont fictifs. Les vrais plans de rééquilibrage du réseau ne sont pas publics.

Ce que vous allez construire

Vous construirez une table des stations : une ligne par station et par jour de travail, avec la cible et quelques caractéristiques. La cible est le nombre de vélos que la station perd entre 7 h et 10 h. Les caractéristiques sont des valeurs connues à 22 h la veille. À partir de cette table, vous créez un classement. Les 20 premières stations deviennent le plan de nuit.

Avant cela, il faut préparer les données. Ce travail est la compétence principale de cette étude de cas. L’exportation contient une colonne erronée dans chaque ligne. Certains trajets n’ont pas de fin, et d’autres durent 0 seconde. Environ 6 % des trajets commencent après un déplacement effectué sans cycliste. Les étapes viennent de tout le cours. Vous profilez les données comme dans Profiler avant de modifier, puis mettez des lignes en quarantaine comme dans Traiter les valeurs désordonnées. Les fonctions de fenêtre viennent de Le temps et le classement. Le moment de la cible et les séparations viennent de Caractéristiques et moment de la cible et de Choisir une séparation. Les vérifications viennent de Les règles de validation.

Les données

Les données proviennent du fichier de juin 2025 de Bike Share Toronto Ridership Data. La Ville de Toronto le publie pour la Toronto Parking Authority. Il contient chaque location du mois : lieu et heure de départ, lieu et heure d’arrivée, et vélo utilisé.

Source et licence

Bike Share Toronto Ridership Data, données ouvertes de la Ville de Toronto, open.toronto.ca, fichier bikeshare_2025_06.csv de bikeshare-ridership-2025.zip, téléchargé le 7 octobre 2026. Contient des renseignements utilisés sous la Licence du gouvernement ouvert – Toronto. Nextia Learning a converti le fichier de l’encodage cp1252 à UTF-8 et ses fins de ligne de CRLF à LF. Nextia Learning l’a aussi compressé avec gzip. Rien d’autre n’a changé.

Tomasz lit le fichier dans pandas et le copie dans une base de données SQLite, bikes.db. Il lit chaque colonne comme du texte (dtype=str) et conserve les valeurs vides comme du texte vide (keep_default_na=False). Ainsi, rien ne change avant son examen, et une valeur vide reste visible sous la forme '' :

import sqlite3

raw = pd.read_csv("data/bike_share_toronto_2025-06.csv.gz", dtype=str, keep_default_na=False)
print(raw.shape)
print(raw.iloc[0].to_string())

con = sqlite3.connect("bikes.db")
raw.to_sql("trips_raw", con, if_exists="replace", index=False)

Le résultat :

(980624, 11)
Trip_Id                            36767099
Trip_Duration                          6946
Start_Station_Id                       7569
Start_Time              2025-06-01 00:01:56
Start_Station_Name    Toronto Inukshuk Park
End_Station_Id                         7753
End_Time                2025-06-01 01:57:42
End_Station_Name      Toronto Inukshuk Park
Bike_Id                                5096
User_Type                            Casual
Bike_Model                           ICONIC

Ce que cela nous apprend. Le fichier contient 980 624 lignes et 11 colonnes. Une ligne représente une location. Regardez bien la première ligne : elle commence à la station 7569 et se termine à la station 7753. Pourtant, les deux noms sont Toronto Inukshuk Park. Deux stations différentes ne devraient pas avoir le même nom. Gardez cela en tête.

À partir d’ici, les requêtes s’exécutent dans SQLite, comme dans le reste du cours. Deux petites fonctions utilitaires affichent un résultat sous forme de table avec son nombre de lignes, comme q.py, ou retournent une seule valeur :

def sql(query, db=None, limit=20):
    """Run a query (on bikes.db, or on `db`) and print the result as a table, with its row count."""
    cursor = (db or con).execute(query)
    rows = cursor.fetchall()
    table = pd.DataFrame(rows[:limit], columns=[d[0] for d in cursor.description], dtype=object)
    print(table.where(table.notna(), "NULL").to_string(index=False))  # show NULL as NULL
    more = f", showing {limit}" if len(rows) > limit else ""
    print(f"({len(rows)} row{'s' if len(rows) != 1 else ''}{more})")


def scalar(query):
    """Run a query that returns one value, and return that value."""
    return con.execute(query).fetchone()[0]

Le dictionnaire de données

Voici les colonnes utilisées dans cette étude de cas, en mots simples. Les trois dernières sont dans le fichier, mais ne servent pas.

Colonne	Signification	Utilisation
Trip_Id	Le numéro du trajet	La clé; départage les égalités dans l’ordre des trajets d’un vélo
Trip_Duration	Durée du trajet en secondes	Règles de qualité
Start_Station_Id, Start_Station_Name	Lieu de départ du trajet	Départs; noms des stations
Start_Time	Heure de départ, YYYY-MM-DD HH:MM:SS, heure locale	Départs; ordre des trajets d’un vélo
End_Station_Id, End_Time	Lieu et heure d’arrivée	Arrivées; déplacements sans cycliste
End_Station_Name	Le nom de la station d’arrivée	Non utilisé : il est erroné (voir ci-dessous)
Bike_Id	Le numéro du vélo	Les trajets d’un vélo, dans l’ordre
User_Type, Bike_Model	Membre ou cycliste occasionnel; modèle du vélo	Non utilisés

Premières vérifications

Tomasz commence par les dimensions : Trip_Id est-il unique, quelles dates le fichier couvre-t-il, et combien de stations et de vélos contient-il?

sql("""
SELECT COUNT(*) AS trips,
       COUNT(DISTINCT Trip_Id) AS trip_ids,
       MIN(Start_Time) AS first_start,
       MAX(Start_Time) AS last_start,
       COUNT(DISTINCT Start_Station_Id) AS stations,
       COUNT(DISTINCT Bike_Id) AS bikes
FROM trips_raw
""")

Le résultat :

 trips trip_ids         first_start          last_start stations bikes
980624   980624 2025-06-01 00:00:15 2025-06-30 23:59:53      916  8874
(1 row)

Ce que cela nous apprend. Chaque trajet a son propre identifiant, donc Trip_Id est la clé. Le fichier couvre tout juin, de juste après minuit le 1er juin à juste avant minuit le 30 juin. Il contient 916 stations et 8 874 vélos. Notez que le fichier sélectionne les trajets selon l’heure de départ. Un trajet commencé le 31 mai et terminé le 1er juin n’y figure pas.

L’encodage

Un ordinateur stocke le texte sous forme d’octets. Un encodage est la table qui transforme ces octets en caractères. La Ville publie ce fichier en cp1252, un ancien encodage Windows. La plupart des outils actuels attendent UTF-8. Les deux concordent pour les lettres anglaises ordinaires. Une erreur apparaît donc seulement dans les quelques noms contenant d’autres caractères. Tomasz trouve ces noms et montre leur apparence avec le mauvais encodage. GLOB compare du texte à un motif; [^ -~] représente un caractère qui n’est pas entre l’espace et ~. Cet intervalle contient les lettres, chiffres et signes ASCII ordinaires :

names = pd.read_sql("""
SELECT DISTINCT Start_Station_Name AS name FROM trips_raw
WHERE Start_Station_Name GLOB '*[^ -~]*'   -- a character outside plain ASCII
""", con)["name"]
for name in names:
    print(name, " | read as cp1252:", name.encode("utf-8").decode("cp1252"))

city_bytes = "25 York St \u2013 Union Station".encode("cp1252")  # how the city's file stores the dash
try:
    city_bytes.decode("utf-8")
except UnicodeDecodeError as error:
    print("The city's file read as UTF-8:", error)

Le résultat :

25 York St – Union Station South  | read as cp1252: 25 York St – Union Station South
Lundy Ave / Étienne Brûlé Park  | read as cp1252: Lundy Ave / Étienne Brûlé Park
The city's file read as UTF-8: 'utf-8' codec can't decode byte 0x96 in position 11: invalid start byte

Ce que cela nous apprend. Seuls deux noms de stations contiennent d’autres caractères que les lettres anglaises ordinaires : un tiret (–) et les lettres É, û et é. Le mauvais encodage provoque une erreur dans un sens, mais reste silencieux dans l’autre. Lire le fichier cp1252 de la Ville en UTF-8 arrête la lecture avec une erreur. C’est le bon cas : vous le remarquez. Lire notre copie UTF-8 en cp1252 donne – et Étienne sans erreur, et les mauvais noms apparaissent dans chaque rapport. Si vous téléchargez un nouveau fichier de la Ville, lisez-le avec encoding="cp1252".

Valeurs vides

Cette étude de cas doit savoir où chaque trajet s’est terminé. Tomasz compte les valeurs vides dans les colonnes importantes :

sql("""
SELECT SUM(End_Station_Id = '') AS no_end_station,
       SUM(End_Time = '') AS no_end_time,
       SUM(End_Station_Id = '' AND End_Time = '') AS both,
       SUM(Bike_Id = '') AS no_bike,
       SUM(Start_Station_Id = '') AS no_start_station
FROM trips_raw
""")

Le résultat :

no_end_station no_end_time both no_bike no_start_station
           830         130  106       0                0
(1 row)

Ce que cela nous apprend. 830 trajets n’ont pas de station d’arrivée et 130 n’ont pas d’heure d’arrivée; 106 n’ont ni l’une ni l’autre. Cela représente environ 1 trajet sur 1 000. Pour ces trajets, nous ne pouvons pas dire où le vélo est arrivé. Ils ne peuvent donc pas compter comme des arrivées. Chaque trajet a un vélo et une station de départ.

Les noms des stations d’arrivée

La première ligne montrait deux stations portant le même nom. Tomasz vérifie combien de fois le nom d’arrivée égale celui de départ, puis compare les identifiants d’arrivée et de départ :

sql("""
SELECT COUNT(*) AS trips,
       SUM(End_Station_Name = Start_Station_Name) AS same_name,
       SUM(End_Station_Id = Start_Station_Id) AS same_station_id
FROM trips_raw
""")
sql("""
SELECT Start_Station_Id, End_Station_Id, Start_Station_Name, End_Station_Name
FROM trips_raw
WHERE End_Station_Id <> Start_Station_Id
LIMIT 3
""")

Le résultat :

 trips same_name same_station_id
980624    980624           42604
(1 row)
Start_Station_Id End_Station_Id              Start_Station_Name                End_Station_Name
            7569           7753           Toronto Inukshuk Park           Toronto Inukshuk Park
            7043           7303 Queens Quay W / Lower Simcoe St Queens Quay W / Lower Simcoe St
            7007           7078           College St / Huron St           College St / Huron St
(3 rows)

Ce que cela nous apprend. End_Station_Name égale Start_Station_Name dans chacune des 980 624 lignes. Pourtant, seulement 42 604 trajets reviennent à leur station de départ. La colonne du nom d’arrivée est donc une copie du nom de départ : un défaut d’exportation. Les identifiants d’arrivée ne sont pas touchés, car ils désignent des stations réelles et différentes.

C’est le genre d’erreur silencieuse que ce cours cherche à repérer. Un rapport des « stations d’arrivée les plus fréquentées » par nom s’exécuterait sans erreur. Il montrerait les stations de départ les plus fréquentées. La solution consiste à ne jamais utiliser End_Station_Name. Chaque nom vient des données de départ, où chaque identifiant a son propre nom.

À vous de jouer : compter les trajets avec un mauvais nom d’arrivée

Combien de trajets finissent à une station connue différente de celle du départ, mais portent le nom de départ dans End_Station_Name? (Dans le carnet, mettez le nombre dans different_station_same_name.)

different_station_same_name = scalar("""
SELECT COUNT(*)
FROM trips_raw
WHERE End_Station_Id <> ''
  AND End_Station_Id <> Start_Station_Id
  AND End_Station_Name = Start_Station_Name
""")
print(different_station_same_name)

937190

Prenez les 980 624 trajets. Excluez les 42 604 qui reviennent à leur station de départ et les 830 sans station d’arrivée. Les 937 190 autres trajets portent un mauvais nom d’arrivée.

Un nom par station

Les noms viennent des données de départ. Tomasz construit donc une table stations à partir de celles-ci. Il vérifie que chaque identifiant a un seul nom et que chaque nom a un seul identifiant. Puis il cherche les identifiants d’arrivée qui n’apparaissent jamais au départ, car ils n’auraient aucun nom :

con.executescript("""
DROP TABLE IF EXISTS stations;
CREATE TABLE stations AS
SELECT Start_Station_Id AS station_id, MIN(Start_Station_Name) AS name
FROM trips_raw
GROUP BY Start_Station_Id;
""")
sql("""
SELECT (SELECT COUNT(*) FROM stations) AS stations,
       (SELECT COUNT(*) FROM (SELECT Start_Station_Id FROM trips_raw
            GROUP BY 1 HAVING COUNT(DISTINCT Start_Station_Name) > 1)) AS ids_with_two_names,
       (SELECT COUNT(*) FROM (SELECT Start_Station_Name FROM trips_raw
            GROUP BY 1 HAVING COUNT(DISTINCT Start_Station_Id) > 1)) AS names_with_two_ids
""")
sql("""
SELECT End_Station_Id, COUNT(*) AS trips
FROM trips_raw
WHERE End_Station_Id <> '' AND End_Station_Id NOT IN (SELECT station_id FROM stations)
GROUP BY End_Station_Id
""")

Le résultat :

stations ids_with_two_names names_with_two_ids
     916                  0                  0
(1 row)
End_Station_Id trips
          7638     2
          7690     5
          7793     1
          8065     2
(4 rows)

Ce que cela nous apprend. Dans les données de départ, les identifiants et les noms correspondent un à un. La table stations contient donc une ligne par station : 916 lignes. Quatre identifiants d’arrivée n’apparaissent jamais au départ en juin. Ensemble, ils représentent 10 trajets, probablement à des stations ouvertes ou fermées pendant le mois. Ils sont trop peu nombreux pour changer les résultats. Nous les notons donc et poursuivons.

Durées

Une location a une durée. Tomasz compte les trajets de 0 seconde, de moins d’une minute et de plus de 24 heures. Il vérifie aussi que la durée égale l’heure d’arrivée moins l’heure de départ, avec une tolérance de 2 secondes :

sql("""
SELECT SUM(CAST(Trip_Duration AS INTEGER) = 0) AS zero_seconds,
       SUM(CAST(Trip_Duration AS INTEGER) < 60) AS under_60_s,
       SUM(CAST(Trip_Duration AS INTEGER) > 86400) AS over_24_h,
       SUM(End_Time <> '' AND ABS((julianday(End_Time) - julianday(Start_Time)) * 86400
           - CAST(Trip_Duration AS INTEGER)) > 2) AS duration_not_end_minus_start
FROM trips_raw
""")

Le résultat :

zero_seconds under_60_s over_24_h duration_not_end_minus_start
         779       2426         5                            0
(1 row)

Ce que cela nous apprend. La durée concorde avec les deux heures dans chaque ligne. Les heures sont donc cohérentes entre elles. Le problème vient des trajets eux-mêmes. 779 trajets durent 0 seconde : un vélo ne peut pas se déplacer en un temps nul. 2 426 durent moins d’une minute, et 5 durent plus d’une journée. Ces derniers correspondent à un vélo perdu ou mal remis à la station, pas à un trajet.

Nettoyer les trajets

Une table typée

La table brute contient du texte dans chaque colonne. Tomasz crée une table typée trips : l’identifiant et la durée deviennent des entiers, et NULLIF transforme '' en NULL. NULL signifie « inconnu » en SQL. COUNT(end_id) et IS NULL fonctionnent maintenant comme le cours l’a montré. Il exclut les noms de cette table; ils restent dans stations.

con.executescript("""
DROP TABLE IF EXISTS trips;
CREATE TABLE trips AS
SELECT CAST(Trip_Id AS INTEGER) AS trip_id,
       CAST(Trip_Duration AS INTEGER) AS duration_s,
       Start_Station_Id AS start_id,
       Start_Time AS start_time,
       NULLIF(End_Station_Id, '') AS end_id,     -- '' becomes NULL: unknown
       NULLIF(End_Time, '') AS end_time,
       Bike_Id AS bike_id,
       User_Type AS user_type
FROM trips_raw;
""")
sql("SELECT COUNT(*) AS trips, COUNT(end_id) AS with_end_station, COUNT(end_time) AS with_end_time FROM trips")

Le résultat :

 trips with_end_station with_end_time
980624           979794        980494
(1 row)

Ce que cela nous apprend. Aucune ligne n’a été perdue : 980 624 trajets. 979 794 ont une station d’arrivée et 980 494 ont une heure d’arrivée. Cela correspond aux 830 et 130 valeurs vides ci-dessus.

Mettre en quarantaine avec une raison

Tous les trajets ne peuvent pas compter comme des trajets effectués par un cycliste. Tomasz ne supprime pas les trajets problématiques. Il attribue une raison à chacun, comme dans Traiter les valeurs désordonnées, et le déplace dans une table quarantine. Les règles s’appliquent dans un ordre fixe. La première qui correspond l’emporte, donc chaque trajet reçoit exactement une raison :

1. Aucune heure d’arrivée, puis aucune station d’arrivée : nous ne pouvons pas situer l’arrivée.
2. Plus de 24 heures : un vélo perdu, pas un trajet.
3. Faux départ : le vélo revient à la même station en 2 minutes ou moins. Une personne l’a pris, a changé d’idée ou trouvé un défaut, puis l’a remis à la station.
4. Moins de 60 secondes vers une autre station : trop rapide pour être un vrai trajet.

con.executescript("""
DROP TABLE IF EXISTS checked;
CREATE TABLE checked AS
SELECT *,
       CASE
           WHEN end_time IS NULL THEN 'no_end_time'
           WHEN end_id IS NULL THEN 'no_end_station'
           WHEN duration_s > 86400 THEN 'over_24_hours'
           WHEN end_id = start_id AND duration_s < 120 THEN 'false_start'
           WHEN duration_s < 60 THEN 'under_60_seconds'
       END AS reason
FROM trips;
DROP TABLE IF EXISTS clean;
CREATE TABLE clean AS SELECT * FROM checked WHERE reason IS NULL;
DROP TABLE IF EXISTS quarantine;
CREATE TABLE quarantine AS SELECT * FROM checked WHERE reason IS NOT NULL;
""")
sql("SELECT COALESCE(reason, '(clean)') AS reason, COUNT(*) AS trips FROM checked GROUP BY reason ORDER BY trips DESC")
print("clean + quarantine =", scalar("SELECT COUNT(*) FROM clean") + scalar("SELECT COUNT(*) FROM quarantine"))

Le résultat :

          reason  trips
         (clean) 974870
     false_start   3665
under_60_seconds   1233
  no_end_station    724
     no_end_time    130
   over_24_hours      2
(6 rows)
clean + quarantine = 980624

Ce que cela nous apprend. 974 870 trajets sont propres. 5 754 trajets (0,6 %) sont en quarantaine. Les faux départs forment le plus grand groupe, avec 3 665 trajets. La dernière ligne est une vérification, pas une décoration. Les trajets propres et ceux en quarantaine totalisent les 980 624 lignes de la table brute. Aucun trajet n’a été perdu ni compté deux fois.

Pourquoi conserver la table de quarantaine? Parce qu’une personne qui lit les résultats demandera « qu’avez-vous exclu? ». La réponse sera une requête, pas un souvenir.

Trouver les vélos déplacés sans cycliste

Le problème

Un vélo qui termine un trajet à la station B reste à B jusqu’à ce que le prochain cycliste le prenne. Son prochain trajet devrait donc commencer à B. S’il commence ailleurs, personne ne l’y a amené à vélo. Une camionnette l’a déplacé, un mécanicien l’a emporté pour le réparer, ou un trajet manque dans le fichier. Ces déplacements comptent pour Leila : ils montrent où les équipes travaillent déjà.

Pour les trouver, Tomasz doit comparer chaque trajet avec le trajet précédent du même vélo. Un GROUP BY ne peut pas le faire, car il regroupe les lignes et perd leur ordre. Une fonction de fenêtre le peut. LAG(end_id) lit end_id dans la ligne précédente. PARTITION BY bike_id crée une fenêtre distincte pour chaque vélo. La ligne précédente correspond donc toujours au trajet précédent du même vélo. ORDER BY start_time, trip_id définit ce qui vient « avant »; trip_id départage deux trajets qui commencent à la même seconde.

LAG dans un petit exemple

D’abord, voici quatre trajets que vous pouvez vérifier à la main. Le vélo B7 a trois trajets, et le vélo B9 en a un :

tiny = sqlite3.connect(":memory:")
tiny.executescript("""
CREATE TABLE trips (trip_id INTEGER, bike_id TEXT, start_time TEXT, start_id TEXT, end_id TEXT);
INSERT INTO trips VALUES
    (1, 'B7', '2025-06-02 07:40', 'A', 'B'),
    (2, 'B7', '2025-06-02 12:10', 'B', 'C'),
    (3, 'B7', '2025-06-03 08:05', 'D', 'B'),
    (4, 'B9', '2025-06-02 09:00', 'C', 'A');
""")
sql("""
SELECT bike_id, trip_id, start_time, start_id, end_id,
       LAG(end_id) OVER (PARTITION BY bike_id ORDER BY start_time, trip_id) AS previous_end
FROM trips
""", db=tiny)

Le résultat :

bike_id trip_id       start_time start_id end_id previous_end
     B7       1 2025-06-02 07:40        A      B         NULL
     B7       2 2025-06-02 12:10        B      C            B
     B7       3 2025-06-03 08:05        D      B            C
     B9       4 2025-06-02 09:00        C      A         NULL
(4 rows)

Ce que cela nous apprend. Le trajet 2 commence à B, où le trajet 1 s’est terminé : un trajet normal. Le trajet 3 commence à D, mais le vélo avait été laissé à C : B7 a été déplacé de C à D sans cycliste. Le trajet 4 appartient à un autre vélo, donc son previous_end vaut NULL. La fenêtre ne passe jamais d’un vélo à un autre : c’est le rôle de PARTITION BY.

Tous les vélos

Voici maintenant la même requête sur les 980 624 trajets. Tomasz utilise tous les trajets, pas seulement ceux qui sont propres. Un trajet mis en quarantaine indique quand même où se trouvait le vélo. Il conserve aussi l’heure de fin précédente pour une vérification, ainsi qu’un numéro d’étape. Trois fonctions utilisent la même fenêtre. WINDOW bike_order AS (...) lui donne donc un nom une seule fois, et chaque fonction indique OVER bike_order. Ensuite, il compte ce qui s’est passé avant chaque trajet :

con.executescript("""
DROP TABLE IF EXISTS bike_steps;
CREATE TABLE bike_steps AS
SELECT trip_id, bike_id, start_time, start_id, end_id, end_time,
       LAG(end_id) OVER bike_order AS previous_end,
       LAG(end_time) OVER bike_order AS previous_end_time,
       ROW_NUMBER() OVER bike_order AS step
FROM trips
WINDOW bike_order AS (PARTITION BY bike_id ORDER BY start_time, trip_id);
""")
sql("""
SELECT CASE
           WHEN step = 1 THEN 'first trip of the bike'
           WHEN previous_end IS NULL THEN 'previous end unknown'
           WHEN previous_end = start_id THEN 'starts where it was left'
           ELSE 'moved: no rider trip'
       END AS what_happened,
       COUNT(*) AS trips,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM trips), 1) AS pct
FROM bike_steps
GROUP BY what_happened
ORDER BY trips DESC
""")

Le résultat :

           what_happened  trips   pct
starts where it was left 910507  92.8
    moved: no rider trip  60554   6.2
  first trip of the bike   8874   0.9
    previous end unknown    689   0.1
(4 rows)

Ce que cela nous apprend. Comme prévu, 92,8 % des trajets commencent là où le vélo avait été laissé. 60 554 trajets (6,2 %) commencent après un déplacement sans cycliste : environ un trajet sur 16. Pour chaque vélo, le premier trajet de juin n’a aucun trajet précédent à comparer : cela représente 8 874 trajets. 689 suivent un trajet sans station d’arrivée, donc nous ne pouvons pas savoir.

À vous : les trajets qui commencent avant la fin du précédent

Un vélo ne peut pas faire deux trajets à la fois. Combien de trajets commencent avant la fin du trajet précédent du même vélo? (Dans le carnet Jupyter, placez le nombre dans overlapping_trips.)

overlapping_trips = scalar("""
SELECT COUNT(*)
FROM bike_steps
WHERE previous_end_time IS NOT NULL
  AND start_time < previous_end_time
""")
print(overlapping_trips)

43

43 trajets chevauchent le trajet précédent de leur vélo. Ce sont des erreurs d’enregistrement, comme une horloge incorrecte ou un numéro de vélo saisi deux fois. Ils sont trop peu nombreux pour changer les résultats. Ils restent donc dans les données, et la fiche du jeu de données les signale.

Quand et où les déplacements ont lieu

Tomasz enregistre les déplacements dans une table. Il les compte selon l’heure où un cycliste a ensuite pris le vélo déplacé :

con.executescript("""
DROP TABLE IF EXISTS moves;
CREATE TABLE moves AS
SELECT trip_id, bike_id, previous_end AS from_id, previous_end_time AS last_seen,
       start_id AS to_id, start_time AS next_start
FROM bike_steps
WHERE previous_end IS NOT NULL AND previous_end <> start_id;
""")
moves_by_hour = pd.read_sql("""
SELECT CAST(strftime('%H', next_start) AS INTEGER) AS hour, COUNT(*) AS moves
FROM moves GROUP BY hour ORDER BY hour
""", con)
print(moves_by_hour.sort_values("moves", ascending=False).head(4).to_string(index=False))
morning_share = moves_by_hour.loc[moves_by_hour["hour"].between(6, 9), "moves"].sum() / moves_by_hour["moves"].sum()
print(f"moved bikes next taken from 6:00 to 9:59: {morning_share:.1%}")

Le résultat :

 hour  moves
    8   6465
   17   4587
    9   4012
   16   3871
moved bikes next taken from 6:00 to 9:59: 23.7%

Vélos déplacés sans cycliste en juin 2025, selon l'heure du trajet du cycliste suivant. Le sommet est à 8 h, pendant les déplacements du matin vers le travail.

Description textuelle du graphique

Un graphique linéaire avec 24 points, un par heure de 0 à 23. Les valeurs sont faibles la nuit, avec un minimum à 4 h (242). Elles augmentent fortement à partir de 6 h (1 266) et de 7 h (2 615), jusqu’au sommet de 8 h (6 465). Elles redescendent ensuite à 4 012 à 9 h. Pendant la journée, elles restent entre environ 3 300 et 3 900. Elles atteignent un deuxième sommet, plus bas, à 17 h (4 587), puis diminuent le soir jusqu’à 1 326 à 22 h.

Ce que cela nous apprend. Les vélos déplacés sont pris le plus souvent à 8 h. 23,7 % sont pris entre 6 h et 9 h 59. Les équipes placent donc déjà les vélos là où les cyclistes du matin en veulent. Le deuxième sommet, à 17 h, correspond au retour à la maison.

D’où les équipes prennent-elles les vélos, et où les apportent-elles?

sql("""
SELECT m.from_id AS station_id, s.name, COUNT(*) AS bikes_taken_away
FROM moves AS m JOIN stations AS s ON s.station_id = m.from_id
GROUP BY m.from_id ORDER BY bikes_taken_away DESC LIMIT 5
""")
sql("""
SELECT m.to_id AS station_id, s.name, COUNT(*) AS bikes_brought
FROM moves AS m JOIN stations AS s ON s.station_id = m.to_id
GROUP BY m.to_id ORDER BY bikes_brought DESC LIMIT 5
""")

Le résultat :

station_id                             name bikes_taken_away
      7015   King St W / Bay St (West Side)             3179
      7019            Temperance St Station             2239
      8028         Ward's Island Ferry Dock             1702
      8027        Hanlan's Point Ferry Dock             1273
      7283 Front St W / Bay St (South Side)             1110
(5 rows)
station_id                     name bikes_brought
      8030 Centre Island Ferry Dock          2208
      7907        365 Lippincott St          1342
      7030  Bay St / Wellesley St W          1323
      7887     800 Fleet St (North)          1292
      7249          144 Harrison St          1192
(5 rows)

Ce que cela nous apprend. Les camionnettes retirent des vélos près des bureaux du centre-ville (King St W / Bay St, Temperance St, Front St W). Elles en retirent aussi à deux quais de traversier des îles. Elles apportent des vélos dans les rues résidentielles (365 Lippincott St, 144 Harrison St) et au quai de traversier de Centre Island. C’est le schéma d’une journée de travail : les vélos s’accumulent au centre-ville, et les équipes les ramènent.

Définir le problème

Avant de créer la table des stations, Tomasz note exactement ce qu’elle prédit. Voici les questions de Caractéristiques et moment de la cible :

• L’unité : une station lors d’un matin de jour ouvrable.
• La cible : la sortie nette de la station de 7 h à 10 h : les départs moins les arrivées des cyclistes, selon les trajets propres. Une station avec 30 départs et 5 arrivées perd 25 vélos; sa sortie nette est donc de 25. Une station qui gagne des vélos a une sortie nette négative.
• Le moment de la prédiction : 22 h la veille, quand l’équipe part.
• Ce qui est connu à 22 h : tous les trajets jusque-là, y compris ceux de ce matin. Le lendemain matin n’est pas connu.
• La décision : classer les stations et réapprovisionner les 20 premières (K = 20).

Deux fuites sont tentantes, et toutes deux utilisent les données de demain. La moyenne d’une station sur le mois entier inclut les matins que nous prédisons. Une moyenne des « cinq derniers jours, aujourd’hui compris » inclut la cible elle-même quand « aujourd’hui » désigne le matin cible. Les caractéristiques doivent utiliser uniquement les jours précédents.

Pourquoi de 7 h à 10 h? C’est la période des déplacements vers le travail. Les vélos laissés par l’équipe la nuit doivent suffire pendant cette période. Pourquoi la sortie nette plutôt que les départs? Une station avec 40 départs et 38 arrivées reste pleine. Une station avec 25 départs et aucune arrivée se vide.

Construire la table des stations

Le piège : un jour manquant

Le premier essai naturel est une table avec une ligne pour chaque station et chaque matin où des trajets ont eu lieu. LAG(net_out) donnerait alors « hier ». Mais un matin calme, sans aucun trajet, n’a pas de ligne. Voici la station A, sans ligne le 4 juin :

tiny.executescript("""
CREATE TABLE mornings (station_id TEXT, day TEXT, net_out INTEGER);
INSERT INTO mornings VALUES ('A', '2025-06-02', 5), ('A', '2025-06-03', 6), ('A', '2025-06-05', 9);
""")
sql("""
SELECT station_id, day, net_out,
       LAG(day) OVER (PARTITION BY station_id ORDER BY day) AS row_before,
       LAG(net_out) OVER (PARTITION BY station_id ORDER BY day) AS yesterday_wrong
FROM mornings
""", db=tiny)

Le résultat :

station_id        day net_out row_before yesterday_wrong
         A 2025-06-02       5       NULL            NULL
         A 2025-06-03       6 2025-06-02               5
         A 2025-06-05       9 2025-06-03               6
(3 rows)

Ce que cela nous apprend. Le 5 juin, la ligne précédente est celle du 3 juin. La valeur appelée yesterday_wrong (hier, incorrect) date donc de deux jours. LAG signifie « la ligne précédente », pas « le jour précédent ». Il ne donne aucun avertissement quand un jour manque.

La solution est une grille : chaque station pour chaque jour, créée avec CROSS JOIN. Un matin sans trajet reçoit un 0. Les départs sont comptés selon l’heure de début, et les arrivées selon l’heure de fin. Chacun utilise l’intervalle semi-ouvert de 7 h à 10 h, sans inclure 10 h, comme dans Le temps et le classement. Un jour ouvrable va du lundi au vendredi; juin 2025 ne compte aucun jour férié en Ontario.

con.executescript("""
DROP TABLE IF EXISTS morning;
CREATE TABLE morning AS
WITH days AS (
    SELECT DISTINCT date(start_time) AS day FROM clean
),
grid AS (                -- every station on every day, so that no day is missing
    SELECT s.station_id, d.day FROM stations AS s CROSS JOIN days AS d
),
departures AS (
    SELECT start_id AS station_id, date(start_time) AS day, COUNT(*) AS n FROM clean
    WHERE time(start_time) >= '07:00:00' AND time(start_time) < '10:00:00'
    GROUP BY 1, 2
),
arrivals AS (
    SELECT end_id AS station_id, date(end_time) AS day, COUNT(*) AS n FROM clean
    WHERE time(end_time) >= '07:00:00' AND time(end_time) < '10:00:00'
    GROUP BY 1, 2
)
SELECT g.station_id, g.day,
       CAST(strftime('%w', g.day) AS INTEGER) BETWEEN 1 AND 5 AS working,
       COALESCE(d.n, 0) AS departures,
       COALESCE(a.n, 0) AS arrivals,
       COALESCE(d.n, 0) - COALESCE(a.n, 0) AS net_out
FROM grid AS g
LEFT JOIN departures AS d ON d.station_id = g.station_id AND d.day = g.day
LEFT JOIN arrivals AS a ON a.station_id = g.station_id AND a.day = g.day;
""")
sql("""
SELECT COUNT(*) AS station_days, COUNT(DISTINCT station_id) AS stations, COUNT(DISTINCT day) AS days,
       SUM(departures + arrivals = 0) AS quiet_mornings, SUM(departures) AS departures, SUM(arrivals) AS arrivals
FROM morning
""")

Le résultat :

station_days stations days quiet_mornings departures arrivals
       27480      916   30           6777     129217   124284
(1 row)

Ce que cela nous apprend. La grille contient 27 480 lignes : 916 stations × 30 jours, sans jour manquant. Pour 6 777 combinaisons station-matin, rien ne s’est passé, et la grille les conserve avec un 0. Il y a plus de départs que d’arrivées (129 217 contre 124 284). Certains trajets du matin se terminent en effet après 10 h.

À quelle fréquence la table sans grille aurait-elle trompé LAG? Tomasz la reconstruit à partir des matins avec un trajet. Il compte les lignes dont la ligne précédente ne correspond pas au jour précédent :

sql("""
WITH sparse AS (        -- the table without the grid: only mornings with a trip
    SELECT * FROM morning WHERE departures + arrivals > 0
),
stepped AS (
    SELECT day, LAG(day) OVER (PARTITION BY station_id ORDER BY day) AS row_before
    FROM sparse
)
SELECT COUNT(*) AS rows_with_a_row_before,
       SUM(julianday(day) - julianday(row_before) > 1) AS row_before_is_not_yesterday
FROM stepped WHERE row_before IS NOT NULL
""")

Le résultat :

rows_with_a_row_before row_before_is_not_yesterday
                 19798                        1766
(1 row)

Ce que cela nous apprend. Sans la grille, 1 766 valeurs appelées « hier » viendraient d’un jour plus ancien. Rien dans le résultat ne le montrerait. Les stations calmes, où l’erreur se produit, sont celles qui intéressent le moins la personne responsable de la planification. Cela rend l’erreur encore plus difficile à repérer lors d’une vérification ponctuelle.

Explorer les besoins de la décision

Tomasz pose deux questions nécessaires au plan, et aucune autre. Il regarde uniquement les semaines 1 à 3, car la semaine 4 sert au test. S’il regardait la semaine de test maintenant, ses choix pourraient s’y adapter par accident.

Première question : les pertes du matin sont-elles un problème tous les jours? Pour chaque matin, il additionne les vélos perdus par les stations qui en perdent. Avec deux valeurs, MAX(net_out, 0) n’est pas une fonction d’agrégation : elle retourne la plus grande des deux. Une station qui gagne des vélos compte donc pour 0 :

sql("""
WITH per_morning AS (
    SELECT day, working, SUM(MAX(net_out, 0)) AS drained_bikes, SUM(net_out > 0) AS draining_stations
    FROM morning
    WHERE day < '2025-06-23'          -- explore weeks 1 to 3 only: week 4 is the test
    GROUP BY day
)
SELECT CASE working WHEN 1 THEN 'working day' ELSE 'weekend' END AS morning,
       COUNT(*) AS mornings,
       ROUND(AVG(drained_bikes)) AS drained_bikes,
       ROUND(AVG(draining_stations)) AS draining_stations
FROM per_morning GROUP BY working
""")

Le résultat :

    morning mornings drained_bikes draining_stations
    weekend        7         898.0             322.0
working day       15        2153.0             407.0
(2 rows)

Ce que cela nous apprend. Un matin de jour ouvrable, environ 407 stations perdent des vélos, soit 2 153 vélos au total. Un matin de fin de semaine, c’est 898 vélos, soit moins de la moitié. Les pertes liées aux déplacements vers le travail concernent les jours ouvrables. La table des stations conserve donc uniquement les jours ouvrables. Les nuits de fin de semaine demandent un autre plan, qui dépasse cette étude de cas.

Deuxième question : les pertes sont-elles réparties, ou se concentrent-elles dans quelques stations? Et les équipes desservent-elles déjà les stations qui perdent le plus de vélos? moved_in_per_morning compte les vélos déplacés qu’un cycliste a pris à cette station avant 10 h un jour ouvrable. Ce nombre est divisé par les 15 matins de jours ouvrables :

sql("""
WITH ranked AS (
    SELECT day, net_out, ROW_NUMBER() OVER (PARTITION BY day ORDER BY net_out DESC, station_id) AS rank
    FROM morning WHERE working = 1 AND day < '2025-06-23'
)
SELECT ROUND(1.0 * SUM(CASE WHEN rank <= 20 THEN net_out END) / SUM(MAX(net_out, 0)), 3) AS top_20_share
FROM ranked
""")
sql("""
WITH moved_in AS (       -- moved bikes that a rider took before 10:00 on a working day
    SELECT to_id AS station_id, COUNT(*) AS bikes FROM moves
    WHERE time(next_start) < '10:00:00' AND next_start < '2025-06-23'
      AND CAST(strftime('%w', next_start) AS INTEGER) BETWEEN 1 AND 5
    GROUP BY to_id
)
SELECT m.station_id, s.name, ROUND(AVG(m.net_out), 1) AS mean_net_out,
       ROUND(COALESCE(MAX(v.bikes), 0) / 15.0, 1) AS moved_in_per_morning
FROM morning AS m
JOIN stations AS s USING (station_id)
LEFT JOIN moved_in AS v USING (station_id)
WHERE m.working = 1 AND m.day < '2025-06-23'
GROUP BY m.station_id ORDER BY mean_net_out DESC LIMIT 8
""")

Le résultat :

top_20_share
       0.194
(1 row)
station_id                               name mean_net_out moved_in_per_morning
      7000       Fort York  Blvd / Capreol Ct         27.6                  0.3
      7682           Bathurst St / Front St W         26.7                  0.2
      7451 Western Battery Rd / Pirandello St         23.6                  1.5
      7887               800 Fleet St (North)         20.8                 21.7
      7524                        Lisgar Park         20.7                  1.3
      7549                  439 Sherbourne St         18.6                 11.0
      7249                    144 Harrison St         18.3                 19.4
      7042     Sherbourne St / Wellesley St E         18.1                 10.6
(8 rows)

Ce que cela nous apprend. Un matin de jour ouvrable, les 20 premières stations représentent 19,4 % de tous les vélos perdus. C’est environ un cinquième du problème, dans 20 des quelque 407 stations qui perdent des vélos : une sur 20. Il vaut donc la peine de bien préparer cette liste de 20 stations. Les premières stations sont dans des secteurs résidentiels autour du centre-ville, où beaucoup de gens vont travailler à vélo. Les équipes desservent déjà bien certaines d’entre elles. 800 Fleet St (North) reçoit 21,7 vélos déplacés par matin, et 144 Harrison St en reçoit 19,4. Mais regardez les deux stations en tête : Fort York Blvd / Capreol Ct et Bathurst St / Front St W. Chacune perd environ 27 vélos par matin et ne reçoit presque aucun vélo déplacé. C’est la première découverte pour Leila.

Des caractéristiques tirées uniquement des jours précédents

Chaque caractéristique est une valeur connue à 22 h la veille. La table des stations en contient quatre. Chacune est une fonction de fenêtre sur les lignes d’une même station :

Caractéristique	Signification	Fenêtre
yesterday	Sortie nette du jour civil précédent	LAG(net_out, 1) sur tous les jours
same_day_last_week	Sortie nette 7 jours avant (même jour de la semaine)	LAG(net_out, 7) sur tous les jours
last_working_day	Sortie nette du jour ouvrable précédent	LAG(net_out, 1) sur les jours ouvrables seulement
mean_5_working	Sortie nette moyenne des 5 jours ouvrables précédents	AVG avec le cadre de 5 PRECEDING à 1 PRECEDING

Les deux types de fenêtres sont au cœur de cette étape. Dans le premier SELECT (l’étape calendar), la fenêtre voit tous les jours. La ligne précédente correspond donc au jour précédent. Le SELECT externe contient WHERE working = 1, et SQL applique WHERE avant les fonctions de fenêtre. Cette fenêtre voit donc uniquement les jours ouvrables. La ligne précédente correspond au jour ouvrable précédent : le lundi, c’est le vendredi.

Le cadre ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING définit les lignes que AVG utilise. Ce sont les cinq lignes avant la ligne courante, et pas la ligne courante. L’arrêt à 1 PRECEDING empêche la cible d’entrer dans sa propre caractéristique. history_days compte les lignes réellement présentes dans le cadre, car les premières lignes de juin en ont moins de cinq.

con.executescript("""
DROP TABLE IF EXISTS station_day;
CREATE TABLE station_day AS
WITH calendar AS (       -- windows over every day: one row back = the day before
    SELECT *,
           LAG(net_out, 1) OVER (PARTITION BY station_id ORDER BY day) AS yesterday,
           LAG(net_out, 7) OVER (PARTITION BY station_id ORDER BY day) AS same_day_last_week
    FROM morning
)
SELECT station_id, day, net_out, yesterday, same_day_last_week,
       LAG(net_out, 1) OVER w AS last_working_day,
       AVG(net_out) OVER (w ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS mean_5_working,
       COUNT(net_out) OVER (w ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS history_days
FROM calendar
WHERE working = 1        -- windows over working days only: one row back = the working day before
WINDOW w AS (PARTITION BY station_id ORDER BY day);
""")
sql("""
SELECT day, net_out, yesterday, same_day_last_week, last_working_day,
       ROUND(mean_5_working, 1) AS mean_5_working, history_days
FROM station_day WHERE station_id = '7000' AND day >= '2025-06-02' AND day < '2025-06-11'
""")

Le résultat :

       day net_out yesterday same_day_last_week last_working_day mean_5_working history_days
2025-06-02      18         3               NULL             NULL           NULL            0
2025-06-03      33        18               NULL               18           18.0            1
2025-06-04      40        33               NULL               33           25.5            2
2025-06-05      27        40               NULL               40           30.3            3
2025-06-06      18        27               NULL               27           29.5            4
2025-06-09      19         0                 18               18           27.2            5
2025-06-10      37        19                 33               19           27.4            5
(7 rows)

Ce que cela nous apprend. Vous pouvez vérifier chaque valeur à la main. Cette station, Fort York Blvd / Capreol Ct, a perdu 18, 33, 40, 27 et 18 vélos pendant la première semaine ouvrable. Le lundi 9 juin, yesterday vaut 0 : c’est le dimanche, sans déplacements vers le travail. last_working_day vaut 18, la valeur du vendredi 6 juin. same_day_last_week vaut 18, celle du lundi précédent. mean_5_working vaut (18 + 33 + 40 + 27 + 18) ÷ 5 = 27,2. La valeur réelle ce lundi était de 19. « Comme hier » indiquait 0 et aurait donc exclu de la liste l’une des stations les plus occupées. Pendant la semaine 1, history_days passe de 0 à 4, et la moyenne utilise seulement les lignes disponibles. C’est pourquoi la semaine 1 ne sert à calculer le score d’aucune règle.

Séparation et références de base

La règle classera les stations pour les matins futurs. La séparation se fait donc selon le temps, comme dans Choisir une séparation. La semaine 1 fournit seulement l’historique : ses lignes n’ont pas cinq jours avant elles. Les semaines 2 et 3 servent à choisir la règle. La semaine 4, du 23 au 30 juin, est le test, utilisé une seule fois à la fin.

VALID = ("2025-06-09", "2025-06-21")   # weeks 2 and 3: choose the rule here
TEST = ("2025-06-23", "2025-07-01")    # week 4 (23 to 30 June): used once, at the end
sql("""
SELECT CASE WHEN day < '2025-06-09' THEN 'week 1 (history only)'
            WHEN day < '2025-06-21' THEN 'weeks 2-3 (choose)'
            ELSE 'week 4 (test)' END AS part,
       COUNT(DISTINCT day) AS mornings, MIN(day) AS first, MAX(day) AS last,
       SUM(history_days < 5) AS rows_without_5_days_of_history
FROM station_day GROUP BY part ORDER BY first
""")

Le résultat :

                 part mornings      first       last rows_without_5_days_of_history
week 1 (history only)        5 2025-06-02 2025-06-06                           4580
   weeks 2-3 (choose)       10 2025-06-09 2025-06-20                              0
        week 4 (test)        6 2025-06-23 2025-06-30                              0
(3 rows)

Ce que cela nous apprend. Il y a 10 matins pour choisir et 6 pour le test. La semaine 1 contient 5 matins et 4 580 lignes sans cinq jours d’historique (916 × 5). Aucune règle ne reçoit de score à partir de ces lignes.

Comment évaluer un classement

Qu’est-ce qu’une bonne liste? Chaque matin, la meilleure liste possible contient les 20 stations qui ont réellement perdu le plus de vélos. Personne ne la connaît à 22 h, mais nous la connaissons après coup. Tomasz évalue donc une règle selon les vélos réellement perdus par ses 20 stations, comparés aux 20 meilleures stations possibles. Une station qui a gagné des vélos compte pour 0, car la réapprovisionner n’a aidé personne. Il appelle ce résultat la part du meilleur résultat possible : 1,0 signifie une liste parfaite.

coverage() fait cela en SQL. ROW_NUMBER() classe les stations deux fois chaque matin : selon la règle, puis selon la valeur réelle. station_id départage les égalités, donc le résultat reste identique à chaque exécution. La règle est un nom de colonne. Elle ne peut donc pas être un paramètre :name, comme les valeurs dans Une extraction reproductible. La fonction accepte seulement un nom simple, pour assurer la sécurité de la requête.

K = 20   # stations the night crew can restock


def coverage(rule, period, k=K, table="station_day"):
    """Rank the stations each morning by `rule` (a column), take the top k, and
    compare the bikes they lost 7:00-10:00 with the best possible top k."""
    if not (rule.isidentifier() and table.isidentifier()):
        raise ValueError("rule and table must be column and table names")
    query = f"""
        WITH ranked AS (
            SELECT day, net_out,
                   ROW_NUMBER() OVER (PARTITION BY day ORDER BY {rule} DESC, station_id) AS plan_rank,
                   ROW_NUMBER() OVER (PARTITION BY day ORDER BY net_out DESC, station_id) AS best_rank
            FROM {table} WHERE day >= :first AND day < :end
        )
        SELECT COUNT(DISTINCT day) AS mornings,
               SUM(CASE WHEN plan_rank <= :k THEN MAX(net_out, 0) ELSE 0 END) AS covered,
               SUM(CASE WHEN best_rank <= :k THEN MAX(net_out, 0) ELSE 0 END) AS best,
               SUM(plan_rank <= :k AND best_rank <= :k) AS hits
        FROM ranked"""
    row = con.execute(query, {"first": period[0], "end": period[1], "k": k}).fetchone()
    mornings, covered, best, hits = row
    return {"rule": rule, "mornings": mornings, "covered": covered, "best": best,
            "share": round(covered / best, 3), "hits_per_morning": round(hits / mornings, 1)}

Les règles sur les semaines 2 et 3

Les quatre caractéristiques forment quatre règles. « Comme hier » est la méthode actuelle de Leila : c’est donc la référence de base à battre. « Le dernier jour ouvrable » en est une version plus juste. La moyenne de cinq jours ouvrables est la règle candidate :

RULES = ["yesterday", "last_working_day", "same_day_last_week", "mean_5_working"]
valid_results = pd.DataFrame([coverage(rule, VALID) for rule in RULES])
print(valid_results.to_string(index=False))

Le résultat :

              rule  mornings  covered  best  share  hits_per_morning
         yesterday        10     3292  4217  0.781              10.2
  last_working_day        10     3474  4217  0.824              10.9
same_day_last_week        10     3541  4217  0.840              11.9
    mean_5_working        10     3655  4217  0.867              12.0

Vélos couverts par les 20 premières stations de chaque règle, en proportion du meilleur résultat possible avec 20 stations, sur les 10 matins de jours ouvrables des semaines 2 et 3. La moyenne des cinq derniers jours ouvrables est la plus élevée.

Description textuelle du graphique

Un graphique à quatre colonnes, sur une échelle de 0 à 1. Hier : 0,78. Jour ouvrable (le jour ouvrable précédent) : 0,82. Semaine précédente (le même jour de la semaine, 7 jours avant) : 0,84. Moyenne sur 5 jours (la moyenne des 5 jours ouvrables précédents), dans une autre couleur : 0,87.

Ce que cela nous apprend. « Comme hier » couvre 0,781 du meilleur résultat possible. La moyenne de cinq jours ouvrables couvre 0,867 : 3 655 vélos contre 3 292 sur 10 matins. Cela représente environ 36 vélos de plus chaque matin. Chaque étape vers le haut du tableau élimine une faiblesse. « Le dernier jour ouvrable » cesse de comparer un lundi à un dimanche. « La semaine précédente » compare des jours semblables. La moyenne sur cinq jours atténue aussi l’effet d’un matin inhabituel, comme un jour de pluie ou un événement. Un seul jour varie beaucoup; une moyenne de plusieurs jours est plus stable.

Tomasz note aussi ce que la règle ne couvre pas. Même la meilleure règle laisse environ 13 % du meilleur résultat possible sans couverture. En moyenne, 12 de ses 20 stations figurent parmi les 20 premières réelles. Les 8 autres perdent aussi des vélos, mais moins.

À vous : une mémoire plus courte est-elle meilleure?

Créez une colonne mean_3_working, la moyenne des 3 jours ouvrables précédents, et évaluez-la sur les semaines 2 et 3. Faites d’abord une prédiction : plus ou moins que 0,867? (Dans le carnet Jupyter, placez le résultat dans mean_3_result.)

con.executescript("""
DROP TABLE IF EXISTS station_day_3;
CREATE TABLE station_day_3 AS
SELECT *,
       AVG(net_out) OVER (PARTITION BY station_id ORDER BY day
                          ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS mean_3_working
FROM station_day;
""")
mean_3_result = coverage("mean_3_working", VALID, table="station_day_3")
print(mean_3_result)

{'rule': 'mean_3_working', 'mornings': 10, 'covered': 3513, 'best': 4217, 'share': 0.833, 'hits_per_morning': 10.9}

Trois jours couvrent 0,833, moins que cinq jours (0,867). Une mémoire plus courte réagit plus vite, mais un jour inhabituel pèse davantage. Tomasz garde cinq jours. Il a fait cet essai sur les semaines 2 et 3, pas sur la semaine de test. Le test reste donc propre.

Erreur courante

Une caractéristique qui connaît la réponse

Symptôme. Le collègue de Tomasz propose deux caractéristiques « meilleures » : la moyenne de chaque station sur juin, et une moyenne des cinq derniers jours, aujourd’hui compris. Les deux obtiennent un meilleur score que la règle honnête.

con.executescript("""
DROP TABLE IF EXISTS station_day_leaky;
CREATE TABLE station_day_leaky AS
SELECT *,
       AVG(net_out) OVER (PARTITION BY station_id) AS june_mean,
       AVG(net_out) OVER (PARTITION BY station_id ORDER BY day
                          ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS mean_5_with_today
FROM station_day;
""")
leaky = pd.DataFrame([coverage(rule, VALID, table="station_day_leaky")
                      for rule in ["mean_5_working", "june_mean", "mean_5_with_today"]])
print(leaky.to_string(index=False))

Le résultat :

             rule  mornings  covered  best  share  hits_per_morning
   mean_5_working        10     3655  4217  0.867              12.0
        june_mean        10     3745  4217  0.888              12.8
mean_5_with_today        10     3858  4217  0.915              13.6

Cause. Les deux caractéristiques contiennent la réponse. La moyenne de juin inclut tous les matins cibles de juin, même ceux qu’elle prédit. Le cadre 4 PRECEDING AND CURRENT ROW inclut la ligne courante, et le net_out de cette ligne est la cible. À 22 h la veille, aucune de ces valeurs n’existe. C’est une fuite de données : une caractéristique qui utilise de l’information provenant d’après le moment de la prédiction.

Correction. Notez le moment de la prédiction, puis vérifiez que la fenêtre de chaque caractéristique se termine avant : 1 PRECEDING, pas CURRENT ROW. Ne calculez jamais une moyenne sur une période qui contient les lignes que vous évaluez.

Conséquence. Sur papier, la règle avec fuite semble meilleure de 5 points (0,915 contre 0,867). La nuit venue, elle ne peut pas être calculée, et la vraie liste serait moins bonne que promis. Un score qui semble trop bon justifie de vérifier d’abord le moment des données de la caractéristique.

Le test final, une seule fois

Le choix repose sur les semaines 2 et 3 : mean_5_working, les 20 premières stations. Tomasz applique maintenant les quatre règles à la semaine 4, encore jamais utilisée :

test_results = pd.DataFrame([coverage(rule, TEST) for rule in RULES])
print(test_results.to_string(index=False))

La sortie :

              rule  mornings  covered  best  share  hits_per_morning
         yesterday         6     1732  2400  0.722               9.0
  last_working_day         6     1887  2400  0.786              10.0
same_day_last_week         6     1913  2400  0.797              10.5
    mean_5_working         6     1999  2400  0.833              11.5

Ce que cela nous apprend. Pendant la semaine de test, la moyenne sur cinq jours couvre 0,833 du meilleur résultat possible. Cela représente 1 999 vélos sur 6 matins, contre 1 732 pour « comme hier » (0,722). C’est environ 45 vélos de plus au bon endroit chaque matin. L’ordre des quatre règles reste le même qu’aux semaines 2 et 3. Le choix tient donc toujours. Toutes les proportions sont un peu plus faibles qu’avant. C’est normal pour des données qui n’ont pas servi au choix.

D’où vient le gain? Tomasz évalue les trois règles principales pour chaque matin de test :

def next_day(day):
    return (pd.Timestamp(day) + pd.Timedelta(days=1)).strftime("%Y-%m-%d")


test_days = pd.read_sql("SELECT DISTINCT day FROM station_day WHERE day >= :first AND day < :end",
                        con, params={"first": TEST[0], "end": TEST[1]})["day"]
by_day = pd.DataFrame([{"day": day, "weekday": pd.Timestamp(day).day_name()[:3],
                        **{rule: coverage(rule, (day, next_day(day)))["share"]
                           for rule in ["yesterday", "last_working_day", "mean_5_working"]}}
                       for day in test_days])
print(by_day.to_string(index=False))

La sortie :

       day weekday  yesterday  last_working_day  mean_5_working
2025-06-23     Mon      0.407             0.685           0.818
2025-06-24     Tue      0.868             0.868           0.872
2025-06-25     Wed      0.852             0.852           0.877
2025-06-26     Thu      0.812             0.812           0.864
2025-06-27     Fri      0.732             0.732           0.794
2025-06-30     Mon      0.528             0.694           0.698

• Comme hier
• Dernier jour ouvrable (pointillés)
• Moyenne de 5 jours ouvrables

Proportion du meilleur résultat possible couverte chaque matin de test. « Comme hier » baisse les deux lundis; du mardi au vendredi, elle égale « dernier jour ouvrable ».

Description textuelle du graphique

Un graphique linéaire avec six matins : lundi 23, mardi 24, mercredi 25, jeudi 26, vendredi 27 et lundi 30 juin. Comme hier : 0,41, 0,87, 0,85, 0,81, 0,73, 0,53. Dernier jour ouvrable : 0,69, 0,87, 0,85, 0,81, 0,73, 0,69. Moyenne de 5 jours ouvrables : 0,82, 0,87, 0,88, 0,86, 0,79, 0,70. Du mardi au vendredi, les deux premières lignes se superposent. La moyenne est la plus élevée chaque matin, avec un écart inférieur à 0,01 le mardi 24 et le lundi 30.

Ce que cela nous apprend. Le gain par rapport à « comme hier » vient surtout des lundis, où « hier » est un dimanche. Le 23 juin, cette règle couvre 0,407, contre 0,818 pour la moyenne. Cette partie du gain vient simplement d’une bonne prise en compte du calendrier. La référence de base plus juste est « le dernier jour ouvrable ». La moyenne la dépasse les 6 matins, mais de moins de 0,01 pour deux d’entre eux. Le lundi 30 juin est difficile pour toutes les règles. C’est la veille de la fête du Canada, et plusieurs personnes ont peut-être pris une longue fin de semaine.

Soyez honnête sur la quantité de preuves : 6 matins de test, dans un seul mois, en été. Le résultat va dans le même sens qu’aux semaines 2 et 3, ce qui le rend plus crédible. Il ne garantit toutefois rien pour novembre.

À vous de jouer : changez une chose, une troisième camionnette

Leila pourrait obtenir une troisième camionnette et réapprovisionner 40 stations. Évaluez « comme hier » et la moyenne sur cinq jours pendant la semaine de test avec k=40. Faites d’abord une prédiction : l’écart augmente-t-il ou diminue-t-il? (Dans le carnet Jupyter, le résultat va dans k40_results.)

k40_results = pd.DataFrame([coverage(rule, TEST, k=40) for rule in ["yesterday", "mean_5_working"]])
print(k40_results.to_string(index=False))

          rule  mornings  covered  best  share  hits_per_morning
     yesterday         6     2890  4027  0.718              19.8
mean_5_working         6     3330  4027  0.827              24.3

Avec 40 stations, la moyenne couvre 0,827 contre 0,718 : l’écart reste d’environ 11 points. La moyenne couvre 3 330 vélos sur 6 matins, soit 440 de plus qu’avec « comme hier ». Une équipe plus grande ne réduit pas l’importance du choix de la règle.

Vérifications avant la livraison

La table des stations servira chaque nuit. Elle reçoit donc des règles, comme dans Les règles de validation. Chaque règle est une requête qui ne doit retourner aucune ligne. Une ligne par règle indique ok (réussite) ou FAIL (échec) :

rules = {
    "a station-day appears twice":
        "SELECT station_id, day FROM morning GROUP BY 1, 2 HAVING COUNT(*) > 1",
    "the grid is not stations x days":
        "SELECT 1 WHERE (SELECT COUNT(*) FROM morning) <> "
        "(SELECT COUNT(*) FROM stations) * (SELECT COUNT(DISTINCT day) FROM morning)",
    "a trip is lost or counted twice":
        "SELECT 1 WHERE (SELECT COUNT(*) FROM clean) + (SELECT COUNT(*) FROM quarantine) <> (SELECT COUNT(*) FROM trips_raw)",
    "net_out is not departures - arrivals":
        "SELECT * FROM morning WHERE net_out <> departures - arrivals",
    "an evaluated row has less than 5 days of history":
        "SELECT * FROM station_day WHERE day >= '2025-06-09' AND history_days < 5",
    "a weekend morning is in the station table":
        "SELECT * FROM station_day WHERE CAST(strftime('%w', day) AS INTEGER) NOT BETWEEN 1 AND 5",
}
for name, query in rules.items():
    bad = len(con.execute(query).fetchall())
    print(f"{'FAIL' if bad else 'ok  '}  {name}" + (f": {bad} rows" if bad else ""))

La sortie :

ok    a station-day appears twice
ok    the grid is not stations x days
ok    a trip is lost or counted twice
ok    net_out is not departures - arrivals
ok    an evaluated row has less than 5 days of history
ok    a weekend morning is in the station table

Ce que cela nous apprend. Toutes les règles passent. La clé (station, jour) est unique, la grille est complète et aucun trajet n’a disparu entre les données brutes et nettoyées. Le calcul de la cible est identique pour chaque ligne. Chaque ligne évaluée possède cinq jours d’historique, et la table ne contient aucun jour de fin de semaine. Si un fichier ultérieur enfreint une règle, la ligne indique FAIL avec le nombre de lignes concernées. Le plan ne doit alors pas être envoyé.

Le plan de nuit

Les données se terminent le lundi 30 juin. Le mardi 1er juillet est la fête du Canada, un jour férié. Le prochain matin ouvrable est donc le mercredi 2 juillet. Son plan utilise les 5 derniers jours ouvrables, du 24 au 30 juin. Tomasz écrit le plan et toute la table des stations dans des fichiers CSV :

plan = pd.read_sql("""
WITH last_5 AS (         -- the 5 working days before Wednesday 2 July (1 July is Canada Day)
    SELECT DISTINCT day FROM station_day ORDER BY day DESC LIMIT 5
)
SELECT m.station_id, s.name, ROUND(AVG(m.net_out), 1) AS expected_net_out
FROM station_day AS m JOIN stations AS s USING (station_id)
WHERE m.day IN (SELECT day FROM last_5)
GROUP BY m.station_id ORDER BY expected_net_out DESC, m.station_id LIMIT 20
""", con)
plan.insert(0, "rank", range(1, len(plan) + 1))
plan.to_csv("night_plan_2025-07-02.csv", index=False)
pd.read_sql("SELECT * FROM station_day ORDER BY day, station_id", con).to_csv("station_day.csv", index=False)
print(plan.head(8).to_string(index=False))
print("station_day.csv:", scalar("SELECT COUNT(*) FROM station_day"), "rows")

La sortie :

 rank station_id                               name  expected_net_out
    1       7000       Fort York  Blvd / Capreol Ct              28.2
    2       7682           Bathurst St / Front St W              26.2
    3       7451 Western Battery Rd / Pirandello St              22.0
    4       7887               800 Fleet St (North)              21.0
    5       7239   Bloor St W / Manning Ave - SMART              19.6
    6       7249                    144 Harrison St              19.6
    7       7100     Dundas St E / Regent Park Blvd              17.8
    8       7524                        Lisgar Park              17.8
station_day.csv: 19236 rows

Ce que cela nous apprend. Voici la liste pour l’équipe, classée selon le nombre de vélos que chaque station devrait perdre. Les deux premières sont les stations repérées pendant l’exploration, que les équipes desservent rarement actuellement. station_day.csv contient 19 236 lignes (916 stations × 21 jours ouvrables), avec la cible et les quatre variables. Cette table soutient le plan. Elle est prête à servir à un modèle dans un cours ultérieur.

Recommandations

Tomasz écrit à Leila avec les mots qu’elle utilise :

• Utilisez la liste sur cinq jours. Chaque soir, classez les stations selon leur perte matinale moyenne sur les cinq derniers jours ouvrables. Réapprovisionnez les 20 premières. Pendant la semaine de test, cette liste a placé 1 999 vélos au bon endroit sur six matins. C’est 1 732 avec « les stations qui se sont vidées ce matin ».
• Ne planifiez jamais un lundi à partir d’un dimanche. Si vous gardez une règle sur un jour, utilisez le dernier jour ouvrable. Cela suffit à corriger la majeure partie de l’écart les lundis.
• Examinez les deux premières dès maintenant. Fort York Blvd / Capreol Ct et Bathurst St / Front St W perdent environ 27 vélos chaque matin ouvrable. Elles ne reçoivent presque aucun vélo déplacé. Vérifiez si elles se vident et pourquoi les équipes ne les visitent pas.
• Planifiez séparément les jours fériés et les fins de semaine. La liste concerne les matins ouvrables. Un matin férié, comme le 1er juillet, exige son propre plan.
• Corrigez l’exportation. Demandez à l’équipe des données de remplir End_Station_Name à partir de la station d’arrivée. D’ici là, prenez les noms dans la liste des stations, jamais dans cette colonne.

Et voici à quoi la liste ne doit pas servir : elle indique quelles stations perdent le plus de vélos, pas combien apporter. Il faudrait connaître le nombre de vélos dans chaque station à 22 h. Ces données ne le fournissent pas.

Limites

• Les départs ne représentent pas la demande. Une personne qui trouve une station vide ne laisse aucune ligne. Pour une station qui se vide tôt, les données montrent moins de départs que les gens en souhaitaient. La perte réelle est donc supérieure à la cible.
• Aucun état des stations. Le fichier ne contient ni capacité ni nombre de vélos à 22 h. Une station qui perd 25 vélos, mais en contient 40 la nuit, n’a pas besoin de camionnette. Le flux de données en direct du système (GBFS, General Bikeshare Feed Specification) fournit ces renseignements. Ce jeu de données n’en contient toutefois aucun historique.
• Un seul mois, en été. Les semaines 2 à 4 de juin 2025. La météo, les vacances scolaires et les événements changent les habitudes. Il faut vérifier la règle à nouveau chaque saison.
• Les déplacements sont déduits. Un déplacement sans cycliste peut venir d’une camionnette, d’une réparation ou d’un enregistrement manquant. Le fichier ne permet pas de les distinguer.
• Un petit test. Six matins, dont un lundi inhabituel. La tendance est claire; l’ampleur exacte du gain ne l’est pas.
• Un classement, pas un modèle. La règle est une moyenne. Un modèle qui utilise la météo ou les événements pourrait faire mieux. Ce sera pour le cours d’apprentissage automatique.

Faites-en un projet pour votre portfolio

Cette étude de cas montre à un employeur que vous savez repérer les problèmes d’une exportation réelle désordonnée. Elle montre aussi que vous savez construire une table vérifiée qui soutient une décision. Personnalisez-la avec un changement : un autre mois, les données ouvertes d’une autre ville, ou la pointe du soir plutôt que du matin. Rendez-la ensuite publique et facile à lire en cinq minutes.

Quoi mettre dans le dépôt :

• README.md, la page que les gens lisent en premier (modèle ci-dessous).
• Le carnet Jupyter, exécuté du début à la fin, avec ses sorties.
• queries/ : les principales étapes SQL sous forme de fichiers .sql (stations, trajets typés, quarantaine, déplacements, grille matinale, table des stations, vérifications). Ainsi, une personne peut les exécuter sans Python.
• station_day.csv et night_plan_2025-07-02.csv, les deux fichiers livrés.
• DATASET.md : source, licence et attribution, changement d’encodage, défauts trouvés, nombres de trajets en quarantaine et limites. Suivez Suivre la provenance des données.
• Pas les données elles-mêmes : la licence le permet, mais un lien et une somme de contrôle suffisent. Le fichier fait 28 Mo.

Un modèle de README :

# Which bike-share stations to restock overnight

## Problem
A night crew can restock 20 stations before the morning peak. Which 20?
Current rule: the stations that emptied this morning ("same as yesterday").

## Data and licence
Bike Share Toronto Ridership Data, June 2025, 980,624 trips (City of Toronto).
Contains information licensed under the Open Government Licence - Toronto.
Changes: cp1252 to UTF-8.

## What was wrong with the data
End_Station_Name copies the start name in 100% of rows (names taken from the
start side). 5,754 trips quarantined with a reason (no end, 0 s, false starts).
60,554 trips (6.2%) follow a move with no rider, found with LAG per bike.

## Approach
SQLite. A complete station x day grid (a sparse table gave 1,766 wrong
"yesterday" values). Target: net outflow 7:00-10:00 on working days.
Features from earlier days only. Rule chosen on weeks 2-3, tested once on week 4.

## Results (week 4, 6 mornings, top 20)
Mean of the last 5 working days: 0.833 of the best possible (1,999 bikes)
Same as yesterday: 0.722 (1,732 bikes). Most of the gain is on Mondays.

## Limits
Departures are not demand; no dock status; one summer month; small test.

Comment le décrire en entrevue, en deux phrases : « J’ai pris un mois de trajets réels de vélos en libre-service, trouvé un défaut d’exportation des noms de stations et 60 000 déplacements sans cycliste, puis construit une table de stations validée en SQL avec des variables provenant seulement des jours précédents. Un simple classement sur cinq jours pour l’équipe de nuit a couvert 83 % du meilleur résultat possible sur une semaine réservée au test, contre 72 % pour la méthode actuelle “comme hier”, et j’ai montré que ce gain vient surtout des lundis. »

Pour aller plus loin :

• Répétez l’étude sur un autre mois du même fichier zip. Vérifiez si la règle sur cinq jours gagne encore. Faites d’abord une prédiction : en hiver, la perte matinale sera-t-elle plus grande ou plus petite?
• Ajoutez une moyenne pondérée (plus de poids aux jours récents) comme cinquième règle. Évaluez-la seulement sur les semaines 2 et 3.
• Trouvez les coordonnées de chaque station dans le flux de données en direct du système. Cartographiez les 20 premières et les destinations des vélos déplacés.
• Construisez le plan du soir : quelles stations se remplissent après 16 h, pour que l’équipe puisse les vider?

Vérification des connaissances

Vérifiez votre compréhension

Tomasz construit la table matinale uniquement avec les stations-matins qui ont eu au moins un trajet. Il utilise LAG(net_out) comme « hier ». La requête s'exécute sans erreur. Quel est le problème, et comment l'étude de cas l'a-t-elle corrigé?

Afficher la réponse

LAG retourne la ligne précédente, pas le jour précédent. Quand une station n’a aucun trajet un matin, ce matin n’a aucune ligne. LAG remonte donc à un jour plus ancien. En juin, cela s’est produit pour 1 766 lignes, sans avertissement. La solution est une grille complète : chaque station chaque jour avec CROSS JOIN, et 0 pour un matin sans trajet. Ainsi, la ligne précédente correspond toujours au jour précédent. LAG(net_out, 7) correspond toujours au même jour de la semaine précédente.

Vérifiez votre compréhension

La règle d'un collègue obtient 0,915 aux semaines 2 et 3, contre 0,867 pour la moyenne sur cinq jours. Elle utilise le cadre ROWS BETWEEN 4 PRECEDING AND CURRENT ROW. Leila devrait-elle l'utiliser?

Afficher la réponse

Non. Le cadre inclut la ligne actuelle, dont net_out est la cible : le matin que nous voulons prédire. À 22 h la veille, cette valeur n’existe pas. La règle ne peut donc pas fonctionner une vraie nuit. Son score élevé vient d’une fuite de données. Le cadre honnête se termine à 1 PRECEDING. Un score meilleur que prévu doit vous inciter à vérifier le moment de chaque variable.

Vérifiez votre compréhension

Pendant la semaine de test, la moyenne sur cinq jours dépasse « comme hier » de 11 points (0,833 contre 0,722). Leila demande : « Donc, la moyenne est beaucoup plus intelligente? » Quelle est votre réponse honnête?

Afficher la réponse

En partie. Le gain vient surtout des deux lundis, où « hier » est un dimanche sans navetteurs (0,407 contre 0,818 le 23 juin). Une règle sur un jour qui utilise le dernier jour ouvrable récupère une grande partie de cet écart (0,786). Face à cette règle plus juste, la moyenne gagne encore chaque matin de test. Elle gagne de 13 points le 23 juin, et de moins d’un point pour deux matins. Une moyenne sur cinq jours est moins bruitée qu’une valeur sur un jour. Les preuves se limitent à six matins dans un mois d’été, et les semaines 2 et 3 vont dans le même sens. La réponse est donc « utilisez la moyenne, et ne planifiez jamais le lundi à partir du dimanche ». Vérifiez à nouveau la saison prochaine.

Récapitulatif

• Vous avez profilé une exportation réelle de 980 624 trajets. Les noms des stations d’arrivée copiaient ceux des stations de départ dans chaque ligne. Vous avez donc pris les noms du côté départ.
• Vous avez examiné le problème d’encodage dans les deux sens : lire cp1252 comme UTF-8 échoue. Lire UTF-8 comme cp1252 donne des résultats erronés sans avertissement.
• Vous avez placé 5 754 trajets en quarantaine, avec une raison par trajet. Vous avez prouvé que données nettoyées plus quarantaine égalaient données brutes.
• LAG par vélo a trouvé 60 554 trajets (6,2 %) après un déplacement sans cycliste, le plus souvent utilisés à 8 h.
• Une grille complète a gardé LAG fiable. Sans elle, 1 766 valeurs « hier » correspondaient à des jours plus anciens.
• Les variables provenaient seulement de lignes antérieures. Une démonstration de fuite a montré comment un cadre CURRENT ROW gonfle un score.
• Pendant la semaine de test, utilisée une seule fois, la moyenne sur cinq jours a couvert 0,833 du meilleur résultat possible. C’était 0,722 pour « comme hier ». Le gain venait surtout des lundis.

Ensuite vient le travail final du cours. Il pose une nouvelle question sur l’exportation de Larkfield. Vous reprenez chaque décision de ce cours et livrez un jeu de données qu’une autre personne peut vérifier.

Leçon suivante : Travail final