End-to-end analysis of synthetic patient healthcare data, from relational database design to statistical inference and interactive dashboard, built on the Synthea synthetic patient population simulator.
Identify patient characteristics associated with higher healthcare utilisation, using a normalised SQLite relational database, SQL window functions, and inferential statistics on a synthetic clinical cohort.
patient-pathway-analysis/
│
├── data/
│ └── raw/ # Raw Synthea CSV files, not tracked by Git
│
├── notebooks/
│ ├── 01_exploration.ipynb # Data ingestion, schema design, database initialisation
│ ├── 02_sql_analysis.ipynb # SQL queries, KPIs, window functions, cohort description
│ └── 03_statistical_analysis.ipynb # Inferential statistics and linear regression
│
├── plots/ # All exported figures (generated by notebooks)
│
├── streamlit_app/
│ ├── app.py # Main entry point
│ ├── 1_Overview.py # Cohort KPIs
│ ├── 2_Pathways.py # Healthcare utilisation analysis
│ ├── 3_Patient_Explorer.py # Individual patient journey
│ └── 4_Statistics.py # Interactive statistical tests
├── src/
│ └── db_utils.py # Database utility functions
│
├── .gitignore
├── requirements.txt
└── README.md
git clone https://github.com/juliettebm/patient-pathway-analysis.git
cd patient-pathway-analysis
pip install -r requirements.txt
The raw CSV files are not tracked by Git. Generate them using the Synthea Patient Generator and place the output files in data/raw/.
Alternatively, pre-generated datasets are available on the Synthea public downloads page.
jupyter notebook notebooks/01_exploration.ipynb
jupyter notebook notebooks/02_sql_analysis.ipynb
jupyter notebook notebooks/03_statistical_analysis.ipynb
streamlit run streamlit_app/app.py
- Loading raw Synthea CSV files into pandas DataFrames
- Schema design with primary keys and foreign key constraints (
PRAGMA foreign_keys) - Data cleaning and column normalisation
- Referential integrity verification
- Exports
patient_pathway.db(SQLite database)
- Cohort description: gender, age distribution, top pathologies
- Healthcare utilisation: visits per patient, top consumers
- Multimorbidity analysis: patients with 5+ distinct conditions (
COUNT(DISTINCT),HAVING) - Temporal reconstruction of care pathways using SQL window functions:
ROW_NUMBER(): visit chronology per patientLAG(): time elapsed between consecutive visitsRANK(): patient ranking by healthcare intensity
- Descriptive statistics with normality assessment (mean vs median, outlier detection)
- Chi-square test (gender vs complex chronic status)
- Mann-Whitney U test (healthcare utilisation in obese vs non-obese patients, non-parametric, justified by skewed distribution)
- Welch t-test (younger vs older patients, justified by unequal group variances)
- OLS linear regression (age as continuous predictor of total visits, with residual diagnostic plot for heteroscedasticity assessment)
Four tables built from raw Synthea CSV files:
| Table | Description | Key |
|---|---|---|
patients |
Demographics (gender, age, city) | patient_id |
encounters |
Healthcare visits | encounter_id → patient_id |
conditions |
Diagnoses over time | condition_id → patient_id |
procedures |
Medical acts performed | procedure_id → patient_id |
- Cohort of 1,146 patients (583 male, 563 female), mean age 44.5 years
- Visit distribution is heavily right-skewed (mean: 58.6 visits, median: 36.0): a small subset of patients drives a disproportionate share of healthcare consumption
- Patients with obesity show significantly higher healthcare utilisation than non-obese patients (Mann-Whitney U = 222,554, p = 8.55e-26)
- Older patients (>45y) accumulate significantly more visits than younger patients (Welch t = -9.206, p < 0.0001)
- Age is a statistically significant predictor of visit count but explains only ~12% of variance (R² = 0.122); clinical phenotype and social determinants play a major role
- Gender is not significantly associated with complex chronic status in this synthetic cohort (chi-square = 0.300, p = 0.584)
The interactive dashboard provides four modules:
| Page | Content |
|---|---|
| Overview | Cohort KPIs (patients, age, gender, visits) |
| Care Pathways | Visit distribution, top users, chronic patients |
| Patient Explorer | Individual clinical timeline by patient ID |
| Statistics | Chi-square, Mann-Whitney, Welch, regression results |
Why 5+ conditions as the multimorbidity threshold? Synthea tracks the full longitudinal history of each patient, including minor acute episodes. The standard 2+ threshold would classify nearly the entire cohort as multimorbid (95.4% of patients meet the 2+ threshold alone). The 5+ threshold isolates a genuinely complex subpopulation for meaningful analysis.
Why Mann-Whitney instead of a t-test for visit comparison? The distribution of total visits is heavily right-skewed with extreme outliers. The normality assumption required by Student's t-test is violated, making Mann-Whitney the appropriate non-parametric alternative.
Why Welch instead of Student's t-test for age groups? Welch's formulation does not assume equal variances between groups, which protects against type I error inflation when group variances differ, as is the case here.
Python 3.x · pandas · NumPy · matplotlib · scipy · statsmodels · SQLite · Streamlit · Jupyter
Walonoski, J., et al. (2017). Synthea: An approach, method, and software mechanism for generating synthetic patients and the synthetic electronic health care record. Journal of the American Medical Informatics Association. Dataset generator: https://synthea.mitre.org/
Synthetic data only, no real patient information.
👉 hospital-readmission-diabetes: Predictive modelling for 30-day readmission risk in diabetic patients (Random Forest, scikit-learn, Streamlit)