Skip to content

OMOP CDM v5.4

omop_etl.py reads dashboard_data.json and a folder of vocabulary files downloaded from Athena, maps every source code to its standard concept, and writes tables in the OMOP Common Data Model (CDM) version 5.4, the format the OHDSI network's analysis tools read.

Module source (omop_etl.py)

Tables · Mapping · Medication codes · Vocabularies · Running it · Checks

Tables written

Table Source Notes
PERSON.csv patients Gender mapped to 8507 (male) and 8532 (female); race and ethnicity concepts left as 0; the registry patient ID in person_source_value
VISIT_OCCURRENCE.csv encounters Ambulatory, inpatient and emergency classes mapped to standard visit concepts
CONDITION_OCCURRENCE.csv Records whose standard concept is in the Condition domain
DRUG_EXPOSURE.csv Records whose standard concept is in the Drug domain sig from the dosage text or display name
PROCEDURE_OCCURRENCE.csv Records whose standard concept is in the Procedure domain
MEASUREMENT.csv Records whose standard concept is in the Measurement domain value_as_number and unit_source_value kept
OBSERVATION.csv Records whose standard concept is in the Observation domain
OBSERVATION_PERIOD.csv Derived Earliest to latest dated record for each person
DEATH.csv patients From deceasedDateTime
CDM_SOURCE.csv Metadata The vocabulary versions used

Records go to the table for the domain of their standard concept, not the bundle table they came from, so an ICD-10-CM code in problems whose standard concept is a procedure is written to PROCEDURE_OCCURRENCE, as the CDM requires.

The tables are identified: PERSON.csv holds registry IDs and every table holds calendar dates. run_all.py writes them under restricted/. See Participant identifiers before sharing them.

Back to top

How codes are mapped

For each source vocabulary and code:

  1. The code is looked up in CONCEPT.csv for that vocabulary to get the source concept.
  2. CONCEPT_RELATIONSHIP.csv rows with relationship_id = 'Maps to' give the standard concepts. A source concept can map to more than one standard concept; each target becomes its own row, in the table for its own domain.
  3. The standard concept's domain_id decides the table.
  4. Each row keeps the standard concept (*_concept_id), the source concept (*_source_concept_id) and the source code (*_source_value), so every mapping can be traced.

A code that does not resolve is still written, with *_concept_id = 0 and the source values filled in, so nothing is dropped without notice. omop_etl.log lists unmapped codes, most frequent first.

Back to top

Medication codes

A FHIR medication or a C-CDA medication with translations often holds several RxNorm codes for one prescription: an ingredient (IN), a clinical drug (SCD) and a brand name (BN). When Athena is loaded, the code is chosen by RxNorm term type:

SCD > SBD > SCDC/SBDC > GPCK/BPCK > SCDF/SBDF > IN > PIN > BN > DF

Clinical and branded drugs include ingredient, strength and dose form, which is what the OHDSI DRUG_STRENGTH table and most drug analyses use. Without Athena, the code chosen during parsing is used, which prefers a display name with a strength (50 MG) and a dose form (Tablet). For other domains, the first coding in the domain's preferred system is used.

Back to top

Downloading the vocabularies

  1. Sign in at athena.ohdsi.org.
  2. Select at least SNOMED, ICD10CM, RxNorm, RxNorm Extension, LOINC, CVX and CPT4. CPT4 needs a separate license step, which Athena describes.
  3. Download, wait for the email with the link, and unzip everything into one folder. It should hold CONCEPT.csv, CONCEPT_RELATIONSHIP.csv and VOCABULARY.csv, among others.

The vocabularies are large. The module reads CONCEPT.csv once and keeps only the concepts in the vocabularies your bundle uses and the standard concepts they map to.

Back to top

Running it

With run_all.py, add --vocab /path/to/athena. To run the module on its own:

python pipeline/omop_etl.py --bundle dashboard_data.json --vocab /path/to/athena \
    --out restricted/omop_output
Option Default Effect
--bundle ./dashboard_data.json Bundle from run_pipeline.py
--vocab ./vocab/ Folder holding the Athena CSV files
--out ./omop_output Folder name prefix; the vocabulary release from VOCABULARY.csv is added, for example omop_output_v20240830/

Because the release is in the folder name, runs against different Athena releases do not overwrite each other.

Back to top

Checks after a run

import os, pandas as pd
out = 'restricted/omop_output_<release>/'
for fn, col in [('CONDITION_OCCURRENCE.csv', 'condition_concept_id'), ('DRUG_EXPOSURE.csv', 'drug_concept_id'),
                ('PROCEDURE_OCCURRENCE.csv', 'procedure_concept_id'), ('MEASUREMENT.csv', 'measurement_concept_id')]:
    df = pd.read_csv(os.path.join(out, fn))
    if len(df):
        print(f'{fn:<28} rows {len(df):>7,}   unmapped {(df[col] == 0).mean():.1%}')

A high unmapped share usually means a vocabulary is missing from the Athena download; check CDM_SOURCE.csv and download again with it selected. For a full check, load the tables into a database and run the OHDSI Data Quality Dashboard.

Values held only in notes, such as ALSFRS-R scores typed into a clinic note, are not in the OMOP tables until they are reviewed and entered as structured data; note_extractions.csv lists the candidates.

Back to top