Analyze ICU Mortality with MIMIC-III

Load real ICU patient data into PostgreSQL and analyze mortality patterns.

Introduction

30 Second Summary

Every day, doctors in intensive care units make life-or-death decisions based on patient data. Behind those decisions are data analysts who surface patterns from thousands of records to improve outcomes.

In this project, you will load real de-identified ICU patient data into a local PostgreSQL database. You will define a clinical cohort using standard research methodology and produce publication-quality visualizations of ICU mortality patterns.

What You'll Build

A clinical data analysis pipeline that connects Python to PostgreSQL, explores the MIMIC-III Demo dataset of 100 ICU patients, and generates mortality and length-of-stay visualizations.

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

  • A local PostgreSQL database loaded with the MIMIC-III Demo dataset (26 tables, 100 ICU patients).
  • A Python analysis script that defines an adult ICU cohort and computes mortality rates by age group.
  • Publication-quality matplotlib and seaborn visualizations showing ICU mortality and length-of-stay patterns suitable for a clinical data portfolio.
  • Secret Mission: Analyze diagnosis patterns using ICD-9 codes and visualize the most common comorbidities in the ICU population.

Are there any prerequisites?

This project assumes you have experience with Python data analysis (pandas), basic SQL knowledge, and familiarity with terminal commands. You do not need prior healthcare domain experience.

Set Up PostgreSQL and Python Environment

Clinical data analysis requires a proper database to store and query patient records. PostgreSQL is the relational database that MIMIC-III was specifically designed for, making it the natural choice for this project.

In this step, you will install PostgreSQL 16 on your Mac, create a dedicated database for the clinical data, and set up a Python environment with the analysis packages you need.

In this step, get ready to:
  • Install PostgreSQL 16 via Homebrew and start it as a background service.
  • Create the mimic database and mimiciii schema.
  • Set up a PyCharm project with a virtual environment and install analysis packages.
Install and start PostgreSQL 16

PostgreSQL 16 is the database engine that will store all 26 MIMIC-III tables locally on your machine. You will install it via Homebrew, add it to your terminal PATH, and start it as a background service.

  • Press Cmd+Space to open Spotlight.
  • Type Terminal and press Enter to open it.
  • Check whether PostgreSQL is already on your system by running:
psql --version

✔️ I see version 16.x

PostgreSQL 16 is already installed on your machine.

  • Make sure it is running as a background service by running:
brew services start postgresql@16

If it was already running, you will see a message saying the service is already started. Either way, you are good to go.

ⓧ I see an older version

You have an older version of PostgreSQL. Install version 16 alongside it using Homebrew.

  • Install PostgreSQL 16 by running:
brew install postgresql@16
  • Add the PostgreSQL 16 binaries to your PATH so your terminal finds them first:
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
  • Reload your shell configuration by running:
source ~/.zshrc
  • Start PostgreSQL 16 as a background service by running:
brew services start postgresql@16

ⓧ Command not found

PostgreSQL is not installed yet. Install it now via Homebrew.

  • Install PostgreSQL 16 by running:
brew install postgresql@16
  • Add the PostgreSQL 16 binaries to your PATH so your terminal can find psql:
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
  • Reload your shell configuration by running:
source ~/.zshrc
  • Start PostgreSQL 16 as a background service by running:
brew services start postgresql@16
  • Verify the service is running by running:
brew services list

You should see postgresql@16 with a started status in the output.

  • Confirm the version by running:
psql --version

You should see a version number starting with 16.

Service not starting?

If brew services list shows an error status, try stopping and restarting the service:

brew services stop postgresql@16
brew services start postgresql@16

If psql --version still shows an older version after updating PATH, close your terminal window completely and open a new one. The source ~/.zshrc command only affects the current session.

Still stuck? Help me troubleshoot PostgreSQL 16 not starting on my Mac.

Create the mimic database and mimiciii schema

MIMIC-III expects a database named mimic with a schema called mimiciii inside it.

What is a schema in PostgreSQL?

A schema is like a folder within a database that groups related tables together. By placing all 26 MIMIC-III tables under the mimiciii schema, you keep them organized and separate from any other tables you might create later.

  • Create the mimic database by running:
createdb mimic
  • Create the mimiciii schema inside the new database by running:
psql -d mimic -c "CREATE SCHEMA IF NOT EXISTS mimiciii;"
  • Verify that the database is accessible by running:
psql -d mimic -c "SELECT current_database();"

You should see mimic returned in the output. This confirms your database and schema are ready for the clinical data.

Seeing a connection refused error?

Make sure the PostgreSQL service is still running with brew services list. If it shows stopped, restart it with brew services start postgresql@16.

If you see a "database already exists" error when running createdb, that is fine. It means the database was already created. Continue to the next command.

Need more help? Help me fix my PostgreSQL connection issue.

Create a Python virtual environment and install packages

A virtual environment keeps this project's Python packages isolated from your system Python. You will create a new project in PyCharm with its own virtual environment, then install the four packages needed for analysis.

  • Press Cmd+Space to open Spotlight.
  • Type PyCharm and press Enter to open it.
  • On the Welcome screen, click New Project.
  • In the Location field, set the path to ~/Desktop/mimic-analysis.
  • Under the interpreter settings, make sure Project venv is selected. This creates a fresh virtual environment using your Python 3.12 installation.
  • Click Create.

PyCharm opens your new project with a virtual environment already activated.

  • Open the built-in terminal by clicking Terminal at the bottom of the PyCharm window.
  • Install the four required packages by running:
pip install psycopg2-binary==2.9.12 pandas==2.3.3 matplotlib==3.11.0 seaborn==0.13.2

What are these packages for?

  • psycopg2-binary connects Python to your PostgreSQL database.
  • pandas loads SQL query results into DataFrames for analysis.
  • matplotlib and seaborn produce publication-quality charts from your clinical data.
  • Verify all packages imported correctly by running:
python -c "import psycopg2; import pandas; import matplotlib; import seaborn; print('All packages imported successfully')"

You should see All packages imported successfully printed in the terminal.

Pip install failing?

Make sure you are running the command inside PyCharm's terminal, not your system terminal. The PyCharm terminal automatically activates the project's virtual environment.

Look for (venv) or (.venv) at the beginning of your terminal prompt. If you do not see it, the virtual environment is not active.

Still having trouble? Help me fix my pip install issue in PyCharm.

Your database is running and your Python environment is ready. Next, you will download real ICU patient data from PhysioNet and load it into PostgreSQL.

Download MIMIC-III Demo and Load into PostgreSQL

Your PostgreSQL database is running and the mimiciii schema is ready to receive data.

Clinical research starts with access to de-identified patient records. In this step, you will download the MIMIC-III Demo dataset from PhysioNet and bulk-load all 26 tables into your local database using a Python script.

In this step, get ready to:
  • Create a PhysioNet account and download the MIMIC-III Demo v1.4 dataset.
  • Write a Python loading script that bulk-loads CSV data into PostgreSQL.
  • Verify the data load by checking row counts on key clinical tables.
Create a PhysioNet account and download MIMIC-III Demo

The MIMIC-III Demo is a 100-patient subset of the full MIMIC-III Clinical Database, hosted on PhysioNet. It uses the same 26-table schema as the full dataset, so everything you learn here transfers directly.

  • Open your browser and navigate to the MIMIC-III Demo page:
  • https://physionet.org/content/mimiciii-demo/1.4/
  • If you do not already have a PhysioNet account, click the sign-up link and create one using your email address.
  • Once signed in, scroll down to the Files section on the MIMIC-III Demo page.
  • Click Download the ZIP file (13.4 MB) to download the dataset to your computer.

What about the Data Use Agreement?

The MIMIC-III Demo is classified as Open Access on PhysioNet. You only need a PhysioNet account to download it. The full MIMIC-III database requires additional credentialing and CITI training, but the demo does not.

Now you need to extract the ZIP and place the CSV files where your Python script expects them.

  • Switch back to the terminal in PyCharm (or your system terminal). Make sure you are in your project directory.
  • Create the target directory structure by running this command:
mkdir -p mimic-iii-demo/1.4
  • Locate the downloaded ZIP file (likely in your ~/Downloads folder) and move it to your project directory by running:
mv ~/Downloads/mimiciii-demo-1.4.zip .
  • Extract the CSV files into your target directory by running:
unzip mimiciii-demo-1.4.zip -d mimic-iii-demo/1.4
  • Confirm the CSV files are in place by running:
ls mimic-iii-demo/1.4

You should see files like ADMISSIONS.csv, PATIENTS.csv, ICUSTAYS.csv, and about 25 other CSV files listed.

Don't see the CSV files?

Some ZIP archives extract into a nested subdirectory. If you see a single folder inside mimic-iii-demo/1.4/ instead of CSV files, move the CSVs up one level. The script expects CSV files directly inside mimic-iii-demo/1.4/.

If the ZIP filename differs from what is shown above, adjust the mv and unzip commands to match your actual downloaded filename.

Help me get MIMIC-III Demo CSV files into my project directory.

Write the data loading script

With the CSVs in place, you need a Python script that reads each file and bulk-loads it into PostgreSQL. The script will use psycopg2's COPY command, which is the fastest way to load large CSV datasets into PostgreSQL.

  • In PyCharm, create a new file called load_mimic.py in the root of your project directory.
  • Add the imports and configuration constants by pasting this code:
import os
import psycopg2
from pathlib import Path

DB_NAME = "mimic"
DB_USER = os.getenv("USER")
SCHEMA = "mimiciii"
DATA_DIR = Path("mimic-iii-demo/1.4")

TABLES = [
    "ADMISSIONS", "CALLOUT", "CAREGIVERS", "CHARTEVENTS", "CPTEVENTS",
    "DATETIMEEVENTS", "DIAGNOSES_ICD", "DRGCODES", "D_CPT",
    "D_ICD_DIAGNOSES", "D_ICD_PROCEDURES", "D_ITEMS", "D_LABITEMS",
    "ICUSTAYS", "INPUTEVENTS_CV", "INPUTEVENTS_MV", "LABEVENTS",
    "MICROBIOLOGYEVENTS", "OUTPUTEVENTS", "PATIENTS", "PRESCRIPTIONS",
    "PROCEDUREEVENTS_MV", "PROCEDURES_ICD", "SERVICES", "TRANSFERS",
]

What does this code do?

DB_USER = os.getenv("USER") grabs your macOS username, which is the default PostgreSQL superuser when installed via Homebrew. This avoids hardcoding a username.

DATA_DIR points to the folder where you extracted the CSV files. The TABLES list defines all 25 clinical tables to load (NOTEEVENTS is excluded because the demo file is empty).

  • Below the TABLES list, add the two helper functions that create the schema and load individual tables:

def create_schema(cur):
    cur.execute(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA};")
    cur.execute(f"SET search_path TO {SCHEMA};")


def load_table(cur, table_name, data_dir):
    csv_path = data_dir / f"{table_name}.csv"
    if not csv_path.exists():
        print(f"  Skipping {table_name}: file not found")
        return

    table_lower = table_name.lower()
    cur.execute(f"DROP TABLE IF EXISTS {SCHEMA}.{table_lower} CASCADE;")

    with open(csv_path, "r") as f:
        header = f.readline().strip()
        columns = header.split(",")
        col_defs = ", ".join(f'"{col.strip().lower()}" TEXT' for col in columns)
        cur.execute(f'CREATE TABLE {SCHEMA}.{table_lower} ({col_defs});')

    with open(csv_path, "r") as f:
        cur.copy_expert(
            f"COPY {SCHEMA}.{table_lower} FROM STDIN WITH CSV HEADER DELIMITER ','",
            f,
        )
    cur.execute(f"SELECT COUNT(*) FROM {SCHEMA}.{table_lower};")
    count = cur.fetchone()[0]
    print(f"  Loaded {table_lower}: {count} rows")

What does this code do?

create_schema ensures the mimiciii schema exists and sets it as the active search path for the session.

load_table does four things for each CSV: drops any existing version of the table, reads the CSV header to auto-generate column definitions (all as TEXT to avoid type-casting issues), uses PostgreSQL's COPY command for high-speed bulk loading, and prints the resulting row count.

  • Finally, add the main function and entry point at the bottom of the file:

def main():
    conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, host="localhost")
    conn.autocommit = True
    cur = conn.cursor()

    print("Creating schema...")
    create_schema(cur)

    print("Loading tables...")
    for table in TABLES:
        load_table(cur, table, DATA_DIR)

    cur.close()
    conn.close()
    print("\nDone! MIMIC-III Demo loaded into PostgreSQL.")


if __name__ == "__main__":
    main()

What does this code do?

main() connects to your local mimic database, enables autocommit (required for COPY operations), then loops through all 25 tables and loads each one.

The if __name__ == "__main__" guard means the script only runs when you execute it directly, not when it is imported.

  • Save load_mimic.py.
  • Run the loading script by executing this command in your PyCharm terminal:
python load_mimic.py

You should see output listing each table with its row count, ending with the message Done! MIMIC-III Demo loaded into PostgreSQL..

Seeing a connection error?

If you see a message about the connection being refused, make sure PostgreSQL is still running. Run brew services list and confirm postgresql@16 shows as started.

If you see a "file not found" error for the CSVs, double-check that the mimic-iii-demo/1.4/ directory contains CSV files (not another nested folder). Run ls mimic-iii-demo/1.4/ to verify.

Help me fix my MIMIC-III data loading script.

✔️ Awesome, I've got everything!

Great. Make sure you have saved load_mimic.py and seen the success message in your terminal.

ⓧ I'd like to double check the full code

Here is the complete load_mimic.py file:

import os
import psycopg2
from pathlib import Path

DB_NAME = "mimic"
DB_USER = os.getenv("USER")
SCHEMA = "mimiciii"
DATA_DIR = Path("mimic-iii-demo/1.4")

TABLES = [
    "ADMISSIONS", "CALLOUT", "CAREGIVERS", "CHARTEVENTS", "CPTEVENTS",
    "DATETIMEEVENTS", "DIAGNOSES_ICD", "DRGCODES", "D_CPT",
    "D_ICD_DIAGNOSES", "D_ICD_PROCEDURES", "D_ITEMS", "D_LABITEMS",
    "ICUSTAYS", "INPUTEVENTS_CV", "INPUTEVENTS_MV", "LABEVENTS",
    "MICROBIOLOGYEVENTS", "OUTPUTEVENTS", "PATIENTS", "PRESCRIPTIONS",
    "PROCEDUREEVENTS_MV", "PROCEDURES_ICD", "SERVICES", "TRANSFERS",
]


def create_schema(cur):
    cur.execute(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA};")
    cur.execute(f"SET search_path TO {SCHEMA};")


def load_table(cur, table_name, data_dir):
    csv_path = data_dir / f"{table_name}.csv"
    if not csv_path.exists():
        print(f"  Skipping {table_name}: file not found")
        return

    table_lower = table_name.lower()
    cur.execute(f"DROP TABLE IF EXISTS {SCHEMA}.{table_lower} CASCADE;")

    with open(csv_path, "r") as f:
        header = f.readline().strip()
        columns = header.split(",")
        col_defs = ", ".join(f'"{col.strip().lower()}" TEXT' for col in columns)
        cur.execute(f'CREATE TABLE {SCHEMA}.{table_lower} ({col_defs});')

    with open(csv_path, "r") as f:
        cur.copy_expert(
            f"COPY {SCHEMA}.{table_lower} FROM STDIN WITH CSV HEADER DELIMITER ','",
            f,
        )
    cur.execute(f"SELECT COUNT(*) FROM {SCHEMA}.{table_lower};")
    count = cur.fetchone()[0]
    print(f"  Loaded {table_lower}: {count} rows")


def main():
    conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, host="localhost")
    conn.autocommit = True
    cur = conn.cursor()

    print("Creating schema...")
    create_schema(cur)

    print("Loading tables...")
    for table in TABLES:
        load_table(cur, table, DATA_DIR)

    cur.close()
    conn.close()
    print("\nDone! MIMIC-III Demo loaded into PostgreSQL.")


if __name__ == "__main__":
    main()
Verify the data load with row counts

The script printed row counts as it ran, but you should independently verify the key tables using psql to confirm the data landed correctly.

  • Run these queries against your mimic database by executing:
psql -d mimic -c "SELECT COUNT(*) FROM mimiciii.patients;"

You should see a count of 100.

  • Check the admissions table by running:
psql -d mimic -c "SELECT COUNT(*) FROM mimiciii.admissions;"

You should see a count of 129.

  • Check the ICU stays table by running:
psql -d mimic -c "SELECT COUNT(*) FROM mimiciii.icustays;"

You should see a count of 136.

Why do these numbers differ?

The dataset contains 100 unique patients, but some patients were admitted to the hospital multiple times (129 total admissions). Within those admissions, some patients had multiple ICU stays (136 total). This one-to-many relationship between patients, admissions, and ICU stays is a core concept in the MIMIC-III data model.

Row counts don't match?

If you see 0 rows, the script may have loaded data into a different schema. Run psql -d mimic -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'mimiciii';" to check which tables exist in the schema.

If the query fails with a relation not found error, re-run python load_mimic.py and watch for any error messages in the output.

Help me verify my MIMIC-III data loaded correctly.

Your database now holds the complete MIMIC-III Demo dataset across 26 tables. Next up, you will explore the clinical data schema and understand how patients, admissions, and ICU stays connect to each other.

Explore the Clinical Data Schema

Your PostgreSQL database is loaded with 100 ICU patients across 26 tables. Now you need to understand the shape of this data before you can analyze it.

Clinical databases like MIMIC-III link patients to hospital admissions to ICU stays using a hierarchy of IDs. In this step, you will connect to PostgreSQL from Python using pandas and psycopg2, explore the relational structure, and understand how the tables connect to each other.

In this step, get ready to:
  • Write SQL queries to inspect the MIMIC-III schema and examine key columns in the PATIENTS and ADMISSIONS tables.
  • Explore patient demographics (gender distribution) and admission types to understand the population.
  • Compute basic statistics and identify the date-shifted nature of patient records.
Set up the analysis script and database connection

Before writing queries, you need a dedicated analysis file that handles the database connection and organizes your exploration code into reusable functions.

  • In PyCharm, create a new file called mimic_analysis.py in the same project directory where load_mimic.py lives.
  • Add the following imports, configuration constants, and connection helper:
import os
from pathlib import Path

import matplotlib.pyplot as plt
import pandas as pd
import psycopg2
import seaborn as sns

# Database connection parameters (same as load_mimic.py)
DB_NAME = "mimic"
DB_USER = os.getenv("USER")
SCHEMA = "mimiciii"
# Output directory for visualizations (created automatically)
RESULTS_DIR = Path("results")
RESULTS_DIR.mkdir(exist_ok=True)


def get_connection():
    # Open a new session to the local PostgreSQL database
    conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, host="localhost")
    return conn

What does this code do?

The imports bring in psycopg2 for the database connection, pandas for running SQL queries directly into DataFrames, and matplotlib / seaborn for the visualizations you will create in a later step.

The DB_USER = os.getenv("USER") line grabs your macOS username automatically. This matches how Homebrew PostgreSQL sets up authentication.

The RESULTS_DIR.mkdir(exist_ok=True) call creates a results/ folder if it does not already exist. This is where visualizations will be saved later.

  • Save mimic_analysis.py.

Seeing an import error?

If PyCharm underlines any import with a red squiggle, make sure the Python interpreter in the bottom-right corner is set to your project's virtual environment (not the system Python). All four packages were installed in Step 1.

Help me fix my interpreter settings.

Write the schema exploration function

The explore_schema() function runs five SQL queries that progressively reveal the structure of MIMIC-III. It covers table listing, patient columns, demographics, admission types, and basic counts.

  • Below get_connection() in mimic_analysis.py, add the full explore_schema() function:
def explore_schema(conn):
    print("=== MIMIC-III Schema Exploration ===\n")

    # Query 1: List all tables in the mimiciii schema
    tables_df = pd.read_sql(
        f"""
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = '{SCHEMA}'
        ORDER BY table_name;
        """,
        conn,
    )
    print(f"Tables in {SCHEMA} schema: {len(tables_df)}")
    print(tables_df.to_string(index=False))

    # Query 2: Sample rows from PATIENTS to see column structure
    patients_df = pd.read_sql(
        f"SELECT * FROM {SCHEMA}.patients LIMIT 5;", conn
    )
    print(f"\nPATIENTS sample:\n{patients_df}")

    # Query 3: Gender distribution across all patients
    gender_df = pd.read_sql(
        f"""
        SELECT gender, COUNT(*) as count
        FROM {SCHEMA}.patients
        GROUP BY gender;
        """,
        conn,
    )
    print(f"\nGender distribution:\n{gender_df}")

    # Query 4: Breakdown of admission types (emergency, elective, etc.)
    admissions_df = pd.read_sql(
        f"""
        SELECT admission_type, COUNT(*) as count
        FROM {SCHEMA}.admissions
        GROUP BY admission_type
        ORDER BY count DESC;
        """,
        conn,
    )
    print(f"\nAdmission types:\n{admissions_df}")

    # Query 5: High-level counts across key tables
    stats_df = pd.read_sql(
        f"""
        SELECT
            (SELECT COUNT(DISTINCT subject_id) FROM {SCHEMA}.patients) as total_patients,
            (SELECT COUNT(*) FROM {SCHEMA}.admissions) as total_admissions,
            (SELECT COUNT(*) FROM {SCHEMA}.icustays) as total_icustays;
        """,
        conn,
    )
    print(f"\nBasic statistics:\n{stats_df}")

What do these queries reveal?

  • Query 1 confirms all 26 MIMIC-III tables loaded correctly by listing them from PostgreSQL's information_schema.
  • Query 2 shows the PATIENTS table columns: subject_id (unique patient identifier), gender, dob (date of birth), and dod (date of death, if applicable).
  • Queries 3 and 4 give a demographic snapshot of who is in the dataset.
  • Query 5 confirms the key counts: 100 patients produced 129 hospital admissions and 136 ICU stays. One patient can have multiple admissions, and one admission can have multiple ICU stays.

How do patients, admissions, and ICU stays connect?

MIMIC-III uses a three-level ID hierarchy. SUBJECT_ID identifies a unique patient. HADM_ID identifies a unique hospital admission for that patient. ICUSTAY_ID identifies a unique ICU stay within that admission.

This means you always join from PATIENTS to ADMISSIONS on subject_id, and from ADMISSIONS to ICUSTAYS on hadm_id. Every analysis query in this project follows this pattern.

  • Save mimic_analysis.py.

Seeing a syntax error in the function?

Make sure the triple-quoted SQL strings inside each pd.read_sql() call are properly indented. Python f-strings with triple quotes are sensitive to leading whitespace inside the parentheses.

Help me debug my explore_schema function.

Add placeholder functions and run the exploration

The analysis script needs placeholder functions for the mortality and visualization code you will write in later steps. Adding them now keeps the file structure complete.

  • Below explore_schema(), add these placeholder functions and the main() entry point:
def compute_mortality_by_age(conn):
    pass


def compute_los_by_outcome(conn):
    pass


def plot_mortality_by_age(mortality_df):
    pass


def plot_los_by_outcome(los_df):
    pass


def main():
    conn = get_connection()
    # Run the schema exploration
    explore_schema(conn)
    conn.close()
    print("\nExploration complete!")


if __name__ == "__main__":
    main()

What does this code do?

The four pass functions are stubs. They define the structure of your analysis pipeline so you can fill them in during Steps 4 and 5.

The main() function opens a connection, calls explore_schema(), then closes the connection cleanly. The if __name__ == "__main__" guard ensures the code only runs when you execute the file directly.

  • Save mimic_analysis.py.
  • Run the script in PyCharm's terminal by running:
python mimic_analysis.py
  • You should see output listing all 26 tables, a PATIENTS sample showing columns like subject_id, gender, dob, and dod, followed by gender counts, admission type counts, and the basic statistics line showing total_patients = 100, total_admissions = 129, total_icustays = 136.

Why do some patients show a date of birth 300 years in the past?

MIMIC-III protects the privacy of elderly patients. Any patient older than 89 at their first admission has their date of birth shifted to approximately 300 years before admission. This results in a calculated age around 300.

Standard clinical research practice is to cap these ages at 91.4 years. You will apply this convention in the next step when computing age-based mortality rates.

Connection refused or role does not exist?

Make sure PostgreSQL is still running by checking brew services list in a separate terminal tab. The postgresql@16 service should show started. If the role error mentions your username, the database may not have the correct superuser. Re-run createdb mimic to verify access.

Help me fix my PostgreSQL connection error.

✔️ Awesome, I've got everything!

Great. Double check you have saved mimic_analysis.py and confirmed the output matches the expected counts.

ⓧ I'd like to double check the full code

import os
from pathlib import Path

import matplotlib.pyplot as plt
import pandas as pd
import psycopg2
import seaborn as sns

DB_NAME = "mimic"
DB_USER = os.getenv("USER")
SCHEMA = "mimiciii"
RESULTS_DIR = Path("results")
RESULTS_DIR.mkdir(exist_ok=True)


def get_connection():
    conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, host="localhost")
    return conn


def explore_schema(conn):
    print("=== MIMIC-III Schema Exploration ===\n")

    tables_df = pd.read_sql(
        f"""
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = '{SCHEMA}'
        ORDER BY table_name;
        """,
        conn,
    )
    print(f"Tables in {SCHEMA} schema: {len(tables_df)}")
    print(tables_df.to_string(index=False))

    patients_df = pd.read_sql(
        f"SELECT * FROM {SCHEMA}.patients LIMIT 5;", conn
    )
    print(f"\nPATIENTS sample:\n{patients_df}")

    gender_df = pd.read_sql(
        f"""
        SELECT gender, COUNT(*) as count
        FROM {SCHEMA}.patients
        GROUP BY gender;
        """,
        conn,
    )
    print(f"\nGender distribution:\n{gender_df}")

    admissions_df = pd.read_sql(
        f"""
        SELECT admission_type, COUNT(*) as count
        FROM {SCHEMA}.admissions
        GROUP BY admission_type
        ORDER BY count DESC;
        """,
        conn,
    )
    print(f"\nAdmission types:\n{admissions_df}")

    stats_df = pd.read_sql(
        f"""
        SELECT
            (SELECT COUNT(DISTINCT subject_id) FROM {SCHEMA}.patients) as total_patients,
            (SELECT COUNT(*) FROM {SCHEMA}.admissions) as total_admissions,
            (SELECT COUNT(*) FROM {SCHEMA}.icustays) as total_icustays;
        """,
        conn,
    )
    print(f"\nBasic statistics:\n{stats_df}")


def compute_mortality_by_age(conn):
    pass


def compute_los_by_outcome(conn):
    pass


def plot_mortality_by_age(mortality_df):
    pass


def plot_los_by_outcome(los_df):
    pass


def main():
    conn = get_connection()
    explore_schema(conn)
    conn.close()
    print("\nExploration complete!")


if __name__ == "__main__":
    main()

You now have a clear picture of the MIMIC-III data model. Next up, you will use this understanding to define an adult ICU cohort and compute mortality rates by age group.

Define Adult ICU Cohort and Analyze Mortality

Your database is loaded and you understand how the tables connect. Now it is time to do what clinical researchers actually do: define a study cohort and measure outcomes.

In clinical research, you never analyze "all patients." You define strict inclusion criteria (who counts as an adult?) and handle data quirks (ages shifted for privacy). In this step, you will apply standard MIMIC-III research methodology to compute in-hospital mortality rates stratified by age group and analyze ICU length of stay patterns.

In this step, get ready to:
  • Write a SQL CTE query that defines an adult ICU cohort and computes age at first admission.
  • Compute in-hospital mortality rates stratified by age group.
  • Calculate ICU length of stay statistics correlated with survival outcome.
Build the adult cohort query and compute mortality rates

Clinical cohort definition is the foundation of any ICU study. You need to decide who counts as an "adult" and handle a privacy quirk in MIMIC-III: patients older than 89 at first admission have a synthetic date of birth shifted ~300 years into the past, producing calculated ages over 200.

Why cap ages at 91.4?

MIMIC-III de-identifies patients over 89 by shifting their date of birth far into the past. This makes their computed age appear as ~300 years. The standard convention is to replace these with a median value of 91.4, which preserves the "elderly" signal without revealing exact age.

  • In mimic_analysis.py, find the compute_mortality_by_age function (currently just pass) and replace its entire body with the following:
def compute_mortality_by_age(conn):
    print("\n=== Adult ICU Cohort Mortality Analysis ===\n")

    query = f"""
    WITH first_admission AS (
        SELECT
            p.subject_id,
            p.gender,
            p.dob::TIMESTAMP,
            MIN(a.admittime::TIMESTAMP) AS first_admittime,
            a.hospital_expire_flag
        FROM {SCHEMA}.patients p
        INNER JOIN {SCHEMA}.admissions a
            ON p.subject_id = a.subject_id
        GROUP BY p.subject_id, p.gender, p.dob, a.hospital_expire_flag
    ),
    age_calc AS (
        SELECT
            subject_id,
            gender,
            hospital_expire_flag,
            EXTRACT(YEAR FROM AGE(first_admittime, dob)) AS age
        FROM first_admission
    ),
    adult_cohort AS (
        SELECT
            subject_id,
            gender,
            hospital_expire_flag::INTEGER,
            CASE
                WHEN age > 200 THEN 91.4
                ELSE age
            END AS age_capped
        FROM age_calc
        WHERE age >= 15
    )
    SELECT
        subject_id,
        gender,
        hospital_expire_flag,
        age_capped,
        CASE
            WHEN age_capped BETWEEN 15 AND 44 THEN '15-44'
            WHEN age_capped BETWEEN 45 AND 64 THEN '45-64'
            WHEN age_capped BETWEEN 65 AND 84 THEN '65-84'
            WHEN age_capped >= 85 THEN '85+'
        END AS age_group
    FROM adult_cohort;
    """

    df = pd.read_sql(query, conn)
    print(f"Adult cohort size: {len(df)} patients")

    # Aggregate mortality statistics per age group
    mortality_df = (
        df.groupby("age_group")
        .agg(
            patient_count=("subject_id", "count"),
            deaths=("hospital_expire_flag", "sum"),
        )
        .reset_index()
    )
    mortality_df["mortality_rate"] = (
        mortality_df["deaths"] / mortality_df["patient_count"] * 100
    )

    # Sort age groups in clinical order
    age_order = ["15-44", "45-64", "65-84", "85+"]
    mortality_df["age_group"] = pd.Categorical(
        mortality_df["age_group"], categories=age_order, ordered=True
    )
    mortality_df = mortality_df.sort_values("age_group")

    print("\nMortality by Age Group:")
    print(mortality_df.to_string(index=False))

    return mortality_df, df

What does this code do?

This function uses a multi-step SQL CTE (Common Table Expression) to define the adult cohort, then pandas to compute mortality statistics.

  • first_admission joins PATIENTS and ADMISSIONS to find each patient's earliest hospital visit.
  • age_calc uses PostgreSQL's AGE() function to compute age in years from the date of birth and first admission time.
  • adult_cohort filters to patients aged 15 or older (the MIMIC-III adult definition) and caps privacy-shifted ages above 200 at 91.4.
  • The final SELECT assigns each patient to an age group (15-44, 45-64, 65-84, 85+).
  • The pandas section groups by age group, counts patients and deaths (using hospital_expire_flag), and computes the mortality rate as a percentage.
  • Save mimic_analysis.py.

Seeing a syntax error in the SQL query?

Make sure the triple-quoted f-string starts with f""" and ends with """ on its own line. Each CTE must be separated by a comma, but there is no comma after the last CTE before the final SELECT.

Help me debug my SQL CTE query for computing patient age from MIMIC-III

Compute ICU length of stay by survival outcome

Length of stay is one of the most studied outcomes in ICU research. The ICUSTAYS table has a los column that stores length of stay in fractional days. Linking it with the admission outcome tells you whether patients who died tended to stay longer or shorter than survivors.

  • In mimic_analysis.py, find the compute_los_by_outcome function and replace its pass body with the following:
def compute_los_by_outcome(conn):
    query = f"""
    SELECT
        i.subject_id,
        i.los::FLOAT AS los_days,
        a.hospital_expire_flag::INTEGER
    FROM {SCHEMA}.icustays i
    INNER JOIN {SCHEMA}.admissions a
        ON i.hadm_id = a.hadm_id
    WHERE i.los IS NOT NULL;
    """

    df = pd.read_sql(query, conn)
    df["outcome"] = df["hospital_expire_flag"].map(
        {0: "Survived", 1: "Died"}
    )

    print("\n=== ICU Length of Stay by Outcome ===")
    print(df.groupby("outcome")["los_days"].describe())

    return df

What does this code do?

  • The query joins ICUSTAYS with ADMISSIONS on hadm_id to link each ICU stay with its admission outcome.
  • los::FLOAT casts the length of stay from TEXT (how we loaded all columns) to a numeric type in fractional days.
  • The .map() call converts the binary flag (0/1) to human-readable labels ("Survived"/"Died") for plotting later.
  • .describe() prints summary statistics (mean, median, min, max) grouped by outcome so you can spot patterns.
  • Save mimic_analysis.py.

Getting an error about casting los to FLOAT?

If any los values contain non-numeric text, the WHERE i.los IS NOT NULL clause should filter those out. Make sure the WHERE clause is included in your query.

Help me fix a type casting error in my ICUSTAYS query

Update main() and run the analysis

With both analysis functions complete, you need to wire them into the main() function so they run when you execute the script.

  • In mimic_analysis.py, scroll down to the main() function at the bottom of the file.
  • Find this code:
def main():
    conn = get_connection()
    explore_schema(conn)
    conn.close()
    print("\nExploration complete!")
  • Replace the entire main() function with this updated version:
def main():
    conn = get_connection()

    explore_schema(conn)
    mortality_df, cohort_df = compute_mortality_by_age(conn)
    los_df = compute_los_by_outcome(conn)

    conn.close()
    print("\nAnalysis complete!")

What changed in main()?

  • compute_mortality_by_age(conn) returns two DataFrames: the aggregated mortality table and the full cohort data.
  • compute_los_by_outcome(conn) returns the length-of-stay DataFrame with outcome labels.
  • Both variables (mortality_df, los_df) will be passed to the plotting functions in the next step.
  • Save mimic_analysis.py.
  • Run the script in your PyCharm terminal by running:
python mimic_analysis.py

You should see the schema exploration output from before, followed by the new mortality analysis. Look for the "Mortality by Age Group" table showing four rows with columns for age_group, patient_count, deaths, and mortality_rate.

What should I see?

The mortality rates should increase with age group. Younger patients (15-44) have a lower mortality rate, while the 85+ group has the highest. This is expected clinical behavior and confirms your cohort definition is working correctly.

The ICU Length of Stay section prints descriptive statistics (count, mean, std, min, max) for "Survived" and "Died" groups separately.

Seeing zero rows in the adult cohort?

Check that your SQL uses p.dob::TIMESTAMP and a.admittime::TIMESTAMP to cast the TEXT columns. Without these casts, the AGE() function cannot compute the difference between two dates.

Also verify that the WHERE age >= 15 filter is in the adult_cohort CTE, not in the final SELECT.

Help me debug why my MIMIC-III adult cohort query returns zero rows

✔️ Awesome, I've got everything!

Great. Double check you have saved mimic_analysis.py and confirmed that mortality rates increase with age group in your terminal output.

ⓧ I'd like to double check the full code

import os
from pathlib import Path

import matplotlib.pyplot as plt
import pandas as pd
import psycopg2
import seaborn as sns

DB_NAME = "mimic"
DB_USER = os.getenv("USER")
SCHEMA = "mimiciii"
RESULTS_DIR = Path("results")
RESULTS_DIR.mkdir(exist_ok=True)


def get_connection():
    conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, host="localhost")
    return conn


def explore_schema(conn):
    print("=== MIMIC-III Schema Exploration ===\n")

    tables_df = pd.read_sql(
        f"""
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = '{SCHEMA}'
        ORDER BY table_name;
        """,
        conn,
    )
    print(f"Tables in {SCHEMA} schema: {len(tables_df)}")
    print(tables_df.to_string(index=False))

    patients_df = pd.read_sql(
        f"SELECT * FROM {SCHEMA}.patients LIMIT 5;", conn
    )
    print(f"\nPATIENTS sample:\n{patients_df}")

    gender_df = pd.read_sql(
        f"""
        SELECT gender, COUNT(*) as count
        FROM {SCHEMA}.patients
        GROUP BY gender;
        """,
        conn,
    )
    print(f"\nGender distribution:\n{gender_df}")

    admissions_df = pd.read_sql(
        f"""
        SELECT admission_type, COUNT(*) as count
        FROM {SCHEMA}.admissions
        GROUP BY admission_type
        ORDER BY count DESC;
        """,
        conn,
    )
    print(f"\nAdmission types:\n{admissions_df}")

    stats_df = pd.read_sql(
        f"""
        SELECT
            (SELECT COUNT(DISTINCT subject_id) FROM {SCHEMA}.patients) as total_patients,
            (SELECT COUNT(*) FROM {SCHEMA}.admissions) as total_admissions,
            (SELECT COUNT(*) FROM {SCHEMA}.icustays) as total_icustays;
        """,
        conn,
    )
    print(f"\nBasic statistics:\n{stats_df}")


def compute_mortality_by_age(conn):
    print("\n=== Adult ICU Cohort Mortality Analysis ===\n")

    query = f"""
    WITH first_admission AS (
        SELECT
            p.subject_id,
            p.gender,
            p.dob::TIMESTAMP,
            MIN(a.admittime::TIMESTAMP) AS first_admittime,
            a.hospital_expire_flag
        FROM {SCHEMA}.patients p
        INNER JOIN {SCHEMA}.admissions a
            ON p.subject_id = a.subject_id
        GROUP BY p.subject_id, p.gender, p.dob, a.hospital_expire_flag
    ),
    age_calc AS (
        SELECT
            subject_id,
            gender,
            hospital_expire_flag,
            EXTRACT(YEAR FROM AGE(first_admittime, dob)) AS age
        FROM first_admission
    ),
    adult_cohort AS (
        SELECT
            subject_id,
            gender,
            hospital_expire_flag::INTEGER,
            CASE
                WHEN age > 200 THEN 91.4
                ELSE age
            END AS age_capped
        FROM age_calc
        WHERE age >= 15
    )
    SELECT
        subject_id,
        gender,
        hospital_expire_flag,
        age_capped,
        CASE
            WHEN age_capped BETWEEN 15 AND 44 THEN '15-44'
            WHEN age_capped BETWEEN 45 AND 64 THEN '45-64'
            WHEN age_capped BETWEEN 65 AND 84 THEN '65-84'
            WHEN age_capped >= 85 THEN '85+'
        END AS age_group
    FROM adult_cohort;
    """

    df = pd.read_sql(query, conn)
    print(f"Adult cohort size: {len(df)} patients")

    mortality_df = (
        df.groupby("age_group")
        .agg(
            patient_count=("subject_id", "count"),
            deaths=("hospital_expire_flag", "sum"),
        )
        .reset_index()
    )
    mortality_df["mortality_rate"] = (
        mortality_df["deaths"] / mortality_df["patient_count"] * 100
    )

    age_order = ["15-44", "45-64", "65-84", "85+"]
    mortality_df["age_group"] = pd.Categorical(
        mortality_df["age_group"], categories=age_order, ordered=True
    )
    mortality_df = mortality_df.sort_values("age_group")

    print("\nMortality by Age Group:")
    print(mortality_df.to_string(index=False))

    return mortality_df, df


def compute_los_by_outcome(conn):
    query = f"""
    SELECT
        i.subject_id,
        i.los::FLOAT AS los_days,
        a.hospital_expire_flag::INTEGER
    FROM {SCHEMA}.icustays i
    INNER JOIN {SCHEMA}.admissions a
        ON i.hadm_id = a.hadm_id
    WHERE i.los IS NOT NULL;
    """

    df = pd.read_sql(query, conn)
    df["outcome"] = df["hospital_expire_flag"].map(
        {0: "Survived", 1: "Died"}
    )

    print("\n=== ICU Length of Stay by Outcome ===")
    print(df.groupby("outcome")["los_days"].describe())

    return df


def plot_mortality_by_age(mortality_df):
    pass


def plot_los_by_outcome(los_df):
    pass


def main():
    conn = get_connection()

    explore_schema(conn)
    mortality_df, cohort_df = compute_mortality_by_age(conn)
    los_df = compute_los_by_outcome(conn)

    conn.close()
    print("\nAnalysis complete!")


if __name__ == "__main__":
    main()

You now have real clinical research output: mortality rates stratified by age group and length-of-stay statistics by outcome. Next up, you will turn these numbers into publication-quality visualizations that belong in a portfolio.

Create Publication-Quality Visualizations

Your analysis script now computes mortality rates by age group and ICU length of stay statistics. The data is there, but numbers alone don't communicate clinical findings effectively.

In this step, you'll turn those DataFrames into publication-quality visualizations using matplotlib and seaborn. These are the same charting libraries used in clinical research papers and health data portfolios.

In this step, get ready to:
  • Create a bar chart showing mortality rate by age group with patient counts annotated.
  • Create a box plot of ICU length of stay by survival outcome.
  • Save all figures as high-resolution PNG files at 300 DPI.
Implement the mortality bar chart

A bar chart is the clearest way to show how mortality rate changes across age groups. Annotating each bar with the patient count gives readers immediate context about sample size.

  • In mimic_analysis.py, find the plot_mortality_by_age function that currently contains only pass.
  • Replace the entire function with the following code:
def plot_mortality_by_age(mortality_df):
    sns.set_style("white")
    fig, ax = plt.subplots(figsize=(8, 5))

    bars = ax.bar(
        mortality_df["age_group"],
        mortality_df["mortality_rate"],
        color=sns.color_palette("Blues_d", n_colors=len(mortality_df)),
        edgecolor="black",
        linewidth=0.5,
    )

    # Annotate each bar with the patient count
    for bar, row in zip(bars, mortality_df.itertuples()):
        ax.text(
            bar.get_x() + bar.get_width() / 2,
            bar.get_height() + 0.5,
            f"n={row.patient_count}",
            ha="center",
            va="bottom",
            fontsize=10,
        )

    ax.set_xlabel("Age Group (years)", fontsize=12)
    ax.set_ylabel("In-Hospital Mortality Rate (%)", fontsize=12)
    ax.set_title(
        "ICU In-Hospital Mortality Rate by Age Group\n(MIMIC-III Demo, N=100 patients)",
        fontsize=13,
        fontweight="bold",
    )
    # Remove top and right spines for a clean clinical look
    ax.spines["top"].set_visible(False)
    ax.spines["right"].set_visible(False)

    plt.tight_layout()
    plt.savefig(RESULTS_DIR / "mortality_by_age_group.png", dpi=300)
    plt.close()
    print(f"\nSaved: {RESULTS_DIR / 'mortality_by_age_group.png'}")

What does this code do?

This function takes the mortality DataFrame from the previous step and draws a bar chart with one bar per age group.

  • sns.set_style("white") sets a clean white background with no grid lines. This is standard for clinical publications.
  • The Blues_d palette gives each bar a progressively darker blue, making the age progression visually intuitive.
  • The annotation loop places n=<count> above each bar so readers know the sample size behind each percentage.
  • Removing the top and right spines is a common style choice in scientific figures to reduce visual clutter.
  • Save mimic_analysis.py.

You won't see a change yet. The next substep adds the second chart, and then you'll run the full script to generate both PNGs.

Implement the length of stay box plot

A box plot reveals the distribution shape and outliers in ICU length of stay data. Splitting by survival outcome shows whether patients who died tended to have different ICU durations.

  • In mimic_analysis.py, find the plot_los_by_outcome function that currently contains only pass.
  • Replace the entire function with the following code:
def plot_los_by_outcome(los_df):
    sns.set_style("white")
    fig, ax = plt.subplots(figsize=(7, 5))

    # Box plot comparing ICU stay duration for survivors vs non-survivors
    sns.boxplot(
        data=los_df,
        x="outcome",
        y="los_days",
        palette={"Survived": "#4C72B0", "Died": "#C44E52"},
        ax=ax,
    )

    ax.set_xlabel("Patient Outcome", fontsize=12)
    ax.set_ylabel("ICU Length of Stay (days)", fontsize=12)
    ax.set_title(
        "ICU Length of Stay by Survival Outcome\n(MIMIC-III Demo)",
        fontsize=13,
        fontweight="bold",
    )
    # Clean clinical styling
    ax.spines["top"].set_visible(False)
    ax.spines["right"].set_visible(False)

    plt.tight_layout()
    plt.savefig(RESULTS_DIR / "los_by_outcome.png", dpi=300)
    plt.close()
    print(f"Saved: {RESULTS_DIR / 'los_by_outcome.png'}")

What does this code do?

This function creates a box plot comparing ICU length of stay between patients who survived and those who died during their hospital admission.

  • sns.boxplot draws the box (interquartile range), whiskers, and outlier dots automatically from the raw data.
  • The custom palette uses a calm blue for Survived and a muted red for Died. These colors are colorblind-friendly and standard in clinical visualizations.
  • dpi=300 ensures the output is print-resolution quality, suitable for publications or portfolio presentations.
  • Save mimic_analysis.py.

You won't see output yet. The next substep wires these functions into main() and runs the script.

Update main() and generate the visualizations

The two plotting functions are ready but main() doesn't call them yet. You need to add two lines so the script produces the PNG files when it runs.

  • In mimic_analysis.py, scroll down to the main() function.
  • Find this line:
    los_df = compute_los_by_outcome(conn)

    conn.close()
  • Add the two plotting calls between los_df = compute_los_by_outcome(conn) and conn.close(), so the section now looks like this:
    los_df = compute_los_by_outcome(conn)

    plot_mortality_by_age(mortality_df)
    plot_los_by_outcome(los_df)

    conn.close()
  • Save mimic_analysis.py.
  • Run the full analysis script by clicking the green play button in PyCharm, or by running this in your terminal:
python mimic_analysis.py
  • Confirm the output files exist by running:
ls results/

You should see mortality_by_age_group.png and los_by_outcome.png listed in the output.

What should I see?

The terminal prints the full schema exploration, mortality table, and length of stay statistics from the previous step. At the end, you should see two "Saved" confirmation lines pointing to the results/ directory.

Open the PNG files in Finder or your image viewer. The mortality chart should show bars increasing in height from the 15-44 group to the 85+ group. The box plot should show two boxes side by side.

Not seeing the PNG files?

  • Check that you added plot_mortality_by_age(mortality_df) and plot_los_by_outcome(los_df) inside main() before the conn.close() line.
  • Make sure the results/ directory exists in your project folder. The script creates it automatically, but if you moved files around it may be missing.
  • If you see a TypeError about NoneType, confirm that compute_mortality_by_age() returns two values and you assigned them to mortality_df, cohort_df.

Why aren't my visualization PNG files being generated?

✔️ Awesome, I've got everything!

Great. Double check you've saved mimic_analysis.py and both PNGs are in your results/ folder.

ⓧ I'd like to double check the full code

Here is the complete mimic_analysis.py file:

import os
from pathlib import Path

import matplotlib.pyplot as plt
import pandas as pd
import psycopg2
import seaborn as sns

DB_NAME = "mimic"
DB_USER = os.getenv("USER")
SCHEMA = "mimiciii"
RESULTS_DIR = Path("results")
RESULTS_DIR.mkdir(exist_ok=True)


def get_connection():
    conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, host="localhost")
    return conn


def explore_schema(conn):
    print("=== MIMIC-III Schema Exploration ===\n")

    tables_df = pd.read_sql(
        f"""
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = '{SCHEMA}'
        ORDER BY table_name;
        """,
        conn,
    )
    print(f"Tables in {SCHEMA} schema: {len(tables_df)}")
    print(tables_df.to_string(index=False))

    patients_df = pd.read_sql(
        f"SELECT * FROM {SCHEMA}.patients LIMIT 5;", conn
    )
    print(f"\nPATIENTS sample:\n{patients_df}")

    gender_df = pd.read_sql(
        f"""
        SELECT gender, COUNT(*) as count
        FROM {SCHEMA}.patients
        GROUP BY gender;
        """,
        conn,
    )
    print(f"\nGender distribution:\n{gender_df}")

    admissions_df = pd.read_sql(
        f"""
        SELECT admission_type, COUNT(*) as count
        FROM {SCHEMA}.admissions
        GROUP BY admission_type
        ORDER BY count DESC;
        """,
        conn,
    )
    print(f"\nAdmission types:\n{admissions_df}")

    stats_df = pd.read_sql(
        f"""
        SELECT
            (SELECT COUNT(DISTINCT subject_id) FROM {SCHEMA}.patients) as total_patients,
            (SELECT COUNT(*) FROM {SCHEMA}.admissions) as total_admissions,
            (SELECT COUNT(*) FROM {SCHEMA}.icustays) as total_icustays;
        """,
        conn,
    )
    print(f"\nBasic statistics:\n{stats_df}")


def compute_mortality_by_age(conn):
    print("\n=== Adult ICU Cohort Mortality Analysis ===\n")

    query = f"""
    WITH first_admission AS (
        SELECT
            p.subject_id,
            p.gender,
            p.dob::TIMESTAMP,
            MIN(a.admittime::TIMESTAMP) AS first_admittime,
            a.hospital_expire_flag
        FROM {SCHEMA}.patients p
        INNER JOIN {SCHEMA}.admissions a
            ON p.subject_id = a.subject_id
        GROUP BY p.subject_id, p.gender, p.dob, a.hospital_expire_flag
    ),
    age_calc AS (
        SELECT
            subject_id,
            gender,
            hospital_expire_flag,
            EXTRACT(YEAR FROM AGE(first_admittime, dob)) AS age
        FROM first_admission
    ),
    adult_cohort AS (
        SELECT
            subject_id,
            gender,
            hospital_expire_flag::INTEGER,
            CASE
                WHEN age > 200 THEN 91.4
                ELSE age
            END AS age_capped
        FROM age_calc
        WHERE age >= 15
    )
    SELECT
        subject_id,
        gender,
        hospital_expire_flag,
        age_capped,
        CASE
            WHEN age_capped BETWEEN 15 AND 44 THEN '15-44'
            WHEN age_capped BETWEEN 45 AND 64 THEN '45-64'
            WHEN age_capped BETWEEN 65 AND 84 THEN '65-84'
            WHEN age_capped >= 85 THEN '85+'
        END AS age_group
    FROM adult_cohort;
    """

    df = pd.read_sql(query, conn)
    print(f"Adult cohort size: {len(df)} patients")

    mortality_df = (
        df.groupby("age_group")
        .agg(
            patient_count=("subject_id", "count"),
            deaths=("hospital_expire_flag", "sum"),
        )
        .reset_index()
    )
    mortality_df["mortality_rate"] = (
        mortality_df["deaths"] / mortality_df["patient_count"] * 100
    )

    age_order = ["15-44", "45-64", "65-84", "85+"]
    mortality_df["age_group"] = pd.Categorical(
        mortality_df["age_group"], categories=age_order, ordered=True
    )
    mortality_df = mortality_df.sort_values("age_group")

    print("\nMortality by Age Group:")
    print(mortality_df.to_string(index=False))

    return mortality_df, df


def compute_los_by_outcome(conn):
    query = f"""
    SELECT
        i.subject_id,
        i.los::FLOAT AS los_days,
        a.hospital_expire_flag::INTEGER
    FROM {SCHEMA}.icustays i
    INNER JOIN {SCHEMA}.admissions a
        ON i.hadm_id = a.hadm_id
    WHERE i.los IS NOT NULL;
    """

    df = pd.read_sql(query, conn)
    df["outcome"] = df["hospital_expire_flag"].map(
        {0: "Survived", 1: "Died"}
    )

    print("\n=== ICU Length of Stay by Outcome ===")
    print(df.groupby("outcome")["los_days"].describe())

    return df


def plot_mortality_by_age(mortality_df):
    sns.set_style("white")
    fig, ax = plt.subplots(figsize=(8, 5))

    bars = ax.bar(
        mortality_df["age_group"],
        mortality_df["mortality_rate"],
        color=sns.color_palette("Blues_d", n_colors=len(mortality_df)),
        edgecolor="black",
        linewidth=0.5,
    )

    for bar, row in zip(bars, mortality_df.itertuples()):
        ax.text(
            bar.get_x() + bar.get_width() / 2,
            bar.get_height() + 0.5,
            f"n={row.patient_count}",
            ha="center",
            va="bottom",
            fontsize=10,
        )

    ax.set_xlabel("Age Group (years)", fontsize=12)
    ax.set_ylabel("In-Hospital Mortality Rate (%)", fontsize=12)
    ax.set_title(
        "ICU In-Hospital Mortality Rate by Age Group\n(MIMIC-III Demo, N=100 patients)",
        fontsize=13,
        fontweight="bold",
    )
    ax.spines["top"].set_visible(False)
    ax.spines["right"].set_visible(False)

    plt.tight_layout()
    plt.savefig(RESULTS_DIR / "mortality_by_age_group.png", dpi=300)
    plt.close()
    print(f"\nSaved: {RESULTS_DIR / 'mortality_by_age_group.png'}")


def plot_los_by_outcome(los_df):
    sns.set_style("white")
    fig, ax = plt.subplots(figsize=(7, 5))

    sns.boxplot(
        data=los_df,
        x="outcome",
        y="los_days",
        palette={"Survived": "#4C72B0", "Died": "#C44E52"},
        ax=ax,
    )

    ax.set_xlabel("Patient Outcome", fontsize=12)
    ax.set_ylabel("ICU Length of Stay (days)", fontsize=12)
    ax.set_title(
        "ICU Length of Stay by Survival Outcome\n(MIMIC-III Demo)",
        fontsize=13,
        fontweight="bold",
    )
    ax.spines["top"].set_visible(False)
    ax.spines["right"].set_visible(False)

    plt.tight_layout()
    plt.savefig(RESULTS_DIR / "los_by_outcome.png", dpi=300)
    plt.close()
    print(f"Saved: {RESULTS_DIR / 'los_by_outcome.png'}")


def main():
    conn = get_connection()

    explore_schema(conn)
    mortality_df, cohort_df = compute_mortality_by_age(conn)
    los_df = compute_los_by_outcome(conn)

    plot_mortality_by_age(mortality_df)
    plot_los_by_outcome(los_df)

    conn.close()
    print("\nAnalysis complete!")


if __name__ == "__main__":
    main()

Your clinical analysis pipeline is complete. You have real ICU data flowing from PostgreSQL through Python into publication-quality charts. Next up, the Secret Mission challenges you to dig deeper into diagnosis patterns using ICD-9 codes.

Secret mission

Analyze Diagnosis Patterns with ICD-9 Codes

Your mortality analysis tells one part of the ICU story. But what conditions are actually bringing patients to the ICU? Decode the ICD-9 diagnosis codes hiding in MIMIC-III and visualize the most common comorbidities across the entire patient population.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • PostgreSQL 16 service (running via Homebrew).
  • The mimic database with the mimiciii schema (26 tables).
  • Python virtual environment and installed packages (psycopg2-binary, pandas, matplotlib, seaborn).
  • Project files (load_mimic.py, mimic_analysis.py, mimic_diagnoses.py).
  • MIMIC-III Demo data (mimic-iii-demo/1.4/ directory, ~103 MB).
  • Visualization outputs (results/ directory with PNG files).

Keep everything running

No action needed. Choose this if you are still actively building on this project or plan to extend the analysis further.

  • PostgreSQL continues running in the background as a Homebrew service. It uses minimal resources on your Mac.
  • Your mimic database and all project files remain on disk, ready for further exploration.
  • Your PhysioNet account remains active for future dataset access.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory. Your data and files stay on disk for when you return.

  • Stop the PostgreSQL service by running:
brew services stop postgresql@16
  • Your mimic database persists on disk. When you are ready to resume, start it again with brew services start postgresql@16.
  • All project files, CSV data, and visualizations remain untouched.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is irreversible.

  • Drop the mimic database by running:
dropdb mimic
  • Stop and uninstall PostgreSQL 16 by running:
brew services stop postgresql@16
brew uninstall postgresql@16
  • Remove the PATH export you added earlier. Open ~/.zshrc in any text editor and delete the line that contains /opt/homebrew/opt/postgresql@16/bin.
  • Delete your project directory (containing all scripts, data, and results) by running:
cd ~/Desktop
rm -rf mimic-iii-demo

What about your PyCharm project files?

If your PyCharm project folder is separate from the mimic-iii-demo data directory, delete it too. In PyCharm, go to File then Close Project. Then delete the project folder from ~/Desktop using Finder or rm -rf.

Nice Work!

Nice Work!

You just built a complete clinical data analysis pipeline using real ICU patient data from MIMIC-III, running entirely on your local machine.

You've learned how to:

  • Loaded the MIMIC-III Demo dataset (100 ICU patients, 26 tables) into a local PostgreSQL database and connected to it from Python using psycopg2 and pandas.
  • Defined an adult ICU cohort using standard clinical research methodology and computed in-hospital mortality rates stratified by age group.
  • Created publication-quality visualizations of mortality patterns and ICU length of stay using matplotlib and seaborn.
  • Secret Mission: Analyzed ICD-9 diagnosis patterns and visualized the most common comorbidities in the ICU population.

Ready to quiz yourself?