.. _td2acenoncesession3Arst:

===================================================
2A.ml - Statistiques descriptives avec scikit-learn
===================================================


.. only:: html

    **Links:** :download:`notebook <td2a_cenonce_session_3A.ipynb>`, :downloadlink:`html <td2a_cenonce_session_3A2html.html>`, :download:`python <td2a_cenonce_session_3A.py>`, :downloadlink:`slides <td2a_cenonce_session_3A.slides.html>`, :githublink:`GitHub|_doc/notebooks/td2a_ml/td2a_cenonce_session_3A.ipynb|*`


`ACP <https://fr.wikipedia.org/wiki/Analyse_en_composantes_principales>`__,
`CAH <https://en.wikipedia.org/wiki/Hierarchical_clustering>`__,
`régression
lineaire <https://fr.wikipedia.org/wiki/R%C3%A9gression_lin%C3%A9aire>`__.

.. code:: ipython3

    %matplotlib inline

.. code:: ipython3

    import matplotlib.pyplot as plt

.. code:: ipython3

    from jyquickhelper import add_notebook_menu
    add_notebook_menu()






.. contents::
    :local:





Introduction
~~~~~~~~~~~~

Les statistiques descriptives sont abordées de la première année à
l’ENSAE. Un des livres que je consulte souvent est celui de Gilles
Saporta : `Probabilités, analyse des données et
statistique <http://www.editionstechnip.com/fr/catalogue-detail/149/probabilites-analyse-des-donnees-et-statistique.html>`__
qui est en français.

Le module `scikit-learn <http://scikit-learn.org/stable/>`__ a largement
contribué au succès de Python dans le domaine du `machine
learning <http://en.wikipedia.org/wiki/Machine_learning>`__. Ce module
inclut de nombreuses techniques regroupées sous le terme statistiques
descriptives. La correspondance anglais-français n’est pas toujours
évidente. Voici quelques termes :


-  `ACP <http://fr.wikipedia.org/wiki/ACP>`__ -
   `PCA <http://scikit-learn.org/stable/modules/decomposition.html#decompositions>`__
-  `k-moyennes <http://fr.wikipedia.org/wiki/Algorithme_des_k-moyennes>`__
   -
   `k-means <http://scikit-learn.org/stable/modules/clustering.html#k-means>`__
-  `CAH <http://en.wikipedia.org/wiki/CAH>`__ - `Hierarchical
   Clustering <http://scikit-learn.org/stable/modules/clustering.html#hierarchical-clustering>`__
-  `k plus proches
   voisins <http://fr.wikipedia.org/wiki/Recherche_des_plus_proches_voisins>`__
   - `k-PPV <http://scikit-learn.org/stable/modules/neighbors.html>`__
-  `analyse linéaire
   discriminante <http://fr.wikipedia.org/wiki/Analyse_discriminante_lin%C3%A9aire>`__
   -
   `LDA <http://scikit-learn.org/stable/modules/classes.html#module-sklearn.lda>`__
-  `régression
   linéaire <http://fr.wikipedia.org/wiki/R%C3%A9gression_lin%C3%A9aire>`__
   - `linear
   regression <http://scikit-learn.org/stable/modules/linear_model.html#ordinary-least-squares>`__

`scikit-learn <http://scikit-learn.org/stable/>`__ est orienté machine
learning, les résultats qu’il produit sont un peu moins complets que
`statsmodels <http://statsmodels.sourceforge.net/>`__ pour les modèles
statistiques linéaires ou
`fastcluster <http://cran.r-project.org/web/packages/fastcluster/vignettes/fastcluster.pdf>`__
pour la CAH. L’objectif de ces deux heures est d’utiliser ces modules
pour étudier un jeu de données :

ACP (Analyse en Composantes Principales)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Le site `data.gouv.fr <https://www.data.gouv.fr/>`__ propose de nombreux
jeux de données dont `Séries chronologiques Education : les élèves du
second
degré <https://www.data.gouv.fr/fr/datasets/series-chronologiques-education-les-eleves-du-second-degre/>`__.
Ces données sont également accessibles comme ceci :

.. code:: ipython3

    import pandas, numpy, pyensae.datasource
    from sklearn.decomposition import PCA
    import matplotlib.pyplot as plt
    
    fichier = pyensae.datasource.download_data("eleve_region.txt")
    df = pandas.read_csv("eleve_region.txt", sep="\t", encoding="utf8", index_col=0)
    print(df.shape)
    df.head(n=5)


.. parsed-literal::
    (27, 21)






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>1993</th>
          <th>1994</th>
          <th>1995</th>
          <th>1996</th>
          <th>1997</th>
          <th>1998</th>
          <th>1999</th>
          <th>2000</th>
          <th>2001</th>
          <th>2002</th>
          <th>...</th>
          <th>2004</th>
          <th>2005</th>
          <th>2006</th>
          <th>2007</th>
          <th>2008</th>
          <th>2009</th>
          <th>2010</th>
          <th>2011</th>
          <th>2012</th>
          <th>2013</th>
        </tr>
        <tr>
          <th>académie</th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>Aix-Marseille</th>
          <td>241357</td>
          <td>242298</td>
          <td>242096</td>
          <td>242295</td>
          <td>243660</td>
          <td>244608</td>
          <td>245536</td>
          <td>247288</td>
          <td>249331</td>
          <td>250871</td>
          <td>...</td>
          <td>250622</td>
          <td>248208</td>
          <td>245755</td>
          <td>243832</td>
          <td>242309</td>
          <td>240664</td>
          <td>240432</td>
          <td>241336</td>
          <td>239051</td>
          <td>240115</td>
        </tr>
        <tr>
          <th>Amiens</th>
          <td>198281</td>
          <td>196871</td>
          <td>195709</td>
          <td>194055</td>
          <td>192893</td>
          <td>191862</td>
          <td>189636</td>
          <td>185977</td>
          <td>183357</td>
          <td>180973</td>
          <td>...</td>
          <td>175610</td>
          <td>172110</td>
          <td>168718</td>
          <td>165295</td>
          <td>163116</td>
          <td>162548</td>
          <td>163270</td>
          <td>164422</td>
          <td>165275</td>
          <td>166345</td>
        </tr>
        <tr>
          <th>Besançon</th>
          <td>116373</td>
          <td>115600</td>
          <td>114282</td>
          <td>113312</td>
          <td>112076</td>
          <td>110261</td>
          <td>108106</td>
          <td>105463</td>
          <td>103336</td>
          <td>102264</td>
          <td>...</td>
          <td>100117</td>
          <td>98611</td>
          <td>97038</td>
          <td>95779</td>
          <td>95074</td>
          <td>94501</td>
          <td>94599</td>
          <td>94745</td>
          <td>94351</td>
          <td>94613</td>
        </tr>
        <tr>
          <th>Bordeaux</th>
          <td>253551</td>
          <td>252644</td>
          <td>249658</td>
          <td>247708</td>
          <td>247499</td>
          <td>245757</td>
          <td>244992</td>
          <td>243047</td>
          <td>243592</td>
          <td>245198</td>
          <td>...</td>
          <td>244805</td>
          <td>244343</td>
          <td>242602</td>
          <td>242933</td>
          <td>243146</td>
          <td>244336</td>
          <td>246806</td>
          <td>250626</td>
          <td>252085</td>
          <td>255761</td>
        </tr>
        <tr>
          <th>Caen</th>
          <td>145435</td>
          <td>144369</td>
          <td>141883</td>
          <td>140658</td>
          <td>139585</td>
          <td>137704</td>
          <td>135613</td>
          <td>133255</td>
          <td>131206</td>
          <td>129271</td>
          <td>...</td>
          <td>125552</td>
          <td>123889</td>
          <td>122550</td>
          <td>121002</td>
          <td>119857</td>
          <td>119426</td>
          <td>119184</td>
          <td>119764</td>
          <td>119010</td>
          <td>119238</td>
        </tr>
      </tbody>
    </table>
    <p>5 rows × 21 columns</p>
    </div>



On veut observer l’évolution du nombre d’élèves. On prend l’année 1993
comme base 100.

.. code:: ipython3

    for c in df.columns:
        if c != "1993":
            df[c] /= df ["1993"]
    df["1993"] /= df["1993"]
    df.head()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>1993</th>
          <th>1994</th>
          <th>1995</th>
          <th>1996</th>
          <th>1997</th>
          <th>1998</th>
          <th>1999</th>
          <th>2000</th>
          <th>2001</th>
          <th>2002</th>
          <th>...</th>
          <th>2004</th>
          <th>2005</th>
          <th>2006</th>
          <th>2007</th>
          <th>2008</th>
          <th>2009</th>
          <th>2010</th>
          <th>2011</th>
          <th>2012</th>
          <th>2013</th>
        </tr>
        <tr>
          <th>académie</th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>Aix-Marseille</th>
          <td>1.0</td>
          <td>1.003899</td>
          <td>1.003062</td>
          <td>1.003886</td>
          <td>1.009542</td>
          <td>1.013470</td>
          <td>1.017315</td>
          <td>1.024574</td>
          <td>1.033038</td>
          <td>1.039419</td>
          <td>...</td>
          <td>1.038387</td>
          <td>1.028385</td>
          <td>1.018222</td>
          <td>1.010255</td>
          <td>1.003944</td>
          <td>0.997129</td>
          <td>0.996168</td>
          <td>0.999913</td>
          <td>0.990446</td>
          <td>0.994854</td>
        </tr>
        <tr>
          <th>Amiens</th>
          <td>1.0</td>
          <td>0.992889</td>
          <td>0.987029</td>
          <td>0.978687</td>
          <td>0.972826</td>
          <td>0.967627</td>
          <td>0.956400</td>
          <td>0.937947</td>
          <td>0.924733</td>
          <td>0.912710</td>
          <td>...</td>
          <td>0.885662</td>
          <td>0.868011</td>
          <td>0.850904</td>
          <td>0.833640</td>
          <td>0.822651</td>
          <td>0.819786</td>
          <td>0.823427</td>
          <td>0.829237</td>
          <td>0.833539</td>
          <td>0.838936</td>
        </tr>
        <tr>
          <th>Besançon</th>
          <td>1.0</td>
          <td>0.993358</td>
          <td>0.982032</td>
          <td>0.973697</td>
          <td>0.963076</td>
          <td>0.947479</td>
          <td>0.928961</td>
          <td>0.906250</td>
          <td>0.887972</td>
          <td>0.878761</td>
          <td>...</td>
          <td>0.860311</td>
          <td>0.847370</td>
          <td>0.833853</td>
          <td>0.823035</td>
          <td>0.816976</td>
          <td>0.812053</td>
          <td>0.812895</td>
          <td>0.814149</td>
          <td>0.810764</td>
          <td>0.813015</td>
        </tr>
        <tr>
          <th>Bordeaux</th>
          <td>1.0</td>
          <td>0.996423</td>
          <td>0.984646</td>
          <td>0.976955</td>
          <td>0.976131</td>
          <td>0.969261</td>
          <td>0.966243</td>
          <td>0.958572</td>
          <td>0.960722</td>
          <td>0.967056</td>
          <td>...</td>
          <td>0.965506</td>
          <td>0.963684</td>
          <td>0.956817</td>
          <td>0.958123</td>
          <td>0.958963</td>
          <td>0.963656</td>
          <td>0.973398</td>
          <td>0.988464</td>
          <td>0.994218</td>
          <td>1.008716</td>
        </tr>
        <tr>
          <th>Caen</th>
          <td>1.0</td>
          <td>0.992670</td>
          <td>0.975577</td>
          <td>0.967154</td>
          <td>0.959776</td>
          <td>0.946842</td>
          <td>0.932465</td>
          <td>0.916251</td>
          <td>0.902162</td>
          <td>0.888858</td>
          <td>...</td>
          <td>0.863286</td>
          <td>0.851851</td>
          <td>0.842644</td>
          <td>0.832001</td>
          <td>0.824128</td>
          <td>0.821164</td>
          <td>0.819500</td>
          <td>0.823488</td>
          <td>0.818304</td>
          <td>0.819871</td>
        </tr>
      </tbody>
    </table>
    <p>5 rows × 21 columns</p>
    </div>



Il n’est pas évident d’analyser ce tableaux de chiffres. On utilise une
ACP pour projeter les académies dans un plan.

.. code:: ipython3

    pca = PCA(n_components=4)
    print(pca.fit(df))
    pca.explained_variance_ratio_


.. parsed-literal::
    PCA(n_components=4)




.. parsed-literal::

    array([9.63024476e-01, 3.37267932e-02, 1.93116666e-03, 6.61046399e-04])



Le premier axe explique l’essentiel de la variance. Les variables n’ont
pas été normalisées car elles évoluent dans les mêmes ordres de
grandeur.

.. code:: ipython3

    plt.bar(numpy.arange(len(pca.explained_variance_ratio_)) + 0.5, 
            pca.explained_variance_ratio_)
    plt.title("Variance expliquée");



.. image:: td2a_cenonce_session_3A_12_0.png


On affiche les académies dans le plan des deux premiers axes :

.. code:: ipython3

    X_reduced = pca.transform(df)
    plt.figure(figsize=(18, 6))
    plt.scatter(X_reduced[:, 0], X_reduced[:, 1])
    
    for label, x, y in zip(df.index, X_reduced[:, 0], X_reduced[:, 1]):
        plt.annotate(
            label, 
            xy=(x, y), xytext=(-10, 10),
            textcoords='offset points', ha='right', va='bottom',
            bbox=dict(boxstyle='round,pad=0.5', fc='yellow', alpha=0.5),
            arrowprops = dict(arrowstyle='->', connectionstyle='arc3,rad=0'))
    plt.title("ACP rapprochant des profils d'évolution similaires");



.. image:: td2a_cenonce_session_3A_14_0.png


Puis on vérifie que deux villes proches ont le même profil d’évolution
au cours des années :

.. code:: ipython3

    sub = df.loc[["Paris", "Bordeaux", "Lyon", "Nice", "La Réunion", "Reims"], :]
    ax = sub.transpose().plot(figsize=(10, 4))
    ax.set_title("Evolution des effectifs au cours du temps");



.. image:: td2a_cenonce_session_3A_16_0.png


L’\ `ACP version
statsmodels <http://statsmodels.sourceforge.net/devel/generated/statsmodels.sandbox.tools.tools_pca.pca.html>`__
produit le même type de résultats. Un exemple est disponible ici : `PCA
and Biplot using Python <http://okomestudio.net/biboroku/?p=2292>`__.

Exercice 1 : CAH (classification ascendante hiérarchique)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Le point commun de ces méthodes est qu’elles ne sont pas supervisées.
L’objectif est de réduire la complexité des données. Réduire le nombre
de dimensions pour l’ACP ou segmenter les observations pour les k-means
et la CAH. On propose d’utiliser une CAH sur les mêmes données.

Le module
`scikit-learn.cluster <http://scikit-learn.org/stable/modules/clustering.html#hierarchical-clustering>`__
ne propose pas de fonction pour dessiner le
`dendrogram <http://en.wikipedia.org/wiki/Dendrogram>`__. Il faudra
utiliser celle-ci :
`dendrogram <http://docs.scipy.org/doc/scipy/reference/generated/scipy.cluster.hierarchy.dendrogram.html>`__
et sans doute s’inspirer du code suivant.

.. code:: ipython3

    from sklearn.cluster import AgglomerativeClustering
    from scipy.cluster.hierarchy import dendrogram
    
    ward = AgglomerativeClustering(linkage='ward', compute_full_tree=True).fit(df)
    dendro = [ ]
    for a,b in ward.children_:
        dendro.append([a, b, float(len(dendro)+1), len(dendro)+1])
        
    fig = plt.figure(figsize=(8, 8))
    ax = fig.add_subplot(1, 1, 1) 
    r = dendrogram(dendro, color_threshold=1, labels=list(df.index),
                   show_leaf_counts=True, ax=ax, orientation="left")    



.. image:: td2a_cenonce_session_3A_19_0.png


Exercice 2 : régression
~~~~~~~~~~~~~~~~~~~~~~~

Ce sont trois méthodes supervisées : on s’en sert pour expliquer prédire
le lien entre deux variables :math:`X` et :math:`Y` (ou ensemble de
variables) ou prédire :math:`Y` en fonction de :math:`X`. Pour cet
exercice, on récupère des données relatives aux salaires `Salaires et
revenus
d’activités <https://www.insee.fr/fr/statistiques/2011542?sommaire=2011795>`__
(les chercher avec la requête *insee données dads* sur un moteur de
recherche). La récupération des données est assez fastidieuse. La
première étape consiste à télécharger les données depuis le site de
l’\ `INSEE <http://www.insee.fr/>`__. La seconde étape consiste à
convertir les données au format
`sqlite3 <https://docs.python.org/3.4/library/sqlite3.html>`__. Pour ce
fichier, ce travail a déjà été effectué et peut être téléchargé depuis
mon site. La base comprend 2 millions de lignes.

.. code:: ipython3

    import pyensae.datasource
    f = pyensae.datasource.download_data("dads2011_gf_salaries11_dbase.zip",
                              website="https://www.insee.fr/fr/statistiques/fichier/2011542/")
    f




.. parsed-literal::
    ['.\\salaries11.dbf', 'varlist_salaries11.dbf', '.\\varmod_salaries11.dbf']



.. code:: ipython3

    import pandas
    try:
        from dbfread import DBF
        use_dbfread = True
    except ImportError as e :
        use_dbfread = False
        
    if use_dbfread:
        import os
        from pyensae.sql.database_exception import ExceptionSQL
        from pyensae.datasource import dBase2sqllite
        print("convert dbase into sqllite")
        try:
            dBase2sqllite("salaries2011.db3", "varlist_salaries11.dbf", overwrite_table="varlist")
            dBase2sqllite("salaries2011.db3", "varmod_salaries11.dbf", overwrite_table="varmod")
            dBase2sqllite("salaries2011.db3", 'salaries11.dbf',
                          overwrite_table="salaries", fLOG = print)
        except ExceptionSQL:
            print("La base de données est déjà renseignée.")
    else :
        print("use of zipped version")
        import pyensae.datasource
        db3 = pyensae.datasource.download_data("salaries2011.zip")
        # pour aller plus vite, données à télécharger au
        # http://www.xavierdupre.fr/enseignement/complements/salaries2011.zip


.. parsed-literal::
    convert dbase into sqllite
    SQL  'drop table if exists salaries11'
    SQL  'create table salaries (A6 TEXT, A17 TEXT, A38 TEXT, REGR TEXT, DEPR TEXT, REGT TEXT, DEPT TEXT, SEXE TEXT, PCS TEXT, CONT_TRAV TEXT, CONV_COLL TEXT, TYP_EMPLOI TEXT, DUREE REAL, DATDEB REAL, DATFIN REAL, CPFD TEXT, DOMEMPL TEXT, DOMEMPL_EM TEXT, FILT TEXT, AGE REAL, CS TEXT, NB_PER REAL, NB_PER_N REAL, NBHEUR REAL, NBHEUR_TOT REAL, TRALCHT TEXT, TREFF TEXT, TRNNETO TEXT, POND REAL)'
    moving line  0  to table salaries
    moving line  20000  to table salaries
    moving line  40000  to table salaries
    moving line  60000  to table salaries
    moving line  80000  to table salaries
    moving line  100000  to table salaries
    moving line  120000  to table salaries
    moving line  140000  to table salaries
    moving line  160000  to table salaries
    moving line  180000  to table salaries
    moving line  200000  to table salaries
    moving line  220000  to table salaries
    moving line  240000  to table salaries
    moving line  260000  to table salaries
    moving line  280000  to table salaries
    moving line  300000  to table salaries
    moving line  320000  to table salaries
    moving line  340000  to table salaries
    moving line  360000  to table salaries
    moving line  380000  to table salaries
    moving line  400000  to table salaries
    moving line  420000  to table salaries
    moving line  440000  to table salaries
    moving line  460000  to table salaries
    moving line  480000  to table salaries
    moving line  500000  to table salaries
    moving line  520000  to table salaries
    moving line  540000  to table salaries
    moving line  560000  to table salaries
    moving line  580000  to table salaries
    moving line  600000  to table salaries
    moving line  620000  to table salaries
    moving line  640000  to table salaries
    moving line  660000  to table salaries
    moving line  680000  to table salaries
    moving line  700000  to table salaries
    moving line  720000  to table salaries
    moving line  740000  to table salaries
    moving line  760000  to table salaries
    moving line  780000  to table salaries
    moving line  800000  to table salaries
    moving line  820000  to table salaries
    moving line  840000  to table salaries
    moving line  860000  to table salaries
    moving line  880000  to table salaries
    moving line  900000  to table salaries
    moving line  920000  to table salaries
    moving line  940000  to table salaries
    moving line  960000  to table salaries
    moving line  980000  to table salaries
    moving line  1000000  to table salaries
    moving line  1020000  to table salaries
    moving line  1040000  to table salaries
    moving line  1060000  to table salaries
    moving line  1080000  to table salaries
    moving line  1100000  to table salaries
    moving line  1120000  to table salaries
    moving line  1140000  to table salaries
    moving line  1160000  to table salaries
    moving line  1180000  to table salaries
    moving line  1200000  to table salaries
    moving line  1220000  to table salaries
    moving line  1240000  to table salaries
    moving line  1260000  to table salaries
    moving line  1280000  to table salaries
    moving line  1300000  to table salaries
    moving line  1320000  to table salaries
    moving line  1340000  to table salaries
    moving line  1360000  to table salaries
    moving line  1380000  to table salaries
    moving line  1400000  to table salaries
    moving line  1420000  to table salaries
    moving line  1440000  to table salaries
    moving line  1460000  to table salaries
    moving line  1480000  to table salaries
    moving line  1500000  to table salaries
    moving line  1520000  to table salaries
    moving line  1540000  to table salaries
    moving line  1560000  to table salaries
    moving line  1580000  to table salaries
    moving line  1600000  to table salaries
    moving line  1620000  to table salaries
    moving line  1640000  to table salaries
    moving line  1660000  to table salaries
    moving line  1680000  to table salaries
    moving line  1700000  to table salaries
    moving line  1720000  to table salaries
    moving line  1740000  to table salaries
    moving line  1760000  to table salaries
    moving line  1780000  to table salaries
    moving line  1800000  to table salaries
    moving line  1820000  to table salaries
    moving line  1840000  to table salaries
    moving line  1860000  to table salaries
    moving line  1880000  to table salaries
    moving line  1900000  to table salaries
    moving line  1920000  to table salaries
    moving line  1940000  to table salaries
    moving line  1960000  to table salaries
    moving line  1980000  to table salaries
    moving line  2000000  to table salaries
    moving line  2020000  to table salaries
    moving line  2040000  to table salaries
    moving line  2060000  to table salaries
    moving line  2080000  to table salaries
    moving line  2100000  to table salaries
    moving line  2120000  to table salaries
    moving line  2140000  to table salaries
    moving line  2160000  to table salaries
    moving line  2180000  to table salaries
    moving line  2200000  to table salaries
    moving line  2220000  to table salaries
    moving line  2240000  to table salaries


Les données des salaires ne sont pas numériques, elles correspondent à
des intervalles qu’on convertit en prenant le milieu de l’intervalle.
Pour le dernier, on prend la borne supérieure.

.. code:: ipython3

    import sqlite3, pandas
    con = sqlite3.connect("salaries2011.db3")
    df = pandas.io.sql.read_sql("select * from varmod", con)
    con.close()
    
    values = df[ df.VARIABLE == "TRNNETO"].copy()
    
    def process_intervalle(s):
        # [14 000 ; 16 000[ euros
        acc = "0123456789;+"
        s0 = "".join(c for c in s if c in acc)
        spl = s0.split(';')
        if len(spl) != 2:
            raise ValueError("Unable to process '{0}'".format(s0))
        try:
            a = float(spl[0])
        except Exception as e:
            raise ValueError("Cannot interpret '{0}' - {1}".format(s, spl))
        b = float(spl[1]) if "+" not in spl[1] else None
        if b is None:
            return a
        else:
            return (a+b) / 2.0
    
    values["montant"] = values.apply(lambda r : process_intervalle(r ["MODLIBELLE"]), axis = 1)
    values.head()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>VARIABLE</th>
          <th>MODALITE</th>
          <th>MODLIBELLE</th>
          <th>montant</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>8957</th>
          <td>TRNNETO</td>
          <td>00</td>
          <td>[0 ; 200[ euros</td>
          <td>100.0</td>
        </tr>
        <tr>
          <th>8958</th>
          <td>TRNNETO</td>
          <td>01</td>
          <td>[200 ; 500[ euros</td>
          <td>350.0</td>
        </tr>
        <tr>
          <th>8959</th>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>8960</th>
          <td>TRNNETO</td>
          <td>03</td>
          <td>[1 000 ; 1 500[ euros</td>
          <td>1250.0</td>
        </tr>
        <tr>
          <th>8961</th>
          <td>TRNNETO</td>
          <td>04</td>
          <td>[1 500 ; 2 000[ euros</td>
          <td>1750.0</td>
        </tr>
      </tbody>
    </table>
    </div>



On crée la base d’apprentissage :

.. code:: ipython3

    import sqlite3, pandas
    con = sqlite3.connect("salaries2011.db3")
    data = pandas.io.sql.read_sql("select TRNNETO,AGE,SEXE from salaries", con)
    con.close()
    
    salaires = data.merge ( values, left_on="TRNNETO", right_on="MODALITE" )
    salaires.head()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>TRNNETO</th>
          <th>AGE</th>
          <th>SEXE</th>
          <th>VARIABLE</th>
          <th>MODALITE</th>
          <th>MODLIBELLE</th>
          <th>montant</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>02</td>
          <td>49.0</td>
          <td>1</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>02</td>
          <td>27.0</td>
          <td>1</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>02</td>
          <td>22.0</td>
          <td>1</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>3</th>
          <td>02</td>
          <td>26.0</td>
          <td>1</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>4</th>
          <td>02</td>
          <td>29.0</td>
          <td>2</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
        </tr>
      </tbody>
    </table>
    </div>



On récupère les variables utiles pour la régression.

.. code:: ipython3

    salaires["M"] = salaires.apply( lambda r : 1 if r["SEXE"] == "1" else 0, axis=1)
    salaires["F"] = 1 - salaires["M"]  # en supposant que le sexe est toujours renseigné
    data = salaires[["AGE", "M", "F", "montant"]]
    data = data[data.M + data.F > 0]
    data.head()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>AGE</th>
          <th>M</th>
          <th>F</th>
          <th>montant</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>49.0</td>
          <td>1</td>
          <td>0</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>27.0</td>
          <td>1</td>
          <td>0</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>22.0</td>
          <td>1</td>
          <td>0</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>3</th>
          <td>26.0</td>
          <td>1</td>
          <td>0</td>
          <td>750.0</td>
        </tr>
        <tr>
          <th>4</th>
          <td>29.0</td>
          <td>0</td>
          <td>1</td>
          <td>750.0</td>
        </tr>
      </tbody>
    </table>
    </div>



Ce type d’écriture est plutôt lent car une fonction Python est exécutée
à chaque itération. Il est préférable dès que c’est possible d’utiliser
les expressions avec des indices sans passer par la fonction
`apply <http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.apply.html>`__
qui créé une copie de chaque ligne avant d’appliquer la fonction à
appliquer à chacune d’entre elles.

.. code:: ipython3

    salaires["M2"] = 0
    salaires.loc[salaires["SEXE"] == "1", "M2"] = 1
    salaires["F2"] = 1 - salaires["M2"]
    salaires.head()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>TRNNETO</th>
          <th>AGE</th>
          <th>SEXE</th>
          <th>VARIABLE</th>
          <th>MODALITE</th>
          <th>MODLIBELLE</th>
          <th>montant</th>
          <th>M</th>
          <th>F</th>
          <th>M2</th>
          <th>F2</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>02</td>
          <td>49.0</td>
          <td>1</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
          <td>1</td>
          <td>0</td>
          <td>1</td>
          <td>0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>02</td>
          <td>27.0</td>
          <td>1</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
          <td>1</td>
          <td>0</td>
          <td>1</td>
          <td>0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>02</td>
          <td>22.0</td>
          <td>1</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
          <td>1</td>
          <td>0</td>
          <td>1</td>
          <td>0</td>
        </tr>
        <tr>
          <th>3</th>
          <td>02</td>
          <td>26.0</td>
          <td>1</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
          <td>1</td>
          <td>0</td>
          <td>1</td>
          <td>0</td>
        </tr>
        <tr>
          <th>4</th>
          <td>02</td>
          <td>29.0</td>
          <td>2</td>
          <td>TRNNETO</td>
          <td>02</td>
          <td>[500 ; 1 000[ euros</td>
          <td>750.0</td>
          <td>0</td>
          <td>1</td>
          <td>0</td>
          <td>1</td>
        </tr>
      </tbody>
    </table>
    </div>



Il ne reste plus qu’à faire la régression.

version scikit-learn
^^^^^^^^^^^^^^^^^^^^

Vous pouvez vous inspirer de cet
`exemple <http://scikit-learn.org/stable/auto_examples/linear_model/plot_ols.html>`__.


version statsmodels
^^^^^^^^^^^^^^^^^^^

L’exemple avec ce module est
`ici <http://statsmodels.sourceforge.net/devel/examples/notebooks/generated/ols.html>`__.