Requêtes usuelles v5.4
Introduction
Nous présentons ici quelques requêtes que l’on utilise fréquemment pour requêter les tables de bases de données au format OMOP.
Ces requêtes se basent sur la version 5.4 du schéma OMOP.
The OMOP CDM v5.4 entity relationship diagram from Martijn Schuemie and Renske Los
Pour chaque tables, les requêtes sont disponibles :
- en utilisant la librairie dplyr de R (directement depuis LinkR)
- en utilisant du SQL (PostgreSQL)
- en utilisant la librairie pandas de Python
Pour le code en dplyr
, nous utiliserons les fonctions join_concept
et count_concept_rows
, qui sont disponibles dans LinkR (pas besoin de les déclarer de nouveau) :
# Permet de faire la jointure des ID et des noms de concepts
join_concept <- function(df, concept_df, key, name, copy = TRUE) {
# Dans LinkR, les variables de données d$... sont de type lazy, tandis que d$concept ne l'est pas.
# Nous utilisons l'option copy = TRUE pour copier temporairement la variable d$... en mémoire locale,
# parce qu'une table lazy ne peut pas être fusionnée directement avec une table locale.
df %>%
dplyr::left_join(
concept_df %>%
dplyr::select(!!key := concept_id, !!name := concept_name),
by = key,
copy = copy
)
}
# Permet de faire le décompte de chaque concept
count_concept_rows <- function(df, group_col, name_col) {
df %>%
dplyr::group_by(dplyr::across(dplyr::all_of(c(group_col, name_col)))) %>%
dplyr::summarize(n = n(), .groups = 'drop') %>%
dplyr::arrange(desc(n))
}
PERSON
- Calcul de l’âge
L’âge est calculé depuis les variables d$visit_occurrence
(hospitalisations) ou d$visit_detail
(séjours dans les unités au cours d’une hospitalisation).
Il s’agit de l’âge du patient à l’admission pour chaque hospitalisation ou admission dans une unité.
# Le code de calcul des dates étant mal converti en SQL, nous collectons les données avec dplyr::collect(),
# ce qui signifie que les données sont copiées dans un dataframe localement.
# Le code dplyr n'est ainsi pas converti en SQL.
d$visit_occurrence %>%
dplyr::left_join(
d$person %>% dplyr::select(person_id, birth_datetime),
by = "person_id"
) %>%
dplyr::collect() %>%
dplyr::mutate(
age = round(as.numeric(difftime(visit_start_datetime, birth_datetime, units = "days")) / 365.25, 1)
)
-- DuckDB / PostgreSQL
SELECT
v.*,
ROUND(
EXTRACT(EPOCH FROM (v.visit_start_datetime - p.birth_datetime)) / (365.25 * 86400),
1
) AS age
FROM
visit_occurrence v
LEFT JOIN
(SELECT person_id, birth_datetime FROM person) p
ON
v.person_id = p.person_id;
import pandas as pd
merged_df = pd.merge(
visit_occurrence,
person[['person_id', 'birth_datetime']],
on='person_id',
how='left'
)
merged_df['age'] = round(
(merged_df['visit_start_datetime'] - merged_df['birth_datetime']).dt.total_seconds() / (365.25 * 86400),
1
)
- Jointure des noms de concepts
d$person %>%
join_concept(d$concept, "gender_concept_id", "gender_concept_name") %>%
join_concept(d$concept, "race_concept_id", "race_concept_name") %>%
join_concept(d$concept, "ethnicity_concept_id", "ethnicity_concept_name")
-- DuckDB / PostgreSQL
SELECT
p.*,
c.concept_name AS gender_concept_name
FROM
person p
LEFT JOIN
(SELECT concept_id AS gender_concept_id, concept_name FROM concept) c
ON
p.gender_concept_id = c.gender_concept_id;
import pandas as pd
concept_df = concept[['concept_id', 'concept_name']].rename(
columns={'concept_id': 'gender_concept_id', 'concept_name': 'gender_concept_name'}
)
merged_df = pd.merge(
person,
concept_df,
how='left',
left_on='gender_concept_id',
right_on='gender_concept_id'
)
VISIT_OCCURRENCE
- Jointure des noms de concepts
d$visit_occurrence %>%
join_concept(d$concept, "visit_concept_id", "visit_concept_name") %>%
join_concept(d$concept, "visit_type_concept_id", "visit_type_concept_name") %>%
join_concept(d$concept, "admitted_from_concept_id", "admitted_from_concept_name") %>%
join_concept(d$concept, "discharge_to_concept_id", "discharge_to_concept_name")
- Calcul du nombre d’occurrences par
visit_concept_name
d$visit_occurrence %>%
join_concept(d$concept, "visit_concept_id", "visit_concept_name") %>%
count_concept_rows("visit_concept_id", "visit_concept_name") %>%
dplyr::collect() %>%
print(n = 100)
VISIT_DETAIL
- Jointure des noms de concepts
d$visit_detail %>%
join_concept(d$concept, "visit_detail_concept_id", "visit_detail_concept_name") %>%
join_concept(d$concept, "visit_detail_type_concept_id", "visit_detail_type_concept_name") %>%
join_concept(d$concept, "admitted_from_concept_id", "admitted_from_concept_name") %>%
join_concept(d$concept, "discharge_to_concept_id", "discharge_to_concept_name")
- Calcul du nombre d’occurrences par
visit_detail_concept_name
d$visit_detail %>%
join_concept(d$concept, "visit_detail_concept_id", "visit_detail_concept_name") %>%
count_concept_rows("visit_detail_concept_id", "visit_detail_concept_name") %>%
dplyr::collect() %>%
print(n = 100)
CONDITION_OCCURRENCE
- Jointure des noms de concepts
d$condition_occurrence %>%
join_concept(d$concept, "condition_concept_id", "condition_concept_name") %>%
join_concept(d$concept, "condition_type_concept_id", "condition_type_concept_name")
- Calcul du nombre d’occurrences par
condition_concept_name
d$condition_occurrence %>%
join_concept(d$concept, "condition_concept_id", "condition_concept_name") %>%
count_concept_rows("condition_concept_id", "condition_concept_name") %>%
dplyr::collect() %>%
print(n = 100)
MEASUREMENT
- Jointure des noms de concepts
d$measurement %>%
join_concept(d$concept, "measurement_concept_id", "measurement_concept_name") %>%
join_concept(d$concept, "measurement_type_concept_id", "measurement_type_concept_name") %>%
join_concept(d$concept, "operator_concept_id", "operator_concept_name") %>%
join_concept(d$concept, "unit_concept_id", "unit_concept_name")
- Calcul du nombre d’occurrences par
measurement_concept_name
d$measurement %>%
join_concept(d$concept, "measurement_concept_id", "measurement_concept_name") %>%
count_concept_rows("measurement_concept_id", "measurement_concept_name") %>%
dplyr::collect() %>%
print(n = 100)
OBSERVATION
- Jointure des noms de concepts
d$observation %>%
join_concept(d$concept, "observation_concept_id", "observation_concept_name") %>%
join_concept(d$concept, "observation_type_concept_id", "observation_type_concept_name") %>%
join_concept(d$concept, "value_as_concept_id", "value_as_concept_name") %>%
join_concept(d$concept, "qualifier_concept_id", "qualifier_concept_name") %>%
join_concept(d$concept, "unit_concept_id", "unit_concept_name")
- Calcul du nombre d’occurrences par
observation_concept_name
d$observation %>%
join_concept(d$concept, "observation_concept_id", "observation_concept_name") %>%
count_concept_rows("observation_concept_id", "observation_concept_name") %>%
dplyr::collect() %>%
print(n = 100)