STAFFING DISTRIBUTION
Strategically assigned key medical staff ahead of the next influenza season using Excel, VLOOKUP, and Tableau for data visualization.
​
Overview
Project:
-
Role: Data Analyst
-
Project Scale: Two Months
-
Stakeholders: CareerFoundry and Staffing Agency
​
Tools used:
-
Excel
-
VLOOKUP
-
Tableau
Introduction
Problem:
The United States has an influenza season where more people than usual suffer from the flu. Some people, particularly those in vulnerable populations, develop serious complications and end up in the hospital. Hospitals and clinics need additional staff to adequately treat these extra patients.
​
Goal:
Examine trends in influenza and proactively plan for staffing needs across the country. Find areas with more vulnerable populations to send extra staff during influenza season.
Approach
Before deciding when, where, and how many staff members to deploy across the US, I completed the following tasks:
​
-
Cleaned and ensured consistency across the five datasets provided by the staffing company.
-
Identified, merged, and analyzed the two most critical datasets: CDC Influenza Deaths and Census Population Data.
-
Assessed which vulnerable groups require additional attention during influenza season.
-
Determined the key factors indicating whether a state needs more or less supplemental staff.
​
​
Additionally, I identified the key success factors for the company:
​
-
A staffing plan that efficiently deploys all available agency staff in line with state requirements, without the need for extra resources.
-
Maintaining minimal occurrences of both understaffing and overstaffing across states (understaffing is defined as a staff-to-patient ratio below 90% of the required level, and overstaffing as above 110%).
​​​
​
Project assumptions:
​
-
Vulnerable populations suffer the most-severe impacts from the flu and are the most likely to end up in the hospital.
-
Flu shots decrease the chance of becoming infected with the flu.
​
Project constraints:
​
-
The staffing agency has a limited number of nurses, physician assistants, and doctors on staff.
-
There’s no money to hire additional medical personnel.
Cleaning the Data
Changes made to the dataset 'CDC Influenza Deaths'
-
Year 20133 was changed to 2013.
-
Changed 144 counts of state abbreviations to full state names.
-
Changed 144 counts of NA to District of Columbia.
-
Changed suppressed data to a random number from 0-9 to account for missing data numbers.
-
Combined age groups <1 and 1-4 to match Census data.
-
Removed data deaths with unstated age.
-
Combined state and year into its own column.
Changes made to the dataset 'Census Population'
-
Separated State and County names
-
Combined age groups to match CDC data set in 10 year age groups
-
Removed Puerto Rico Since there was no data in the other data set
-
Combined Population from each county into a combined state population for each year
-
Combined state and year into its own column
Hypothesis
After Thorough analysis and merging datasets using VLOOKUP, the following hypothesis was made:
​
​
​
If the influenza infected person is 65 years or older, then the probability of dying from influenza is higher.
​
​​​
At an alpha of 0.05 (or confidence level of 95 percent) there’s a significant correlation between age and risk of death from influenza. With this we reject the null hypothesis and conclude that persons who are 65 years or older are at higher risk of death from influenza than those under the age of 65 years.
Datasets Used
After consideration, only two data sets were deemed necessary and were merged for this analysis:​​​​
Census Population
This data set is provided by the US Census Bureau and provides population count by state and county from 2009-2017.​​​
CDC Influenza Deaths
Provided by the Center for Disease Control, this dataset shows the count of deaths from influenza by geography, time, age, and gender from 2009-2017.
Remaining Analysis
-
Now that the vulnerable population has been confidently identified, we can figure out how to send staff accordingly.
-
Find which states have a larger vulnerable population, categorize states accordingly.
-
Allocate staff according to state category based on vulnerable population.
Next Steps
-
Put together visualizations showing the death rate from influenza for this vulnerable population.
-
Final Presentation with data and statistical analysis to support hypothesis. Excel and Tableau used for the Final Presentation.
Final Project
Final Recommendations
The staffing company should focus on states with higher counts of the elderly population, especially California and New York. Other factors to consider are hospitals that are already short staffed, and other vulnerable populations at risk.
Reducing death rates from influenza means focusing on prevention: staffing hospitals and clinics properly, vaccinations for healthcare workers and patients, and frequent (and correct) hand washing for everyone.
Project Reflections
Working with data from 2009-2017 gives us enough information to forecast a typical flu season for the next 2-3 years. That being said, as viruses develop and evolve, we must try to keep up with data collection to help.
Although these datasets were helpful (and absolutely necessary) for this forecast, they are not completely comprehensive. With countless risk factors to consider, it's nearly impossible to analyze every aspect that may affect a population. Pre-existing conditions, family health history, overall health of the patients, air pollution, economic status, etc. will all have an impact on a patient's health and outcome during influenza season. Working with as much data as possible will ensure the most accurate results.
Making recommendations and putting a standard of care in place with hospitals and clinics can only help so much. Promoting public awareness about the importance of health, hygiene, and vaccinations will help the general public prepare for influenza season as well.
