Transmettre l'information d'une machine à une autre, d'un logiciel à un autre, d'une base de données à une autre est un problème récurrent. Le format le plus simple pour des données est le format csv. Ca marche bien pour les tables mais cela ne permet de transmettre aisément des données non structurées.
from jyquickhelper import add_notebook_menu
add_notebook_menu()
from sklearn.datasets import load_iris as load_data
from pandas import DataFrame
data = load_data()
df = DataFrame(data.data, columns=data.feature_names)
df['fleur'] = [data.target_names[t] for t in data.target]
df.tail()
sepal length (cm) | sepal width (cm) | petal length (cm) | petal width (cm) | fleur | |
---|---|---|---|---|---|
145 | 6.7 | 3.0 | 5.2 | 2.3 | virginica |
146 | 6.3 | 2.5 | 5.0 | 1.9 | virginica |
147 | 6.5 | 3.0 | 5.2 | 2.0 | virginica |
148 | 6.2 | 3.4 | 5.4 | 2.3 | virginica |
149 | 5.9 | 3.0 | 5.1 | 1.8 | virginica |
Le plus simple...
from io import StringIO
buffer = StringIO()
df.to_csv(buffer, index=False)
text = buffer.getvalue()
text[:300]
'sepal length (cm),sepal width (cm),petal length (cm),petal width (cm),fleur\r\n5.1,3.5,1.4,0.2,setosa\r\n4.9,3.0,1.4,0.2,setosa\r\n4.7,3.2,1.3,0.2,setosa\r\n4.6,3.1,1.5,0.2,setosa\r\n5.0,3.6,1.4,0.2,setosa\r\n5.4,3.9,1.7,0.4,setosa\r\n4.6,3.4,1.4,0.3,setosa\r\n5.0,3.4,1.5,0.2,setosa\r\n4.4,2.9,1.4,0.2,setosa\r\n4.9,3.1'
r = df.to_json(orient='records')
r[:400]
'[{"sepal length (cm)":5.1,"sepal width (cm)":3.5,"petal length (cm)":1.4,"petal width (cm)":0.2,"fleur":"setosa"},{"sepal length (cm)":4.9,"sepal width (cm)":3.0,"petal length (cm)":1.4,"petal width (cm)":0.2,"fleur":"setosa"},{"sepal length (cm)":4.7,"sepal width (cm)":3.2,"petal length (cm)":1.3,"petal width (cm)":0.2,"fleur":"setosa"},{"sepal length (cm)":4.6,"sepal width (cm)":3.1,"petal lengt'
On ajoute les endroits où ces fleurs sont présentes. On voudrait que toutes les informations soient présentes dans le même fichier. Comment fait-on ?
locations = {'virginica': ['Florida', 'Georgia'],
'setosa': ['Maine', 'Alaska', 'Quebec'],
'versicolor': ['Quebec', 'Georgia', 'Ireland', 'Main']}
La question sous-jacente est : vaut-il mieux avoir deux fichiers plats, l'un pour les données décrivant les fleurs, l'autre pour les localisations ou un seul fusionnant les deux informations ?
from io import StringIO
buffer = StringIO()
df.to_csv(buffer, index=False)
text = buffer.getvalue()
text[:300]
'sepal length (cm),sepal width (cm),petal length (cm),petal width (cm),fleur\r\n5.1,3.5,1.4,0.2,setosa\r\n4.9,3.0,1.4,0.2,setosa\r\n4.7,3.2,1.3,0.2,setosa\r\n4.6,3.1,1.5,0.2,setosa\r\n5.0,3.6,1.4,0.2,setosa\r\n5.4,3.9,1.7,0.4,setosa\r\n4.6,3.4,1.4,0.3,setosa\r\n5.0,3.4,1.5,0.2,setosa\r\n4.4,2.9,1.4,0.2,setosa\r\n4.9,3.1'
df.to_csv("fleurs.csv", index=False)
import os
os.listdir(".")
['.ipynb_checkpoints', '2020_covid.ipynb', '2020_edit.ipynb', '2020_json_xml.ipynb', '2020_numpy.ipynb', '2020_pandas.ipynb', '2020_profile.ipynb', '2020_regex.ipynb', '2020_suffix.ipynb', '2020_surface.ipynb', '2020_topk.ipynb', '2020_tsp.ipynb', 'data.csv', 'fleurs.csv']
import pandas
df2 = pandas.read_csv("fleurs.csv")
df2.head()
sepal length (cm) | sepal width (cm) | petal length (cm) | petal width (cm) | fleur | |
---|---|---|---|---|---|
0 | 5.1 | 3.5 | 1.4 | 0.2 | setosa |
1 | 4.9 | 3.0 | 1.4 | 0.2 | setosa |
2 | 4.7 | 3.2 | 1.3 | 0.2 | setosa |
3 | 4.6 | 3.1 | 1.5 | 0.2 | setosa |
4 | 5.0 | 3.6 | 1.4 | 0.2 | setosa |
virtuel = StringIO(text)
df3 = pandas.read_csv(virtuel)
df3.head()
sepal length (cm) | sepal width (cm) | petal length (cm) | petal width (cm) | fleur | |
---|---|---|---|---|---|
0 | 5.1 | 3.5 | 1.4 | 0.2 | setosa |
1 | 4.9 | 3.0 | 1.4 | 0.2 | setosa |
2 | 4.7 | 3.2 | 1.3 | 0.2 | setosa |
3 | 4.6 | 3.1 | 1.5 | 0.2 | setosa |
4 | 5.0 | 3.6 | 1.4 | 0.2 | setosa |
json_text = df.to_json(orient='records')
json_text[:400]
'[{"sepal length (cm)":5.1,"sepal width (cm)":3.5,"petal length (cm)":1.4,"petal width (cm)":0.2,"fleur":"setosa"},{"sepal length (cm)":4.9,"sepal width (cm)":3.0,"petal length (cm)":1.4,"petal width (cm)":0.2,"fleur":"setosa"},{"sepal length (cm)":4.7,"sepal width (cm)":3.2,"petal length (cm)":1.3,"petal width (cm)":0.2,"fleur":"setosa"},{"sepal length (cm)":4.6,"sepal width (cm)":3.1,"petal lengt'
import json
res = json.loads(json_text)
for i, r in enumerate(res):
print(i, type(r), r)
if i >= 5:
break
0 <class 'dict'> {'sepal length (cm)': 5.1, 'sepal width (cm)': 3.5, 'petal length (cm)': 1.4, 'petal width (cm)': 0.2, 'fleur': 'setosa'} 1 <class 'dict'> {'sepal length (cm)': 4.9, 'sepal width (cm)': 3.0, 'petal length (cm)': 1.4, 'petal width (cm)': 0.2, 'fleur': 'setosa'} 2 <class 'dict'> {'sepal length (cm)': 4.7, 'sepal width (cm)': 3.2, 'petal length (cm)': 1.3, 'petal width (cm)': 0.2, 'fleur': 'setosa'} 3 <class 'dict'> {'sepal length (cm)': 4.6, 'sepal width (cm)': 3.1, 'petal length (cm)': 1.5, 'petal width (cm)': 0.2, 'fleur': 'setosa'} 4 <class 'dict'> {'sepal length (cm)': 5.0, 'sepal width (cm)': 3.6, 'petal length (cm)': 1.4, 'petal width (cm)': 0.2, 'fleur': 'setosa'} 5 <class 'dict'> {'sepal length (cm)': 5.4, 'sepal width (cm)': 3.9, 'petal length (cm)': 1.7, 'petal width (cm)': 0.4, 'fleur': 'setosa'}
res[3]['sepal width (cm)']
3.1
virtuel = StringIO(json_text)
res2 = json.load(virtuel)
res2[:3]
[{'sepal length (cm)': 5.1, 'sepal width (cm)': 3.5, 'petal length (cm)': 1.4, 'petal width (cm)': 0.2, 'fleur': 'setosa'}, {'sepal length (cm)': 4.9, 'sepal width (cm)': 3.0, 'petal length (cm)': 1.4, 'petal width (cm)': 0.2, 'fleur': 'setosa'}, {'sepal length (cm)': 4.7, 'sepal width (cm)': 3.2, 'petal length (cm)': 1.3, 'petal width (cm)': 0.2, 'fleur': 'setosa'}]
html_text = df.to_html(index=False)
print(html_text[:500])
<table border="1" class="dataframe"> <thead> <tr style="text-align: right;"> <th>sepal length (cm)</th> <th>sepal width (cm)</th> <th>petal length (cm)</th> <th>petal width (cm)</th> <th>fleur</th> </tr> </thead> <tbody> <tr> <td>5.1</td> <td>3.5</td> <td>1.4</td> <td>0.2</td> <td>setosa</td> </tr> <tr> <td>4.9</td> <td>3.0</td> <td>1.4</td> <td>0.2</td> <td>setosa</td> </tr>
df_html = pandas.read_html(html_text)
df_html[0].tail()
sepal length (cm) | sepal width (cm) | petal length (cm) | petal width (cm) | fleur | |
---|---|---|---|---|---|
145 | 6.7 | 3.0 | 5.2 | 2.3 | virginica |
146 | 6.3 | 2.5 | 5.0 | 1.9 | virginica |
147 | 6.5 | 3.0 | 5.2 | 2.0 | virginica |
148 | 6.2 | 3.4 | 5.4 | 2.3 | virginica |
149 | 5.9 | 3.0 | 5.1 | 1.8 | virginica |
df_html = pandas.read_html(html_text + html_text)
len(df_html)
2
df.head()
sepal length (cm) | sepal width (cm) | petal length (cm) | petal width (cm) | fleur | |
---|---|---|---|---|---|
0 | 5.1 | 3.5 | 1.4 | 0.2 | setosa |
1 | 4.9 | 3.0 | 1.4 | 0.2 | setosa |
2 | 4.7 | 3.2 | 1.3 | 0.2 | setosa |
3 | 4.6 | 3.1 | 1.5 | 0.2 | setosa |
4 | 5.0 | 3.6 | 1.4 | 0.2 | setosa |
locations = {'virginica': ['Florida', 'Georgia'],
'setosa': ['Maine', 'Alaska', 'Quebec'],
'versicolor': ['Quebec', 'Georgia', 'Ireland', 'Main']}
obs = []
for fleur, loc in locations.items():
for l in loc:
obs.append({"fleur": fleur, "location": l})
obs
[{'fleur': 'virginica', 'location': 'Florida'}, {'fleur': 'virginica', 'location': 'Georgia'}, {'fleur': 'setosa', 'location': 'Maine'}, {'fleur': 'setosa', 'location': 'Alaska'}, {'fleur': 'setosa', 'location': 'Quebec'}, {'fleur': 'versicolor', 'location': 'Quebec'}, {'fleur': 'versicolor', 'location': 'Georgia'}, {'fleur': 'versicolor', 'location': 'Ireland'}, {'fleur': 'versicolor', 'location': 'Main'}]
df_locations = pandas.DataFrame(obs)
df_locations
fleur | location | |
---|---|---|
0 | virginica | Florida |
1 | virginica | Georgia |
2 | setosa | Maine |
3 | setosa | Alaska |
4 | setosa | Quebec |
5 | versicolor | Quebec |
6 | versicolor | Georgia |
7 | versicolor | Ireland |
8 | versicolor | Main |
merged = df.merge(df_locations, left_on="fleur", right_on="fleur")
merged.head(10)
sepal length (cm) | sepal width (cm) | petal length (cm) | petal width (cm) | fleur | location | |
---|---|---|---|---|---|---|
0 | 5.1 | 3.5 | 1.4 | 0.2 | setosa | Maine |
1 | 5.1 | 3.5 | 1.4 | 0.2 | setosa | Alaska |
2 | 5.1 | 3.5 | 1.4 | 0.2 | setosa | Quebec |
3 | 4.9 | 3.0 | 1.4 | 0.2 | setosa | Maine |
4 | 4.9 | 3.0 | 1.4 | 0.2 | setosa | Alaska |
5 | 4.9 | 3.0 | 1.4 | 0.2 | setosa | Quebec |
6 | 4.7 | 3.2 | 1.3 | 0.2 | setosa | Maine |
7 | 4.7 | 3.2 | 1.3 | 0.2 | setosa | Alaska |
8 | 4.7 | 3.2 | 1.3 | 0.2 | setosa | Quebec |
9 | 4.6 | 3.1 | 1.5 | 0.2 | setosa | Maine |
merged.shape
(450, 6)
locations
{'virginica': ['Florida', 'Georgia'], 'setosa': ['Maine', 'Alaska', 'Quebec'], 'versicolor': ['Quebec', 'Georgia', 'Ireland', 'Main']}
obs2 = []
for fleur, loc in locations.items():
obs2.append({"fleur": fleur, "location": loc})
obs2
[{'fleur': 'virginica', 'location': ['Florida', 'Georgia']}, {'fleur': 'setosa', 'location': ['Maine', 'Alaska', 'Quebec']}, {'fleur': 'versicolor', 'location': ['Quebec', 'Georgia', 'Ireland', 'Main']}]
df_locations2 = pandas.DataFrame(obs2)
df_locations2
fleur | location | |
---|---|---|
0 | virginica | [Florida, Georgia] |
1 | setosa | [Maine, Alaska, Quebec] |
2 | versicolor | [Quebec, Georgia, Ireland, Main] |
merged = df.merge(df_locations2, left_on="fleur", right_on="fleur")
merged.head(10)
sepal length (cm) | sepal width (cm) | petal length (cm) | petal width (cm) | fleur | location | |
---|---|---|---|---|---|---|
0 | 5.1 | 3.5 | 1.4 | 0.2 | setosa | [Maine, Alaska, Quebec] |
1 | 4.9 | 3.0 | 1.4 | 0.2 | setosa | [Maine, Alaska, Quebec] |
2 | 4.7 | 3.2 | 1.3 | 0.2 | setosa | [Maine, Alaska, Quebec] |
3 | 4.6 | 3.1 | 1.5 | 0.2 | setosa | [Maine, Alaska, Quebec] |
4 | 5.0 | 3.6 | 1.4 | 0.2 | setosa | [Maine, Alaska, Quebec] |
5 | 5.4 | 3.9 | 1.7 | 0.4 | setosa | [Maine, Alaska, Quebec] |
6 | 4.6 | 3.4 | 1.4 | 0.3 | setosa | [Maine, Alaska, Quebec] |
7 | 5.0 | 3.4 | 1.5 | 0.2 | setosa | [Maine, Alaska, Quebec] |
8 | 4.4 | 2.9 | 1.4 | 0.2 | setosa | [Maine, Alaska, Quebec] |
9 | 4.9 | 3.1 | 1.5 | 0.1 | setosa | [Maine, Alaska, Quebec] |
json_text = merged.to_json(orient='records')
json_text[:200]
'[{"sepal length (cm)":5.1,"sepal width (cm)":3.5,"petal length (cm)":1.4,"petal width (cm)":0.2,"fleur":"setosa","location":["Maine","Alaska","Quebec"]},{"sepal length (cm)":4.9,"sepal width (cm)":3.0'
df.to_excel("data.xlsx", index=False)
dfe = pandas.read_excel("data.xlsx", engine='openpyxl')
dfe.tail()
sepal length (cm) | sepal width (cm) | petal length (cm) | petal width (cm) | fleur | |
---|---|---|---|---|---|
145 | 6.7 | 3.0 | 5.2 | 2.3 | virginica |
146 | 6.3 | 2.5 | 5.0 | 1.9 | virginica |
147 | 6.5 | 3.0 | 5.2 | 2.0 | virginica |
148 | 6.2 | 3.4 | 5.4 | 2.3 | virginica |
149 | 5.9 | 3.0 | 5.1 | 1.8 | virginica |
from zipfile import ZipFile
with ZipFile('data.zip', 'w') as myzip:
myzip.write('data.xlsx')
myzip.write("2020_json_xml.ipynb")
import glob
glob.glob("*.zip")
['data.zip']
Protobuf est un format de sérialisation au même titre que pickle. La sérialisation désigne un procédé qui permet d'enregister tout un tas d'information sous la forme d'ensemble d'octets contigü. En gros, on enregistre un ensemble de variables en même quel qu'il soit, dans un unique fichier contigü. json est un format utilisé par les outils de sérialisation. Tout est en texte et la plupart du temps, les nombres réelles prennent moins de place quand ils sont stockés comme ils sont sockés en mémoire sur 8 octets. C'est le premier problème résolu par protobuf. Ensuite, le format json est très générique mais il suggère de stocker le nom des colonnes à chaque ligne, c'est une information qui est dupliqué à chaque ligne... protobuf propose une façon de ne pas les sotcker du tout. Je passe les détails. Ils reviendront quand le problème de communiquer efficacement des données se posera.