Régression logistique avec Oracle
Le JO de Rio ont été l’occasion de mettre à l’affiche un certain nombre de sports dont la couverture médiatique est faible le reste du temps. C’est le cas du Canoë-Kayak, discipline qui a permis à la France de remporter 3 médailles.
On a pu voir deux types d’épreuves:
- le slalom qui consiste dans un parcours en eau vive ou il faut passer entre des portes
- la course en ligne qui consiste dans un sprint sur un plan d’eau calme
De mon expérience, le profil physique des sportifs de ces deux famille est sensiblement différent. Les champions de course en ligne sont souvent plus massifs ; le sprint nécessitant une grande force physique. En eau vive, tout en étant athlétiques, les sportifs sont moins musculeux, la discipline privilégiant la « glisse ».
Pour objectiver mes observations, j’ai collecté les informations de tous les participants à des épreuves individuelles de Canoë-Kayak aux JO depuis 2000 (Jeux Olympiques de Sydney, Athènes, Pékin, Londres) via le site www.sports-reference.com.
Pour les sportifs ayant participé à plusieurs olympiades, j’ai conservé l’âge moyen et le dernier pays qu’ils ont représentés.
Ces données ont servi à bâtir un modèle de régression logistique binaire.
Dans un second temps, les caractéristiques des compétiteurs individuels des JO de Rio ont été récupérées depuis le site www.rio2016.com. Ces dernières ont permis le scoring du modèle.
Pour chaque dataset (JO Rio et JO pré-Rio), les compétiteurs pour lesquels des informations étaient manquantes ont été supprimées.
La variable cible est le type d’épreuve : « slalom » ou « sprint » – donc de type binaire. Les prédicteurs sont la taille, le poids, le sexe, l’âge et le pays du sportif.
Chargement des données
Les données sont accessibles ici au format Table Externe DataPump: JO_CK_PRERIO et JO_CK_RIO.
SQL>
SQL> CREATE TABLE jo_ck_prerio
2 (
3 nom VARCHAR2 (50),
4 type_epreuve VARCHAR2 (6),
5 age NUMBER,
6 sexe VARCHAR2 (1),
7 taille NUMBER,
8 poids NUMBER,
9 pays VARCHAR2 (80)
10 )
11 ORGANIZATION EXTERNAL
12 (TYPE oracle_datapump
13 DEFAULT DIRECTORY datadir
14 LOCATION ('JO_CK_PRERIO.dp'));
Table created.
SQL>
SQL> CREATE TABLE jo_ck_rio
2 (
3 nom VARCHAR2 (50),
4 type_epreuve VARCHAR2 (6),
5 age NUMBER,
6 sexe VARCHAR2 (1),
7 taille NUMBER,
8 poids NUMBER,
9 pays VARCHAR2 (80)
10 )
11 ORGANIZATION EXTERNAL
12 (TYPE oracle_datapump
13 DEFAULT DIRECTORY datadir
14 LOCATION ('JO_CK_RIO.dp'));
Table created.
SQL>
SQL> set pages 30
SQL> column nom format a20
SQL> column pays format a15
SQL>
SQL> SELECT *
2 FROM jo_ck_prerio
3 ORDER BY DBMS_RANDOM.VALUE
4 FETCH FIRST 10 ROWS ONLY;
NOM TYPE_E AGE S TAILLE POIDS PAYS
-------------------- ------ ---------- - ---------- ---------- ---------------
Anne Rikala Sprint 33 F 170 67 Finland
Michal Gajownik Sprint 18 M 169 70 Poland
Emir Mujcinovic Slalom 30 M 187 76 Croatia
ð‗Ón Th┐ Cßch Sprint 20 F 161 54 Vietnam
Thomas Schmidt Slalom 26 M 172 69 Germany
Floris Braat Slalom 24 M 180 74 Netherlands
Moe Kaifuchi Slalom 26 F 159 55 Japan
Tony Lespoir Sprint 29 M 182 85 Seychelles
Caroline Queen Slalom 20 F 157 66 United States
Krzysztof Bieryt Slalom 30 M 178 80 Poland
10 rows selected.
SQL>
SQL>
SQL> SELECT *
2 FROM jo_ck_rio
3 ORDER BY DBMS_RANDOM.VALUE
4 FETCH FIRST 10 ROWS ONLY;
NOM TYPE_E AGE S TAILLE POIDS PAYS
-------------------- ------ ---------- - ---------- ---------- ---------------
Miroslav Kirchev Sprint 26 M 186 78 Bulgaria
Richard Merjan Slalom 27 M 160 57 Lebanon
Olivera Moldovan Sprint 27 F 158 62 Serbia
Yvonne Schuring Sprint 38 F 175 68 Austria
Angel Kodinov Sprint 18 M 188 88 Bulgaria
Pavlo Altukhov Sprint 20 M 185 90 Ukraine
Stefanie Horn Slalom 25 F 168 59 Italy
Joaquim Lobo Sprint 21 M 172 66 Mozambique
Giovanni de Gennaro Slalom 24 M 185 80 Italy
Vincent Farkas Sprint 23 M 193 90 Slovakia
10 rows selected.
SQL>
Paramétrage du modèle
SQL> CREATE TABLE ck_settings 2 ( 3 setting_name VARCHAR2 (30), 4 setting_value VARCHAR2 (30) 5 ); Table created. SQL> SQL> BEGIN 2 INSERT INTO ck_settings (setting_name, setting_value) 3 VALUES ( 4 DBMS_DATA_MINING.algo_name, 5 DBMS_DATA_MINING.algo_generalized_linear_model); 6 7 INSERT INTO ck_settings (setting_name, setting_value) 8 VALUES (DBMS_DATA_MINING.prep_auto, DBMS_DATA_MINING.prep_auto_on); 9 10 INSERT INTO ck_settings (setting_name, setting_value) 11 VALUES ( 12 DBMS_DATA_MINING.glms_ftr_selection, 13 DBMS_DATA_MINING.glms_ftr_selection_enable); 14 15 INSERT INTO ck_settings (setting_name, setting_value) 16 VALUES ( 17 DBMS_DATA_MINING.glms_ftr_identification, 18 DBMS_DATA_MINING.glms_ftr_ident_complete); 19 20 INSERT INTO ck_settings (setting_name, setting_value) 21 VALUES ( 22 DBMS_DATA_MINING.glms_select_block, 23 DBMS_DATA_MINING.glms_select_block_enable); 24 INSERT INTO ck_settings (setting_name, setting_value) 25 VALUES ( 26 DBMS_DATA_MINING.glms_prune_model, 27 DBMS_DATA_MINING.glms_prune_model_enable); 28 29 COMMIT; 30 END; 31 / PL/SQL procedure successfully completed. SQL>
Le paramétrage indique :
- le type d’algorithme à utiliser DBMS_DATA_MINING.algo_generalized_linear_model. Une régression logistique sera réalisée dans la mesure ou la variable cible est catégorielle.
- la désactivation de la fonctionnalité de préparation automatique dans la mesure où ces dernières ont été préalablement nettoyées
- le traitement en bloc des variables catégorielles (DBMS_DATA_MINING.glms_select_block_enable) – c’est-à-dire qu’on n’ajoute pas sélectivement certaines modalités de la variable au modèle (soit on ajoute toutes les modalités, soit on retire la variable)
- la détermination automatique des prédicteurs avec suppression de ceux non significatifs (DBMS_DATA_MINING.glms_ftr_selection_enable, DBMS_DATA_MINING.glms_ftr_ident_complete, DBMS_DATA_MINING.glms_prune_model_enable). Il s’agit de supprimer itérativement les éléments dont les effets apparaissent le moins significatif en se basant sur un test du rapport de vraisemblance.
Création du modèle
On précise qu’il s’agit d’un modèle de classification dont la cible est la variable TYPE_EPREUVE. Le dataset d’apprentissage se trouve dans JO_CK_PRERIO et la variable NOM est ignorée:
SQL> DECLARE 2 l_xform DBMS_DATA_MINING_TRANSFORM.transform_list; 3 BEGIN 4 DBMS_DATA_MINING_TRANSFORM.set_transform (l_xform, 5 'NOM', 6 NULL, 7 NULL, 8 NULL); 9 10 DBMS_DATA_MINING.create_model ( 11 model_name => 'LOGREG_CK', 12 mining_function => DBMS_DATA_MINING.classification, 13 data_table_name => 'JO_CK_PRERIO', 14 case_id_column_name => NULL, 15 target_column_name => 'TYPE_EPREUVE', 16 settings_table_name => 'CK_SETTINGS', 17 xform_list => l_xform); 18 END; 19 / PL/SQL procedure successfully completed. SQL>
Scoring du modèle
Le modèle est appliquée aux données des JO de Rio afin de vérifier sa qualité prédictive.
SQL> SELECT *
2 FROM (SELECT type_epreuve AS epreuve_reelle,
3 PREDICTION (logreg_ck USING *) AS epreuve_predict
4 FROM jo_ck_rio)
5 PIVOT
6 (COUNT (*)
7 FOR epreuve_predict
8 IN ('Slalom' slalom_pred, 'Sprint' sprint_pred))
9 ORDER BY 1;
EPREUV SLALOM_PRED SPRINT_PRED
------ ----------- -----------
Slalom 40 21
Sprint 13 110
SQL>
La matrice de confusion ci-dessus permet d’évaluer le taux de réussite à 81.5% (40+110)/(40+21+13+110).
Analyse du modèle
La p-valeur est très faible, le modèle est donc significatif.
SQL> SELECT *
2 FROM TABLE (DBMS_DATA_MINING.get_model_details_global ('LOGREG_CK'))
3 WHERE global_detail_name LIKE '%P_VALUE';
GLOBAL_DETAIL_NAME GLOBAL_DETAIL_VALUE
------------------------------ -------------------
LR_CHI_SQ_P_VALUE 6.8959E-37
SQL>
D’autre part, on peut constater que seules les variables POIDS, SEXE et TAILLE ont été maintenues dans le modèle final.
SQL> column class format a15
SQL> column attribute_name format a20
SQL> column attribute_value format a20
SQL>
SQL> SELECT class,
2 attribute_name,
3 attribute_value,
4 coefficient,
5 p_value
6 FROM TABLE (DBMS_DATA_MINING.get_model_details_glm ('LOGREG_CK'))
7 ORDER BY attribute_name, attribute_value;
CLASS ATTRIBUTE_NAME ATTRIBUTE_VALUE COEFFICIENT P_VALUE
--------------- -------------------- -------------------- ----------- ----------
Slalom POIDS -.29005196 7.3193E-20
Slalom SEXE F -3.1906121 5.0268E-13
Slalom TAILLE .101887325 .000237583
Slalom 3.79165831 .308946949
SQL>
La catégorie de référence étant Slalom, le modèle logit se traduit par l’équation suivante :
On peut vérifier que la fonction PREDICTION_PROBABILITY utilise bien cette formule de calcul en comparant sa sortie avec une version codée manuellement:
SQL> set numformat 9.999999
SQL> WITH FUNCTION calc_prob (p_poids NUMBER, p_taille NUMBER, p_sexe VARCHAR2)
2 RETURN NUMBER
3 IS
4 l_prob NUMBER;
5 BEGIN
6 l_prob :=
7 1
8 / ( 1
9 + EXP (
10 -3.79165831302755
11 + 0.290051964566268 * p_poids
12 + 3.19061208475395
13 * (CASE WHEN p_sexe = 'F' THEN 1 ELSE 0 END)
14 - 0.101887324838025 * p_taille));
15 IF l_prob < 0.5
16 THEN
17 RETURN 1 - l_prob;
18 ELSE
19 RETURN l_prob;
20 END IF;
21 END;
22 SELECT calc_prob (poids, taille, sexe),
23 PREDICTION_PROBABILITY (logreg_ck USING *)
24 FROM jo_ck_rio
25 WHERE ROWNUM < 6
26 /
CALC_PROB(POIDS,TAILLE,SEXE) PREDICTION_PROBABILITY(LOGREG_CKUSING*)
---------------------------- ---------------------------------------
.880047 .880047
.880047 .880047
.824317 .824317
.875819 .875819
.721147 .721147
SQL>
A noter l’utilisation d’un bloc PL/SQL dans la clause WITH (fonctionnalité 12c).