Hospital Length of Stay Project

Background:

Data analytics offers a solution to optimizing performance and strategic decision-making for organizations. Understanding what has happened within an organization and why it has happened, allows for the opportunity to predict what will happen next. These predictions are important for improving upon current processes and for making well-informed decisions in the process of planning for operation needs and the allocation of resources.

One of the many areas data analytics can be applied to is healthcare. Applying data analytics to hospital management can help improve operational efficiency, the quality of patient care, and the overall satisfaction of both patients and employees. For instance, having an accurate prediction of inpatient length of stay can help hospitals better manage the inflow of patients by ensuring sufficient staffing and resources. This prevents the problem of understaffing which is detrimental to providing quality care to patients and to the wellbeing of healthcare workers. A prediction of an individual patient’s length of stay based on information taken from their clinical history and vitals is also valuable because this can prevent a patient from being discharged too quickly or stay for longer than needed. Readmission can be prevented if a patient who is at risk for complications or severe disease is not discharged too soon.

I created this project with the goal of analyzing a healthcare data set in order to apply my knowledge of SQL and PowerBI to further develop my technical skills with these tools. I wanted to put myself in the shoes of a healthcare data analyst by exploring potential questions that stakeholders would appreciate insights into. As a healthcare worker myself, it was interesting to gain perspective of the bigger picture with regards to hospital management.

After acquainting myself with the “Predicting Hospital Length of Stay” data set, I came up with the following questions for my exploratory analysis:

1. What is the average length of stay (LOS)?
   • Across all facilities vs. each facility
   • Monthly vs. annually
2. What is the admission rate compared to the discharge rate?
   • Are the facilities at or near capacity?
   • Which facilities have extra beds?
3. What relationships affect (increase) LOS?
   • Readmission
   • Secondary diagnosis
   • Type of treatment and other conditions that stand out?
4. As a stakeholder, why is it important to determine and predict LOS?
   • What is the significance of increased LOS?

The Data:

The “Predicting Hospital Length of Stay” data set was obtained from GitHub and includes two tables in csv format, as well as a data dictionary.

‘LengthOfStay.csv’ consisted of 100,000 rows of unique ID representing hospital admission and 26 columns containing demographics.

‘MetaData_Facilities.csv’ consisted of the unique facilities and their respective capacity.

This data set was generated by a third-party for use as a template for Microsoft Machine Learning Services’ initiative to develop a predictive model for length of stay (LOS) that hospitals can deploy.

This data set and initiative stood out to me because it gives health care facilities the ability to implement machine learning in a customized manner, where each facility’s specific history, trends, and needs can be considered when formulating a prediction for LOS. Each facility is provided with a personalized solution rather than having to applying generalized solutions and strategies for improvement efforts. A solution that fits one facility may not be optimal for another facility.

Though I am solely performing analysis to explore and understand the LOS trends within this template data set, I find the overarching goal of this initiative very valuable because it can solve real-world problems in health care management.

Assumptions:

It is important to note the following assumptions that I made about the data set due to missing information:

• All admissions were adults since age was not recorded
• Every row represents a new, unique encounter but does not mean a unique patient, since many patients have been readmitted
• Readmissions are within 180 days of discharge according to data dictionary (normally within 30 day of discharge)
• Data set was taken from the US because the average LOS (ALOS) is closer to the reported US average of 5.5 days than the Canadian average of 7.2 days
• ALOS does not include childbirth data
• Discharges are not due to death
• Vitals were taken at the beginning of the encounter because the time and frequency of these measurements were not indicated
• There is no baseline of facility capacity since the data set is a snapshot of one year’s data and rolls into the subsequent year to follow through with discharges

Missing information that could prove to be valuable in predicting LOS includes age, location, condition at discharge, type of treatment administered, and social factors.

Analysis:

Analysis was performed using the online SQL editor CSVFiddle and visualizations were created in PowerBI, which can be viewed here.

**SQL Queries Used:**

• Commands
  • SELECT (FROM, *, WHERE, AND, OR, BETWEEN, LIKE)
  • GROUP BY
  • ORDER BY
• Aggregate functions
  • SUM, COUNT, AVG, MIN, MAX
• Joins
  • INNER JOIN

**PowerBI Tools Used:**

• Get data from csv file
• Create relationships
• Create visualizations
  • Column charts, bar charts, line charts, pie charts, cards
• Create new columns
• Create new measures - writing DAX code
  • If statements, COUNTROWS, COUNTX, FILTER
• Create new table
• Using slicers
• Design themes

I initially used SQL queries to quickly extract information from the data set to get a better understanding of the data that I was working with. For example, I determined the average, minimum, and maximum number of days of patient LOS at each facility using the following query.

I used the following query to determine how often a secondary diagnosis is associated with LOS greater than the average ~5 days, alternatively between 5 and 17 days. Note that secondary diagnosis with values 1, 2, or 3 were relevant as determined from diagnosis types and definitions standards.

When I attempted to explore relationships such as the aforementioned, I learned that there are limitations to using SQL. It was difficult to quickly assess and understand relationships when the output contained too many rows. I turned to using PowerBI to easily visualize the relationships that I wanted to explore using column charts, bar charts, line charts, and pie charts. Here is a link to my PowerBI project which includes dashboards for visualizing this data.

Another important lesson I learned, is ensuring data quality because any discrepancies can affect the analysis and impact the resulting insights. It is vital for the data to be accurate at every step and presented correctly. It was useful to practice quality checks using SQL queries while working in PowerBI. It can be as simple as confirming the number of rows of data (or cases).

Another example was confirming that the data represented in my bar chart is accurate using the following SQL query.

**DAX Code Used:**

ALOS_Behavioral1East = CALCULATE(AVERAGE(LengthOfStay[lengthofstay]), LengthOfStay[facid] = "E")

Lengthofstay_shortorlong = IF(VALUE('LengthOfStay'[lengthofstay]) > 5, "Long (>5 days)", "Short (<5 days)")

Readmissioncount = COUNTROWS(FILTER(LengthOfStay, LengthOfStay[Readmission] = "Yes"))

SecondaryDiagnosisWithAdmission = IF(VALUE(LengthOfStay[secondarydiagnosisnonicd9]) = 1 || VALUE(LengthOfStay[secondarydiagnosisnonicd9]) = 2 || VALUE(LengthOfStay[secondarydiagnosisnonicd9]) = 3, "Yes", "No")

Through my analysis and project planning, I learned many lessons about data analysis and visualization. Understanding the data with respect to how was collected, the context surrounding it, its limitations, and how it can answer my questions is important for the analysis process. It is also important for recognizing and correcting any discrepancies in the data. It is crucial to perform accurate analysis in order to provide the most accurate information and insights to stakeholders since this impacts downstream decision-making.

I also furthered honed my skills in SQL and PowerBI. My SQL takeaways are that there are many ways to write a query to obtain a particular output. Though it is a useful tool for quick retrieval of data, it does not provide easy visualization of large data outputs. PowerBI on the other hand, is effective for creating easily digestible visualizations to illustrate relationships and trends in the data. Choosing the appropriate visualization to best represent the data that answers the question at hand is important for leveraging the utility of PowerBI.

As with any project, keeping your goal or the question you would like to answer in mind is important. Asking myself questions such as “Is what I am doing relevant and going to answer the question?”, “Does this data make sense?”, “Will my audience understand the analysis and what these results mean for them?”.

Completing this project furthered my ability with SQL and PowerBI, where I learned to implement and perform quality control practices during my analysis. I was successful in troubleshooting bugs that I came across by understanding the error messages or issue, correcting it to my best ability, and determining whether the solution made sense or not. I utilized online resources such as Stack Overflow and Microsoft Fabric/PowerBI community, if further work was required.

Insights:

1. The average LOS (ALOS) across all facilities is 4 days compared to 5.16 days, 4.89 days, 4.83 days, 3.28 days, and 3.27 days at Behavioral 1 East, General Medicine 3 South, Geriatrics 2 East, Pulmonary 2 West, General Medicine 3 West, respectively. ALOS at each facility remained fairly consistent at each respective facility month to month and overall in 2012.

   Significance: Knowing which facilities within the health authority have longer LOS can shine the spotlight on them. They have the opportunity to recognize their performance and improve upon it by reviewing and continuing to monitor trends. Longer than ALOS may indicate inefficient care, suboptimal care, wrong treatment, or delayed diagnosis.

2. The admission rate versus the discharge rate was challenging to determine because there was no baseline of the starting capacity of each facility. However, when simply comparing the admission and discharge numbers side-by-side on a daily basis within one facility in the span of a month, a fair amount of fluctuation in the numbers was observed. (If anyone has advice on how to approach this problem, please let me know!)

   Significance: Reviewing this comparison is important to determine the number of beds available for incoming patients within each facility. Monitoring long term trends can provide insight into patient volume trends. It tells us which overcrowded facilities require more resources to sustain the demand and which facilities have extra resources that are a financial drain. Waitlist management also benefits from this information.

3. Readmission is associated with longer LOS across all facilities. Long term stays (>5 days) are predominantly due to readmissions and this trend is increasingly evident moving toward the maximum days of LOS.

   Similar to the readmission trend, secondary diagnosis is more evident in long term stays across. Notably, secondary diagnosis is present even beginning at 1 day of stay (General Medicine 3 West, Pulmonary 2 West) and 3 days of stay (Behavioral 1 East, General Medicine 3 South). In addition, secondary diagnosis is present in over two thirds of patients within each number of day category.

   Significance: Recognizing high readmission rates could infer that patient follow-up care may be lacking, ineffective treatment, and inadequate care during the initial visit. Abundant secondary diagnosis signifies that patients might have a coexisting condition that may have worsened during their stay if untreated or missed, increasing the LOS and possibly resulting in subsequent readmission.

4. These insights inform stakeholders about the current state of operations and help them make informed decisions about future planning. Seeking ways to appropriately allocate resources, schedule staff, decrease wait times, and reduce readmissions works towards their goal of avoiding inefficient hospital operation and reducing financial burden. Besides these business needs, they want to uphold a high standard of quality for patient care and improve overall patient outcomes.