Hospital Patient Flow Analysis

Project Overview

Dataset Description

The hospital database contains four primary tables that capture the complete patient journey from admission to discharge.

SQL Analysis

1. Average Length of Stay by Department

Calculate the average number of days patients spend in each department to identify departments with unusually long stays.

SELECT
    d.department_name,
    COUNT(a.admission_id) AS total_admissions,
    ROUND(AVG(
        DATEDIFF(a.discharge_date, a.admission_date)
    ), 1) AS avg_length_of_stay_days,
    MIN(DATEDIFF(a.discharge_date, a.admission_date)) AS min_stay,
    MAX(DATEDIFF(a.discharge_date, a.admission_date)) AS max_stay
FROM admissions a
JOIN departments d ON a.department_id = d.department_id
WHERE a.discharge_date IS NOT NULL
GROUP BY d.department_name
ORDER BY avg_length_of_stay_days DESC;

2. Bed Occupancy Rate by Department

Measure how efficiently each department uses its available beds. An occupancy rate above 85% often signals capacity strain.

SELECT
    d.department_name,
    d.total_beds,
    COUNT(CASE WHEN b.status = 'Occupied' THEN 1 END) AS occupied_beds,
    ROUND(
        COUNT(CASE WHEN b.status = 'Occupied' THEN 1 END) * 100.0
        / d.total_beds, 1
    ) AS occupancy_rate_pct
FROM departments d
JOIN beds b ON d.department_id = b.department_id
GROUP BY d.department_name, d.total_beds
ORDER BY occupancy_rate_pct DESC;

3. Peak Admission Hours

Identify the busiest hours for admissions so staffing can be aligned with demand.

SELECT
    HOUR(admission_date) AS admission_hour,
    COUNT(*) AS total_admissions,
    ROUND(COUNT(*) * 100.0 / (
        SELECT COUNT(*) FROM admissions
    ), 1) AS pct_of_total
FROM admissions
GROUP BY HOUR(admission_date)
ORDER BY total_admissions DESC;

4. Readmission Rates (within 30 days)

Find patients who were readmitted within 30 days of discharge, grouped by department, to identify quality-of-care concerns.

WITH discharge_events AS (
    SELECT
        a1.patient_id,
        a1.admission_id AS first_admission,
        a1.discharge_date,
        a1.department_id,
        a2.admission_id AS readmission_id,
        a2.admission_date AS readmission_date,
        DATEDIFF(a2.admission_date, a1.discharge_date) AS days_to_readmit
    FROM admissions a1
    JOIN admissions a2
        ON a1.patient_id = a2.patient_id
        AND a2.admission_date > a1.discharge_date
        AND DATEDIFF(a2.admission_date, a1.discharge_date) <= 30
    WHERE a1.discharge_date IS NOT NULL
)
SELECT
    d.department_name,
    COUNT(DISTINCT de.first_admission) AS discharges_with_readmit,
    COUNT(DISTINCT a_total.admission_id) AS total_discharges,
    ROUND(
        COUNT(DISTINCT de.first_admission) * 100.0
        / COUNT(DISTINCT a_total.admission_id), 1
    ) AS readmission_rate_pct
FROM departments d
JOIN admissions a_total
    ON d.department_id = a_total.department_id
    AND a_total.discharge_date IS NOT NULL
LEFT JOIN discharge_events de
    ON d.department_id = de.department_id
GROUP BY d.department_name
ORDER BY readmission_rate_pct DESC;

Python Analysis

1. Time Series of Daily Admissions

Visualize admission trends over time to spot seasonal patterns and anomalies.

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates

# Load admissions data
admissions = pd.read_csv('admissions.csv', parse_dates=['admission_date', 'discharge_date'])

# Daily admission counts
daily_admissions = (
    admissions
    .set_index('admission_date')
    .resample('D')
    .size()
    .rename('admissions')
)

# Plot with 7-day rolling average
fig, ax = plt.subplots(figsize=(14, 5))
ax.bar(daily_admissions.index, daily_admissions.values,
       alpha=0.3, color='steelblue', label='Daily admissions')
ax.plot(daily_admissions.rolling(7).mean(),
        color='darkblue', linewidth=2, label='7-day rolling avg')

ax.set_title('Daily Hospital Admissions', fontsize=16, fontweight='bold')
ax.set_xlabel('Date')
ax.set_ylabel('Number of Admissions')
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))
ax.legend()
plt.tight_layout()
plt.savefig('daily_admissions_trend.png', dpi=150)
plt.show()

2. Heatmap of Admissions by Hour and Day of Week

Create a heatmap showing the busiest times to help with shift scheduling.

import seaborn as sns
import numpy as np

# Extract hour and day of week
admissions['hour'] = admissions['admission_date'].dt.hour
admissions['day_of_week'] = admissions['admission_date'].dt.day_name()

# Pivot table for heatmap
day_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday',
             'Friday', 'Saturday', 'Sunday']
heatmap_data = (
    admissions
    .groupby(['day_of_week', 'hour'])
    .size()
    .unstack(fill_value=0)
    .reindex(day_order)
)

fig, ax = plt.subplots(figsize=(16, 6))
sns.heatmap(heatmap_data, cmap='YlOrRd', annot=True, fmt='d',
            linewidths=0.5, ax=ax, cbar_kws={'label': 'Admissions'})
ax.set_title('Admissions Heatmap: Day of Week vs Hour',
             fontsize=16, fontweight='bold')
ax.set_xlabel('Hour of Day')
ax.set_ylabel('')
plt.tight_layout()
plt.savefig('admissions_heatmap.png', dpi=150)
plt.show()

3. Sankey Diagram of Patient Flow

Visualize how patients move through the hospital from admission type to department to discharge outcome.

import plotly.graph_objects as go

# Prepare flow data: Admission Type -> Department -> Outcome
departments = pd.read_csv('departments.csv')
admissions_dept = admissions.merge(departments, on='department_id')

# Define discharge outcome based on readmission flag
admissions_dept['outcome'] = np.where(
    admissions_dept['discharge_date'].isna(), 'Still Admitted',
    'Discharged'
)

# Build Sankey node and link lists
admission_types = admissions_dept['admission_type'].unique().tolist()
dept_names = admissions_dept['department_name'].unique().tolist()
outcomes = ['Discharged', 'Still Admitted']

labels = admission_types + dept_names + outcomes
label_idx = {name: i for i, name in enumerate(labels)}

sources, targets, values = [], [], []

# Admission Type -> Department
for _, row in (admissions_dept
    .groupby(['admission_type', 'department_name'])
    .size().reset_index(name='count')).iterrows():
    sources.append(label_idx[row['admission_type']])
    targets.append(label_idx[row['department_name']])
    values.append(row['count'])

# Department -> Outcome
for _, row in (admissions_dept
    .groupby(['department_name', 'outcome'])
    .size().reset_index(name='count')).iterrows():
    sources.append(label_idx[row['department_name']])
    targets.append(label_idx[row['outcome']])
    values.append(row['count'])

fig = go.Figure(go.Sankey(
    node=dict(label=labels, pad=20, thickness=20),
    link=dict(source=sources, target=targets, value=values)
))
fig.update_layout(title_text='Patient Flow: Admission Type → Department → Outcome',
                  font_size=12, width=1000, height=600)
fig.write_html('patient_flow_sankey.html')
fig.show()

Excel Dashboard

Build an operational dashboard that hospital administrators can monitor daily.

Key Insights

Quiz