Geographic-Variation-in-Care-Delivery

Group 4

INFO-B 512 Scientific and Clinical Data Management

MS Health Informatics Program

Indiana University, Indianapolis

---

Overview

This project analyzes geographic variations in healthcare delivery using OpenEMR clinical data. A normalized relational database schema with ER diagrams was designed and implemented in MySQL using phpMyAdmin. Clinical data was extracted using SQL queries executed through Python, and analyzed to identify patterns in immunization rates, encounter frequency, and prescription trends across geographic regions using Pandas and Matplotlib.

---

Project Objectives

• Design and implement normalized relational database schema with ER diagrams.
• Extract and analyze OpenEMR clinical data using SQL and Python.
• Identify geographic variations in immunization rates and encounter frequency.
• Analyze prescription patterns across different regions.
• Visualize healthcare delivery trends by location.

---

Technologies

• Database: MySQL, phpMyAdmin
• Programming: Python 3.12
• Libraries: mysql-connector-python, pandas, matplotlib, seaborn, numpy
• Environment: Jupyter Notebook

---

Database Schema

ER Diagram

Tables

1. patient_data

• Primary Key: pid (BIGINT)
• Attributes:
  • DOB (DATE)
  • sex (VARCHAR)
  • city (VARCHAR)
  • state (VARCHAR)

2. facility

• Primary Key: id (INT)
• Attributes:
  • name (VARCHAR)
  • city (VARCHAR)
  • state (VARCHAR)

3. form_encounter

• Primary Key: id (BIGINT)
• Foreign Keys:
  • pid (BIGINT) references patient_data(pid)
  • facility_id (INT) references facility(id)
• Attributes:
  • date (DATE)
  • reason_for_visit (VARCHAR)

4. immunizations

• Primary Key: id (BIGINT)
• Foreign Key: pid (BIGINT) references patient_data(pid)
• Attributes:
  • administered_date (DATE)
  • cdc_vaccine_code (VARCHAR)

5. prescriptions

• Primary Key: id (BIGINT)
• Foreign Key: encounter_id (BIGINT) references form_encounter(id)
• Attributes:
  • drug_name (VARCHAR)

6. procedure_codes

• Primary Key: id (BIGINT)
• Foreign Key: form_encounter_id (BIGINT) references form_encounter(id)
• Attributes:
  • cpt_code (VARCHAR)

---

Key Analysis

1. Immunization Rates by City

• Boston: 84 immunizations (highest)
• Haverhill: 82 immunizations
• Analysis covers 20 cities across Massachusetts

2. Encounter Frequency & Follow-up Patterns

• Average encounters per patient: 43.23
• 30-day follow-up rate: 30.97%
• Average time to follow-up: 15.5 days

3. Common Encounter Reasons

• General examination: Most frequent
• Encounter for problem (procedure): Second most common
• Follow-up encounter: Third most common

4. Top Prescribed Medications

1. Acetaminophen 325 MG (38 prescriptions)
2. Amoxicillin/Clavulanate (21 prescriptions)
3. Lisinopril 10 MG (21 prescriptions)

5. Facility Utilization

• Obstetrics and Gynecology Unit: 4,323 encounters

---

Key Findings

• Geographic concentration with Boston leading in immunizations and overall healthcare utilization.
• High patient engagement (43.23 encounters) but 30.97% follow-up rate needs improvement.
• General examinations dominates encounter reasons across regions.
• Acetaminophen leads overall prescriptions, followed by Amoxicillin/Clavulanate and Lisinopril.

---