Données multidimensionnelles SQL - correction

Links: notebook, html ., PDF, python, slides ., presentation ., GitHub

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

%pylab 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()
Populating the interactive namespace from numpy and matplotlib
run previous cell, wait for 2 seconds

Exercice 1 : filtre

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

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

import random  #loi uniforme
def echantillon(proportion):
    return 1 if random.random() < proportion else 0
import sqlite3
from pandas.io import sql
cnx = sqlite3.connect('mortalite.db3')
cnx.create_function('echantillon', 1, echantillon)

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

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

Exercice 3 : reducer SQL

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

Il faut compléter le programme suivant.

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)
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)
df.head()
annee age age_num mediane
0 1960 None NaN 66.7
1 1960 Y01 1 73.7
2 1960 Y02 2 72.8
3 1960 Y03 3 71.9
4 1960 Y04 4 71.0

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

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)
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()
annee age age_num mediane2
0 1960 None NaN 66.7
1 1960 Y01 1 74.0
2 1960 Y02 2 73.2
3 1960 Y03 3 72.3
4 1960 Y04 4 71.3

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.

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)
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()
annee age age_num quantiles
0 1960 None NaN 4.400000;72.800000;20
1 1960 Y01 1 73.000000;74.000000;10
2 1960 Y02 2 72.100000;73.200000;10
3 1960 Y03 3 71.200000;72.300000;10
4 1960 Y04 4 70.300000;71.300000;10
cnx.close()