Resumiendo mediciones

Resumiendo mediciones#

De una base de datos accesible por SQL que contiene información sobre experimentos, imágenes adquiridas durante los experimentos y mediciones correspondientes, nos gustaría extraer estadísticas resumidas.

import pandas as pd
from pandasql import sqldf

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

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

Datos de ejemplo#

Solo como recordatorio, nuestra tabla de visión general contiene la información clave recuperada de nuestra base de datos sobre el número de células contadas en imágenes que fueron adquiridas durante experimentos.

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

query_database(query)

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

100 rows × 5 columns

Agrupando filas#

La declaración GROUP BY permite agrupar tablas largas en otras más cortas y nos permite hacer estadísticas descriptivas. Por ejemplo, podemos determinar el número más pequeño y más grande de células observadas durante un experimento usando los llamados agregadores MIN() y MAX().

query = """
SELECT experiment_name, MIN(number_of_cells), MAX(number_of_cells)
FROM overview
GROUP BY experiment_name
"""

query_database(query)

	experiment_name	MIN(number_of_cells)	MAX(number_of_cells)
0	Control	113	159
1	High dose	9	151
2	Medium dose	7	158

Los grupos también pueden formarse utilizando múltiples columnas. Aquí también vemos que es común especificar las columnas utilizadas para agrupar dos veces, una vez después de GROUP BY y otra vez después de SELECT porque queremos ver la agrupación en la tabla.

query = """
SELECT experiment_name, Filename, MIN(number_of_cells), MAX(number_of_cells)
FROM overview
GROUP BY experiment_name, Filename
"""

query_database(query)

	experiment_name	Filename	MIN(number_of_cells)	MAX(number_of_cells)
0	Control	image6.tif	147	156
1	Control	image7.tif	113	122
2	Control	image8.tif	145	159
3	Control	image9.tif	140	149
4	High dose	image0.tif	9	96
5	High dose	image1.tif	15	151
6	High dose	image2.tif	9	97
7	Medium dose	image3.tif	15	158
8	Medium dose	image4.tif	7	76
9	Medium dose	image5.tif	14	147

Una estrategia similar utiliza el agregador COUNT para contar el número de imágenes adquiridas por experimento.

query = """
SELECT experiment_name, COUNT(Filename)
FROM overview
GROUP BY experiment_name
"""

query_database(query)

	experiment_name	COUNT(Filename)
0	Control	40
1	High dose	30
2	Medium dose	30

Combinando estadísticas#

Supongamos que las mediciones en nuestra base de datos resultan de un análisis de lapso de tiempo. Una mirada rápida a un solo archivo de imagen y las mediciones correspondientes muestra que el número de células está disminuyendo con el tiempo.

query = """
SELECT *
FROM overview
WHERE Filename = "image0.tif"
"""

query_database(query)

	Unnamed: 0	experiment_name	Filename	time_after_exposure_in_s	number_of_cells
0	0	High dose	image0.tif	0	96
1	1	High dose	image0.tif	1	47
2	2	High dose	image0.tif	2	32
3	3	High dose	image0.tif	3	24
4	4	High dose	image0.tif	4	19
5	5	High dose	image0.tif	5	16
6	6	High dose	image0.tif	6	13
7	7	High dose	image0.tif	7	11
8	8	High dose	image0.tif	8	10
9	9	High dose	image0.tif	9	9

Ahora podemos determinar el número promedio de células al comienzo del experimento promediando los primeros 3 puntos de tiempo.

query = """
SELECT Filename, AVG(number_of_cells) as number_of_cells
FROM overview
WHERE time_after_exposure_in_s < 3
GROUP BY Filename
"""

query_database(query)

	Filename	number_of_cells
0	image0.tif	58.333333
1	image1.tif	93.000000
2	image2.tif	58.000000
3	image3.tif	97.333333
4	image4.tif	46.333333
5	image5.tif	89.666667
6	image6.tif	150.666667
7	image7.tif	113.666667
8	image8.tif	151.000000
9	image9.tif	147.666667

query = """
SELECT Filename, AVG(number_of_cells) as number_of_cells
FROM overview
WHERE time_after_exposure_in_s < 3
GROUP BY Filename
"""
cell_count_early = query_database(query)

query = """ 
SELECT Filename, AVG(number_of_cells) as number_of_cells
FROM overview
WHERE time_after_exposure_in_s > 6
GROUP BY Filename;
"""
cell_count_late = query_database(query)

query = """
SELECT cell_count_early.Filename, (cell_count_late.number_of_cells / cell_count_early.number_of_cells) AS cell_count_ratio
FROM cell_count_early
INNER JOIN cell_count_late ON cell_count_early.Filename = cell_count_late.Filename
"""

cell_count_ratio_overview = query_database(query)
cell_count_ratio_overview

	Filename	cell_count_ratio
0	image0.tif	0.171429
1	image1.tif	0.182796
2	image2.tif	0.172414
3	image3.tif	0.171233
4	image4.tif	0.172662
5	image5.tif	0.174721
6	image6.tif	0.986726
7	image7.tif	1.055718
8	image8.tif	0.991170
9	image9.tif	0.959368

Ahora sabemos la fracción de células que sobrevivieron en qué archivo. Necesitamos usar la declaración JOIN nuevamente para saber en qué experimento se observaron estas proporciones de supervivencia.

query = """
SELECT experiment.Comment, cell_count_ratio_overview.cell_count_ratio
FROM cell_count_ratio_overview
INNER JOIN image ON cell_count_ratio_overview.Filename = image.Filename
INNER JOIN experiment ON image.experiment_ID = experiment.ID
"""

query_database(query)

	Comment	cell_count_ratio
0	High dose	0.171429
1	High dose	0.182796
2	High dose	0.172414
3	Medium dose	0.171233
4	Medium dose	0.172662
5	Medium dose	0.174721
6	Control	0.986726
7	Control	1.055718
8	Control	0.991170
9	Control	0.959368

También estos pueden resumirse utilizando la declaración GROUP BY. Los buenos científicos no solo informan el promedio de estas mediciones, sino también el número de imágenes que se han analizado.

query = """
SELECT 
    experiment.Comment as experiment_name, 
    AVG(cell_count_ratio_overview.cell_count_ratio) as mean_cell_count_ratio, 
    COUNT(cell_count_ratio_overview.cell_count_ratio) as num_images
FROM cell_count_ratio_overview
INNER JOIN image ON cell_count_ratio_overview.Filename = image.Filename
INNER JOIN experiment ON image.experiment_ID = experiment.ID
GROUP BY experiment.Comment
"""

query_database(query)

	experiment_name	mean_cell_count_ratio	num_images
0	Control	0.998246	4
1	High dose	0.175546	3
2	Medium dose	0.172872	3

Ejercicio#

Determinar el recuento promedio de células durante todo el lapso de tiempo para todas las imágenes individualmente.

Resumir estas mediciones para imágenes individuales para presentar el promedio de cada experimento.