Capstone Project Summary December 2024

Introduction

For my capstone project for my M.S. in Data Science from Northeastern University, I worked with a stakeholder at the Observational Health Data Sciences and Informatics Center (OHDSI) at Northeastern to study stroke survivor aftercare. Using a combination of SQL and python, I created a cohort/phenotype of stroke patients from a large commercial health insurance claims database built in the Observational Medical Outcomes Partnership (OMOP) Common Data Model (CDM) standard. Then I performed a statistical analysis in R of factors affecting the likelihood of receiving speech language treatment for aphasia, dysarthria, and dysphagia for stroke patients. The full details of the project and results can be found in capstone_project_summary.md.

This README file contains technical instructions to create the cohort of stroke patients either through amazon redshift connector in python or by running SQL files in a database management tool.

Repository Structure

.
├─ figs
│  ├─ model_analysis                       # figures for capstone project results  - Dec. 2024
│  │  └─...
│  ├─ stroke_cohort_eda                    # figures for initial stroke cohort eda - Oct. 2024
│     └─... 
├─ src
│  ├─ analysis
│  │  ├─ eda.py                            # creates the figs in '/figs/stroke_cohort_eda'
│  │  ├─ pharmetrix_eda.sql                # a small sample of sql analsysis performed during the project
│  │  └─ stroke_cohort_analysis.Rmd        # R Markdown containing all modeling code
│  ├─ sql_for_modeling                     # sql files creating relational database tables used for modeling
│  │  ├─ model_1_full.sql       
│  │  ├─ model_1_insurance.sql      
│  │  ├─ model_1_target.sql       
│  │  └─ stroke_cohort.sql           
│  └─ create_stroke_cohort_and_model_1.py  # python file with parameterized functions to create all tables
├─ .gitignore                              # config.py file contains personal database credentials
├─ README.md                               # technical instructions to create tables
├─ capstone_project_summary.md             # results from stroke cohort analysis - Dec. 2024
├─ environment.yaml
└─ preliminary_cohort_eda.md               # initial EDA of stroke cohort - Oct. 2024

Instructions to create the cohort, model target vector, and model feature matrix in python or directly in a dbms

First, acquire database credentials through OHDSI Lab, and save them in a config file. The config file is imported to all scripts that connect to the database, but it is included in the .gitignore file for security reasons.

Run create_stroke_cohort_and_model_1.py

This script contains a series of functions that create the necessary tables to create a full stroke cohort and the data matrix for the statistical analysis. At the end of the file, you will need to change the names of the following tables so that you are writing to your own personal schema in the OHDSI Lab database rather than work_tilton_ca204.

in_patient_stroke_table = "work_tilton_ca204.inpatient_stroke_demo" 
cohort_table = "work_tilton_ca204.stroke_cohort_w_conditions_demo" 
model_target_table = "work_tilton_ca204.model_1_target_demo" 
full_model_table = "work_tilton_ca204.model_1_full_demo" 
insurance_table = "work_tilton_ca204.model_1_insurance_demo" 

Alternatively, you can create the tables directly in a database management system tool like DBeaver. Run the sql files in src/sql_for_modeling in the following order:

1. stroke_cohort.sql
2. model_1_target.sql
3. model_1_full.sql
4. model_1_insurance.sql

Note: you will need to specify the table names so the sql queries write the data to your personal schema. All the table names are in {curly brackets}. For example, you will need to find and replace all instances of {stroke_cohort} into the exact table name you want to write to.