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