EHR Data to Clinical Risk Scores

Build a Charlson Comorbidity Index pipeline from real hospital EHR data.

Introduction

30 Second Summary

When you arrive at a hospital, your medical record quickly becomes a web of codes, tables, and timestamps spread across dozens of systems. Every diagnosis, lab result, and medication gets its own structured entry that clinicians and researchers query to understand patient risk.

In this project, you will take real de-identified hospital data from 100 patients and build a clinical risk scoring pipeline. You will go from raw database tables to a per-patient comorbidity score that summarizes overall patient illness burden.

What You'll Build

You run the final cell in your Jupyter notebook and see a scored patient cohort with each patient's Charlson Comorbidity Index broken down by the conditions that contributed to it.

By the end of this project, you'll have:

  • A queryable SQLite database of 33 hospital tables (admissions, diagnoses, labs, medications, procedures) that you can explore through SQL in your notebook.
  • The ability to look up any ICD code and see its human-readable meaning, its place in the coding hierarchy, and which Charlson category it maps to.
  • A complete scoring pipeline that calculates each patient's risk score and shows exactly which conditions contributed to it.
  • Secret Mission: Add age-adjusted Charlson scoring using the original 1987 age brackets.

Are there any prerequisites?

You need basic Python familiarity (variables, functions, running cells in Jupyter) and basic SQL awareness (SELECT, FROM, WHERE). No clinical coding or health informatics experience is required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for health data science.

Set Up Your Clinical Data Environment

Every health data science workflow starts with real patient data. Without a queryable database, clinical algorithms stay theoretical.

In this step, you will install the TinyEHR package and use it to build a local SQLite database from 100 de-identified patient records. These records come from Beth Israel Deaconess Medical Center and follow the MIMIC-IV schema, the same schema used by hospital analytics departments worldwide.

In this step, get ready to:
  • Install the tinyehr package and required Python libraries.
  • Build a local SQLite database from 100 de-identified patient records.
  • Connect to the database and verify all tables are accessible.
Install tinyehr and import your libraries

TinyEHR is a Python package that wraps real de-identified hospital data from the MIMIC-IV Clinical Database Demo. It gives you a one-line function to build a full SQLite database locally.

  • In your Jupyter notebook, create a new cell and install the required packages by running this command:
!pip install tinyehr pandas matplotlib

What does this command do?

The ! prefix tells Jupyter to run this as a terminal command instead of Python code.

  • tinyehr provides the patient data and the database builder.
  • pandas lets you run SQL queries and get back clean DataFrames.
  • matplotlib handles the visualizations you will create in later steps.
  • Wait for the installation to finish. You should see Successfully installed messages in the output for each package.

Seeing a permission error or install failure?

  • Make sure you included the ! at the start. Without it, Jupyter treats the line as Python code and throws a syntax error.
  • If you see a timeout error, check your internet connection and re-run the cell.

Help me troubleshoot a pip install error in Jupyter Notebook.

  • Now create a new cell below the install cell and add your import statements by pasting this code:
# Cell 1: Install and import
# Run this cell first: !pip install tinyehr pandas matplotlib
import tinyehr
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
  • Run this cell. If it completes without any error, your imports are working correctly.

What are these libraries for?

  • tinyehr builds the database from MIMIC-IV patient data.
  • sqlite3 is Python's built-in module for connecting to SQLite databases.
  • pandas (imported as pd) runs SQL queries and returns results as DataFrames.
  • matplotlib.pyplot (imported as plt) will generate charts later in the project.
Build the database and verify your connection

TinyEHR's build_sqlite() function downloads 100 de-identified patient records and structures them into a local SQLite database with 30+ tables. This is the same table layout hospitals use for their EHR systems.

  • Create a new cell below your imports and paste the following code:
# Cell 2: Build the database and connect
db_path = tinyehr.build_sqlite(format="tinyehr_mimic_format")
conn = sqlite3.connect(db_path)
print(f"Database created at: {db_path}")

# Verify: list all tables
tables = pd.read_sql("""
    SELECT name FROM sqlite_master 
    WHERE type='table' 
    ORDER BY name;
""", conn)
print(f"\nTotal tables: {len(tables)}")
print(tables.to_string())

Before you run this cell, what do you think the output will show? How many tables would a hospital need to store patient data?

  • Run the cell.

You should see the database file path printed, followed by a list of 30+ tables sorted alphabetically. Look for key tables like admissions, diagnoses_icd, patients, labevents, and prescriptions.

What does this code do?

  • tinyehr.build_sqlite(format="tinyehr_mimic_format") creates a SQLite database file containing 33 tables (22 hospital, 9 ICU, plus clinical notes and metadata).
  • sqlite3.connect(db_path) opens a connection to that database so you can run SQL against it.
  • pd.read_sql() sends a SQL query through the connection and returns the results as a pandas DataFrame.
  • The query SELECT name FROM sqlite_master WHERE type='table' is a built-in SQLite command that lists every table in the database.

Not seeing the table list?

  • If you see a ModuleNotFoundError, go back to your install cell and re-run it. Make sure the install completed successfully before running the import cell.
  • If the database creation hangs, check that your internet connection is active. TinyEHR downloads the data from HuggingFace on first use.
  • If you see 0 tables, make sure you passed the format argument exactly as format="tinyehr_mimic_format".

Help me troubleshoot my TinyEHR database build in Jupyter Notebook.

Why so many tables?

Hospitals do not store everything in one giant spreadsheet. They split data across normalized tables so that patient demographics, admissions, lab results, medications, and diagnoses each have their own structure. This is called database normalization.

In the next step, you will see how these tables connect through shared keys like subject_id and hadm_id.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook.

ⓧ I'd like to double check the full code

Your notebook should have two code cells at this point. Here is the complete code across both cells:

Cell 1 (imports):

# Cell 1: Install and import
# Run this cell first: !pip install tinyehr pandas matplotlib
import tinyehr
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt

Cell 2 (build and verify):

# Cell 2: Build the database and connect
db_path = tinyehr.build_sqlite(format="tinyehr_mimic_format")
conn = sqlite3.connect(db_path)
print(f"Database created at: {db_path}")

# Verify: list all tables
tables = pd.read_sql("""
    SELECT name FROM sqlite_master 
    WHERE type='table' 
    ORDER BY name;
""", conn)
print(f"\nTotal tables: {len(tables)}")
print(tables.to_string())

You now have a working clinical database with 30+ tables of real de-identified patient data, queryable from your notebook. Next up, you will explore how this data is structured and trace a patient's journey from admission to diagnosis.

Explore How Hospital Data Is Structured

Your database is live and you confirmed 30+ tables sitting inside it. Now you need to understand how those tables fit together.

Hospitals do not store patient information in one giant spreadsheet. Instead, they split it across dozens of normalized tables: one for demographics, one for admissions, one for diagnoses, and so on. Understanding why the data is split this way (and how the pieces reconnect) is the foundation of every query you will write as a health data scientist.

In this step, get ready to:
  • Query the patients, admissions, and diagnoses_icd tables individually to see what each one holds.
  • JOIN the three tables together to trace a patient from demographics through admission to diagnosis.
  • Examine diagnosis sequencing to understand primary vs secondary diagnoses.
Query the patients, admissions, and diagnoses tables

Each table in the database answers a different question about a patient's hospital stay. You will query three core tables one at a time to see what kind of information each holds.

  • Create a new cell in your notebook (Cell 3) and query the patients table by adding this code:
# Cell 3: Explore patients table
patients = pd.read_sql("""
    SELECT subject_id, gender, anchor_age, anchor_year, anchor_year_group, dod
    FROM patients
    LIMIT 10;
""", conn)
print("=== PATIENTS TABLE (first 10) ===")
print(patients.to_string())

What does this code do?

  • subject_id is the unique identifier for each patient across the entire database.
  • anchor_age is the patient's age in their anchor_year. If a patient is over 89, it gets capped to 91 for privacy.
  • dod is the de-identified date of death (null if the patient is alive).
  • Run the cell. You should see a table with 10 rows showing patient demographics including gender, age, and year group.
  • Create a new cell (Cell 4) and query the admissions table by adding this code:
# Cell 4: Explore admissions table
admissions = pd.read_sql("""
    SELECT subject_id, hadm_id, admittime, dischtime, 
           admission_type, hospital_expire_flag
    FROM admissions
    LIMIT 10;
""", conn)
print("\n=== ADMISSIONS TABLE (first 10) ===")
print(admissions.to_string())

What does this code do?

  • hadm_id is the unique identifier for each hospital admission. One patient can have multiple admissions.
  • admittime and dischtime track when the patient entered and left the hospital.
  • hospital_expire_flag indicates whether the patient died during this admission (1 = yes, 0 = no).
  • Run the cell. You should see 10 rows with admission timestamps, types (EMERGENCY, ELECTIVE, etc.), and the expire flag.
  • Create a new cell (Cell 5) and query the diagnoses for a single patient by adding this code:
# Cell 5: Explore diagnoses_icd table
diagnoses = pd.read_sql("""
    SELECT subject_id, hadm_id, seq_num, icd_code, icd_version
    FROM diagnoses_icd
    WHERE subject_id = (SELECT subject_id FROM patients LIMIT 1)
    ORDER BY hadm_id, seq_num;
""", conn)
print("\n=== DIAGNOSES for one patient (ordered by sequence) ===")
print(diagnoses.to_string())

What does this code do?

  • This filters to a single patient so you can see all their diagnoses across admissions without drowning in data.
  • seq_num is the priority order. Lower numbers mean higher priority diagnoses.
  • icd_code is the diagnosis code itself. icd_version tells you whether it is ICD-9 (value 9) or ICD-10 (value 10).
  • Run the cell. You should see rows ordered by hadm_id and seq_num, with cryptic codes like I25.10 or 427.31 that you will decode in the next step.

Seeing an empty DataFrame?

If your diagnoses DataFrame shows zero rows, the subquery might not have matched. Try replacing the WHERE clause with WHERE subject_id = (SELECT subject_id FROM diagnoses_icd LIMIT 1) to pick a patient who definitely has diagnoses.

help me debug why my diagnoses query returns empty results

Why so many tables instead of one spreadsheet?

This design is called database normalization. Storing demographics separately from admissions and diagnoses means the hospital never duplicates a patient's gender across hundreds of diagnosis rows.

It also means one patient can have many admissions, and each admission can have many diagnoses, without any row limit or column explosion. The tradeoff is that you must JOIN tables to see the full picture.

Trace the full patient journey with a JOIN

Each table answered one question in isolation. Now you will connect all three tables with a JOIN to trace the full path: who is this patient, when were they admitted, and what were they diagnosed with.

  • Before you write this query, predict what columns you expect to see when patient demographics, admission dates, and diagnosis codes are combined into a single result.
  • Create a new cell (Cell 6) and join all three tables by adding this code:
# Cell 6: Three-table JOIN - trace the full patient journey
patient_journey = pd.read_sql("""
    SELECT 
        p.subject_id,
        p.gender,
        p.anchor_age,
        a.hadm_id,
        a.admittime,
        a.admission_type,
        d.seq_num,
        d.icd_code,
        d.icd_version
    FROM patients p
    JOIN admissions a ON p.subject_id = a.subject_id
    JOIN diagnoses_icd d ON a.hadm_id = d.hadm_id
    WHERE d.seq_num <= 3
    ORDER BY p.subject_id, a.hadm_id, d.seq_num
    LIMIT 20;
""", conn)
print("\n=== PATIENT JOURNEY (top 3 diagnoses per admission) ===")
print(patient_journey.to_string())

What does this code do?

  • The first JOIN connects patients to admissions on subject_id. This links each patient's demographics to their hospital stays.
  • The second JOIN connects admissions to diagnoses on hadm_id. This attaches the diagnosis codes recorded during each stay.
  • WHERE d.seq_num <= 3 limits results to the top 3 priority diagnoses per admission, keeping the output readable.
  • Run the cell. You should see 20 rows combining patient demographics (gender, age) alongside admission details (date, type) and diagnosis codes with their sequence numbers.

Notice how one patient can appear on multiple rows. Each row is a unique combination of patient + admission + diagnosis. This is the relational model at work.

Getting a syntax error?

Check that your triple-quoted SQL string has no stray characters after the closing """. Also confirm that the table alias letters (p, a, d) match what you used in the FROM/JOIN clauses.

help me fix a SQL JOIN syntax error in my Jupyter notebook

Examine primary vs secondary diagnoses

You probably noticed the seq_num column in your results. This number carries real clinical meaning.

When seq_num = 1, the diagnosis is the primary reason for admission. Every other seq_num represents a secondary diagnosis: existing conditions, complications, or comorbidities discovered during the stay.

  • What do you think the distribution looks like? Do most admissions have just 1 or 2 diagnoses, or many more?
  • Create a new cell (Cell 7) and examine the diagnosis sequencing by adding this code:
# Cell 7: Primary vs secondary diagnoses
primary_vs_secondary = pd.read_sql("""
    SELECT 
        seq_num,
        COUNT(*) as count,
        CASE 
            WHEN seq_num = 1 THEN 'Primary (reason for admission)'
            ELSE 'Secondary (comorbidities/complications)'
        END as meaning
    FROM diagnoses_icd
    GROUP BY seq_num
    ORDER BY seq_num
    LIMIT 15;
""", conn)
print("\n=== DIAGNOSIS SEQUENCING ===")
print(primary_vs_secondary.to_string())

What does this code do?

  • The query groups all diagnoses by their seq_num and counts how many fall into each priority level.
  • The CASE statement adds a human-readable label: seq_num = 1 is the primary diagnosis, everything else is secondary.
  • This distinction matters for billing (insurers pay based on primary diagnosis) and for clinical research (using only primary diagnoses vs all diagnoses changes your risk scores).
  • Run the cell. You should see a table where seq_num = 1 is labeled as the primary reason for admission, and all higher sequence numbers are labeled as secondary.
  • Look at the counts column. Notice how seq_num = 1 has fewer rows than seq_num = 2 or seq_num = 3. Each admission has exactly one primary diagnosis but often many secondaries.

Why does primary vs secondary matter for risk scoring?

Later in this project, you will calculate comorbidity scores using these diagnosis codes. If you only count primary diagnoses, you miss most of a patient's disease burden. If you count all diagnoses, you get a fuller (but noisier) picture.

Health data scientists must decide which approach fits their research question. You will compare both approaches in Step 5.

Counts look unexpectedly low?

Remember this is a 100-patient demo dataset, not the full MIMIC-IV database with 300,000+ patients. Low absolute counts are normal. Focus on the relative pattern: many more secondary diagnoses than primary ones.

help me understand the diagnosis counts in my TinyEHR query

✔️ Awesome, I've got everything!

Great. Make sure you have run all five cells (Cells 3 through 7) and each produced output without errors.

ⓧ I'd like to double check the full code

Your notebook should now contain these cells in order. Scroll up and confirm each one is present:

  • Cell 1: Install and import (import tinyehr, import sqlite3, import pandas as pd, import matplotlib.pyplot as plt).
  • Cell 2: Build database and list tables (tinyehr.build_sqlite, sqlite3.connect, table listing query).
  • Cell 3: Explore patients table (SELECT from patients LIMIT 10).
  • Cell 4: Explore admissions table (SELECT from admissions LIMIT 10).
  • Cell 5: Explore diagnoses_icd table (SELECT from diagnoses_icd for one patient).
  • Cell 6: Three-table JOIN (patient_journey DataFrame).
  • Cell 7: Primary vs secondary diagnoses (primary_vs_secondary DataFrame).

You now understand how hospital data is split across normalized tables and how to reconnect the pieces with JOINs. Next up, you will decode those cryptic ICD codes into human-readable diagnosis names and explore the coding hierarchy that makes clinical algorithms possible.

Decode ICD Codes and Clinical Vocabularies

In the previous step, you traced a patient's journey across three normalized tables and learned how seq_num separates primary from secondary diagnoses.

But those diagnosis codes are still opaque strings like "4139" or "I21.0". Without the coding system that gives them structure, you cannot build any clinical algorithm on top of them. In this step, you will decode those codes into human-readable descriptions and learn how the ICD hierarchy organizes every diagnosis into chapters and blocks.

In this step, get ready to:
  • JOIN diagnosis codes to their dictionary table to get human-readable titles.
  • Explore the hierarchical structure of ICD-9 and ICD-10 codes.
  • Discover why real hospital data contains both ICD-9 and ICD-10 codes in the same dataset.
Decode ICD codes with the dictionary table

The d_icd_diagnoses table is a lookup dictionary that maps every ICD code to its full English title. By JOINing your diagnosis records to this dictionary, you turn cryptic codes into clinically meaningful descriptions.

  • Create a new cell in your notebook (Cell 8) and run this query to see the 10 most frequent diagnoses with their full titles:
# Cell 8: Decode ICD codes with the dictionary table
decoded_diagnoses = pd.read_sql("""
    SELECT 
        d.icd_code,
        d.icd_version,
        dict.long_title,
        COUNT(*) as frequency
    FROM diagnoses_icd d
    JOIN d_icd_diagnoses dict 
        ON d.icd_code = dict.icd_code 
        AND d.icd_version = dict.icd_version
    GROUP BY d.icd_code, d.icd_version, dict.long_title
    ORDER BY frequency DESC
    LIMIT 10;
""", conn)
print("\n=== TOP 10 MOST FREQUENT DIAGNOSES (with titles) ===")
print(decoded_diagnoses.to_string())

What does this query do?

  • The JOIN connects each row in diagnoses_icd to its matching row in d_icd_diagnoses using both icd_code and icd_version as the key. You need both columns because the same numeric code can mean different things in ICD-9 vs ICD-10.
  • The long_title column gives you the full English description (e.g., "4139" becomes "Angina pectoris, unspecified").
  • GROUP BY and COUNT(*) reveal which diagnoses appear most often across all admissions.
  • Run the cell. You should see a table of 10 rows, each showing an ICD code, its version number (9 or 10), a human-readable title, and how many times it appears in the dataset.

Seeing an empty result or an error?

  • If you get a "no such table" error, check that you spelled d_icd_diagnoses exactly (note the underscores and lowercase).
  • Make sure your conn variable is still active from Cell 2. If you restarted the kernel, re-run Cells 1 and 2 first.

Help me debug my ICD dictionary JOIN query

Explore how ICD codes are hierarchically structured

ICD codes are not random identifiers. The first characters of each code tell you which body system or disease category it belongs to. In ICD-10, all codes starting with "I" are circulatory system conditions. In ICD-9, codes "250.xx" are all forms of diabetes. This hierarchy is what makes automated clinical algorithms possible.

  • Create Cell 9 and run this query to see how code prefixes map to clinical chapters:
# Cell 9: Explore ICD code hierarchy
icd_hierarchy = pd.read_sql("""
    SELECT 
        d.icd_code,
        d.icd_version,
        dict.long_title,
        CASE 
            WHEN d.icd_version = 10 THEN SUBSTR(d.icd_code, 1, 1)
            ELSE SUBSTR(d.icd_code, 1, 3)
        END as code_prefix,
        CASE 
            WHEN d.icd_version = 10 AND SUBSTR(d.icd_code, 1, 1) = 'I' THEN 'Circulatory System'
            WHEN d.icd_version = 10 AND SUBSTR(d.icd_code, 1, 1) = 'E' THEN 'Endocrine/Metabolic'
            WHEN d.icd_version = 10 AND SUBSTR(d.icd_code, 1, 1) = 'J' THEN 'Respiratory System'
            WHEN d.icd_version = 10 AND SUBSTR(d.icd_code, 1, 1) = 'C' THEN 'Neoplasms'
            WHEN d.icd_version = 9 AND SUBSTR(REPLACE(d.icd_code, '.', ''), 1, 3) BETWEEN '390' AND '459' THEN 'Circulatory System'
            WHEN d.icd_version = 9 AND SUBSTR(REPLACE(d.icd_code, '.', ''), 1, 3) BETWEEN '240' AND '279' THEN 'Endocrine/Metabolic'
            ELSE 'Other'
        END as chapter
    FROM diagnoses_icd d
    JOIN d_icd_diagnoses dict 
        ON d.icd_code = dict.icd_code 
        AND d.icd_version = dict.icd_version
    ORDER BY d.icd_version, d.icd_code
    LIMIT 20;
""", conn)
print("\n=== ICD CODE HIERARCHY EXAMPLES ===")
print(icd_hierarchy.to_string())

What does this query do?

  • The first CASE expression extracts the code_prefix. For ICD-10, a single letter defines the chapter. For ICD-9, the first three digits define the block.
  • The second CASE expression maps those prefixes to human-readable chapter names like "Circulatory System" or "Neoplasms".
  • Notice the REPLACE(d.icd_code, '.', '') for ICD-9 codes. TinyEHR stores codes with decimal points (like "413.9"), so you strip the dot before extracting the prefix. This foreshadows a data cleaning challenge you will solve in the next step.
  • Run the cell. You should see 20 rows with each code's prefix and chapter assignment. Look for ICD-10 codes starting with "I" mapped to Circulatory System and ICD-9 numeric codes mapped by their three-digit prefix.

All rows showing "Other" for the chapter?

  • This is expected for some rows. The query only maps a few chapters explicitly. If ALL rows show "Other", check that the CASE WHEN conditions use single quotes around the letters and that SUBSTR is spelled correctly.
  • Make sure the ORDER BY d.icd_version, d.icd_code clause is present. Without it, you might only see codes that fall into the "Other" bucket by chance.

Help me understand the ICD hierarchy query output

Discover the ICD-9 to ICD-10 mix in real data

The United States transitioned from ICD-9 to ICD-10 in October 2015. Hospital records from before that date use the older numeric system. Records from after use the newer alphanumeric system. Because this dataset spans admissions from both eras, you will encounter both coding systems side by side.

Before you run the next query, think about this: do you expect to see more ICD-9 codes or more ICD-10 codes in a dataset of 100 patients from Beth Israel Deaconess Medical Center?

  • Create Cell 10 and run this query to see the version split:
# Cell 10: ICD-9 vs ICD-10 distribution
version_dist = pd.read_sql("""
    SELECT 
        icd_version,
        COUNT(*) as code_count,
        COUNT(DISTINCT subject_id) as patient_count
    FROM diagnoses_icd
    GROUP BY icd_version;
""", conn)
print("\n=== ICD VERSION DISTRIBUTION ===")
print(version_dist.to_string())

What does this query do?

  • It groups all diagnosis records by icd_version (which is either 9 or 10) and counts how many codes and how many distinct patients exist in each group.
  • The result tells you how much of the data uses the old coding system vs the new one. Any clinical algorithm you build (like the Charlson score in the next step) must handle BOTH systems to work on real-world hospital data.
  • Run the cell. You should see two rows: one for icd_version 9 and one for icd_version 10, each with a code count and patient count.

How to spot ICD-9 vs ICD-10 at a glance

ICD-9 codes are purely numeric (like "4139" or "250.00"). ICD-10 codes always start with a letter (like "I21.0" or "E11.9"). If you see a letter prefix, it is ICD-10.

Only seeing one row in the output?

  • Make sure you are querying diagnoses_icd (not d_icd_diagnoses). The dictionary table has all possible codes. The diagnoses table has only the codes actually assigned to patients.
  • If you see only version 9 or only version 10, your database built correctly but the 100-patient sample may lean heavily toward one era. The important thing is that the icd_version column exists and is queryable.

Help me understand the ICD version distribution results

✔️ Awesome, I've got everything!

Great. Make sure you have saved all three cells (8, 9, and 10) in your notebook.

ⓧ I'd like to double check the full code

Scroll up through your notebook and confirm the following cells are present in order:

  • Cell 1: Install and import (tinyehr, sqlite3, pandas, matplotlib).
  • Cell 2: Build the database and connect.
  • Cell 3: Explore patients table.
  • Cell 4: Explore admissions table.
  • Cell 5: Explore diagnoses_icd table.
  • Cell 6: Three-table JOIN (patient journey).
  • Cell 7: Primary vs secondary diagnoses.
  • Cell 8: Decode ICD codes with the dictionary table.
  • Cell 9: Explore ICD code hierarchy.
  • Cell 10: ICD-9 vs ICD-10 distribution.

You can now decode any ICD code in the dataset into a human-readable description and place it in the clinical hierarchy. Next up, you will use this knowledge to build something clinicians actually rely on: the Charlson Comorbidity Index, a single score that summarizes how sick a patient is overall.

Build the Charlson Comorbidity Scoring Pipeline

You've explored how hospital data is structured and decoded the ICD coding system. You can now read individual diagnosis codes and understand what they mean.

Individual codes tell you one fact at a time, but clinicians and researchers need a single number that answers: "how sick is this patient overall?" The Charlson Comorbidity Index (CCI) solves this by mapping ICD codes to 17 disease categories, each with a clinical weight that reflects mortality risk.

In this step, get ready to:
  • Attempt the standard Charlson mapping approach and discover a data cleaning issue.
  • Fix the issue and build the complete 17-category mapping for both ICD-9 and ICD-10.
  • Calculate weighted Charlson scores per patient and produce a scored cohort.
Attempt the standard ICD-to-Charlson mapping

The standard approach uses SUBSTR and CASE WHEN to check the first few characters of each ICD code against known Charlson category codes.

  • Test this approach by running a quick mapping query on your ICD-10 circulatory codes. Paste this into a new cell:
# Test: map ICD codes to Charlson categories using SUBSTR
test_mapping = pd.read_sql("""
    SELECT 
        icd_code,
        icd_version,
        SUBSTR(icd_code, 1, 3) as first_3,
        SUBSTR(icd_code, 1, 4) as first_4,
        CASE WHEN
            SUBSTR(icd_code, 1, 3) IN ('I21', 'I22')
            OR SUBSTR(icd_code, 1, 4) = 'I252'
        THEN 'Myocardial Infarction'
        ELSE 'No match'
        END as charlson_category
    FROM diagnoses_icd
    WHERE icd_version = 10 AND icd_code LIKE 'I2%'
    LIMIT 15;
""", conn)
print(test_mapping.to_string())

Before you run this cell, what do you expect to see for a code like I25.2 in the first_4 column? Will it match I252?

  • Run the cell now.

Look at the first_4 column. For a code stored as I25.2, SUBSTR(icd_code, 1, 4) returns I25. instead of I252. The decimal point takes up a character position, so 4-character matches silently fail.

Why does this happen?

TinyEHR stores ICD codes with decimal points inserted (e.g., I25.2 instead of I252) to match how codes appear in clinical practice. Standard Charlson SQL patterns expect codes WITHOUT decimals.

This is a real data cleaning issue you would encounter working with any EHR system. The fix is straightforward: strip the decimals before matching.

Fix the decimal issue and build the complete mapping

The solution is REPLACE(icd_code, '.', '') which strips all decimal points before matching. You will now build Cell 11 using this fix to map all 17 Charlson categories using the Quan et al. (2005) algorithm.

  • Create a new cell in your notebook. You will build the full scoring pipeline in this single cell, pasting each section directly below the previous one.
  • Start Cell 11 by pasting the opening and the clean_diag CTE that strips decimals:
# Cell 11: Charlson Comorbidity Index - complete scoring pipeline
charlson_scores = pd.read_sql("""
WITH clean_diag AS (
    SELECT 
        d.subject_id,
        d.hadm_id,
        d.seq_num,
        REPLACE(d.icd_code, '.', '') AS icd_code,
        d.icd_version
    FROM diagnoses_icd d
),

comorbidities AS (
    SELECT
        subject_id,
        hadm_id,

What does the clean_diag CTE do?

  • REPLACE(d.icd_code, '.', '') AS icd_code strips every decimal point so codes like I25.2 become I252.
  • Every downstream SUBSTR match now works against clean codes without decimals.
  • Directly below in the same cell, paste the first group of comorbidity mappings (weight 1 conditions, cardiovascular):

        -- Myocardial Infarction (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('410', '412'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('I21', 'I22'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) = 'I252')
        THEN 1 ELSE 0 END) AS myocardial_infarct,

        -- Congestive Heart Failure (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) = '428')
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 5) IN ('39891','40201','40211','40291','40401','40403','40411','40413','40491','40493'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('I43', 'I50'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('I099','I110','I130','I132','I255','I420','I425','I426','I427','I428','I429','P290'))
        THEN 1 ELSE 0 END) AS congestive_heart_failure,

        -- Peripheral Vascular Disease (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('440', '441'))
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('0930','4373','4471','5571','5579','V434'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('I70', 'I71'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('I731','I738','I739','I771','I790','I792','K551','K558','K559','Z958','Z959'))
        THEN 1 ELSE 0 END) AS peripheral_vascular_disease,

        -- Cerebrovascular Disease (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) BETWEEN '430' AND '438')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('G45', 'G46'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'I60' AND 'I69')
        THEN 1 ELSE 0 END) AS cerebrovascular_disease,

What does this pattern do?

Each category uses the same structure: MAX(CASE WHEN ... THEN 1 ELSE 0 END). If ANY diagnosis code for a patient-admission matches the category's code list, the flag becomes 1. Otherwise it stays 0.

The mappings cover both ICD-9 and ICD-10 codes because the dataset spans the 2015 US transition between systems.

  • Continue in the same cell with the neurological and pulmonary conditions:

        -- Dementia (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) = '290')
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('2941', '3312'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('F00', 'F01', 'F02', 'F03', 'G30'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('F051', 'G311'))
        THEN 1 ELSE 0 END) AS dementia,

        -- Chronic Pulmonary Disease (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) BETWEEN '490' AND '505')
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('4168', '4169', '5064', '5081', '5088'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'J40' AND 'J47')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'J60' AND 'J67')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('I278', 'I279', 'J684', 'J701', 'J703'))
        THEN 1 ELSE 0 END) AS chronic_pulmonary_disease,

        -- Rheumatic Disease (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('4465', '7100', '7101', '7102', '7103', '7104', '7140', '7141', '7142', '7148'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('M05', 'M06', 'M32', 'M33', 'M34'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('M315', 'M351', 'M353', 'M360'))
        THEN 1 ELSE 0 END) AS rheumatic_disease,

        -- Peptic Ulcer Disease (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('531', '532', '533', '534'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('K25', 'K26', 'K27', 'K28'))
        THEN 1 ELSE 0 END) AS peptic_ulcer_disease,
  • Continue with the metabolic and organ-specific conditions:

        -- Mild Liver Disease (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('570', '571'))
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('0706', '0709', '5733', '5734', '5738', '5739', 'V427'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('B18', 'K70', 'K73', 'K74'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('K700', 'K701', 'K702', 'K703', 'K709', 'K713', 'K714', 'K715', 'K717', 'K760', 'K762', 'K763', 'K764', 'K768', 'K769', 'Z944'))
        THEN 1 ELSE 0 END) AS mild_liver_disease,

        -- Diabetes Without Complications (weight 1)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('2500', '2501', '2502', '2503', '2508', '2509'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('E100', 'E101', 'E106', 'E108', 'E109', 'E110', 'E111', 'E116', 'E118', 'E119', 'E120', 'E121', 'E126', 'E128', 'E129', 'E130', 'E131', 'E136', 'E138', 'E139', 'E140', 'E141', 'E146', 'E148', 'E149'))
        THEN 1 ELSE 0 END) AS diabetes_without_cc,

        -- Diabetes With Complications (weight 2)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('2504', '2505', '2506', '2507'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('E102', 'E103', 'E104', 'E105', 'E107', 'E112', 'E113', 'E114', 'E115', 'E117', 'E122', 'E123', 'E124', 'E125', 'E127', 'E132', 'E133', 'E134', 'E135', 'E137', 'E142', 'E143', 'E144', 'E145', 'E147'))
        THEN 1 ELSE 0 END) AS diabetes_with_cc,

        -- Paraplegia (weight 2)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('342', '343'))
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('3341', '3440', '3441', '3442', '3443', '3444', '3445', '3446', '3449'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('G81', 'G82'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('G041', 'G114', 'G801', 'G802', 'G830', 'G831', 'G832', 'G833', 'G834', 'G839'))
        THEN 1 ELSE 0 END) AS paraplegia,
  • Continue with the remaining high-weight conditions (renal, cancer, severe liver, and AIDS):

        -- Renal Disease (weight 2)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('582', '585', '586', 'V56'))
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('5830', '5831', '5832', '5834', '5836', '5837', 'V420', 'V451'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('N18', 'N19'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('I120', 'I131', 'N032', 'N033', 'N034', 'N035', 'N036', 'N037', 'N052', 'N053', 'N054', 'N055', 'N056', 'N057', 'N250', 'Z490', 'Z491', 'Z492', 'Z940', 'Z992'))
        THEN 1 ELSE 0 END) AS renal_disease,

        -- Malignant Cancer (weight 2)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) BETWEEN '140' AND '172')
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 3) BETWEEN '174' AND '195')
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('200', '201', '202', '203', '204', '205', '206', '207', '208', 'V10'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C00' AND 'C26')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C30' AND 'C34')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C37' AND 'C41')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C43' AND 'C58')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C60' AND 'C76')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C81' AND 'C85')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C88' AND 'C97')
        THEN 1 ELSE 0 END) AS malignant_cancer,

        -- Severe Liver Disease (weight 3)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('4560', '4561', '4562'))
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('5722', '5723', '5724', '5728'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('I850', 'I859', 'I864', 'I982', 'K704', 'K711', 'K721', 'K729', 'K765', 'K766', 'K767'))
        THEN 1 ELSE 0 END) AS severe_liver_disease,
  • Finish the comorbidities CTE with the two highest-weight conditions:

        -- Metastatic Solid Tumor (weight 6)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('196', '197', '198', '199'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('C77', 'C78', 'C79', 'C80'))
        THEN 1 ELSE 0 END) AS metastatic_solid_tumor,

        -- AIDS/HIV (weight 6)
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('042', '043', '044'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('B20', 'B21', 'B22', 'B24'))
        THEN 1 ELSE 0 END) AS aids

    FROM clean_diag
    GROUP BY subject_id, hadm_id
)

Why do the weights vary?

Charlson's original 1987 study assigned weights based on 1-year mortality risk. Conditions like MI and diabetes (weight 1) have moderate risk. Renal disease and cancer (weight 2-3) carry higher risk. Metastatic cancer and AIDS (weight 6) have the highest mortality impact.

Calculate weighted scores per patient

Now you need the final SELECT statement that combines the 17 comorbidity flags into a single weighted score per patient-admission.

  • Directly below in the same cell, paste the final SELECT with the scoring formula:

SELECT
    subject_id,
    hadm_id,
    myocardial_infarct,
    congestive_heart_failure,
    peripheral_vascular_disease,
    cerebrovascular_disease,
    dementia,
    chronic_pulmonary_disease,
    rheumatic_disease,
    peptic_ulcer_disease,
    mild_liver_disease,
    diabetes_without_cc,
    diabetes_with_cc,
    paraplegia,
    renal_disease,
    malignant_cancer,
    severe_liver_disease,
    metastatic_solid_tumor,
    aids,
    -- Charlson score with original 1987 weights
    myocardial_infarct
    + congestive_heart_failure
    + peripheral_vascular_disease
    + cerebrovascular_disease
    + dementia
    + chronic_pulmonary_disease
    + rheumatic_disease
    + peptic_ulcer_disease
  • Continue with the mutual-exclusivity logic and the closing Python code:
    + MAX(mild_liver_disease, 3 * severe_liver_disease)
    + MAX(2 * diabetes_with_cc, diabetes_without_cc)
    + MAX(2 * malignant_cancer, 6 * metastatic_solid_tumor)
    + 2 * paraplegia
    + 2 * renal_disease
    + 6 * aids
    AS charlson_comorbidity_index
FROM comorbidities
ORDER BY charlson_comorbidity_index DESC;
""", conn)

print("\n=== CHARLSON COMORBIDITY INDEX SCORES ===")
print(f"Patients scored: {len(charlson_scores)}")
print(f"Score range: {charlson_scores['charlson_comorbidity_index'].min()} - {charlson_scores['charlson_comorbidity_index'].max()}")
print(f"Mean score: {charlson_scores['charlson_comorbidity_index'].mean():.1f}")
print(f"Median score: {charlson_scores['charlson_comorbidity_index'].median():.1f}")
print("\nTop 10 highest-scoring admissions:")
print(charlson_scores.head(10).to_string())

Why use MAX() for some conditions?

Some Charlson categories are mutually exclusive. A patient with both mild liver disease (weight 1) and severe liver disease (weight 3) should only count the higher-scoring one. MAX(mild_liver_disease, 3 * severe_liver_disease) picks whichever contributes more to the score.

The same logic applies to diabetes (uncomplicated vs complicated) and cancer (non-metastatic vs metastatic). SQLite's MAX(a, b) returns the larger value, equivalent to GREATEST(a, b) in PostgreSQL.

Before you run Cell 11, what do you expect for a patient with metastatic cancer and no other conditions? What score should they get?

  • Run Cell 11 now.

You should see a table with subject_id, hadm_id, 17 individual comorbidity flag columns (0/1 each), and a charlson_comorbidity_index column. Patients with severe conditions like metastatic cancer or AIDS will appear at the top with the highest scores.

Seeing a syntax error?

  • Make sure all sections are pasted into ONE cell with no gaps or repeated lines between them.
  • Check that the comorbidities CTE closes with a single closing parenthesis before the final SELECT.
  • Verify the query ends with """, conn) on its own line after the semicolon.

Help me debug my Charlson scoring SQL query

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook.

ⓧ I'd like to double check the full code

Cell 11 is too long to display inline. Scroll up and confirm your cell contains all of the following sections in order:

  • charlson_scores = pd.read_sql(""" opening line.
  • clean_diag CTE with REPLACE(d.icd_code, '.', '').
  • comorbidities CTE with all 17 MAX(CASE WHEN ...) blocks (MI through AIDS).
  • Final SELECT listing all 17 flags plus the weighted charlson_comorbidity_index calculation with MAX() for mutual exclusivity.
  • """, conn) closing line followed by 6 print() statements showing scores and top 10.

You now have a working clinical risk scoring pipeline. Raw ICD codes go in, and a single weighted number summarizing patient illness severity comes out. Next, you will analyze these scores and compare how primary vs secondary diagnoses affect risk assessment.

Analyze Your Comorbidity Scores

In the previous step, you built a complete Charlson scoring pipeline and saw raw numbers for each patient. Numbers alone do not tell a health data scientist much.

Interpreting and validating those numbers is what separates a working query from a credible clinical analysis. In this step, you will visualize the score distribution, identify which conditions dominate, and test how diagnosis sequencing changes patient risk scores.

In this step, get ready to:
  • Visualize score distributions with a histogram.
  • Identify the most common comorbidity categories in the cohort.
  • Compare primary-only vs all-diagnoses scoring to measure how diagnosis sequencing affects risk assessment.
Visualize score distribution with a histogram

A histogram reveals how scores cluster across your cohort. Are most patients low-risk with a few outliers, or is the burden spread evenly?

  • Create a new cell in your notebook (Cell 12) and plot the score distribution by pasting this code:
# Cell 12: Visualize score distribution
fig, ax = plt.subplots(figsize=(10, 6))
charlson_scores['charlson_comorbidity_index'].hist(bins=range(0, charlson_scores['charlson_comorbidity_index'].max() + 2), 
                                                     edgecolor='black', ax=ax)
ax.set_xlabel('Charlson Comorbidity Index')
ax.set_ylabel('Number of Admissions')
ax.set_title('Distribution of Charlson Comorbidity Index Scores\n(100 patients, TinyEHR)')
plt.tight_layout()
plt.savefig('charlson_distribution.png', dpi=100)
plt.show()

What does this code do?

The bins=range(0, max + 2) creates one bar per integer score value so you can see exactly how many admissions fall at each level.

The chart is saved to charlson_distribution.png so you have a reusable figure for reports or presentations.

  • Run the cell.
  • You should see a histogram displayed inline showing the distribution of Charlson scores, with most admissions clustered at lower scores and a tail stretching toward higher values.

Not seeing the histogram?

Make sure you ran Cell 11 first so that charlson_scores exists in memory. If you restarted the kernel, re-run all previous cells from Cell 1.

Help me debug my matplotlib histogram.

Identify the most common comorbidity categories

The histogram shows you the overall score distribution, but which specific conditions are driving those scores? Knowing the most prevalent comorbidities tells you what diseases dominate in your patient population.

  • Create Cell 13 and calculate comorbidity prevalence by pasting this code:
# Cell 13: Most common comorbidity categories
comorbidity_cols = ['myocardial_infarct', 'congestive_heart_failure', 
                    'peripheral_vascular_disease', 'cerebrovascular_disease',
                    'dementia', 'chronic_pulmonary_disease', 'rheumatic_disease',
                    'peptic_ulcer_disease', 'mild_liver_disease', 
                    'diabetes_without_cc', 'diabetes_with_cc', 'paraplegia',
                    'renal_disease', 'malignant_cancer', 'severe_liver_disease',
                    'metastatic_solid_tumor', 'aids']

prevalence = charlson_scores[comorbidity_cols].sum().sort_values(ascending=False)
print("\n=== COMORBIDITY PREVALENCE (admissions with condition) ===")
for condition, count in prevalence.items():
    if count > 0:
        print(f"  {condition}: {int(count)} ({count/len(charlson_scores)*100:.1f}%)")

What does this code do?

The list comorbidity_cols names all 17 Charlson categories. Calling .sum() on those columns counts how many admissions flagged each condition (since each flag is 0 or 1).

The results are sorted descending so the most common conditions appear first. The loop prints both the raw count and the percentage of total admissions.

  • Run the cell.
  • You should see a ranked list showing which comorbidity categories appear most frequently in the cohort, with each condition's count and percentage.

Why does prevalence matter?

In hospital analytics, knowing your population's disease profile shapes resource planning. If a large share of admissions involve congestive heart failure, that drives staffing, equipment, and readmission-prevention programs.

Compare primary-only vs all-diagnoses scoring

In Step 2, you explored the seq_num column and learned that seq_num=1 is the primary diagnosis (reason for admission) while higher numbers are secondary conditions. Now you will see how that distinction changes patient risk scores in practice.

If you score using only primary diagnoses, you capture the main reason the patient came in. Scoring with all diagnoses captures the full comorbidity burden. The difference reveals how much clinical context hides in secondary codes.

  • Create Cell 14 and start building the primary-only scoring query by pasting this first section of code:
# Cell 14: Primary-only vs All-diagnoses comparison
charlson_primary_only = pd.read_sql("""
WITH clean_diag AS (
    SELECT 
        d.subject_id,
        d.hadm_id,
        REPLACE(d.icd_code, '.', '') AS icd_code,
        d.icd_version
    FROM diagnoses_icd d
    WHERE d.seq_num = 1
),

comorbidities AS (
    SELECT
        subject_id,
        hadm_id,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('410', '412'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('I21', 'I22'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) = 'I252')
        THEN 1 ELSE 0 END) AS myocardial_infarct,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) = '428')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('I43', 'I50'))
        THEN 1 ELSE 0 END) AS congestive_heart_failure,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('440', '441'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('I70', 'I71'))
        THEN 1 ELSE 0 END) AS peripheral_vascular_disease,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) BETWEEN '430' AND '438')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'I60' AND 'I69')
        THEN 1 ELSE 0 END) AS cerebrovascular_disease,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) = '290')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('F00','F01','F02','F03','G30'))
        THEN 1 ELSE 0 END) AS dementia,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) BETWEEN '490' AND '505')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'J40' AND 'J47')
        THEN 1 ELSE 0 END) AS chronic_pulmonary_disease,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 4) IN ('2500','2501','2502','2503','2508','2509'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 4) IN ('E100','E101','E109','E110','E111','E119'))
        THEN 1 ELSE 0 END) AS diabetes_without_cc,
  • Directly below in the same cell, add the remaining comorbidity mappings and the final scoring SELECT:
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) BETWEEN '140' AND '172')
            OR (icd_version = 9 AND SUBSTR(icd_code, 1, 3) BETWEEN '174' AND '195')
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C00' AND 'C76')
        THEN 1 ELSE 0 END) AS malignant_cancer,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('196','197','198','199'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('C77','C78','C79','C80'))
        THEN 1 ELSE 0 END) AS metastatic_solid_tumor,
        MAX(CASE WHEN
            (icd_version = 9 AND SUBSTR(icd_code, 1, 3) IN ('582','585','586'))
            OR (icd_version = 10 AND SUBSTR(icd_code, 1, 3) IN ('N18','N19'))
        THEN 1 ELSE 0 END) AS renal_disease
    FROM clean_diag
    GROUP BY subject_id, hadm_id
)

SELECT
    subject_id,
    hadm_id,
    myocardial_infarct + congestive_heart_failure 
    + peripheral_vascular_disease + cerebrovascular_disease
    + dementia + chronic_pulmonary_disease + diabetes_without_cc
    + MAX(2 * malignant_cancer, 6 * metastatic_solid_tumor)
    + 2 * renal_disease
    AS charlson_primary_only
FROM comorbidities
ORDER BY subject_id, hadm_id;
""", conn)

How is this different from the full scoring query?

The key difference is WHERE d.seq_num = 1 in the clean_diag CTE. This filters to only the primary diagnosis for each admission, ignoring all secondary conditions.

The mapping is also simplified to fewer categories because primary-only data rarely triggers the rarer conditions. The structure follows the same CTE pattern you built in Step 4.

  • Still in the same Cell 14, add the merge and comparison logic directly below:
# Merge and compare
comparison = charlson_scores[['subject_id', 'hadm_id', 'charlson_comorbidity_index']].merge(
    charlson_primary_only, on=['subject_id', 'hadm_id'], how='left'
)
comparison['charlson_primary_only'] = comparison['charlson_primary_only'].fillna(0)
comparison['score_difference'] = comparison['charlson_comorbidity_index'] - comparison['charlson_primary_only']

print("\n=== PRIMARY-ONLY vs ALL-DIAGNOSES SCORING ===")
print(f"Mean score (all diagnoses): {comparison['charlson_comorbidity_index'].mean():.2f}")
print(f"Mean score (primary only):  {comparison['charlson_primary_only'].mean():.2f}")
print(f"Mean difference:            {comparison['score_difference'].mean():.2f}")
print(f"\nAdmissions where score changes: {(comparison['score_difference'] != 0).sum()} / {len(comparison)}")
print("\nLargest differences:")
print(comparison.nlargest(5, 'score_difference')[['subject_id', 'hadm_id', 'charlson_comorbidity_index', 'charlson_primary_only', 'score_difference']].to_string())

What does the merge do?

The merge joins your full-scoring results (charlson_scores) with the primary-only results on subject_id and hadm_id. The score_difference column shows exactly how many points each patient gains from secondary diagnoses.

Before you run this cell, think about what you expect. Do you think primary-only scoring will be much lower than all-diagnoses scoring, or about the same?

  • Run the cell.

You should see the mean scores for both approaches, the mean difference between them, the number of admissions where the score changes, and a table of the 5 largest differences. The all-diagnoses scores will be consistently higher because secondary codes capture conditions the patient has but was not admitted for.

Why does this matter for clinical research?

Risk adjustment studies must decide whether to use primary diagnoses only (conservative, less noise) or all diagnoses (comprehensive, captures full burden). The score difference you see here is exactly the kind of sensitivity analysis that hospital analytics teams run before choosing their scoring approach.

Seeing all zeros in the primary-only scores?

Check that the SQL query includes WHERE d.seq_num = 1 (not seq_num = 0). In MIMIC-IV, primary diagnoses start at sequence number 1, not 0.

Help me debug my primary-only comparison query.

Close the database connection
  • Create Cell 15 and close the database connection by running this code:
# Cell 15: Close connection
conn.close()
print("\n=== ANALYSIS COMPLETE ===")
print("Database connection closed.")
  • You should see ANALYSIS COMPLETE and Database connection closed. printed in the output.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your notebook file.

ⓧ I'd like to double check the full code

Your notebook is too long to show inline. Scroll through your cells and confirm you have all 15 cells in order:

  • Cells 1-11 from previous steps (install, connect, explore, decode, scoring pipeline).
  • Cell 12: Histogram visualization with plt.savefig('charlson_distribution.png', dpi=100).
  • Cell 13: Comorbidity prevalence with the comorbidity_cols list and prevalence loop.
  • Cell 14: Primary-only vs all-diagnoses comparison with charlson_primary_only query, merge, and score_difference output.
  • Cell 15: conn.close() with the analysis complete message.

You now have a complete, end-to-end clinical risk scoring analysis. You started with raw hospital records and ended with validated comorbidity scores, distribution insights, and a direct measurement of how diagnosis sequencing changes risk assessment. This is the exact workflow used in hospital analytics departments and health data science roles.

Secret mission

Age-Adjusted Charlson Scoring

The original 1987 Charlson paper included age as a scoring component, because older patients have higher baseline mortality risk independent of their comorbidities. Can you extend your pipeline to produce the full age-adjusted Charlson Comorbidity Index?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources available, pause them to come back later, or delete them entirely.

Resources you used:

  • Jupyter notebook file containing your analysis cells.
  • Local SQLite database file created by TinyEHR.
  • The charlson_distribution.png histogram image.
  • The tinyehr Python package installed via pip.

Keep everything running

No action needed. Choose this if you plan to continue exploring the EHR data or extending your scoring pipeline.

  • Your Jupyter notebook, SQLite database, and histogram image remain on disk.
  • The tinyehr package stays installed in your Python environment.
  • You can reopen the notebook and re-run cells at any time.

Pause - I'll come back to this later

Shut down your Jupyter server to free up memory. Your files stay on disk so you can pick up where you left off.

  • In your terminal running Jupyter, press Ctrl+C to stop the server.
  • Your notebook, database, and image files remain saved on disk.
  • To resume later, start Jupyter again and reopen your notebook.

Delete - I don't want to use this again

Remove all project files and uninstall the TinyEHR package to start fresh.

  • Stop your Jupyter server by pressing Ctrl+C in the terminal.
  • Delete your Jupyter notebook file (the .ipynb file you created for this project).
  • Delete the charlson_distribution.png file from the same directory.

Remove the SQLite database and TinyEHR package by running these commands in your terminal:

pip uninstall tinyehr -y

The SQLite database file was created at the path printed in Cell 2 (the db_path variable). Delete that file as well:

rm -f ~/.tinyehr/*.db

Not sure where the database is?

Check the output from Cell 2 of your notebook. The exact path was printed after Database created at: when you first ran the project.

Well Done!

Well Done!

Nice work. You just built a complete clinical risk scoring pipeline from raw hospital data to patient-level comorbidity scores, using the same methodology hospital analytics teams rely on every day.

You've learned how to:

  • Build a queryable SQLite database from real de-identified hospital records and explore the normalized EHR architecture that splits patient data across dozens of linked tables.
  • Implement the Charlson Comorbidity Index scoring pipeline, mapping both ICD-9 and ICD-10 codes to 17 disease categories with clinically weighted risk scores.
  • Analyze how primary vs secondary diagnosis sequencing changes patient risk scores, demonstrating a critical consideration for any clinical research or billing algorithm.
  • Secret Mission: Added age-adjusted scoring using the original 1987 Charlson age brackets to produce the full clinical tool.

Ready to quiz yourself?