Données multidimensionnelles SQL - énoncé#

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

Ce notebook propose l’utilisation de SQL avec SQLite pour manipuler les données depuis un notebook (avec le module sqlite3).

%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()
Populating the interactive namespace from numpy and matplotlib

Représentation#

Le module pandas 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 :

NbImage("cube1.png")
../_images/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 :

NbImage("cube2.png")
../_images/seance5_sql_multidimensionnelle_enonce_6_0.png

C’est assez simple. Prenons un exemple : table de mortalité de 1960 à 2010 qu’on récupère à l’aide de la fonction 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.

from actuariat_python.data import table_mortalite_euro_stat
table_mortalite_euro_stat()
'mortalite.txt'
import os
os.stat("mortalite.txt")
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)
import pandas
df = pandas.read_csv("mortalite.txt", sep="\t", encoding="utf8", low_memory=False)
df.head()
annee valeur age age_num indicateur genre pays
0 2009 0.00080 Y01 1 DEATHRATE F AM
1 2008 0.00067 Y01 1 DEATHRATE F AM
2 2007 0.00052 Y01 1 DEATHRATE F AM
3 2006 0.00123 Y01 1 DEATHRATE F AM
4 2013 0.00016 Y01 1 DEATHRATE F AT

Les indicateurs pour deux âges différents :

df [ ((df.age=="Y60") | (df.age=="Y61")) & (df.annee == 2000) & (df.pays=="FR") & (df.genre=="F")]
annee valeur age age_num indicateur genre pays
86895 2000 0.00502 Y60 60 DEATHRATE F FR
88435 2000 0.00486 Y61 61 DEATHRATE F FR
482573 2000 25.80000 Y60 60 LIFEXP F FR
484098 2000 24.90000 Y61 61 LIFEXP F FR
876261 2000 0.00501 Y60 60 PROBDEATH F FR
877801 2000 0.00485 Y61 61 PROBDEATH F FR
1272615 2000 0.99499 Y60 60 PROBSURV F FR
1274155 2000 0.99515 Y61 61 PROBSURV F FR
1668215 2000 93076.00000 Y60 60 PYLIVED F FR
1669740 2000 92618.00000 Y61 61 PYLIVED F FR
2061149 2000 93310.00000 Y60 60 SURVIVORS F FR
2062674 2000 92843.00000 Y61 61 SURVIVORS F FR
2454128 2000 2405594.00000 Y60 60 TOTPYLIVED F FR
2455653 2000 2312517.00000 Y61 61 TOTPYLIVED F FR

Exercice 1 : filtre#

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

#

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

df.shape
(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 qui est une façon de faire du SQL sans serveur (cela prend quelques minutes). On utilise la méthode to_sql.

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.

import pandas
example = pandas.read_sql('select * from mortalite where age_num==50 limit 5', cnx)
example
index annee valeur age age_num indicateur genre pays
0 69225 2009 0.00290 Y50 50 DEATHRATE F AM
1 69226 2008 0.00333 Y50 50 DEATHRATE F AM
2 69227 2007 0.00292 Y50 50 DEATHRATE F AM
3 69228 2006 0.00371 Y50 50 DEATHRATE F AM
4 69229 2013 0.00194 Y50 50 DEATHRATE F AT

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.

cnx.close()

Sous Windows, on peut consulter la base avec le logiciel SQLiteSpy.

NbImage("sqlite.png")
../_images/seance5_sql_multidimensionnelle_enonce_24_0.png

Sous Linux ou Max, on peut utiliser une extension Firefox SQLite Manager. Dans ce notebook, on utilisera la commande magique %%SQL du module pyensae :

%load_ext pyensae
%SQL_connect mortalite.db3
<pyensae.sql.sql_interface_database.InterfaceSQLDatabase at 0xaa11f28>
%SQL_tables
['mortalite']
%SQL_schema mortalite
{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)}
%%SQL
SELECT COUNT(*) FROM mortalite
COUNT(*)
0 2760921
%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 :

La première fonction est simple :

sample = df.sample(frac=0.1)
sample.shape, df.shape
((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 et la fonction suivante :

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

Pseudo Map/Reduce avec SQLite#

La liste des mots-clés du langage SQL utilisés par SQLite 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.

import sqlite3, pandas
from pandas.io import sql
cnx = sqlite3.connect('mortalite.db3')
pandas.read_sql('select pays,count(*) from mortalite group by pays', cnx)
pays count(*)
0 AM 7224
1 AT 79464
2 AZ 12642
3 BE 97524
4 BG 97524
5 BY 5418
6 CH 97524
7 CY 37926
8 CZ 97524
9 DE 52374
10 DE_TOT 97524
11 DK 72240
12 EA16 35688
13 EA17 35688
14 EA18 21672
15 EA19 21672
16 EE 97524
17 EEA30 21672
18 EEA31 21672
19 EFTA 36120
20 EL 95718
21 ES 70434
22 EU27 21672
23 EU28 21672
24 FI 61404
25 FR 28896
26 FX 48762
27 GE 10836
28 HR 23478
29 HU 97524
30 IE 50646
31 IS 93912
32 IT 52374
33 LI 36120
34 LT 79464
35 LU 79464
36 LV 21672
37 MD 12642
38 ME 16254
39 MK 36120
40 MT 50901
41 NL 52374
42 NO 97524
43 PL 43344
44 PT 97524
45 RO 83076
46 RS 25284
47 RU 9030
48 SE 83076
49 SI 57792
50 SK 97524
51 TR 9030
52 UA 10836
53 UK 37926

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.

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)
df.sort_values("nb_country", ascending=False).head(n=2)
nb_country nb_rows
37 104 5
36 102 2
df.plot(x="nb_country", y="nb_rows")
<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.

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)
df.head()
annee age age_num nb_country
0 2006 None None 104
1 2007 None None 104
2 2008 None None 104
3 2009 None None 104
4 2010 None None 104

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.

Exercice 3 : reducer SQL#

Il faut compléter le programme suivant.

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

cnx.create_aggregate("ReducerMediane", 1, ReducerMediane)
#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)
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 montre comment faire les principales opérations.