Combinando tablas

Cuando se trabaja con muchas tablas en una base de datos relacional, a menudo tiene sentido combinar tablas en tablas resumen y almacenarlas antes de un procesamiento adicional. Como recordatorio, adquirimos images durante los experiments y las correspondientes measurements. Ahora nos gustaría extraer una tabla de overview que contenga la información más importante de image, experiments y measurements.

import pandas as pd
from pandasql import sqldf

Nuevamente comenzamos cargando nuestras tablas desde el disco.

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

Una vez más, definimos una función que puede consultar la base de datos.

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

Inner Join

SQL también permite combinar tablas usando la declaración JOIN. El más común es el INNER JOIN que permite combinar dos tablas de manera que las filas se emparejen según una conexión definida ON en la que se combinan las tablas. El INNER JOIN se asegura de que solo se muestren las filas para las que ambas tablas tienen entradas. Es por eso que el experimento ID 4 no se muestra en la siguiente tabla. No se almacenaron imágenes durante el experimento 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

Outer Join

Si uno quisiera combinar las tablas de manera que también se liste el experimento 4, necesitamos usar un OUTER JOIN. En este caso, como la tabla de experimentos está a la izquierda de nuestra declaración SQL, usamos el LEFT OUTER JOIN. El experimento 4 se lista entonces en nuestra tabla con algunos NaNs en las columnas de las imágenes.

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

Combinando muchas tablas

Las declaraciones JOIN anteriores también se pueden usar en cadenas para combinar más de dos tablas. Por ejemplo, podríamos estar interesados en el número de células a lo largo del tiempo para cada imagen en cada experimento

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

Seleccionando columnas

Cuando una tabla se vuelve demasiado ancha y tiene columnas que realmente no necesitamos para nuestro análisis, se recomienda reemplazar el * en la declaración SELECT con nombres de columnas.

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

Renombrando columnas

Usando la declaración AS, también podemos renombrar columnas y hacer que nuestra nueva tabla sea más fácil de leer y entender.

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

Como esta declaración es bastante complicada y no deberíamos copiarla y pegarla una y otra vez, podemos almacenarla en un DataFrame de 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 tabla overview es parte de nuestra base de datos. Nota: El resumen aún no se ha guardado en el disco.

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

Para reutilizarlo en lecciones posteriores, guardamos el resumen en el disco.

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