Medications

Last updated on 2025-09-17 | Edit this page

Overview

Questions

Where are medications stored ?

Objectives

Know that exposure of a patient to medications is mainly stored in the drug_exposure table
Understand that drug concepts can be at different levels of granularity
Understand that source values are mapped to a standard vocabulary

Introduction

The OMOP drug_exposure table stores exposure of a patient to medications.

The main columns are :

column name	content
drug_exposure_id	unique identifier given that person can get multiple exposures per visit
person_id	the patient
drug_concept_id	standard drug identifier, can be at different levels of granularity
drug_exposure_start_date	may also be an optional start_datetime
drug_exposure_end_date	may also be an optional end_datetime
drug_type_concept_id	where the record came from e.g. EHR administration record
drug_source_value	in UCLH the NHS dm+d Dictionary of Medicines and Devices ID
drug_source_concept_id	OMOP concept ID for the source value

Drug data can be very complicated, as can the process of converting from the source data to OMOP. You may not find what you expect depending on this and the quality of the source data.

Drug concepts

The standard OHDSI drug vocabularies are called RxNorm and RxNormExtension. RxNorm contains all drugs currently on the US market. RxNormExtension is maintained by the OHDSI community and contains all other drugs.

A particular concept_id can be at one of a number of different levels in a drug hierarchy. These are the main levels in RxNorm which are stored in concept_class_id in the concept table.

RxNorm concept_class_id	Description
Ingredient	A base active drug ingredient, without strength or dose form (e.g., Ibuprofen).
Clinical Drug Component	A drug component with strength but no form (e.g., Ibuprofen 200 mg).
Clinical Drug	A combination of an ingredient, strength, and dose form (e.g., Ibuprofen 200 mg Oral Tablet).

Drug mapping in the NHS

Drugs in the NHS are standardised to the NHS Dictionary of Medicines and Devices (dm+d). dm+d is included in OMOP so there are values of OMOP concept_id for each dm+d. However because dm+d is not a standard vocabulary in OMOP it is translate once more to get to a standard OMOP concept id in RxNorm or RxNormExtension that can be used in collaborative studies. If there is a drug_concept_id value of 0 and there are source codes this can be because that drug doesn’t map to a standard ID. Reminder that the source values are stored in these columns.

column name	content
drug_source_value	in UCLH the NHS dm+d Dictionary of Medicines and Devices ID
drug_source_concept_id	OMOP concept ID for the source value

TODO do we want to include about classifying medications by type ? It is not straightforward. I did something in omopcept but probably too tricky to include here.

R

# TODO later use the examples below or similar to create exercises
# OR do we want to make our own example data, e.g. including dm+d
# explore some drug_exposure data

library(CDMConnector)
library(DBI)
library(duckdb)
library(dplyr)


dbName <- "GiBleed" #smaller than other datasets quick to download

requireEunomia(datasetName = dbName)
con <- dbConnect(duckdb(), eunomiaDir(datasetName = dbName))
cdm <- cdmFromCon(con = con, cdmSchema = "main", writeSchema = "main", cdmName = dbName)


#67k records
#cdm$drug_exposure |> tally()

drugs_and_concept <- cdm$drug_exposure |>
  left_join(cdm$concept, join_by(drug_concept_id==concept_id)) |> 
  collect()
  
drugs_and_concept |> 
  count(concept_class_id, sort=TRUE) |> 
  head(5)
  
#  concept_class_id        n
#1 Clinical Drug       36218
#2 CVX                 25710
#3 Ingredient           2764
#4 Branded Pack         1640
#5 Quant Clinical Drug  1019  

#note that CVX is an RxNorm id for vaccines

# top 5 clinical drugs
drugs_and_concept |> 
  filter(concept_class_id=="Clinical Drug") |> 
  count(drug_concept_id, concept_name, sort=TRUE) |> 
  head(5)  
  
#  drug_concept_id concept_name                                                   n
#1         1127433 Acetaminophen 325 MG Oral Tablet                            9365
#2        19059056 Aspirin 81 MG Oral Tablet                                   4380
#3         1713671 Amoxicillin 250 MG / Clavulanate 125 MG Oral Tablet         3851
#4         1127078 Acetaminophen 160 MG Oral Tablet                            2158
#5        40229134 Acetaminophen 21.7 MG/ML / Dextromethorphan Hydrobromide …  1993
 
  
# top 5 ingredients
# in this case probably drugs that couldn't be converted to a more granular level
drugs_and_concept |> 
  filter(concept_class_id=="Ingredient") |> 
  count(drug_concept_id, concept_name, sort=TRUE) |> 
  head(3)    

#  drug_concept_id concept_name     n
#1         1118084 celecoxib     1844
#2         1124300 Diclofenac     850
#3         1367571 heparin         35

# TODO decide if we want to include drug quantity. Would need different example data.
# no drug quantity information in GiBleed
drugs_and_concept |> count(quantity, sort=TRUE)
# but there are plenty of days_supply
# oh dear, drug_strength has no rows so can't do the query below

# If you do have quantity data it can be converted to standard units using the
# drug_strength table that is part of the vocabularies.
# This will depend on the dose form which can be obtained from the relationship table.
# where the relationship_id is ‘Has dose form’.
# probably don't want to go into further here
# e.g. Tablets can be looked up 
#"Acetaminophen 325 MG Oral Tablet" from above 
cdm$drug_strength |>  filter(drug_concept_id==1127433L) |> 
  #add name for the ingredient
  left_join(select(cdm$concept,concept_id,concept_name), by=join_by(ingredient_concept_id==concept_id)) |> 
  rename(ingredient_concept_name=concept_name) |> 
  #add name for the amount unit
  left_join(select(cdm$concept,concept_id,concept_name), by=join_by(amount_unit_concept_id==concept_id)) |> 
  rename(amount_unit_concept_name=concept_name) |>   
  select(drug_concept_id, ingredient_concept_name, amount_value, amount_unit_concept_name) |> 
  collect()

# to get all drugs containing a particular ingredient
# you can also use the drug_strength table

Key Points

Know that exposure of a patient to medications is mainly stored in the drug_exposure table
Understand that drug concepts can be at different levels of granularity
Understand that source values are mapped to a standard vocabulary