.. _seance5sqlmultidimensionnellecorrectionrst:

=============================================
Données multidimensionnelles SQL - correction
=============================================


.. only:: html

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


Correction de la séance sur l’utilisation du SQL depuis un notebook.

.. code:: ipython3

    %matplotlib inline
    import matplotlib.pyplot as plt
    plt.style.use('ggplot')
    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:





Exercice 1 : filtre
-------------------

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

.. code:: ipython3

    from actuariat_python.data import table_mortalite_euro_stat 
    table_mortalite_euro_stat()
    import pandas
    df = pandas.read_csv("mortalite.txt", sep="\t", encoding="utf8", low_memory=False)
    # ...

.. code:: ipython3

    import os
    if not os.path.exists('mortalite.db3'):
        import sqlite3
        from pandas.io import sql
        cnx = sqlite3.connect('mortalite.db3')
        df.to_sql(name='mortalite', con=cnx)
        cnx.close()

Exercice 2 : échantillon aléatoire
----------------------------------

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

Exercice 3 : reducer SQL
------------------------

.. code:: ipython3

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

Il faut compléter le programme suivant.

.. 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]
        
    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>
    <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</td>
          <td>73.7</td>
        </tr>
        <tr>
          <th>2</th>
          <td>1960</td>
          <td>Y02</td>
          <td>2</td>
          <td>72.8</td>
        </tr>
        <tr>
          <th>3</th>
          <td>1960</td>
          <td>Y03</td>
          <td>3</td>
          <td>71.9</td>
        </tr>
        <tr>
          <th>4</th>
          <td>1960</td>
          <td>Y04</td>
          <td>4</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>
    <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</td>
          <td>74.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>1960</td>
          <td>Y02</td>
          <td>2</td>
          <td>73.2</td>
        </tr>
        <tr>
          <th>3</th>
          <td>1960</td>
          <td>Y03</td>
          <td>3</td>
          <td>72.3</td>
        </tr>
        <tr>
          <th>4</th>
          <td>1960</td>
          <td>Y04</td>
          <td>4</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>
    <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</td>
          <td>73.000000;74.000000;10</td>
        </tr>
        <tr>
          <th>2</th>
          <td>1960</td>
          <td>Y02</td>
          <td>2</td>
          <td>72.100000;73.200000;10</td>
        </tr>
        <tr>
          <th>3</th>
          <td>1960</td>
          <td>Y03</td>
          <td>3</td>
          <td>71.200000;72.300000;10</td>
        </tr>
        <tr>
          <th>4</th>
          <td>1960</td>
          <td>Y04</td>
          <td>4</td>
          <td>70.300000;71.300000;10</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    cnx.close()