.. _seance5sqlmultidimensionnelleenoncerst:

=========================================
Données multidimensionnelles SQL - énoncé
=========================================


.. only:: html

    **Links:** :download:`notebook <seance5_sql_multidimensionnelle_enonce.ipynb>`, :downloadlink:`html <seance5_sql_multidimensionnelle_enonce2html.html>`, :download:`PDF <seance5_sql_multidimensionnelle_enonce.pdf>`, :download:`python <seance5_sql_multidimensionnelle_enonce.py>`, :downloadlink:`slides <seance5_sql_multidimensionnelle_enonce.slides.html>`, :githublink:`GitHub|_doc/notebooks/sessions/seance5_sql_multidimensionnelle_enonce.ipynb|*`


Ce notebook propose l’utilisation de SQL avec
`SQLite <https://sqlite.org/>`__ pour manipuler les données depuis un
notebook (avec le module
`sqlite3 <https://docs.python.org/3.6/library/sqlite3.html>`__).

.. code:: ipython3

    %matplotlib inline
    import matplotlib.pyplot as plt
    plt.style.use('ggplot')
    import pyensae
    from pyquickhelper.helpgen import NbImage
    from jyquickhelper import add_notebook_menu
    add_notebook_menu()


.. parsed-literal::
    Populating the interactive namespace from numpy and matplotlib






.. contents::
    :local:





Représentation
~~~~~~~~~~~~~~

Le module `pandas <http://pandas.pydata.org/>`__ manipule des tables et
c’est la façon la plus commune de représenter les données. Lorsque les
données sont multidimensionnelles, on distingue les coordonnées des
valeurs :

.. code:: ipython3

    NbImage("cube1.png")




.. image:: seance5_sql_multidimensionnelle_enonce_4_0.png



Dans cet exemple, il y a :


-  3 coordonnées : Age, Profession, Annéee
-  2 valeurs : Espérance de vie, Population

On peut représenter les donnés également comme ceci :

.. code:: ipython3

    NbImage("cube2.png")




.. image:: seance5_sql_multidimensionnelle_enonce_6_0.png



C’est assez simple. Prenons un exemple : `table de mortalité de 1960 à
2010 <http://www.data-publica.com/opendata/7098--population-et-conditions-sociales-table-de-mortalite-de-1960-a-2010>`__
qu’on récupère à l’aide de la fonction
`table_mortalite_euro_stat <http://www.xavierdupre.fr/app/actuariat_python/helpsphinx/actuariat_python/data/population.html#actuariat_python.data.population.table_mortalite_euro_stat>`__.
C’est assez long (4-5 minutes) sur l’ensemble des données car elles
doivent être prétraitées (voir la documentation de la fonction). Pour
écouter, il faut utiliser le paramètre *stop_at*.

.. code:: ipython3

    from actuariat_python.data import table_mortalite_euro_stat 
    table_mortalite_euro_stat()




.. parsed-literal::
    'mortalite.txt'



.. code:: ipython3

    import os
    os.stat("mortalite.txt")




.. parsed-literal::
    os.stat_result(st_mode=33206, st_ino=4222124650782732, st_dev=2797837379, st_nlink=1, st_uid=0, st_gid=0, st_size=105075819, st_atime=1453573270, st_mtime=1453573285, st_ctime=1453573270)



.. code:: ipython3

    import pandas
    df = pandas.read_csv("mortalite.txt", sep="\t", encoding="utf8", low_memory=False)
    df.head()






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>annee</th>
          <th>valeur</th>
          <th>age</th>
          <th>age_num</th>
          <th>indicateur</th>
          <th>genre</th>
          <th>pays</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>2009</td>
          <td>0.00080</td>
          <td>Y01</td>
          <td>1</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2008</td>
          <td>0.00067</td>
          <td>Y01</td>
          <td>1</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2007</td>
          <td>0.00052</td>
          <td>Y01</td>
          <td>1</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>3</th>
          <td>2006</td>
          <td>0.00123</td>
          <td>Y01</td>
          <td>1</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>4</th>
          <td>2013</td>
          <td>0.00016</td>
          <td>Y01</td>
          <td>1</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AT</td>
        </tr>
      </tbody>
    </table>
    </div>



Les indicateurs pour deux âges différents :

.. code:: ipython3

    df [ ((df.age=="Y60") | (df.age=="Y61")) & (df.annee == 2000) & (df.pays=="FR") & (df.genre=="F")]






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>annee</th>
          <th>valeur</th>
          <th>age</th>
          <th>age_num</th>
          <th>indicateur</th>
          <th>genre</th>
          <th>pays</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>86895</th>
          <td>2000</td>
          <td>0.00502</td>
          <td>Y60</td>
          <td>60</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>88435</th>
          <td>2000</td>
          <td>0.00486</td>
          <td>Y61</td>
          <td>61</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>482573</th>
          <td>2000</td>
          <td>25.80000</td>
          <td>Y60</td>
          <td>60</td>
          <td>LIFEXP</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>484098</th>
          <td>2000</td>
          <td>24.90000</td>
          <td>Y61</td>
          <td>61</td>
          <td>LIFEXP</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>876261</th>
          <td>2000</td>
          <td>0.00501</td>
          <td>Y60</td>
          <td>60</td>
          <td>PROBDEATH</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>877801</th>
          <td>2000</td>
          <td>0.00485</td>
          <td>Y61</td>
          <td>61</td>
          <td>PROBDEATH</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>1272615</th>
          <td>2000</td>
          <td>0.99499</td>
          <td>Y60</td>
          <td>60</td>
          <td>PROBSURV</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>1274155</th>
          <td>2000</td>
          <td>0.99515</td>
          <td>Y61</td>
          <td>61</td>
          <td>PROBSURV</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>1668215</th>
          <td>2000</td>
          <td>93076.00000</td>
          <td>Y60</td>
          <td>60</td>
          <td>PYLIVED</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>1669740</th>
          <td>2000</td>
          <td>92618.00000</td>
          <td>Y61</td>
          <td>61</td>
          <td>PYLIVED</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>2061149</th>
          <td>2000</td>
          <td>93310.00000</td>
          <td>Y60</td>
          <td>60</td>
          <td>SURVIVORS</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>2062674</th>
          <td>2000</td>
          <td>92843.00000</td>
          <td>Y61</td>
          <td>61</td>
          <td>SURVIVORS</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>2454128</th>
          <td>2000</td>
          <td>2405594.00000</td>
          <td>Y60</td>
          <td>60</td>
          <td>TOTPYLIVED</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>2455653</th>
          <td>2000</td>
          <td>2312517.00000</td>
          <td>Y61</td>
          <td>61</td>
          <td>TOTPYLIVED</td>
          <td>F</td>
          <td>FR</td>
        </tr>
      </tbody>
    </table>
    </div>



Exercice 1 : filtre
~~~~~~~~~~~~~~~~~~~

On veut comparer les espérances de vie pour deux pays et deux années.

.. code:: ipython3

    #

Données trop grosses pour tenir en mémoire : SQLite
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: ipython3

    df.shape




.. parsed-literal::
    (2760921, 7)



Les données sont trop grosses pour tenir dans une feuille Excel. Pour
les consulter, il n’y a pas d’autres moyens que d’en regarder des
extraits. Que passe quand même ceci n’est pas possible ? Quelques
solutions :


-  augmenter la mémoire de l’ordinateur, avec 20 Go, on peut faire
   beaucoup de choses
-  stocker les données dans un serveur SQL
-  stocker les données sur un système distribué (cloud, Hadoop, …)

La seconde option n’est pas toujours simple, il faut installer un
serveur SQL. Pour aller plus vite, on peut simplement utiliser
`SQLite <https://www.sqlite.org/>`__ qui est une façon de faire du SQL
sans serveur (cela prend quelques minutes). On utilise la méthode
`to_sql <http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html>`__.

.. code:: ipython3

    import sqlite3
    from pandas.io import sql
    cnx = sqlite3.connect('mortalite.db3')
    try:
        df.to_sql(name='mortalite', con=cnx)
    except ValueError as e:
        if "Table 'mortalite' already exists" not in str(e):
            # seulement si l'erreur ne vient pas du fait que cela 
            # a déjà été fait
            raise e
    # on peut ajouter d'autres dataframe à la table comme si elle était créée par morceau
    # voir le paramètre if_exists de la fonction to_sql

On peut maintenant récupérer un morceau avec la fonction
`read_sql <http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html?highlight=read_sql#pandas.read_sql>`__.

.. code:: ipython3

    import pandas
    example = pandas.read_sql('select * from mortalite where age_num==50 limit 5', cnx)
    example






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>index</th>
          <th>annee</th>
          <th>valeur</th>
          <th>age</th>
          <th>age_num</th>
          <th>indicateur</th>
          <th>genre</th>
          <th>pays</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>69225</td>
          <td>2009</td>
          <td>0.00290</td>
          <td>Y50</td>
          <td>50</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>1</th>
          <td>69226</td>
          <td>2008</td>
          <td>0.00333</td>
          <td>Y50</td>
          <td>50</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>2</th>
          <td>69227</td>
          <td>2007</td>
          <td>0.00292</td>
          <td>Y50</td>
          <td>50</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>3</th>
          <td>69228</td>
          <td>2006</td>
          <td>0.00371</td>
          <td>Y50</td>
          <td>50</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>4</th>
          <td>69229</td>
          <td>2013</td>
          <td>0.00194</td>
          <td>Y50</td>
          <td>50</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AT</td>
        </tr>
      </tbody>
    </table>
    </div>



L’ensemble des données restent sur le disque, seul le résultat de la
requête est chargé en mémoire. Si on ne peut pas faire tenir les données
en mémoire, il faut soit en obtenir une vue partielle (un échantillon
aléatoire, un vue filtrée), soit une vue agrégrée.

Pour finir, il faut fermer la connexion pour laisser d’autres
applications ou notebook modifier la base ou tout simplement supprimer
le fichier.

.. code:: ipython3

    cnx.close()

Sous Windows, on peut consulter la base avec le logiciel
`SQLiteSpy <http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index>`__.

.. code:: ipython3

    NbImage("sqlite.png")




.. image:: seance5_sql_multidimensionnelle_enonce_24_0.png



Sous Linux ou Max, on peut utiliser une extension Firefox `SQLite
Manager <https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/?src>`__.
Dans ce notebook, on utilisera la commande magique
`%%SQL <http://www.xavierdupre.fr/app/pyensae/helpsphinx/notebooks/pyensae_sql_magic.html>`__
du module
`pyensae <http://www.xavierdupre.fr/app/pyensae/helpsphinx/>`__ :

.. code:: ipython3

    %load_ext pyensae
    %SQL_connect mortalite.db3




.. parsed-literal::
    <pyensae.sql.sql_interface_database.InterfaceSQLDatabase at 0xaa11f28>



.. code:: ipython3

    %SQL_tables




.. parsed-literal::
    ['mortalite']



.. code:: ipython3

    %SQL_schema mortalite




.. parsed-literal::
    {0: ('index', int),
     1: ('annee', int),
     2: ('valeur', float),
     3: ('age', str),
     4: ('age_num', float),
     5: ('indicateur', str),
     6: ('genre', str),
     7: ('pays', str)}



.. code:: ipython3

    %%SQL
    SELECT COUNT(*) FROM mortalite






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>COUNT(*)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>2760921</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    %SQL_close

Exercice 2 : échantillon aléatoire
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Si on ne peut pas faire tenir les données en mémoire, on peut soit
regarder les premières lignes soit prendre un échantillon aléatoire.
Deux options :


-  `Dataframe.sample <http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sample.html>`__
-  `create_function <https://docs.python.org/3.4/library/sqlite3.html#sqlite3.Connection.create_function>`__

La première fonction est simple :

.. code:: ipython3

    sample = df.sample(frac=0.1)
    sample.shape, df.shape




.. parsed-literal::
    ((276092, 7), (2760921, 7))



Je ne sais pas si cela peut être réalisé sans charger les données en
mémoire. Si les données pèsent 20 Go, cette méthode n’aboutira pas.
Pourtant, on veut juste un échantillon pour commencer à regarder les
données. On utilise la seconde option avec
`create_function <https://docs.python.org/3.4/library/sqlite3.html#sqlite3.Connection.create_function>`__
et la fonction suivante :

.. code:: ipython3

    import random  #loi uniforme
    def echantillon(proportion):
        return 1 if random.random() < proportion else 0

.. code:: ipython3

    import sqlite3
    from pandas.io import sql
    cnx = sqlite3.connect('mortalite.db3')

.. code:: ipython3

    cnx.create_function('echantillon', 1, echantillon)

Que faut-il écrire ici pour récupérer 1% de la table ?

.. code:: ipython3

    import pandas
    #example = pandas.read_sql(' ???  ', cnx)
    #example

.. code:: ipython3

    cnx.close()

Pseudo Map/Reduce avec SQLite
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

La liste des `mots-clés du langage SQL utilisés par
SQLite <https://www.sqlite.org/keyword_index.html>`__ n’est pas aussi
riche que d’autres solutions de serveurs SQL. La médiane ne semble pas
en faire partie. Cependant, pour une année, un genre, un âge donné, on
voudrait calculer la médiane de l’espérance de vie sur l’ensembles des
pays.

.. code:: ipython3

    import sqlite3, pandas
    from pandas.io import sql
    cnx = sqlite3.connect('mortalite.db3')

.. code:: ipython3

    pandas.read_sql('select pays,count(*) from mortalite group by pays', cnx)






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>pays</th>
          <th>count(*)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>AM</td>
          <td>7224</td>
        </tr>
        <tr>
          <th>1</th>
          <td>AT</td>
          <td>79464</td>
        </tr>
        <tr>
          <th>2</th>
          <td>AZ</td>
          <td>12642</td>
        </tr>
        <tr>
          <th>3</th>
          <td>BE</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>4</th>
          <td>BG</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>5</th>
          <td>BY</td>
          <td>5418</td>
        </tr>
        <tr>
          <th>6</th>
          <td>CH</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>7</th>
          <td>CY</td>
          <td>37926</td>
        </tr>
        <tr>
          <th>8</th>
          <td>CZ</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>9</th>
          <td>DE</td>
          <td>52374</td>
        </tr>
        <tr>
          <th>10</th>
          <td>DE_TOT</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>11</th>
          <td>DK</td>
          <td>72240</td>
        </tr>
        <tr>
          <th>12</th>
          <td>EA16</td>
          <td>35688</td>
        </tr>
        <tr>
          <th>13</th>
          <td>EA17</td>
          <td>35688</td>
        </tr>
        <tr>
          <th>14</th>
          <td>EA18</td>
          <td>21672</td>
        </tr>
        <tr>
          <th>15</th>
          <td>EA19</td>
          <td>21672</td>
        </tr>
        <tr>
          <th>16</th>
          <td>EE</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>17</th>
          <td>EEA30</td>
          <td>21672</td>
        </tr>
        <tr>
          <th>18</th>
          <td>EEA31</td>
          <td>21672</td>
        </tr>
        <tr>
          <th>19</th>
          <td>EFTA</td>
          <td>36120</td>
        </tr>
        <tr>
          <th>20</th>
          <td>EL</td>
          <td>95718</td>
        </tr>
        <tr>
          <th>21</th>
          <td>ES</td>
          <td>70434</td>
        </tr>
        <tr>
          <th>22</th>
          <td>EU27</td>
          <td>21672</td>
        </tr>
        <tr>
          <th>23</th>
          <td>EU28</td>
          <td>21672</td>
        </tr>
        <tr>
          <th>24</th>
          <td>FI</td>
          <td>61404</td>
        </tr>
        <tr>
          <th>25</th>
          <td>FR</td>
          <td>28896</td>
        </tr>
        <tr>
          <th>26</th>
          <td>FX</td>
          <td>48762</td>
        </tr>
        <tr>
          <th>27</th>
          <td>GE</td>
          <td>10836</td>
        </tr>
        <tr>
          <th>28</th>
          <td>HR</td>
          <td>23478</td>
        </tr>
        <tr>
          <th>29</th>
          <td>HU</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>30</th>
          <td>IE</td>
          <td>50646</td>
        </tr>
        <tr>
          <th>31</th>
          <td>IS</td>
          <td>93912</td>
        </tr>
        <tr>
          <th>32</th>
          <td>IT</td>
          <td>52374</td>
        </tr>
        <tr>
          <th>33</th>
          <td>LI</td>
          <td>36120</td>
        </tr>
        <tr>
          <th>34</th>
          <td>LT</td>
          <td>79464</td>
        </tr>
        <tr>
          <th>35</th>
          <td>LU</td>
          <td>79464</td>
        </tr>
        <tr>
          <th>36</th>
          <td>LV</td>
          <td>21672</td>
        </tr>
        <tr>
          <th>37</th>
          <td>MD</td>
          <td>12642</td>
        </tr>
        <tr>
          <th>38</th>
          <td>ME</td>
          <td>16254</td>
        </tr>
        <tr>
          <th>39</th>
          <td>MK</td>
          <td>36120</td>
        </tr>
        <tr>
          <th>40</th>
          <td>MT</td>
          <td>50901</td>
        </tr>
        <tr>
          <th>41</th>
          <td>NL</td>
          <td>52374</td>
        </tr>
        <tr>
          <th>42</th>
          <td>NO</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>43</th>
          <td>PL</td>
          <td>43344</td>
        </tr>
        <tr>
          <th>44</th>
          <td>PT</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>45</th>
          <td>RO</td>
          <td>83076</td>
        </tr>
        <tr>
          <th>46</th>
          <td>RS</td>
          <td>25284</td>
        </tr>
        <tr>
          <th>47</th>
          <td>RU</td>
          <td>9030</td>
        </tr>
        <tr>
          <th>48</th>
          <td>SE</td>
          <td>83076</td>
        </tr>
        <tr>
          <th>49</th>
          <td>SI</td>
          <td>57792</td>
        </tr>
        <tr>
          <th>50</th>
          <td>SK</td>
          <td>97524</td>
        </tr>
        <tr>
          <th>51</th>
          <td>TR</td>
          <td>9030</td>
        </tr>
        <tr>
          <th>52</th>
          <td>UA</td>
          <td>10836</td>
        </tr>
        <tr>
          <th>53</th>
          <td>UK</td>
          <td>37926</td>
        </tr>
      </tbody>
    </table>
    </div>



Il n’y a pas le même nombre de données selon les pays, il est probable
que le nombre de pays pour lesquels il existe des données varie selon
les âges et les années.

.. code:: ipython3

    query = """SELECT nb_country, COUNT(*) AS nb_rows FROM (
                    SELECT annee,age,age_num, count(*) AS nb_country FROM mortalite 
                    WHERE indicateur=="LIFEXP" AND genre=="F"
                    GROUP BY annee,age,age_num
                ) GROUP BY nb_country"""
    df = pandas.read_sql(query, cnx)

.. code:: ipython3

    df.sort_values("nb_country", ascending=False).head(n=2)






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>nb_country</th>
          <th>nb_rows</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>37</th>
          <td>104</td>
          <td>5</td>
        </tr>
        <tr>
          <th>36</th>
          <td>102</td>
          <td>2</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    df.plot(x="nb_country", y="nb_rows")




.. parsed-literal::
    <matplotlib.axes._subplots.AxesSubplot at 0xaa32b00>



Soit un nombre inconstant de pays. Le fait qu’on est 100 pays suggère
qu’on ait une erreur également.

.. code:: ipython3

    query = """SELECT annee,age,age_num, count(*) AS nb_country FROM mortalite 
                    WHERE indicateur=="LIFEXP" AND genre=="F"
                    GROUP BY annee,age,age_num
                    HAVING nb_country >= 100"""
    df = pandas.read_sql(query, cnx)

.. code:: ipython3

    df.head()






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>annee</th>
          <th>age</th>
          <th>age_num</th>
          <th>nb_country</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>2006</td>
          <td>None</td>
          <td>None</td>
          <td>104</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2007</td>
          <td>None</td>
          <td>None</td>
          <td>104</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2008</td>
          <td>None</td>
          <td>None</td>
          <td>104</td>
        </tr>
        <tr>
          <th>3</th>
          <td>2009</td>
          <td>None</td>
          <td>None</td>
          <td>104</td>
        </tr>
        <tr>
          <th>4</th>
          <td>2010</td>
          <td>None</td>
          <td>None</td>
          <td>104</td>
        </tr>
      </tbody>
    </table>
    </div>



Ce sont des valeurs manquantes. Le problème pour calculer la médiane
pour chaque observation est qu’il faut d’abord regrouper les lignes de
la table par indicateur puis choisir la médiane dans chaque de ces
petits groupes. On s’inspire pour cela de la logique Map/Reduce et de la
fonction
`create_aggregate <https://docs.python.org/3.4/library/sqlite3.html#sqlite3.Connection.create_aggregate>`__.

Exercice 3 : reducer SQL
~~~~~~~~~~~~~~~~~~~~~~~~

Il faut compléter le programme suivant.

.. code:: ipython3

    class ReducerMediane:
        def __init__(self):
            # ???
            pass
        def step(self, value):
            # ???
            #
            pass
        def finalize(self):
            # ???
            # return ... //2 ]
            pass
        
    cnx.create_aggregate("ReducerMediane", 1, ReducerMediane)    

.. code:: ipython3

    #query = """SELECT annee,age,age_num, ...... AS mediane FROM mortalite 
    #                WHERE indicateur=="LIFEXP" AND genre=="F"
    #                GROUP BY annee,age,age_num"""
    #df = pandas.read_sql(query, cnx)

.. code:: ipython3

    cnx.close()

Notion d’index
~~~~~~~~~~~~~~

En SQL et pour de grandes tables, la notion d’index joue un rôle
important pour accélérer les opérations de jointures (``JOIN``) ou de
regroupement (``GROUP BY``). L’article `A thorough guide to SQLite
database operations in
Python <http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html>`__
montre comment faire les principales opérations.