.. _sqlmapreducerst:

====================================
Mapper, Reducers customisés avec SQL
====================================


.. only:: html

    **Links:** :download:`notebook <sql_map_reduce.ipynb>`, :downloadlink:`html <sql_map_reduce2html.html>`, :download:`PDF <sql_map_reduce.pdf>`, :download:`python <sql_map_reduce.py>`, :downloadlink:`slides <sql_map_reduce.slides.html>`, :githublink:`GitHub|_doc/notebooks/sql/sql_map_reduce.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

    from jyquickhelper import add_notebook_menu
    add_notebook_menu()






.. contents::
    :local:





.. code:: ipython3

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

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:: sql_map_reduce_5_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:: sql_map_reduce_7_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 sparkouille.datasets 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=33776997205344496, st_dev=1182297439, st_nlink=1, st_uid=0, st_gid=0, st_size=112417907, st_atime=1523378271, st_mtime=1523378288, st_ctime=1523378271)



.. code:: ipython3

    import pandas
    df = pandas.read_csv("mortalite.txt", sep="\t", encoding="utf8", low_memory=False)
    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>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>2016</td>
          <td>0.00044</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AL</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2015</td>
          <td>0.00043</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AL</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2014</td>
          <td>0.00043</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AL</td>
        </tr>
        <tr>
          <th>3</th>
          <td>2016</td>
          <td>0.00035</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>4</th>
          <td>2015</td>
          <td>0.00035</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</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>
    <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>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>92886</th>
          <td>2000</td>
          <td>5.020000e-03</td>
          <td>Y60</td>
          <td>60.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>94532</th>
          <td>2000</td>
          <td>4.860000e-03</td>
          <td>Y61</td>
          <td>61.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>516236</th>
          <td>2000</td>
          <td>2.580000e+01</td>
          <td>Y60</td>
          <td>60.0</td>
          <td>LIFEXP</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>517871</th>
          <td>2000</td>
          <td>2.490000e+01</td>
          <td>Y61</td>
          <td>61.0</td>
          <td>LIFEXP</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>938100</th>
          <td>2000</td>
          <td>5.010000e-03</td>
          <td>Y60</td>
          <td>60.0</td>
          <td>PROBDEATH</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>939746</th>
          <td>2000</td>
          <td>4.850000e-03</td>
          <td>Y61</td>
          <td>61.0</td>
          <td>PROBDEATH</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>1361922</th>
          <td>2000</td>
          <td>9.949900e-01</td>
          <td>Y60</td>
          <td>60.0</td>
          <td>PROBSURV</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>1363568</th>
          <td>2000</td>
          <td>9.951500e-01</td>
          <td>Y61</td>
          <td>61.0</td>
          <td>PROBSURV</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>1785194</th>
          <td>2000</td>
          <td>9.307600e+04</td>
          <td>Y60</td>
          <td>60.0</td>
          <td>PYLIVED</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>1786829</th>
          <td>2000</td>
          <td>9.261800e+04</td>
          <td>Y61</td>
          <td>61.0</td>
          <td>PYLIVED</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>2206508</th>
          <td>2000</td>
          <td>9.331000e+04</td>
          <td>Y60</td>
          <td>60.0</td>
          <td>SURVIVORS</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>2208143</th>
          <td>2000</td>
          <td>9.284300e+04</td>
          <td>Y61</td>
          <td>61.0</td>
          <td>SURVIVORS</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>2627855</th>
          <td>2000</td>
          <td>2.405594e+06</td>
          <td>Y60</td>
          <td>60.0</td>
          <td>TOTPYLIVED</td>
          <td>F</td>
          <td>FR</td>
        </tr>
        <tr>
          <th>2629490</th>
          <td>2000</td>
          <td>2.312517e+06</td>
          <td>Y61</td>
          <td>61.0</td>
          <td>TOTPYLIVED</td>
          <td>F</td>
          <td>FR</td>
        </tr>
      </tbody>
    </table>
    </div>



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

On charge une grosse base de données (assez petite pour que la séance ne
soit pas trop longue).

.. code:: ipython3

    df.shape




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



Les données sont trop grosses pour tenir dans une feuille Excel et les
consulter il n’y a pas d’autres moyens que d’en regarder des extraits.
Que passe-t-il quand les données sont encore plus grosses et qu’elles ne
tiennent pas en mémoire ? 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>
    <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>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>74015</td>
          <td>2016</td>
          <td>0.00148</td>
          <td>Y50</td>
          <td>50.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AL</td>
        </tr>
        <tr>
          <th>1</th>
          <td>74016</td>
          <td>2015</td>
          <td>0.00174</td>
          <td>Y50</td>
          <td>50.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AL</td>
        </tr>
        <tr>
          <th>2</th>
          <td>74017</td>
          <td>2014</td>
          <td>0.00196</td>
          <td>Y50</td>
          <td>50.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AL</td>
        </tr>
        <tr>
          <th>3</th>
          <td>74018</td>
          <td>2016</td>
          <td>0.00283</td>
          <td>Y50</td>
          <td>50.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</td>
        </tr>
        <tr>
          <th>4</th>
          <td>74019</td>
          <td>2015</td>
          <td>0.00296</td>
          <td>Y50</td>
          <td>50.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AM</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:: sql_map_reduce_23_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 0x257a407d400>



.. 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>
    <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>COUNT(*)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>2956833</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    %SQL_close

Cas 1 : filtrer pour créer un é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::
    ((295683, 7), (2956833, 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')

On déclare la fonction à la base de données.

.. code:: ipython3

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

On veut récupérer environ 1% de la table ? On écrit d’abord le filtre.

.. code:: ipython3

    sample = pandas.read_sql('SELECT * FROM mortalite WHERE echantillon(0.01)', cnx)
    sample.shape




.. parsed-literal::
    (29515, 8)



.. code:: ipython3

    sample.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>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>50</td>
          <td>1975</td>
          <td>0.00134</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>AT</td>
        </tr>
        <tr>
          <th>1</th>
          <td>129</td>
          <td>2009</td>
          <td>0.00089</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>BG</td>
        </tr>
        <tr>
          <th>2</th>
          <td>137</td>
          <td>2001</td>
          <td>0.00103</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>BG</td>
        </tr>
        <tr>
          <th>3</th>
          <td>281</td>
          <td>2001</td>
          <td>0.00030</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>CZ</td>
        </tr>
        <tr>
          <th>4</th>
          <td>289</td>
          <td>1993</td>
          <td>0.00051</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>DEATHRATE</td>
          <td>F</td>
          <td>CZ</td>
        </tr>
      </tbody>
    </table>
    </div>



On ferme la connexion.

.. 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

    pays = pandas.read_sql('SELECT pays, COUNT(*) FROM mortalite GROUP BY pays', cnx)
    pays.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>pays</th>
          <th>count(*)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>AL</td>
          <td>5418</td>
        </tr>
        <tr>
          <th>1</th>
          <td>AM</td>
          <td>10836</td>
        </tr>
        <tr>
          <th>2</th>
          <td>AT</td>
          <td>84882</td>
        </tr>
        <tr>
          <th>3</th>
          <td>AZ</td>
          <td>16254</td>
        </tr>
        <tr>
          <th>4</th>
          <td>BE</td>
          <td>102942</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>
    <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>nb_country</th>
          <th>nb_rows</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>40</th>
          <td>100</td>
          <td>7</td>
        </tr>
        <tr>
          <th>39</th>
          <td>98</td>
          <td>2</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    ax = df.plot(x="nb_country", y="nb_rows")
    ax.set_title("Nombre de données par pays");



.. image:: sql_map_reduce_49_0.png


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>
    <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>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>100</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2007</td>
          <td>None</td>
          <td>None</td>
          <td>100</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2008</td>
          <td>None</td>
          <td>None</td>
          <td>100</td>
        </tr>
        <tr>
          <th>3</th>
          <td>2009</td>
          <td>None</td>
          <td>None</td>
          <td>100</td>
        </tr>
        <tr>
          <th>4</th>
          <td>2010</td>
          <td>None</td>
          <td>None</td>
          <td>100</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>`__.

Cas 2 : reducer customisé avec SQL
----------------------------------

Le reducer se présente toujours sous la forme suivante :

.. code:: ipython3

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

Qu’on renseigne de la sorte :

.. code:: ipython3

    class ReducerMediane:
        def __init__(self):
            self.indicateur = []
        def step(self, value):
            if value >= 0:
                self.indicateur.append(value)
        def finalize(self):
            self.indicateur.sort()
            return self.indicateur[len(self.indicateur)//2]

On le déclare ensuite à *sqllite3*.

.. code:: ipython3

    cnx.create_aggregate("ReducerMediane", 1, ReducerMediane) 

.. code:: ipython3

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

.. code:: ipython3

    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>annee</th>
          <th>age</th>
          <th>age_num</th>
          <th>mediane</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>1960</td>
          <td>None</td>
          <td>NaN</td>
          <td>66.7</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1960</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>73.7</td>
        </tr>
        <tr>
          <th>2</th>
          <td>1960</td>
          <td>Y02</td>
          <td>2.0</td>
          <td>72.8</td>
        </tr>
        <tr>
          <th>3</th>
          <td>1960</td>
          <td>Y03</td>
          <td>3.0</td>
          <td>71.9</td>
        </tr>
        <tr>
          <th>4</th>
          <td>1960</td>
          <td>Y04</td>
          <td>4.0</td>
          <td>71.0</td>
        </tr>
      </tbody>
    </table>
    </div>



Un reducer à deux entrées même si cela n’a pas beaucoup de sens ici :

.. code:: ipython3

    class ReducerMediane2:
        def __init__(self):
            self.indicateur = []
        def step(self, value, value2):
            if value >= 0:
                self.indicateur.append(value)
            if value2 >= 0:
                self.indicateur.append(value2)
        def finalize(self):
            self.indicateur.sort()
            return self.indicateur[len(self.indicateur)//2]
        
    cnx.create_aggregate("ReducerMediane2", 2, ReducerMediane2)

.. code:: ipython3

    query = """SELECT annee,age,age_num, ReducerMediane2(valeur, valeur+1) AS mediane2 FROM mortalite 
                    WHERE indicateur=="LIFEXP" AND genre=="F"
                    GROUP BY annee,age,age_num"""
    df = pandas.read_sql(query, cnx)
    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>annee</th>
          <th>age</th>
          <th>age_num</th>
          <th>mediane2</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>1960</td>
          <td>None</td>
          <td>NaN</td>
          <td>66.7</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1960</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>74.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>1960</td>
          <td>Y02</td>
          <td>2.0</td>
          <td>73.2</td>
        </tr>
        <tr>
          <th>3</th>
          <td>1960</td>
          <td>Y03</td>
          <td>3.0</td>
          <td>72.3</td>
        </tr>
        <tr>
          <th>4</th>
          <td>1960</td>
          <td>Y04</td>
          <td>4.0</td>
          <td>71.3</td>
        </tr>
      </tbody>
    </table>
    </div>



Il n’est apparemment pas possible de retourner deux résultats mais on
peut utiliser une ruse qui consise à les concaténer dans une chaîne de
caracères.

.. code:: ipython3

    class ReducerQuantile:
        def __init__(self):
            self.indicateur = []
        def step(self, value):
            if value >= 0:
                self.indicateur.append(value)
        def finalize(self):
            self.indicateur.sort()
            q1 = self.indicateur[len(self.indicateur)//4]
            q2 = self.indicateur[3*len(self.indicateur)//4]
            n = len(self.indicateur)
            return "%f;%f;%s" % (q1,q2,n)
        
    cnx.create_aggregate("ReducerQuantile", 1, ReducerQuantile)

.. code:: ipython3

    query = """SELECT annee,age,age_num, ReducerQuantile(valeur) AS quantiles FROM mortalite 
                    WHERE indicateur=="LIFEXP" AND genre=="F"
                    GROUP BY annee,age,age_num"""
    df = pandas.read_sql(query, cnx)
    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>annee</th>
          <th>age</th>
          <th>age_num</th>
          <th>quantiles</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>1960</td>
          <td>None</td>
          <td>NaN</td>
          <td>4.400000;72.800000;20</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1960</td>
          <td>Y01</td>
          <td>1.0</td>
          <td>73.000000;74.000000;10</td>
        </tr>
        <tr>
          <th>2</th>
          <td>1960</td>
          <td>Y02</td>
          <td>2.0</td>
          <td>72.100000;73.200000;10</td>
        </tr>
        <tr>
          <th>3</th>
          <td>1960</td>
          <td>Y03</td>
          <td>3.0</td>
          <td>71.200000;72.300000;10</td>
        </tr>
        <tr>
          <th>4</th>
          <td>1960</td>
          <td>Y04</td>
          <td>4.0</td>
          <td>70.300000;71.300000;10</td>
        </tr>
      </tbody>
    </table>
    </div>



On ferme la connexion.

.. 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.