Requêtes usuelles v5.4

Par Boris Delange, Antoine Lamer | 02.09.2024 | MàJ 12.11.2024

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.

Le schéma de données du modèle commun OMOP v5.4 de Martijn Schuemie et Renske Los

Pour chaque table, 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 la fonction join_concepts qui est disponible dans LinkR (pas besoin de les déclarer de nouveau) :

# Permet de faire la jointure des ID et des noms de concepts

join_concepts <- function(df, concept_df, cols) {
  
  for (col in cols) {
    key <- paste0(col, "_concept_id")
    name <- paste0(col, "_concept_name")
    
    df <- df %>%
      dplyr::left_join(
        concept_df %>%
          dplyr::select(!!key := concept_id, !!name := concept_name),
        by = key,
        copy = TRUE
      ) %>%
      dplyr::relocate(!!name, .after = !!key)
  }
  
  return(df)
}

Dans les exemples, les données des tables OMOP sont chargées dans une liste d$.

# Charger les tables dans une liste 'd'
d <- list()

for (table in c("person", "visit_detail")){
    sql <- glue::glue_sql("SELECT * FROM {`table`}, .con = con)
    d[[table]] <- DBI::dbGetQuery(con, sql)
}

CONDITION_OCCURRENCE

Jointure des noms de concepts

d$condition_occurrence %>%
    join_concepts(d$concept, c("condition", "condition_type"))

Calcul du nombre d’occurrences par condition_concept_name

d$condition_occurrence %>%
    join_concepts(d$concept, "condition") %>%
    dplyr::collect() %>%
    dplyr::count(condition_concept_name, sort = TRUE) %>%
    print(n = 100)

DRUG_STRENGTH

Jointure des noms de concepts

d$drug_exposure %>%
    join_concepts(d$concept, c("drug", "drug_type", "route")) %>%
    dplyr::left_join(
        d$drug_strength %>%
            join_concepts(d$concept, c("ingredient", "amount_unit", "numerator_unit", "denominator_unit")) %>%
            dplyr::select(
                drug_concept_id, ingredient_concept_id, ingredient_concept_name,
                amount_value, amount_unit_concept_id, amount_unit_concept_name,
                numerator_value, numerator_unit_concept_id, numerator_unit_concept_name,
                denominator_value, denominator_unit_concept_id, denominator_unit_concept_name
            ),
        by = "drug_concept_id",
        copy = TRUE
    )

Calcul du nombre d’occurrences par drug_concept_name

d$drug_exposure %>%
    join_concepts(d$concept, "drug") %>%
    dplyr::count(drug_concept_name, sort = TRUE) %>%
    dplyr::collect() %>%
    print(n = 100)

Affichage des administrations

Comme indiqué ici, il existe différents cas pour calculer les doses des administrations médicamenteuses.

Nous pouvons retenir deux cas :

le champ amount_value est rempli, dans ce cas c’est celui-ci qu’il faut utiliser (cas des médicaments per os)
le champ amount_value n’est pas rempli, dans ce cas il faut utiliser le le champ numerator_value (cas des administrations intra-veineuses, sous-cutanées, en aérosols et en patchs)

Nous allons créer un champ amount, qui sera la synthèse des champs amount_value et numerator_value, ce sera la quantité délivrée du médicament.

Ensuite, nous pourrons déterminer le débit, en rapportant le champ amount au temps entre le début et la fin de l’administration. Nous allons pour cela créer un champ duration_hours, qui sera le nombre d’heures pendant lequel est délivré le médicament.

Nous créons également un champ daily_dose pour calculer la dose quotidienne moyenne sur la période, exprimée en daily_dose_unit.

Le modèle OMOP a initialement été développé pour des analyses épidémiologiques, il n’était pas important de savoir précisément la date et l’heure de l’administration d’un médicament, mais uniquement la quantité reçue globalement sur une période temps.

Ceci pose problème dans le cas des entrepôts de données de santé, où l’heure précise d’administration d’un médicament nous importe.

Il est donc important lors du processus d’ETL de créer une nouvelle ligne à chaque changement de dose, et une ligne par administration intermittente.

Par exemple, si un patient reçoit 1 g de paracétamol toutes les 6 heures pendant 5 jours, plutôt que de créer une ligne avec quantity = 5*4 = 20 et la date de fin à + 5 jours de la date de début, on préférera créer une ligne par administration, soit 20 lignes avec les horaires précis d’administration, avec date de début = date de fin.

d$drug_exposure %>%
    join_concepts(d$concept, c("drug", "drug_type", "route")) %>%
    dplyr::left_join(
        d$drug_strength %>%
            join_concepts(d$concept, c("ingredient", "amount_unit", "numerator_unit", "denominator_unit")) %>%
            dplyr::select(
                drug_concept_id, ingredient_concept_id, ingredient_concept_name,
                amount_value, amount_unit_concept_id, amount_unit_concept_name,
                numerator_value, numerator_unit_concept_id, numerator_unit_concept_name,
                denominator_value, denominator_unit_concept_id, denominator_unit_concept_name
            ),
        by = "drug_concept_id",
        copy = TRUE
    ) %>%
    dplyr::collect() %>%
    dplyr::arrange(person_id, drug_exposure_start_datetime) %>%
    dplyr::mutate(
        amount = dplyr::case_when(
            !is.na(amount_value) ~ quantity * amount_value,
            !is.na(numerator_value) ~ quantity * numerator_value
        ),
        amount_unit = dplyr::case_when(
            !is.na(amount_value) ~ amount_unit_concept_name,
            !is.na(numerator_value) ~ numerator_unit_concept_name
        ),
        duration_hours = as.numeric(difftime(drug_exposure_end_datetime, drug_exposure_start_datetime, units = "hours")),
        rate = dplyr::case_when(
            !is.na(numerator_value) & !is.na(duration_hours) & duration_hours > 0 ~ amount / duration_hours
        ),
        rate_unit = dplyr::case_when(
            !is.na(rate) & !is.na(amount_unit) ~ paste0(amount_unit, " per hour")
        ),
        daily_dose = dplyr::case_when(
            is.na(rate) & !is.na(amount) ~ amount / duration_hours * 24
        ),
        daily_dose_unit = dplyr::case_when(
            is.na(rate) & !is.na(amount_unit) ~ paste0(amount_unit, " per day")
        )
    ) %>%
    dplyr::select(
        person_id, drug_concept_name,
        drug_exposure_start_datetime, drug_exposure_end_datetime, duration_hours,
        amount, amount_unit, rate, rate_unit, daily_dose, daily_dose_unit
    )

MEASUREMENT

Jointure des noms de concepts

d$measurement %>%
    join_concepts(d$concept, c("measurement", "measurement_type", "operator", "unit"))

Calcul du nombre d’occurrences par measurement_concept_name et unit_concept_name

d$measurement %>%
    join_concepts(d$concept, c("measurement", "unit")) %>%
    dplyr::count(measurement_concept_name, unit_concept_name, sort = TRUE) %>%
    dplyr::collect() %>%
    print(n = 100)

OBSERVATION

Jointure des noms de concepts

d$observation %>%
    join_concepts(d$concept, c("observation", "observation_type", "value_as", "qualifier", "unit"))

Calcul du nombre d’occurrences par observation_concept_name

d$observation %>%
    join_concepts(d$concept, "observation") %>%
    dplyr::count(observation_concept_name, sort = TRUE) %>%
    dplyr::collect() %>%
    print(n = 100)

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_concepts(d$concept, c("gender", "race", "ethnicity"))

-- 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'
)

Calcul du nombre d’occurrences par gender_concept_name

d$person %>%
    join_concepts(d$concept, "gender") %>%
    dplyr::count(gender_concept_name, sort = TRUE) %>%
    dplyr::collect() %>%
    print(n = 100)

VISIT_DETAIL

Jointure des noms de concepts

d$visit_detail %>%
    join_concepts(d$concept, c("visit_detail", "visit_detail_type", "admitted_from", "discharge_to"))

Calcul du nombre d’occurrences par visit_detail_concept_name

d$visit_detail %>%
    join_concepts(d$concept, "visit_detail")) %>%
    dplyr::count(visit_detail_concept_name, sort = TRUE) %>%
    dplyr::collect() %>%
    print(n = 100)

VISIT_OCCURRENCE

Jointure des noms de concepts

d$visit_occurrence %>%
    join_concepts(d$concept, c("visit", "visit_type", "admitted_from", "discharge_to"))

Calcul du nombre d’occurrences par visit_concept_name

d$visit_occurrence %>%
    join_concepts(d$concept, "visit") %>%
    dplyr::count(visit_concept_name, sort = TRUE) %>%
    dplyr::collect() %>%
    print(n = 100)

Dernière modification 24.03.2025: LinkR & D4G article (320f18b)