Back

Hospital Patients Record

Introduction

Patients medical records are essential documents that outline a patient's medical history. It holds vital information abouts their previous diagnosis, procedures, prescription and more.

Analysis of this records provides insights into hospital performance, cost saving procedures, and optimization of cost.

This project analyzes a synthetic data of approximately 1000 patients to provide insight into recent hospital performance

Explore Dashboard

Project Objectives

  • To use Power Query to explore, clean, engineer new features and analyze the data
  • To build an interactive dashboard in Power BI that shows hospital KPI’s and provide insights into hospital performance

Project Links

Power BI

.

Tools used

  • Microsoft Power BI

Skills Utilized

  • Data Cleaning
  • Feature Engineering
  • Data Analysis
  • Data Visualization
  • Dashboard & Storytelling

Insights

Insight 1

Introduction

  • The hospital had a total of 974 patients from 2011 – 2022.
  • 51% are male while 49% are female
  • 70% of the patients are white, 17% are black, the rest were Native, Asian and Hawaiian
  • About 55% of the patients are from the city of Boston. Quincy city ranked second with about 8.2% of the population.

Insight 2

Encounter Analysis

  • The hospital had a total of 27,891 visits from 2011 – 2022
  • The average visit hour is 7 hours
  • The top two reasons for visits are ambulatory (45%) and outpatients (23%)
  • Year 2014 and 2021 had the highest number of visits at more than 300 visits. it remained just above 2000 visits for other years
  • About 75% of patients who visited were Elderly or senior citizens

Insight 3

Admission Analysis

  • A total of 153 patients have been admitted from 2011 – 2022 resulting in 1,153 admissions
  • 72 patients out of the 153 admitted were re-admitted during the period with re-admission rate at 47%
  • The average length of stay for inpatients is two(2) days
  • The minimum length of stay is one day, while the maximum is 344 days resulting from patients with third degree burns.

Insight 4

Procedure Analysis

  • A total of 47,701 procedures have been performed from 2011 – 2022
  • There are 163 unique procedures performed
  • The average time per procedure is about 33 minutes
  • The top three most performed procedures are the assessment of health and social care needs, hospice care and depression screening.

Insight 5

Insurance Analysis

  • There are a total  of 9 insurance payers.
  • Medicare rank top as the most used payer with about 11,371 usage
  • Out of the 47,701 procedures, only 12,110 are covered by insurance.
  • Medicare rank top covering 62% of the cost, followed by Medicaid who covered 27% of the cost.

Insight 6

Financial Analysis

  • The total claim is $101,514,375.52 with average cost per visit at $3,639
  • The total procedure cost is $105,517,711 and average cost per procedure is $2,212
  • Insurance covered a total of $31,097,506.99 while patients paid a total of $70,416,868.53
  • 34% of revenue generated comes from electrical cardioversion procedure. Other procedures contributes less than 5% to the revenue.

Recommendation

  • 70% of reasons for visit were not indicated. This shows the need to improve on the documentation of the hospital visits.
  • About 75% of procedures were not covered by insurance and this resulted in high out of pocket expenses for patients. There is need to sensitize the patients about subscribing to an health insurance scheme to help cover more procedures and lessen their expenses.