Analyzing Electronic Health Records with OMOP Common Data Model and Python

Introduction

Electronic Health Records (EHRs) are notoriously messy and stored in countless proprietary formats across different hospital systems. The OMOP (Observational Medical Outcomes Partnership) Common Data Model (CDM) solves this by standardizing clinical data into a uniform structure and vocabulary. In this tutorial, we will use Python and Pandas to analyze a synthetic patient cohort structured in the OMOP CDM format, demonstrating how to extract clinical features for machine learning models.

Architecture Overview

The OMOP CDM is a relational schema centered around the PERSON table, linking to various clinical event domains using standardized CONCEPT_IDs.

erDiagram
    PERSON ||--o{ CONDITION_OCCURRENCE : "has diagnoses"
    PERSON ||--o{ DRUG_EXPOSURE : "takes medications"
    PERSON ||--o{ MEASUREMENT : "has lab results"
    PERSON ||--o{ VISIT_OCCURRENCE : "visits hospital"
    
    CONDITION_OCCURRENCE }o--|| CONCEPT : "maps to SNOMED/ICD10"
    DRUG_EXPOSURE }o--|| CONCEPT : "maps to RxNorm"
    MEASUREMENT }o--|| CONCEPT : "maps to LOINC"
        

Prerequisites

• Python 3.8+
• Pandas, SQLAlchemy
• Basic understanding of SQL and relational databases

Step 1: Install Dependencies

pip install pandas sqlalchemy psycopg2-binary

Step 2: Connecting to the OMOP Database

The OMOP CDM is typically hosted in a relational database (like PostgreSQL). We will use SQLAlchemy to establish a connection.

import pandas as pd
from sqlalchemy import create_engine

# Replace with your actual database credentials
db_url = "postgresql://user:password@localhost:5432/omop_synthetic_db"
engine = create_engine(db_url)

print("Connected to OMOP database successfully.")

Step 3: Extracting Cohort Demographics

The OMOP CDM centers around the PERSON table. We can query this table to get basic demographic information like age, gender, and race, mapped to standardized concept IDs.

# Query the PERSON table joined with CONCEPT for human-readable labels
query_demographics = """
SELECT 
    p.person_id,
    p.year_of_birth,
    c_gender.concept_name AS gender,
    c_race.concept_name AS race
FROM person p
LEFT JOIN concept c_gender ON p.gender_concept_id = c_gender.concept_id
LEFT JOIN concept c_race ON p.race_concept_id = c_race.concept_id
LIMIT 5;
"""

df_demographics = pd.read_sql_query(query_demographics, engine)
print(df_demographics)

Step 4: Extracting Clinical Events (Conditions and Drugs)

Clinical events are stored in domain-specific tables like CONDITION_OCCURRENCE (diagnoses) and DRUG_EXPOSURE (medications). We will extract all patients who have been diagnosed with Type 2 Diabetes (SNOMED concept ID 320128).

# Query for patients with Type 2 Diabetes
query_t2d = """
SELECT 
    co.person_id,
    co.condition_start_date,
    c.concept_name AS condition_name
FROM condition_occurrence co
JOIN concept c ON co.condition_concept_id = c.concept_id
WHERE c.concept_id = 320128 -- Type 2 Diabetes Mellitus
"""

df_t2d_patients = pd.read_sql_query(query_t2d, engine)
print(f"Found {len(df_t2d_patients)} patients with Type 2 Diabetes.")

Step 5: Feature Engineering for Machine Learning

To train predictive models (e.g., predicting 30-day readmission), we need to flatten this longitudinal relational data into a single feature matrix (one row per patient). We can pivot the conditions into binary features.

# Get all conditions for the diabetic cohort
diabetic_patient_ids = tuple(df_t2d_patients['person_id'].unique())

query_features = f"""
SELECT 
    co.person_id,
    c.concept_name AS condition_name
FROM condition_occurrence co
JOIN concept c ON co.condition_concept_id = c.concept_id
WHERE co.person_id IN {diabetic_patient_ids}
"""

df_all_conditions = pd.read_sql_query(query_features, engine)

# Create a binary feature matrix (One-Hot Encoding)
# 1 if the patient has the condition, 0 otherwise
feature_matrix = df_all_conditions.pivot_table(
    index='person_id', 
    columns='condition_name', 
    aggfunc=lambda x: 1, 
    fill_value=0
)

# Merge demographics with the feature matrix
final_ml_dataset = df_demographics.set_index('person_id').join(feature_matrix).fillna(0)
print(final_ml_dataset.head())

Troubleshooting: Big Data Challenges

• Memory Limits: Loading an entire hospital's MEASUREMENT table (lab results) into a Pandas DataFrame will crash most machines. For large datasets, push the pivot/aggregation logic into the SQL query itself, or use chunking (pd.read_sql_query(..., chunksize=10000)).
• Concept Hierarchies: Searching for "Diabetes" exactly (Concept 320128) might miss child concepts like "Type 2 Diabetes with Retinopathy". You should join with the CONCEPT_ANCESTOR table to automatically include all descendant concepts in your cohort definitions.

Conclusion

The OMOP Common Data Model provides an incredibly powerful abstraction layer for health data. By writing queries against the standardized OMOP schemas, your feature engineering pipelines and machine learning models instantly become interoperable across hundreds of global institutions, eliminating the need to rewrite ETL code for every new hospital dataset.