Healthcare Patient Waiting List Analysis

Summary

This project transforms raw healthcare waiting list data (2018-2021) into actionable insights using Power BI. My objective was to improve resource allocation, enhance patient satisfaction, and enable proactive planning by analyzing key metrics like average median waiting times and total waitlists. The analysis provides granular breakdowns by patient category (Inpatient vs. Outpatient), specialty, and age profile, empowering administrators to make data-driven decisions that alleviate service pressure and enhance operational efficiency.

1. Introduction

Long patient waiting lists are a critical challenge globally, impacting patient outcomes, operational efficiency, and satisfaction. Without robust analysis, healthcare providers struggle to understand trends, identify bottlenecks, and strategically allocate resources. This project addresses this by analyzing a publicly available dataset focusing on Inpatient (admitted) and Outpatient (no admission) categories. My analysis aimed to provide a holistic view of demand and pinpoint areas for intervention.

2. Project Objectives & Goals

My project aimed to provide a comprehensive understanding of patient waiting lists through clear objectives:

Objectives:

• Track current patient waiting list status for an up-to-date overview.
• Analyze historical monthly trends for Inpatient and Outpatient categories to identify patterns.
• Conduct detailed specialty-level and age profile analysis to uncover demand hotspots and demographic impacts.

Key Deliverables:

1. Current Waitlist Tracking: A dashboard system accurately displays the "Latest Month Wait List" (e.g., 640K patients) against "PY Latest Wait List" (e.g., 709K patients) for immediate comparison.
2. Historical Trend Analysis: In-depth time-series analysis (2018-2021) visualizes "Monthly Trend - Day Care/Inpatients vs Outpatients," highlighting fluctuations like the notable rise in Outpatient waitlists towards late 2020/early 2021 for forecasting.
3. Granular Specialty & Age Profile Insights: Detailed analysis segments waitlists by medical specialty and age groups. The "Time Band VS Age Profile (Average)" chart visually represents wait times across demographics (0-15, 16-64, 65+) and time bands (e.g., 18+ Months, 0-3 Months), identifying age groups disproportionately affected. (e.g., 16-64 age group notable in "18+ Months").

3. Data Scope & Metrics

My analysis used a publicly available healthcare dataset from 2018 to 2021, providing a four-year window for trend identification and assessing waiting list evolution.

Key Metrics & Insights:

• Average Median Waiting List: Calculated and visualized as a robust measure of typical waiting duration.
• Current Total Wait List: A critical operational metric prominently displayed with comparisons to previous periods.
• Case Type Split: Dashboard clearly shows Outpatients as the majority (e.g., 72.49%), followed by Day Cases (e.g., 16.89%) and Inpatients (e.g., 10.62%), crucial for understanding demand.

4. Dashboard Design & Key Insights (Based on Visual Analysis)

My Power BI dashboards provide both high-level overviews and granular analytical capabilities:

4.1 Summary Page (Main Dashboard View)

The dashboard summary page offers immediate insights:

• Header Filters: Include "Calc Method" (Average, Median), "Specialty Name" (All), "Case Type" (All), and "Archive Date" (1/31/2018 - 3/31/2021) for interactive drill-down.
• Total Wait List Comparison: Displays "Latest Month Wait List" (640K) vs. "PY Latest Wait List" (709K) for instant performance comparison.
• Case Type Split: Doughnut chart vividly shows Outpatient cases dominating (72.49%), followed by Day Cases (16.89%) and Inpatients (10.62%)—crucial for resource planning.
• Time Band VS Age Profile (Average): Stacked bar chart breaks down waitlist numbers across age profiles (0-15, 16-64, 65+) and time bands (e.g., 18+ Months, 0-3 Months), identifying age groups disproportionately affected. (e.g., 16-64 age group notable in "18+ Months").
• Top 5 Specialty: Concise list highlights specialties with highest average waitlist numbers (e.g., "Paediatric Dermatology" (168), "Paediatric ENT" (148)) for demand identification.
• Monthly Trend - Day Care/Inpatients vs Outpatients: Line chart visualizes temporal trends, showing stable Inpatient/Day Case trends but a significant Outpatient increase (late 2019 through 2020 into 2021, reaching 629K), vital for forecasting.

4.2 Detailed Page for Granular Analysis

This page facilitates in-depth exploration, featuring:

• Further drill-down on specialties to view waitlist distribution across age, time bands, and monthly trends.
• More in-depth analysis of patient demographics or specific procedures contributing to wait times.
• Potential for anonymized patient-level details to support operational decisions.

5. Technical Approach & Tools

I developed this project using a structured data analysis pipeline:

• Data Cleaning & Preprocessing: Ingested and meticulously cleaned raw public dataset, handling missing values, inconsistencies, and preparing it for analysis.
• Exploratory Data Analysis (EDA): Conducted extensive EDA with statistical summaries and preliminary visualizations to understand data structure, distributions, and initial patterns.
• Data Modeling & Visualization (Power BI): Leveraged Power BI for:
  • Data Modeling: Established robust relationships between tables for flexible analysis.
  • DAX Calculations: Developed custom measures (e.g., "Average Median Waiting List") and calculated columns for complex comparisons.
  • Interactive Dashboard Creation: Designed visually appealing and interactive dashboards, enabling dynamic filtering and drill-down for tailored insights.

6. Recommendations for Improving Patient Flow

Based on the insights derived from this analysis, here are key recommendations to optimize hospital operations and improve patient flow:

• Target Bottleneck Specialties: Prioritize resource allocation and process improvements for specialties consistently showing the highest wait times (e.g., Paediatric Dermatology, Paediatric ENT). This might involve increasing staff, optimizing scheduling, or reviewing specific procedural steps.
• Optimize Outpatient Scheduling: Given the significant increase in Outpatient waitlists, reassess current outpatient scheduling models. Consider implementing dynamic scheduling, virtual consultations where appropriate, or expanding clinic hours during peak demand periods.
• Leverage Age-Specific Insights: Use the age profile analysis to tailor interventions. For instance, if a specific age group faces disproportionately long waits for certain procedures, explore dedicated clinics or specialized support for that demographic.
• Enhance Patient Communication: Implement proactive communication strategies to keep patients informed about their waitlist status and estimated waiting times. Clear communication can manage expectations and improve patient satisfaction even when waits are unavoidable.
• Continuous Monitoring & Feedback Loop: Establish a system for continuous monitoring of the dashboard's key metrics. Regularly review trends and implement a feedback loop with clinical and administrative staff to identify emerging issues and assess the effectiveness of implemented changes.
• Investigate Root Causes of Delays: For persistent long waits, conduct deeper dives into the underlying reasons. Is it resource unavailability, lack of specific equipment, staff shortages, or inefficiencies in patient handovers? Addressing these root causes is crucial for sustainable improvement.

7. Impact & Business Value

This "Healthcare Patient Waiting List Analysis" project delivers significant value to healthcare administrators and policymakers by providing:

• Optimized Resource Allocation: Identifies high-demand specialties and wait time distribution by age, enabling strategic allocation of resources (staff, beds, equipment) to reduce bottlenecks.
• Improved Patient Experience: Insights into long wait times allow for targeted interventions, shortening queues, improving patient flow, and enhancing satisfaction.
• Proactive Planning: Historical trend analysis forms a solid foundation for forecasting future demand, enabling proactive planning for staffing, infrastructure, and policy to prevent backlogs.
• Data-Driven Decision Making: Equips stakeholders with objective, evidence-based insights, shifting decision-making from intuition to a more informed, strategic approach for operational and long-term planning.

8. Conclusion

This project showcases my strong command of data analysis, robust Power BI skills, and understanding of healthcare challenges. By transforming complex waiting list data into clear, interactive, and actionable insights, the dashboards serve as a powerful tool for monitoring, analyzing, and improving patient access to care, highlighting data's critical role in modern healthcare management.