Combinaison de tables

Combinaison de tables#

Lorsqu’on travaille avec de nombreuses tables dans une base de données relationnelle, il est souvent judicieux de combiner les tables dans des tables récapitulatives et de les stocker avant tout traitement ultérieur. Pour rappel, nous avons acquis des images pendant les expériences et les mesures correspondantes. Nous voudrions maintenant extraire une table aperçu qui contient les informations les plus importantes des tables image, experiments et measurements.

import pandas as pd
from pandasql import sqldf

Nous commençons à nouveau par charger nos tables depuis le disque.

experiment = pd.read_csv("../../data/experiments.csv")
image = pd.read_csv("../../data/images.csv")
measurement = pd.read_csv("../../data/measurements.csv")

Encore une fois, nous définissons une fonction qui peut interroger la base de données.

query_database = lambda q: sqldf(q, globals())

Jointure interne#

SQL permet également de combiner des tables en utilisant l’instruction JOIN. La plus courante est la INNER JOIN qui permet de combiner deux tables de manière à ce que les lignes soient appariées selon une connexion définie ON sur laquelle les tables sont combinées. La INNER JOIN s’assure que seules les lignes pour lesquelles les deux tables ont des entrées sont affichées. C’est pourquoi l’expérience ID 4 n’apparaît pas dans le tableau suivant. Il n’y avait pas d’images stockées pendant l’expérience 4.

query = """
SELECT * 
FROM experiment
INNER JOIN image ON experiment.ID = image.experiment_ID
"""

query_database(query)

	Unnamed: 0	ID	Comment	Concentration	Unnamed: 0	ID	Filename	experiment_ID
0	0	1	High dose	50	0	1	image0.tif	1
1	0	1	High dose	50	1	2	image1.tif	1
2	0	1	High dose	50	2	3	image2.tif	1
3	1	2	Medium dose	20	3	4	image3.tif	2
4	1	2	Medium dose	20	4	5	image4.tif	2
5	1	2	Medium dose	20	5	6	image5.tif	2
6	2	3	Control	0	6	7	image6.tif	3
7	2	3	Control	0	7	8	image7.tif	3
8	2	3	Control	0	8	9	image8.tif	3
9	2	3	Control	0	9	10	image9.tif	3

Jointure externe#

Si l’on voulait combiner les tables de manière à ce que l’expérience 4 soit également répertoriée, nous devons utiliser une OUTER JOIN. Dans ce cas, comme la table des expériences est à gauche de notre instruction SQL, nous utilisons le LEFT OUTER JOIN. L’expérience 4 est alors répertoriée dans notre table avec quelques NaNs dans les colonnes des images.

query = """
SELECT * 
FROM experiment
LEFT OUTER JOIN image ON experiment.ID = image.experiment_ID
"""

query_database(query)

	Unnamed: 0	ID	Comment	Concentration	Unnamed: 0	ID	Filename	experiment_ID
0	0	1	High dose	50	0.0	1.0	image0.tif	1.0
1	0	1	High dose	50	1.0	2.0	image1.tif	1.0
2	0	1	High dose	50	2.0	3.0	image2.tif	1.0
3	1	2	Medium dose	20	3.0	4.0	image3.tif	2.0
4	1	2	Medium dose	20	4.0	5.0	image4.tif	2.0
5	1	2	Medium dose	20	5.0	6.0	image5.tif	2.0
6	2	3	Control	0	6.0	7.0	image6.tif	3.0
7	2	3	Control	0	7.0	8.0	image7.tif	3.0
8	2	3	Control	0	8.0	9.0	image8.tif	3.0
9	2	3	Control	0	9.0	10.0	image9.tif	3.0
10	3	4	Super high dose	1000	NaN	NaN	None	NaN

Combinaison de nombreuses tables#

Les instructions JOIN ci-dessus peuvent également être utilisées en chaîne pour combiner plus de deux tables. Par exemple, nous pourrions être intéressés par le nombre de cellules au fil du temps pour chaque image dans chaque expérience.

query = """
SELECT * 
FROM experiment
LEFT OUTER JOIN image ON experiment.ID = image.experiment_ID
INNER JOIN measurement ON image.ID = measurement.image_ID
"""

query_database(query)

	Unnamed: 0	ID	Comment	Concentration	Unnamed: 0	ID	Filename	experiment_ID	Unnamed: 0	image_ID	time_after_exposure_in_s	number_of_cells
0	0	1	High dose	50	0	1	image0.tif	1	0	1	0	96
1	0	1	High dose	50	0	1	image0.tif	1	1	1	1	47
2	0	1	High dose	50	0	1	image0.tif	1	2	1	2	32
3	0	1	High dose	50	0	1	image0.tif	1	3	1	3	24
4	0	1	High dose	50	0	1	image0.tif	1	4	1	4	19
...	...	...	...	...	...	...	...	...	...	...	...	...
95	2	3	Control	0	9	10	image9.tif	3	95	10	5	142
96	2	3	Control	0	9	10	image9.tif	3	96	10	6	148
97	2	3	Control	0	9	10	image9.tif	3	97	10	7	144
98	2	3	Control	0	9	10	image9.tif	3	98	10	8	141
99	2	3	Control	0	9	10	image9.tif	3	99	10	9	140

100 rows × 12 columns

Sélection de colonnes#

Lorsqu’une table devient trop large et comporte des colonnes dont nous n’avons pas vraiment besoin pour notre analyse, il est recommandé de remplacer le * dans l’instruction SELECT par des noms de colonnes.

query = """
SELECT experiment.Comment, image.Filename, measurement.time_after_exposure_in_s, measurement.number_of_cells
FROM experiment
LEFT OUTER JOIN image ON experiment.ID = image.experiment_ID
INNER JOIN measurement ON image.ID = measurement.image_ID
"""

query_database(query)

	Comment	Filename	time_after_exposure_in_s	number_of_cells
0	High dose	image0.tif	0	96
1	High dose	image0.tif	1	47
2	High dose	image0.tif	2	32
3	High dose	image0.tif	3	24
4	High dose	image0.tif	4	19
...	...	...	...	...
95	Control	image9.tif	5	142
96	Control	image9.tif	6	148
97	Control	image9.tif	7	144
98	Control	image9.tif	8	141
99	Control	image9.tif	9	140

100 rows × 4 columns

Renommage de colonnes#

En utilisant l’instruction AS, nous pouvons également renommer les colonnes et rendre notre nouvelle table plus facile à lire et à comprendre.

query = """
SELECT experiment.Comment AS experiment_name, image.Filename, measurement.time_after_exposure_in_s, measurement.number_of_cells
FROM experiment
LEFT OUTER JOIN image ON experiment.ID = image.experiment_ID
INNER JOIN measurement ON image.ID = measurement.image_ID
"""

query_database(query)

	experiment_name	Filename	time_after_exposure_in_s	number_of_cells
0	High dose	image0.tif	0	96
1	High dose	image0.tif	1	47
2	High dose	image0.tif	2	32
3	High dose	image0.tif	3	24
4	High dose	image0.tif	4	19
...	...	...	...	...
95	Control	image9.tif	5	142
96	Control	image9.tif	6	148
97	Control	image9.tif	7	144
98	Control	image9.tif	8	141
99	Control	image9.tif	9	140

100 rows × 4 columns

Comme cette instruction est assez compliquée et que nous ne devrions pas la copier-coller encore et encore, nous pouvons la stocker dans un DataFrame pandas overview.

query = """
SELECT experiment.Comment AS experiment_name, image.Filename, measurement.time_after_exposure_in_s, measurement.number_of_cells
FROM experiment
LEFT OUTER JOIN image ON experiment.ID = image.experiment_ID
INNER JOIN measurement ON image.ID = measurement.image_ID
"""

overview = query_database(query)

La table overview fait partie de notre base de données. Note : L’aperçu n’a pas encore été sauvegardé sur le disque.

query = """
SELECT * 
FROM overview
"""

query_database(query)

	experiment_name	Filename	time_after_exposure_in_s	number_of_cells
0	High dose	image0.tif	0	96
1	High dose	image0.tif	1	47
2	High dose	image0.tif	2	32
3	High dose	image0.tif	3	24
4	High dose	image0.tif	4	19
...	...	...	...	...
95	Control	image9.tif	5	142
96	Control	image9.tif	6	148
97	Control	image9.tif	7	144
98	Control	image9.tif	8	141
99	Control	image9.tif	9	140

100 rows × 4 columns

Pour une réutilisation dans les leçons ultérieures, nous sauvegardons le résumé sur le disque.

overview.to_csv("../../data/overview.csv")

Combinaison de tables

Contents

Combinaison de tables#

Jointure interne#

Jointure externe#

Combinaison de nombreuses tables#

Sélection de colonnes#

Renommage de colonnes#