Données multidimensionnelles SQL - correction

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

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

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

Populating the interactive namespace from numpy and matplotlib

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