Showcase your achievements in the Maveryx Community by submitting a Success Story now!
SUBMISSION INSTRUCTIONSStarting with hospital data, we have sourced hospital data in a JSON format. Using Alteryx we are able to extract this data into a list of 6,700 institutions with their staffed capacity, bed capacity and utilization rates all with geo-coordinates:
To get COVID case data, we are using Alteryx to connect directly to the John Hopkins University data and convert their time series file into an analytics ready format. Vantage uses Snowflake with Azure in this case and built a custom bulk loading macro to load data in bulk to snowflake via Azure blob storage.
Next , Vantage extracted details on cases from Italy and from the state of Florida where patient details including demographics are available. From this data, Vantage has built a model using the Alteryx logistics regression algorithm to determine probability of hospitalization and ICU admission by gender and age. Using data from Italy, Vantage was able to model the length of hospitalization stay required.
Using US census data from each county on demographics, we are able to determine for each county what is the expected hospitalization rate, and ICU rate per confirmed case.
Next onto our hospital data, we can bring in ICU beds and hospital beds for each county in the US. This represented an initial challenge as some counties in the US do not have hospitals and many do not have ICU facilities. To account for this, we used the spatial distance tool and grouped the case count and expected hospitalization rate per case for each county to the nearest facility (either ICU or normal hospital).
Armed with this data, we can now use utilization estimates for hospital capacity pre-Covid and take live case data combined with our modelling on expected hospitalization rates in each county (and surrounding counties in cases where no ICU or hospital capacity exists) to create a live model of critical care shortages and or surpluses. We have used Tableau as our output tool and created some dynamic parameters where a user can change the length of an average hospitalization stay, or change assumptions on the pre-covid bed availability to account for suspension of elective or non urgent procedures.
Analysts who are prepared to use tools like Alteryx and become experts in bringing together these valuable but varied and disparate data sources and combine them with their own proprietary data will be able to create massive value for their organizations.
This is very impressive! I was wondering what data you used for COVID-19 cases in Italy, and where you extracted it from. Thanks