Dashboard 4 — Hospital Operations

1. Purpose

The Hospital Operations page combines patient movement and clinical procedure data — tracking transfers between care units, event types (admit, transfer, discharge, ED), transfer durations, and procedure volumes by age and ICD code.

Questions this page answers: How many transfer events occur and how long do patients stay in each unit? Which care units see the most traffic? What types of transfer events dominate? How many procedures are performed, and which codes and age groups lead?

Transfer data

vw_transfer_analysis — 1,190 rows from hosp/transfers joined to admissions and patients. One row per transfer event with care unit, event type, and duration.

Procedure data

vw_procedure_analysis — 722 rows from hosp/procedures_icd joined to admissions and patients. One row per ICD procedure code on an admission.

2. KPI Cards

Five headline metrics across the top — three from transfers, two from procedures. All recomputed from CSV exports; exact match with the live dashboard (Power BI rounds 1,190 transfers to 1K).

1,190

Total Transfers

✓ verified (1K in PBI)

100

Transferred Patients

✓ verified

51.22

Avg Transfer Duration (hrs)

✓ verified

722

Total Procedures

✓ verified

Procedure Patients

✓ verified

Card	Value	Source View	DAX
Total Transfers	1,190	`vw_transfer_analysis`	`COUNTROWS(vw_transfer_analysis)`
Transferred Patients	100	`vw_transfer_analysis`	`DISTINCTCOUNT(patient_id)`
Avg Transfer Duration	51.22 hrs	`vw_transfer_analysis`	`AVERAGE(transfer_duration_hours)`
Total Procedures	722	`vw_procedure_analysis`	`COUNTROWS(vw_procedure_analysis)`
Procedure Patients	92	`vw_procedure_analysis`	`DISTINCTCOUNT(patient_id)`

Derived: 4.3 transfer events per admission (1,190 ÷ 275). 8 patients have no procedure records. 187 admissions (68%) have at least one procedure; average 3.9 procedures per procedural admission.

3. Transfer Analysis

Three visuals powered by vw_transfer_analysis.

Visual 1 · Horizontal Bar Chart

Total Transfers by care_unit

Care Unit	Transfers	Share
(Blank)	275	23.1%
EMERGENCY DEPARTMENT	236	19.8%
MEDICINE	77	6.5%
MED/SURG	48	4.0%
NEUROLOGY	46	3.9%
MEDICINE/CARDIOLOGY	43	3.6%
CARDIAC SURGERY	39	3.3%
TRANSPLANT	39	3.3%
MEDICAL INTENSIVE CARE UNIT (MICU)	36	3.0%
DISCHARGE LOUNGE	36	3.0%

Finding: (Blank) care_unit = 275 — these are DISCHARGE events with no assigned unit (one per admission). Excluding blanks, EMERGENCY DEPARTMENT leads at 236 (25.8% of named units). ICU and specialty units (MICU, SICU, CVICU) each contribute 3–4% of named-unit transfers.

Visual 2 · Horizontal Bar Chart

Average transfer_duration_hours by care_unit

Only rows with a non-null duration and named care unit. Sorted by highest average stay.

Care Unit	Avg Duration (hrs)	~Days
MEDICINE/CARDIOLOGY INTERMEDIATE	332.21	~13.8
PSYCHIATRY	195.85	~8.2
HEMATOLOGY/ONCOLOGY	126.45	~5.3
VASCULAR	105.09	~4.4
CORONARY CARE UNIT (CCU)	96.00	~4.0
TRANSPLANT	95.70	~4.0
HEMATOLOGY/ONCOLOGY INTERMEDIATE	89.03	~3.7
MEDICAL/SURGICAL ICU (MICU/SICU)	84.69	~3.5
MEDICAL INTENSIVE CARE UNIT (MICU)	83.09	~3.5
TRAUMA SICU (TSICU)	80.10	~3.3

Finding: Power BI displays 332 hours for Medicine/Cardiology Intermediate — the longest unit stay. Psychiatry (~196 hrs) and Hematology/Oncology (~126 hrs) follow. ED visits average only 3.4 hours (short-stay triage), while ICU-level units cluster around 80–96 hours.

Visual 3 · Donut Chart

Total Transfers by event_type

Event Type	Count	Share
TRANSFER	404	33.95%
DISCHARGE	275	23.11%
ADMIT	275	23.11%
ED	236	19.83%

Finding: Four event types partition the 1,190 records. TRANSFER (34%) is the largest — intra-hospital moves between units. ADMIT and DISCHARGE each equal 275 (one per admission). ED events (236) represent emergency department visits that may not result in full admission.

4. Procedure Analysis

Two visuals powered by vw_procedure_analysis.

Visual 4 · Vertical Bar Chart

Total Procedures by age_group

Age Group	Procedures	Share
50–64	286	39.6%
65–79	208	28.8%
35–49	130	18.0%
80+	62	8.6%
18–34	36	5.0%

Finding: 68.4% of procedures occur in patients aged 50–79 — consistent with Dashboards 1–3. The 50–64 band alone accounts for 39.6%. Male patients have more procedures (425 vs 297 female, 58.9%).

Visual 5 · Horizontal Bar Chart

Total Procedures by procedure_code

Procedure Code	Count	ICD
02HV33Z	23	ICD-10
3897	22	ICD-9
966	18	ICD-9
9671	15	ICD-9
3893	13	ICD-9
3961	13	ICD-9
5491	12	ICD-9
9604	11	ICD-9
3891	10	ICD-9
3E0G76Z	10	ICD-10

Finding: Top code 02HV33Z (ICD-10 — insertion of infusion device) leads at 23. ICD-9 codes dominate the top 10 (3897 = central venous catheter, 966 = enteral nutrition, 9671 = continuous invasive mechanical ventilation). Overall split: ICD-9: 401 (55.5%) · ICD-10: 321 (44.5%). 187 primary procedures (one per procedural admission).

5. Cross-Cutting Analysis

Transfer Volume by Admission Type

Admission Type	Transfer Events	Share
EW EMER.	477	40.1%
OBSERVATION ADMIT	182	15.3%
URGENT	166	13.9%
EU OBSERVATION	97	8.2%
SURGICAL SAME DAY ADMISSION	80	6.7%

Patient Coverage

All 100 patients appear in transfer data. Only 92 patients (92%) have procedure records — 8 patients had admissions with no coded procedures in this subset.

Operational Flow

The transfer event lifecycle per admission: ADMIT (275) → intra-hospital TRANSFERs (404 total, ~1.5 per admission) → DISCHARGE (275), with ED visits (236) representing a parallel emergency pathway. High TRANSFER count relative to admissions indicates active bed management and specialty unit routing.

6. DAX Measures

Five measures on this page (two views). Full reference: powerbi/mimic_powerbi_dax_measures.md

Total Transfers = COUNTROWS(vw_transfer_analysis)

Transferred Patients =
DISTINCTCOUNT(vw_transfer_analysis[patient_id])

Avg Transfer Duration =
AVERAGE(vw_transfer_analysis[transfer_duration_hours])

Total Procedures = COUNTROWS(vw_procedure_analysis)

Procedure Patients =
DISTINCTCOUNT(vw_procedure_analysis[patient_id])

7. Key Takeaways

1,190 transfer events — 4.3 per admission; Power BI rounds to 1K. All 100 patients have transfer records.
Event mix — TRANSFER (34%), DISCHARGE (23%), ADMIT (23%), ED (20%).
ED dominates named units — 236 ED events; blank care_unit = discharge records.
Longest unit stays — Medicine/Cardiology Intermediate (~332 hrs), Psychiatry (~196 hrs).
722 procedures — 92 patients, 187 admissions; 8 patients without procedures.
50–64 leads procedures — 286 (39.6%); aligns with older adult cohort from other dashboards.
ICD-9/10 mix — catheter, ventilation, and nutrition codes dominate top 10.

8. Verification

import pandas as pd
tx = pd.read_csv("data/databricks_output/exports/vw_transfer_analysis.csv")
pr = pd.read_csv("data/databricks_output/exports/vw_procedure_analysis.csv")
assert len(tx) == 1190
assert tx["patient_id"].nunique() == 100
assert round(tx["transfer_duration_hours"].mean(), 2) == 51.22
assert len(pr) == 722
assert pr["patient_id"].nunique() == 92

Output: data/databricks_output/exports/hospital_operations_analysis.json