With COVID-19 on the rise hospitals have become overloaded with patients. Along with staff, it is highly important to know the number of beds and their utilization rate to better serve the patients. In order to get the utilization rate of beds in each hospital for each state, it is important to know the number of licensed beds operated by each state and the type of hospital, to treat and address the patient needs during COVID-19. Hence the graphs using AWS QuickSight are visualized and analyzed based on the Hospital type, number of available licensed beds grouped by each US State (Color Coded).
What it does
This project visualizes and analyses data and presents data in visual format to describe the number of licensed beds available for each Hospital type in each US state. It also shows the potential increase in bed capacity for each state & average ventilator usage and licensed beds for each state. The project also shows the visual representation of data that is filtered based on the Hospital type and the highest ventilator usage for the hospital type.
How I built it
I subscribed to "USA Hospital Beds - COVID-19 | Definitive Healthcare" delivered by “Rearc” using Amazon AWS Data Exchange. New datasets are added every day for this product, so I created a CloudFormation distribution from an existing template to update the data in S3 from AWS Data Exchange. The CloudFormation created a stack by creating resources like S3 bucket to store the hospital bed data, Lambda functions to manage daily update of data in the S3 bucket, IAM Permission Roles and events. The datasets that are in .CSV and .GeoJSON format is extracted from S3 and tables are created using Extract, Transform, Load function and crawlers in AWS Glue. I created a trigger "ETL_Trigger_COVID_Beds" and scheduled a job "JOB_ETL_COVID_BED" to run every once a week to update the table contents. The job uses a python script to map the fields from S3 to Athena. The database "database-usa-hospital-beds" with table name "hospitalusa_hospital_beds_csv" is created in Athena. For data visualization and analysis, I used AWS QuickSight. The input data for generating visualizations in QuickSight is from Athena.
Challenges I ran into
There were number of decision factors to consider apart from challenges.The bed utilization is updated every day and hence the need and the decision to use AWS CloudFormation . Also, the hospital data needed to be filtered and color coded to understand the bed utilization rate of all the hospitals in each state . So the use of filters is pivotal to know important questions such as the highest number of licensed beds available for a hospital type in a single state.
Accomplishments that I'm proud of
Not only learnt new topics in AWS and used the data visualization tools in QuickSight. Having subscribed to "USA Hospital Beds - COVID-19 | Definitive Healthcare" by "Rearc", learnt to use data to visualize, interpret in QuickSight and use data to make informed decisions.
What I learned
Apart from the technical stuff, also learnt about the various Hospital types and the different types of hospital beds that can help hospitals to visualize and analyze data to utilize beds effectively during the pandemic times.
What's next for Utilization chart of hospital Beds during Covid19
To use forecasting and insights to predict the hospital beds for each hospital based on the number of patients in the hospitals and patients scheduled to exist on a particular date.