Khi-2 d’indépendance avec Oracle
Je voyage quotidiennement entre Tours et Paris en utilisant le TGV. Il y a peu, je discutais avec un ancien collègue qui lui aussi « navette » chaque jour entre Orléans et Paris. De son coté, il utilise le réseau Intercité.
La question était de savoir s’il y avait une différence significative dans la ponctualité des trains entre sa ligne (Corail Orléans/Paris) et la mienne (TGV Tours/Paris)…
Suite à cette discussion, j’ai découvert que la SNCF mettait à disposition un grand nombre de données relatives à la ponctualité des trains sur leur portail open-data (https://data.sncf.com/).
Vérifions donc sur pièce ce qu’il en est!
1) Téléchargement des fichiers de données au format CSV
Les fichiers utilisés dans cet article sont disponible ici: regularite-mensuelle-intercites & regularite-mensuelle-tgv
2) Création des tables de destination dans une base Oracle 12c
SQL> CREATE TABLE ponctualite_tgv 2 ( 3 dt VARCHAR2 (7), 4 axe VARCHAR2 (30), 5 depart VARCHAR2 (30), 6 arrivee VARCHAR2 (30), 7 nb_trains_prog NUMBER (5), 8 nb_trains_circ NUMBER (5), 9 nb_trains_annul NUMBER (5), 10 nb_trains_retard NUMBER (5), 11 taux_regul NUMBER (4) 12 ); Table created. SQL> SQL> SQL> CREATE TABLE ponctualite_corail 2 AS 3 SELECT * FROM ponctualite_tgv; Table created. SQL>
3) Chargement des données
J’utilise ici la fonctionnalité SQL*Loader Express.
C:\RTI\Stats\SNCF>sqlldr rafa/rafa@localhost/STATPDB data=regularite-mensuelle-intercites.csv table=ponctualite_corail terminated_by=';' field_names=all_ignore SQL*Loader: Release 12.1.0.2.0 - Production on Fri May 15 15:13:47 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Express Mode Load, Table: PONCTUALITE_CORAIL Path used: External Table, DEGREE_OF_PARALLELISM=AUTO Table PONCTUALITE_CORAIL: 750 Rows successfully loaded. Check the log files: ponctualite_corail.log ponctualite_corail_%p.log_xt for more information about the load. C:\RTI\Stats\SNCF>sqlldr rafa/rafa@localhost/STATPDB data=regularite-mensuelle-tgv.csv table=ponctualite_tgv terminated_by=';' field_names=all_ignore SQL*Loader: Release 12.1.0.2.0 - Production on Fri May 15 15:14:16 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Express Mode Load, Table: PONCTUALITE_TGV Path used: External Table, DEGREE_OF_PARALLELISM=AUTO Table PONCTUALITE_TGV: 4100 Rows successfully loaded. Check the log files: ponctualite_tgv.log ponctualite_tgv_%p.log_xt for more information about the load. C:\RTI\Stats\SNCF>
4) Analyse des données
On va utiliser un test du Khi-2 pour vérifier s’il y a indépendance entre la distribution des retards et le type de train (TGV/Corail).
La fonction STATS_CROSSTAB permet d’effectuer un tel test. Néanmoins, son implémentation par Oracle ne permet pas de travailler à partir d’une table de contingence mais seulement à partir d’un échantillon d’observations unitaires.
Or, ici, nous disposons de données agrégées mois par mois. Il va donc falloir les convertir en une série d’enregistrements – un par train ayant circulé avec un indicateur (O/N) informant de son éventuel retard!
Pour les deux lignes (Corail Orléans/Paris & TGV Tours/Paris), on détermine d’abord le nombre de trains ayant circulés et ceux ayant été retardés (à noter que les données sont filtrées pour correspondre aux trajets d’intérêt au cours d’une même période):
SQL> SELECT SUM (nb_trains_circ) circ, SUM (nb_trains_retard) retard
2 FROM ponctualite_tgv
3 WHERE TO_DATE (dt, 'YYYY-MM') >= TO_DATE ('2014-01', 'YYYY-MM')
4 AND (depart = 'ST PIERRE DES CORPS' OR arrivee = 'ST PIERRE DES CORPS');
CIRC RETARD
---------- ----------
11190 1325
SQL> SELECT SUM (nb_trains_circ) circ, SUM (nb_trains_retard) retard
2 FROM ponctualite_corail
3 WHERE depart LIKE 'ORL%' OR arrivee LIKE 'ORL%';
CIRC RETARD
---------- ----------
10131 1735
SQL>
On « éclate » ensuite ces chiffres sous la forme d’enregistrements individuels à l’aide d’un générateur de lignes (opérateur hiérarchique CONNECT BY appliqué à DUAL).
Les enregistrements générés vont ensuite être passées à la fonction STATS_CROSSTAB:
SQL> SELECT STATS_CROSSTAB (type_train, retard, 'CHISQ_SIG') p_value, 2 STATS_CROSSTAB (type_train, retard, 'CHISQ_OBS') khi_2 3 FROM ( SELECT 'TGV' type_train, 4 CASE WHEN LEVEL <= 1325 THEN 'O' ELSE 'N' END retard 5 FROM DUAL 6 CONNECT BY LEVEL <= 11190 7 UNION ALL 8 SELECT 'CORAIL' type_train, 9 CASE WHEN LEVEL <= 1735 THEN 'O' ELSE 'N' END retard 10 FROM DUAL 11 CONNECT BY LEVEL <= 10131); P_VALUE KHI_2 ---------- ---------- 4.2138E-28 120.806054 SQL>
La p-value obtenue est très faible, on peut donc rejeter l’hypothèse d’indépendance (H0) et considérer qu’il y a bien un lien statistiquement significatif entre les retards à l’arrivée et la ligne de train empruntée.