IE 6600 Data Computation and Visualization Spring 2022
Embedded Files
Analysing COVID-19 Nursing Home Data
GROUP 23
Data set used is based on information on COVID-19 reported by nursing homes to the Centers for Disease Control and Prevention's National Healthcare Safety Network (NHSN) COVID-19 Long Term Care Facility Module.
The Biden administration has used the Defense Production Act to put N95 and other medical supplies at the top of the priority list. Despite factory attempts to produce billions more hospitals in the United States remain concerned about their supply.
Through our tasks, we provide a state level analysis on the shortage of support and staff and availability of supplies.
Index
Index
Introduction
Introduction
The data collected consists details from a State, its particular county, the various service providers or nursing homes on a particular end of the week.
Here we have summarized the number of unique nursing homes in each State. We see that generally, the number of nursing homes increases with the number of counties and the population of the State. Texas being the highest while DC and Guam have the lowest.
Data pre-processing
Data pre-processing
Columns used
Columns used
Week Ending
Federal Provider Number
Provider Name
Provider Address
Provider City
Provider State
Provider Zip Code
Provider Phone Number
County
Submitted Data
Shortage of Nursing Staff
Shortage of Clinical Staff
Shortage of Aides
Shortage of Other Staff
Gloves No Longer Available in 7 Days
Alcohol-Based Hand Rub (ABHR) No Longer Available in 7 Days
N95 Respirator No Longer Available in 7 Days
Face Masks No Longer Available in 7 Days
Eye Protection No Longer Available in 7 Days
Steps taken
Steps taken
Created two data sets from the master data set given at https://data.cms.gov/covid-19/covid-19-nursing-home-data to complete the required tasks.
-DATA SET ONE-
Calculated Total Number of Providers in a week in a Provider State.
Eg: Texas had 1217 total number of providers initially, which changed to 1216 and 1215 at different points in time.
Calculated shortage in any given week for a particular entity by using count where 'Y' value was given.
Then found percentage of nursing homes with shortage in any given week for a particular entity using total number of providers.
Based on this, created two visualizations-
Time series of percentage of count of each parameter to show week-wise distribution with filters for each State for a particular type of shortage.
Created a Choropleth of percentage of count of each parameter to show week-wise distribution with filters for each State for a particular type of availability.
-DATA SET TWO-
Calculated Total Number of Providers in a week in a Provider State.
Calculated availability in any given week for a particular entity by using count where 'N' value was given.
Then found percentage of nursing homes with availability in any given week for a particular entity using total number of providers.
Based on this, created two visualizations-
Time series of percentage of count of each parameter to show week-wise distribution with filters for each State for a particular type of availability.
Created a Choropleth of percentage of count of each parameter to show week-wise distribution with filters for each State for a particular type of availability.
Assumptions
Assumptions
Excluded the rows where 'Submitted Data' value is 'N'.
Excluded Alaska and islands in the graph to maximize the main graph area.
We have considered our version of threshold for defining shortage and availability by studying the given data.
Task 1 : State level analysis on shortage of aides, clinical staff, nursing staff and other staff
Task 1 : State level analysis on shortage of aides, clinical staff, nursing staff and other staff
The data consists information for shortage of resources from 2020 to 2022.
Looking at the trend it is considered that the shortage of more than 20% of shortage, signals a dire lack of resources in a particular state for that particular week.
The analysis below is done considering the benchmark as - Percentage of Nursing homes having shortage is more than 20 percent on average over the period of 3 years. It is used to determine if the individual state has a shortage at any given point. It is seen that the count of states with shortage of aides is 30 states while there is no shortage of clinical staff when scaled over time and across states in comparison with others.
Task 2 : State level analysis on availability of eye protection, gloves, hand sanitizer, N95 masks and surgical masks
Task 2 : State level analysis on availability of eye protection, gloves, hand sanitizer, N95 masks and surgical masks
The data consists shortage information from 2021 to 2022.
We see that when all states are considered, the percentage of availability of the various supplies is more than 90%.
Conclusion
Conclusion
Minnesota had the highest shortage in Nursing Staff in the start of 2022.
Puerto Rico had the most shortage of Clinical Staff from the end of 2021 to 2022.
Maine , Minnesota , New Hampshire see the most shortage in other staff occasionally from the end of 2021 to 2022.
Alaska had the highest shortage for two consecutive weeks in October 2021 and January 2022.
Shortages are encountered when there is a surge in cases and the medical facilities are not ready for it or when a saturation has reached and it becomes difficult for them to take care of the supplies.
Team
Team
Akshit Goyal
Akshit Goyal
MS in Data Analytics '23
Apurva Nandgaonkar
Apurva Nandgaonkar
MS in Data Analytics '23
Pearl Pullan
Pearl Pullan
MS in Engineering Management '23
Google Sites
Report abuse