Preparing For Influenza Season
Goal:
Analyze flu season across US states and seasonal trends in order to support an effective medical staffing plan for the staffing agency.
​
Project Overview:
The United States has an influenza season where more people than usual suffer from the flu. Vulnerable populations, develop serious complications and end up in the hospital. Hospitals and clinics rely on medical staffing agencies to provide additional staff to adequately treat these extra patients.
Objective and Scope:
The objective of this project is to develops a data-driven staffing strategy for a medical staffing agency, enabling them to meet the increased demand for medical staff during the influenza season. The scope of the project covers hospitals in each of the 50 states of the United States.
​
Tools:
Excel: Data exploration, cleaning, integration, and statistical analysis.
Tableau: Data visualization and storytelling.
My Role:
As a Business Intelligence Analyst, I managed the project from data collection to presenting the final insights. I ensured data integrity, performed statistical analyses, created visualizations, and developed recommendations to support stakeholders' decision-making.
​
​
​
​
Data Overview
Data Sets:
​
Influenza deaths by geography
Source: CDC through their National Center for Health Statistics
Population data by geography, time, age, and gender
Source: US Census Bureau
Influenza Laboratory Tests
Source of Data: Center of Disease Control (CDC)
​
Skills and Techniques:
Translating business requirements
Data cleaning and profiling
Data Quality Checks
Data transformation and integration with VLOOKUP
Statistical hypothesis testing
Visual analysis
Insight development
Data visualization on Tableau
Presentation development
Storytelling
Presenting results to an audience
​
Key Business Questions:
Higher influenza mortality with age?
Where in the USA are additional medical staff most likely needed?
When in the year do we need additional medical staff?
​
Data Prep and Exploration
Data Profiling and Integrity
​
Exploratory Analysis: Understanding data types and variables.
Consistency Checks: Evaluating anomalies, missing values, and duplicates.
Quality Checks: Ensuring data timeliness and completeness.
Data Transformation and Integration
​
Mapping and Integration: Using pivot tables and VLOOKUP for data integration.
Statistical Analysis: Calculating variance, standard deviation, and correlation coefficients. Identifying outliers with 2 standard deviations.
Statistical Hypothesis Testing
​
Research Hypothesis: “If state has more population aged over 65 years, then the chances of influenza deaths (mortality) increase thus increasing the staffing needs in that state.”
Null Hypothesis: “The total influenza deaths for population aged over 65 years is less or equal to the total influenza death for the population aged between 5-64 years”
​
p-value Calculation: Using a one-tail test, the p-value was less than 0.05, allowing us to reject the null hypothesis with 95% confidence. This indicates that the elderly population over 65 years are more likely to die from influenza and its complications, necessitating increased staffing.
Analysis, Insights and Visualizations
During this phase, analysis and documentation was completed to build out the key insights that would contribute to the conclusions and recommendations for stakeholders.
​
Key Insights:
State-Level Mortality: Identified states with the highest influenza mortality rates, correlating with a higher population of individuals aged over 65 years.
​
Highest
California: 47,483
New York: 36,576
Texas: 22,140
Lowest
Vermont: 20
Wyoming: 76
Delaware: 134
Seasonal Trends: Analyzed seasonal patterns, pinpointing peak influenza periods to anticipate staffing needs.
​
Highest:
January
February
March
Lowest:
June
July
September
​
Regions with Deaths over 65 years: Visualized geographical distribution by region of influenza mortality over65 years.
​
​
Mortality rates and population demographics
Recommendations
Prioritized States for Additional Medical Staff Deployment in the Upcoming Influenza Season
​
States with High Vulnerable Population (> 65 years) = Higher Influenza Death Counts
In priority order: California, Texas, Florida, New York, Pennsylvania, Illinoise, Ohio, Michigan, North Carolina, Georgia, Tenesse
​
States with colder, longer winters = High Influenza Deaths
Influenza is shown to peak during the winter months therefore colder states have a higher influenza mortality rates
North Dakota, Minnesota, Wyoming, Montana, Maine, Wisconsin, Idaho
Storytelling and Presentation
Using Tableau, I created an interactive storyboard that conveyed the project findings and recommendations effectively. The presentation was structured to guide stakeholders through the data analysis process, insights, and actionable recommendations.
​
​
​
A video presentation for stakeholders was made to provide an update on current progress.
​
​
​
​
​
Next Steps to look into were uncovered
Staff-to-Patient Ratio Analysis: Determine the optimal number of additional staff required per state based on patient inflow projections.
Vaccination Efficacy Study: Analyze vaccination rates and their impact on reducing influenza-related mortality.
Exposure Minimization: Evaluate and implement measures to reduce influenza virus exposure in high-risk areas.
​
