Arbre de décision avec Oracle
A la suite du scandale des moteurs Volkswagen truqués, on a beaucoup parlé de l’impact de la fraude sur le bonus-malus écologique octroyé par le gouvernement. Par curiosité, je me suis intéressé aux données officielles collectées par l’ADEME afin de savoir s’il était possible d’inférer le montant dudit bonus-malus en fonction des caractéristiques techniques d’un véhicule.
Pour cela, j’ai eu recours à un arbre de décision. Il s’agit d’une méthode d’apprentissage supervisé qui permet de formuler des règles explicites de classification.
Mon idée était de produire l’arbre en utilisant un ensemble d’apprentissage constitué de véhicules des marques non-impliquées dans l’affaire. Puis, dans un second temps, d’utiliser comme jeu de test les véhicules des marques incriminées afin de voir si la performance de classification divergeait significativement…
Oracle propose une implémentation d’arbre de décision basée sur l’algorithme CART. A noter que l’utilisation de cette fonctionnalité requiert l’option Oracle Advanced Analytics.
Pour cette analyse, j’ai utilisé le fichier de données correspondant à l’année 2014 car il présentait plus d’informations (type de carrosserie en particulier) que celui de 2015: mars-2014-complete
Chargement des données
Une table externe est utilisée pour accéder aux données depuis la base de données:
SQL> CREATE TABLE exttab_autos
2 (
3 lib_mrq VARCHAR2 (40),
4 lib_mod_doss VARCHAR2 (40),
5 lib_mod VARCHAR2 (40),
6 dscom VARCHAR2 (200),
7 cnit VARCHAR2 (40),
8 tvv VARCHAR2 (40),
9 cod_cbr VARCHAR2 (6),
10 hybride VARCHAR2 (5),
11 puiss_admin_98 NUMBER,
12 puiss_max NUMBER,
13 typ_boite_nb_rapp VARCHAR2 (5),
14 conso_urb NUMBER,
15 conso_exurb NUMBER,
16 conso_mixte NUMBER,
17 co2 NUMBER,
18 co_typ_1 NUMBER,
19 hc NUMBER,
20 nox NUMBER,
21 hcnox NUMBER,
22 ptcl NUMBER,
23 masse_ordma_min NUMBER,
24 masse_ordma_max NUMBER,
25 champ_v9 VARCHAR2 (40),
26 date_maj VARCHAR2 (40),
27 Carrosserie VARCHAR2 (40),
28 gamme VARCHAR2 (40)
29 )
30 ORGANIZATION EXTERNAL
31 (
32 TYPE oracle_loader
33 DEFAULT DIRECTORY datadir
34 ACCESS PARAMETERS
35 (
36 RECORDS DELIMITED BY NEWLINE
37 SKIP 1
38 FIELDS TERMINATED BY ';'
39 MISSING FIELD VALUES ARE NULL
40 )
41 LOCATION ('mars-2014-complete.csv')
42 )
43 REJECT LIMIT 0
44 NOPARALLEL;
Table created.
SQL>
Création d’une fonction de détermination du montant du bonus-malus
A partir de la quantité de CO2 rejetée par un véhicule, la fonction get_bonus_malus renvoie le bonus/malus auquel il est éligible. Les seuils et les montants associés sont publiés sur le site du ministère de l’écologie et du développement durable.
SQL> CREATE OR REPLACE FUNCTION get_bonus_malus (p_co2 NUMBER) 2 RETURN VARCHAR2 3 RESULT_CACHE 4 IS 5 BEGIN 6 RETURN CASE 7 WHEN p_co2 BETWEEN 0 AND 20 THEN 'A (-6300)' 8 WHEN p_co2 BETWEEN 21 AND 60 THEN 'B (-4000)' 9 WHEN p_co2 BETWEEN 131 AND 135 THEN 'D (150)' 10 WHEN p_co2 BETWEEN 136 AND 134 THEN 'E (250)' 11 WHEN p_co2 BETWEEN 141 AND 145 THEN 'F (500)' 12 WHEN p_co2 BETWEEN 146 AND 150 THEN 'G (900)' 13 WHEN p_co2 BETWEEN 151 AND 155 THEN 'H (1600)' 14 WHEN p_co2 BETWEEN 156 AND 175 THEN 'I (2200)' 15 WHEN p_co2 BETWEEN 176 AND 180 THEN 'J (3000)' 16 WHEN p_co2 BETWEEN 181 AND 185 THEN 'K (3600)' 17 WHEN p_co2 BETWEEN 186 AND 190 THEN 'L (4000)' 18 WHEN p_co2 BETWEEN 191 AND 200 THEN 'M (6500)' 19 WHEN p_co2 > 200 THEN 'N (8000)' 20 ELSE 'C (0)' 21 END; 22 END; 23 / Function created. SQL>
Création des vues d’analyse
La vue v_autos extrait les données d’intérêts de exttab_autos – à savoir, on s’intéresse aux véhicules grand-public essence ou diesel (d’où l’exclusion des 4×4 et des minibus). On applique ensuite la fonction get_bonus_malus et enfin on supprime les doublons:
SQL> CREATE OR REPLACE VIEW v_autos
2 AS
3 SELECT DISTINCT
4 TRIM (lib_mrq) lib_mrq,
5 TRIM (lib_mod) lib_mod,
6 TRIM (cod_cbr) cod_cbr,
7 TRIM (hybride) hybride,
8 puiss_admin_98,
9 puiss_max,
10 SUBSTR (TYP_BOITE_NB_RAPP, 1, 1) typ_boite,
11 TO_NUMBER (REPLACE (SUBSTR (TYP_BOITE_NB_RAPP, 3, 1), '.', '0'))
12 nb_rapp,
13 conso_urb,
14 conso_exurb,
15 conso_mixte,
16 masse_ordma_min,
17 masse_ordma_max,
18 decode(carrosserie,'COMBISPCACE','COMBISPACE',carrosserie) carrosserie,
19 gamme,
20 get_bonus_malus (co2) bonus_malus
21 FROM exttab_autos
22 WHERE carrosserie NOT IN ('MINIBUS','TS TERRAINS/CHEMINS','CABRIOLET')
23 AND cod_cbr IN ('ES', 'GO');
View created.
SQL>
Les vues v_autos_apprentissage et v_autos_test assurent respectivement la division du jeu de données en un jeu d’apprentissage (incluant les marques non-incriminées dans le scandale) et un jeu de test (incluant les marques incriminées):
SQL> CREATE OR REPLACE VIEW v_autos_apprentissage
2 AS
3 SELECT *
4 FROM v_autos
5 WHERE lib_mrq NOT IN ('VOLKSWAGEN',
6 'SEAT',
7 'SKODA',
8 'AUDI');
View created.
SQL>
SQL>
SQL> CREATE OR REPLACE VIEW v_autos_test
2 AS
3 SELECT *
4 FROM v_autos
5 WHERE lib_mrq IN ('VOLKSWAGEN',
6 'SEAT',
7 'SKODA',
8 'AUDI');
View created.
SQL>
SQL> SELECT COUNT (*) FROM v_autos_apprentissage;
COUNT(*)
----------
2722
SQL> SELECT COUNT (*) FROM v_autos_test;
COUNT(*)
----------
654
SQL>
On a donc une distribution 80/20 des effectifs entre l’ensemble d’apprentissage et celui de test.
Création de la table de paramétrage du modèle
A chaque étape de croissance de l’arbre, l’algorithme tente de trouver l’attribut de partage qui produit des sous-ensembles les plus homogènes. Deux options sont disponibles pour quantifier cette homogénéité: le critère de Gini ou l’entropie. Ici, j’utilise l’entropie (DBMS_DATA_MINING.TREE_IMPURITY_ENTROPY) qui a donné de meilleurs résultats lors de mes tests:
SQL> CREATE TABLE autos_dt_settings 2 ( 3 setting_name VARCHAR2 (30), 4 setting_value VARCHAR2 (30) 5 ); Table created. SQL> SQL> SQL> BEGIN 2 INSERT INTO autos_dt_settings (setting_name, setting_value) 3 VALUES ( 4 DBMS_DATA_MINING.algo_name, 5 DBMS_DATA_MINING.algo_decision_tree); 6 7 INSERT INTO autos_dt_settings (setting_name, setting_value) 8 VALUES ( 9 DBMS_DATA_MINING.TREE_IMPURITY_METRIC, 10 DBMS_DATA_MINING.TREE_IMPURITY_ENTROPY); 11 COMMIT; 12 END; 13 / PL/SQL procedure successfully completed. SQL>
Création du modèle
Les champs LIB_MRQ et LIB_MOD – respectivement, la marque et le modèle des véhicules – sans valeur informative, sont exclus de l’analyse via une opération de transformation (DBMS_DATA_MINING_TRANSFORM):
SQL> DECLARE 2 l_xform DBMS_DATA_MINING_TRANSFORM.transform_list; 3 BEGIN 4 DBMS_DATA_MINING_TRANSFORM.set_transform (l_xform, 5 'LIB_MRQ', 6 NULL, 7 NULL, 8 NULL); 9 10 DBMS_DATA_MINING_TRANSFORM.set_transform (l_xform, 11 'LIB_MOD', 12 NULL, 13 NULL, 14 NULL); 15 16 DBMS_DATA_MINING.CREATE_MODEL ( 17 model_name => 'AUTOS_DT_MODEL', 18 mining_function => DBMS_DATA_MINING.classification, 19 data_table_name => 'V_AUTOS_APPRENTISSAGE', 20 case_id_column_name => '', 21 target_column_name => 'BONUS_MALUS', 22 settings_table_name => 'AUTOS_DT_SETTINGS', 23 xform_list => l_xform); 24 END; 25 / PL/SQL procedure successfully completed. SQL>
Visualisation du modèle
Le modèle produit est un document au format PMML qui détaille les règles de classification:
SQL> set long 300
SQL> SELECT DBMS_DATA_MINING.get_model_details_xml ('AUTOS_DT_MODEL')
2 AS DT_DETAILS
3 FROM DUAL;
DT_DETAILS
--------------------------------------------------------------------------------
<PMML version="2.1">
<Header copyright="Copyright (c) 2004, Oracle Corporation. All rights reserved
."/>
<DataDictionary numberOfFields="8">
<DataField name="BONUS_MALUS" optype="categorical"/>
<DataField name="CARROSSERIE" optype="categorical"/>
<DataField name="COD_CBR" optype="cate
SQL>
Il est possible d’afficher sous forme textuelle les règles en question via un script publié sur le site du blog de l’équipe ODM.
J’ai modifié légèrement ce script pour ne récupérer que les terminaisons (feuilles) de l’arbre et la règle associée: dt_rules
SQL> @C:\RTI\Stats\dt_rules.sql
Enter value for model_name: AUTOS_DT_MODEL
RECORD_COUNT PREDICTION FULL_SIMPLE_RULE
------------ --------------- -----------------------------------------------------------------------------------------------------------------------
985 C (0) (CONSO_MIXTE <= 4.9500000475) 30 D (150) (CONSO_MIXTE > 4.9500000475) AND (MASSE_ORDMA_MAX <= 1504.5) AND (COD_CBR in ("GO" )) AND (CONSO_MIXTE <= 5.25)
18 C (0) (MASSE_ORDMA_MAX <= 1504.5) AND (CONSO_MIXTE <= 5.649999857) AND (COD_CBR in ("GO" )) AND (CONSO_MIXTE > 5.25)
195 C (0) (CONSO_MIXTE > 4.9500000475) AND (MASSE_ORDMA_MAX <= 1504.5) AND (CONSO_MIXTE <= 5.649999857) AND (COD_CBR in ("ES" ))
73 D (150) (MASSE_ORDMA_MAX <= 1504.5) AND (CONSO_MIXTE > 5.649999857) AND (CONSO_MIXTE <= 5.850000143)
64 C (0) (CONSO_MIXTE <= 6.0499999525) AND (MASSE_ORDMA_MAX <= 1504.5) AND (CONSO_MIXTE > 5.850000143)
93 D (150) (CONSO_MIXTE > 4.9500000475) AND (MASSE_ORDMA_MAX > 1504.5) AND (CONSO_MIXTE <= 5.149999857) 86 C (0) (MASSE_ORDMA_MAX > 1504.5) AND (CONSO_MIXTE <= 5.350000143) AND (CONSO_MIXTE > 5.149999857)
70 F (500) (MASSE_ORDMA_MAX > 1504.5) AND (CONSO_MIXTE > 5.350000143) AND (CONSO_MIXTE <= 5.5499999525) 88 G (900) (MASSE_ORDMA_MAX > 1504.5) AND (CONSO_MIXTE <= 5.75) AND (CONSO_MIXTE > 5.5499999525)
78 H (1600) (CONSO_MIXTE <= 6.0499999525) AND (MASSE_ORDMA_MAX > 1504.5) AND (CONSO_MIXTE > 5.75) AND (COD_CBR in ("GO" ))
28 C (0) (CONSO_MIXTE <= 6.0499999525) AND (MASSE_ORDMA_MAX > 1504.5) AND (CONSO_MIXTE > 5.75) AND (COD_CBR in ("ES" ))
73 I (2200) (CONSO_MIXTE > 6.0499999525) AND (CONSO_MIXTE <= 6.4500000475) AND (COD_CBR in ("GO" )) 73 F (500) (CONSO_MIXTE > 6.0499999525) AND (COD_CBR in ("ES" )) AND (CONSO_MIXTE <= 6.25)
91 G (900) (CONSO_MIXTE <= 6.4500000475) AND (COD_CBR in ("ES" )) AND (CONSO_MIXTE > 6.25)
14 I (2200) (CONSO_MIXTE > 6.4500000475) AND (CONSO_MIXTE <= 6.75) AND (COD_CBR in ("GO" )) 56 H (1600) (CONSO_MIXTE > 6.4500000475) AND (COD_CBR in ("ES" )) AND (CONSO_MIXTE <= 6.649999857)
26 I (2200) (CONSO_MIXTE <= 6.75) AND (COD_CBR in ("ES" )) AND (CONSO_MIXTE > 6.649999857)
12 K (3600) (CONSO_MIXTE > 6.75) AND (COD_CBR in ("GO" )) AND (CONSO_MIXTE <= 7.149999857)
10 M (6500) (CONSO_MIXTE <= 7.75) AND (COD_CBR in ("GO" )) AND (CONSO_MIXTE > 7.149999857)
137 I (2200) (CONSO_MIXTE > 6.75) AND (COD_CBR in ("ES" )) AND (CONSO_MIXTE <= 7.4500000475)
30 J (3000) (CONSO_MIXTE <= 7.75) AND (COD_CBR in ("ES" )) AND (CONSO_MIXTE > 7.4500000475)
29 K (3600) (CONSO_MIXTE > 7.75) AND (CONSO_MIXTE <= 7.9500000475)
28 L (4000) (CONSO_MIXTE <= 8.150000095) AND (CONSO_MIXTE > 7.9500000475)
36 M (6500) (CONSO_MIXTE <= 8.650000095) AND (CONSO_MIXTE > 8.150000095)
299 N (8000) (CONSO_MIXTE > 8.650000095)
26 rows selected.
SQL>
Pour chaque véhicule, on peut aussi visualiser via PREDICTION_DETAILS les critères utilisés pour aboutir à l’une des feuilles de l’arbre:
SQL> column BONUS_MALUS format a15
SQL> column DETAIL format a95
SQL> set long 500
SQL> SELECT bonus_malus,
2 PREDICTION (AUTOS_DT_MODEL USING a.*) prediction,
3 PREDICTION_DETAILS (AUTOS_DT_MODEL USING a.*) detail
4 FROM v_autos_test a
5 WHERE lib_mod = 'GOLF' AND puiss_max = 90 AND carrosserie='BERLINE';
BONUS_MALUS PREDICTION DETAIL
--------------- --------------- -----------------------------------------------------------------------------------------------
G (900) G (900) <Details algorithm="Decision Tree" class="3" node="39">
<Attribute name="CONSO_MIXTE" actualValue="6.300000191" operator="between" range="(6.25:6.45000
00475]" weight=".62" rank="1"/>
<Attribute name="COD_CBR" actualValue="ES" operator="in" range="ES" weight=".143" rank="2"/>
</Details>
H (1600) H (1600) <Details algorithm="Decision Tree" class="4" node="41">
<Attribute name="CONSO_MIXTE" actualValue="6.5" operator="between" range="(6.4500000475:6.64999
9857]" weight=".726" rank="1"/>
<Attribute name="COD_CBR" actualValue="ES" operator="in" range="ES" weight=".108" rank="2"/>
</Details>
SQL>
Performance du modèle
La performance globale du classifieur est très proche (supérieure à 90%) pour les deux échantillons:
SQL> WITH classification_perf 2 AS ( SELECT jeu, pred_correcte, COUNT (*) cnt 3 FROM (SELECT 'TEST' jeu, 4 CASE 5 WHEN a.bonus_malus = 6 PREDICTION (AUTOS_DT_MODEL USING *) 7 THEN 8 1 9 ELSE 10 0 11 END 12 pred_correcte 13 FROM v_autos_test a 14 UNION ALL 15 SELECT 'APPRENTISSAGE' jeu, 16 CASE 17 WHEN a.bonus_malus = 18 PREDICTION (AUTOS_DT_MODEL USING *) 19 THEN 20 1 21 ELSE 22 0 23 END 24 pred_correcte 25 FROM v_autos_apprentissage a) 26 GROUP BY jeu, pred_correcte) 27 SELECT jeu, ROUND (100 * SUM (pred_correcte * cnt) / SUM (cnt), 1) pct_global 28 FROM classification_perf 29 GROUP BY jeu; JEU PCT_GLOBAL ------------- ---------- APPRENTISSAGE 92.1 TEST 93.4 SQL>
On peut aussi produire une matrice de confusion afin de visualiser la distribution des prédictions correctes/incorrectes.
A noter l’utilisation de la clause PIVOT pour permuter les données réelles sous forme de colonnes:
SQL> column PRED format a10
SQL> set NUMFORMAT 9999
SQL> SELECT *
2 FROM (SELECT bonus_malus, PREDICTION (AUTOS_DT_MODEL USING *) AS pred
3 FROM v_autos_test) PIVOT (COUNT (*)
4 FOR bonus_malus
5 IN ('A (-6300)' A,
6 'B (-4000)' B,
7 'C (0)' C,
8 'D (150)' D,
9 'E (250)' E,
10 'F (500)' F,
11 'G (900)' G,
12 'H (1600)' H,
13 'I (2200)' I,
14 'J (3000)' J,
15 'K (3600)' K,
16 'L (4000)' L,
17 'M (6500)' M,
18 'N (8000)' N))
19 ORDER BY 1;
PRED A B C D E F G H I J K L M N
---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
C (0) 0 0 369 7 0 3 0 0 0 0 0 0 0 0
D (150) 0 0 3 47 0 0 0 0 0 0 0 0 0 0
F (500) 0 0 0 0 0 19 1 0 0 0 0 0 0 0
G (900) 0 0 0 0 0 1 46 0 0 0 0 0 0 0
H (1600) 0 0 0 0 0 0 4 36 9 0 0 0 0 0
I (2200) 0 0 0 0 0 0 0 3 39 4 0 0 0 0
J (3000) 0 0 0 0 0 0 0 0 1 5 0 0 0 0
K (3600) 0 0 0 0 0 0 0 0 0 1 4 0 0 0
L (4000) 0 0 0 0 0 0 0 0 0 1 0 10 0 0
M (6500) 0 0 0 0 0 0 0 0 0 0 0 3 9 2
N (8000) 0 0 0 0 0 0 0 0 0 0 0 0 0 27
11 rows selected.
SQL>
En conclusion, l’analyse des données techniques des véhicules par rapport aux catégories de bonus/malus écologique ne permet pas de mettre en évidence d’incohérences pour les véhicules volkswagen. Cela-dit, c’est finalement assez logique car, si j’ai bien compris, la fraude porterait plutôt sur un autre polluant (le NOX) que le CO2… 😉