library(ohiCovidMetrics)
library(readr)
library(zoo)
library(tidyverse)
library(scales)
library(stringr)
require(lubridate)
require(glue)
emr <- "L:/OHI Data Transfer/EMRESOURCE/Daily EEI/Prod Ready/Metrics_test_2.csv"
Hx <- read_csv(emr)
class(Hx$Report_Date)
head(Hx$Report_Date)
Hx <- Hx %>%
mutate(
Report_Date  = as.Date(Report_Date, format = "%m/%d/%Y")
)
Hx <- Hx %>%
mutate(Region = case_when(
Region ==1~ "Northwest",
Region ==2~ "North Central",
Region ==3~	"Northeast",
Region ==4~ "Western" ,
Region ==5~ "South Central",
Region ==6~ "Fox Valley Area",
Region ==7~ "Southeast"))
#Create dummy variables for text fields
Hx <- Hx %>%
mutate(
StaffToday = if_else(Critical_Staff_Today=="Yes", 1, 0), # 1 if hospital reports being at critical staffing capacity today
StaffWeek = if_else(Critical_Staff_Week=="Yes", 1, 0),  # 1 if hospital reports being at critical staffing capacity likely within a week
PeakCensus = if_else(Hospital_Status== "Peak Census", 1,0),  # 1 if hospital reports operating at "Peak Census" (no more normal beds available; emergency protocols)
Hx_IBA = if_else(IBA__Intermediate_Care > 0 | # 1 if hospital reports having ANY bed availability (this is like converse of PeakCensus)
IBA__Medical_Surgical > 0 |
IBA__Neg_Flow_Isolation > 0 |
IBA__ICU > 0, 1,0),
Med_Surg_Census =if_else(Med_Surg_Status=="Peak Census", 1,0),# 1 if hospital reports that medical/surgical beds are operating at "Peak Census"
Med_Surg_Denominator = if_else(Med_Surg_Status=="Peak Census"| Med_Surg_Status=="Open", 1, 0), # Tally of hospitals with medical/surgical beds
ICU_Census = if_else(ICU_Status=="Peak Census", 1,0), # 1 if hospital reports that ICU is operating at "Peak Census"
ICU_Denominator = if_else(ICU_Status=="Peak Census"| ICU_Status=="Open", 1, 0)  # Tally of hospitals with ICU beds
)
#Create aggregated HERC file with all variables except trajectories----
Hx_agg_All <- Hx %>%
group_by(Region, Report_Date) %>%
summarise(
NumberOfHospitals =n_distinct(Hospital),
#Critical Staffing
NumStaffToday =sum(StaffToday, na.rm=T), # Number of hospitals facing critical staffing shortages today
PrctStaffToday =(sum(StaffToday, na.rm=T)/NumberOfHospitals), # Percent of hospitals facing critical staffing shortages today
NumStaffWeek =sum(StaffWeek, na.rm=T), # Number of hospitals facing critical staffing shortages within a week
PrctStaffWeek  =(sum(StaffWeek, na.rm=T)/NumberOfHospitals), # Number of hospitals facing critical staffing shortages within a week
#Peak Census
NumPeakCensus = sum(PeakCensus, na.rm=T), # Number of hospitals operating at peak census
PrctPeakCensus = (sum(PeakCensus, na.rm=T)/sum(NumberOfHospitals, na.rm=T)), # Percent of hospitals operating at peak census
#inverse of peak census is whether the hospital has any immediate bed availability (IBA). This is also a check on the Peak Census variable in case not updated.
Prct_IBA =sum(Hx_IBA, na.rm=T)/NumberOfHospitals, #Percent of hospitals with any immediate bed availability
Hx_IBA =sum(Hx_IBA, na.rm=T),
Med_Surg_Denominator =sum(Med_Surg_Denominator, na.rm=T),
ICU_Denominator = sum(ICU_Denominator, na.rm=T),
NumMed_Surg_Census =sum(Med_Surg_Census, na.rm=T), #Number of hospitals whose medical/surgical beds are operating at peak census
PrctMed_Surg_Census =(sum(Med_Surg_Census, na.rm=T)/sum(Med_Surg_Denominator, na.rm=T)), #Percent of hospitals whose medical/surgical beds are operating at peak census
NumICU_Census = sum(ICU_Census, na.rm=T), #Number of hospitals whose ICU beds are operating at peak census
PrctICU_Census = (sum(ICU_Census, na.rm=T)/sum(ICU_Denominator, na.rm=T)), # Percent of hospitals whose ICU beds are operating at peak census
#ventilated patients
VentCOVIDpxPrct = (sum(Hosp_and_Vent_Cov_Patients, na.rm=T)/sum(Total___COVID_patients, na.rm=T)), # Percent of currently hospitalized COVID patients on a ventilator
Hosp_and_Vent_Cov_Patients = sum(Hosp_and_Vent_Cov_Patients, na.rm=T),
Total___COVID_patients = sum(Total___COVID_patients, na.rm=T),
# bed use
totalbeds = sum(
(sum(Total_Intermediate_Care_Beds, na.rm=T)),
(sum(Total_ICU_Beds, na.rm=T)),
(sum(Total_Neg_Flow_Isolation_Beds, na.rm=T)),
(sum(Total_Medical_Surgical_Beds, na.rm=T)) #total beds at the hospital
),
beds_IBA = sum(
(sum(IBA__Intermediate_Care, na.rm=T)),
(sum(IBA__Medical_Surgical, na.rm=T)),
(sum(IBA__Neg_Flow_Isolation, na.rm=T)),
(sum(IBA__ICU, na.rm=T))    #Number of beds with immediate availability (staffed, ready for patients)
),
PrctBeds_IBA = (sum(beds_IBA, na.rm=T))/(sum(totalbeds, na.rm=T)), # Percent of beds available
dailyCOVID_px = sum(Total___COVID_patients, na.rm=T), # Daily total number of COVID patients in the hospital
totalICU = sum(Total_ICU_Beds, na.rm=T), # total number of ICU beds
ICU_IBA = sum(IBA__ICU, na.rm=T), # immediately available ICU beds
#dailyCOVID_ICUpx = sum(`__ICU_COVID_patients`, na.rm=T), # Daily total number of COVID patients in the ICU
PrctICU_IBA = (sum(ICU_IBA, na.rm=T))/(sum(ICU_Denominator, na.rm=T)), # Percent of ICU beds immediately available
#edited to ICU_Denominator on 2/15
# ventilators
num_px_vent = sum(Number_of_Ventilated_Patients, na.rm=T), # Number of ventilated patients (COVID plus any other reason)
# including other bed availability individually just in case we want to use them in Tableau.
# Fields below are number of immediately available beds by bed type
intermed_beds_IBA = sum(IBA__Intermediate_Care, na.rm=T),
negflow_beds_IBA = sum(IBA__Neg_Flow_Isolation, na.rm=T),
medsurg_beds_IBA = sum(IBA__Medical_Surgical, na.rm=T),
Under30 = sum(
(sum(Admit_Peds, na.rm=T)),
(sum(Admit_18_19, na.rm = T)),
(sum(Admit_20_29, na.rm=T))
),
Age30to59 =sum(
(sum(Admit_30_39, na.rm=T)),
(sum(Admit_40_49, na.rm = T)),
(sum(Admit_50_59, na.rm=T))
),
Age60Plus =sum(
(sum(Admit_60_69, na.rm=T)),
(sum(Admit_70_79, na.rm = T)),
(sum(Admit_80_Plus, na.rm = T))
),
AgeUnknown = sum(Admit_Age_Unknown, na.rm = T),
AgeDenominator = Under30 + Age30to59 +Age60Plus,
PrctUnder30 = Under30/AgeDenominator,
Prct30to59 = Age30to59/AgeDenominator,
Prct60Plus = Age60Plus/AgeDenominator,
RowType = "Daily"
)
#Append state level metrics for all of the above
Hx_agg_State <- Hx_agg_All %>%
group_by(Report_Date) %>%
summarise(
NumberOfHospitals =sum(NumberOfHospitals, na.rm = T),
NumStaffToday   =sum(NumStaffToday, na.rm=T),
PrctStaffToday  =sum(NumStaffToday, na.rm=T)/NumberOfHospitals,
NumStaffWeek    =sum(NumStaffWeek, na.rm=T),
PrctStaffWeek   =sum(NumStaffWeek, na.rm=T)/NumberOfHospitals, #calculation corrected
NumPeakCensus   =sum(NumPeakCensus, na.rm=T),
PrctPeakCensus  =sum(NumPeakCensus, na.rm=T)/NumberOfHospitals, #calculation corrected
Prct_IBA        =sum(Hx_IBA, na.rm=T)/NumberOfHospitals,
VentCOVIDpxPrct = (sum(Hosp_and_Vent_Cov_Patients, na.rm=T)/sum(Total___COVID_patients, na.rm=T)),
NumMed_Surg_Census =sum(NumMed_Surg_Census, na.rm = T),
PrctMed_Surg_Census=(sum(NumMed_Surg_Census, na.rm=T)/sum(Med_Surg_Denominator, na.rm=T)),
PrctICU_Census =(sum(NumICU_Census, na.rm=T)/sum(ICU_Denominator, na.rm=T)),
totalbeds        = sum(totalbeds, na.rm=T),
beds_IBA         = sum(beds_IBA  , na.rm=T),
dailyCOVID_px    = sum(dailyCOVID_px , na.rm=T),
totalICU         = sum(totalICU, na.rm=T),
ICU_IBA          = sum(ICU_IBA, na.rm=T),
num_px_vent      = sum(num_px_vent , na.rm=T),
intermed_beds_IBA= sum( intermed_beds_IBA, na.rm=T),
negflow_beds_IBA = sum( negflow_beds_IBA , na.rm=T),
medsurg_beds_IBA = sum( medsurg_beds_IBA , na.rm=T),
PrctBeds_IBA = (sum(beds_IBA, na.rm=T))/(sum(totalbeds, na.rm=T)),
PrctICU_IBA = (sum(ICU_IBA, na.rm=T))/(sum(ICU_Denominator, na.rm=T)),
#age groups of admissions
Under30            =sum(Under30, na.rm=T),
Age30to59          =sum(Age30to59, na.rm=T),
Age60Plus          =sum(Age60Plus, na.rm=T),
AgeUnknown         =sum(AgeUnknown, na.rm=T),
AgeDenominator     =sum(AgeDenominator, na.rm=T),
PrctUnder30        =sum(Under30, na.rm=T)/AgeDenominator,
Prct30to59         =sum(Age30to59, na.rm=T)/AgeDenominator,
Prct60Plus=sum(Age60Plus, na.rm=T)/AgeDenominator,
RowType = "Daily"
)
Hx_agg_State <- Hx_agg_State %>%
mutate(
Region = "Wisconsin")
Hx_agg_Daily <- bind_rows(Hx_agg_All, Hx_agg_State)
Hx_agg_Daily <- Hx_agg_Daily %>%
select( Report_Date,
Region,
RowType,
PrctPeakCensus,
PrctMed_Surg_Census,
PrctICU_Census,
Prct_IBA,
VentCOVIDpxPrct,
)
Hx_agg_Daily <- Hx_agg_Daily %>%
mutate(
PrctPeakCensus  = PrctPeakCensus*100,
Prct_IBA       = Prct_IBA*100,
PrctMed_Surg_Census= PrctMed_Surg_Census*100,
PrctICU_Census  =  PrctICU_Census*100,
VentCOVIDpxPrct  =  VentCOVIDpxPrct*100,
)
#remove data from before 6/3 (earliest date in combined metrics)
Hx_agg_Daily <- subset(Hx_agg_Daily, Report_Date >= "2020-06-03")
Hx_agg_Daily <- subset(Hx_agg_Daily, Report_Date <= "2021-06-09")
#grab combined-metrics
cm <- read.csv("K:/DPH/COVID-19 Visualizations/Metrics Dashboard/data for combined file/merge-others-output/combined-metrics.csv")
class(cm$Date)
head(cm$Date)
cm <- cm %>%
mutate(
Date  = as.Date(Date, format = "%m/%d/%Y")
# Date  = as.Date(Date, format = "%Y-%m-%d")
)
head(cm$Date)
cm_newvar <- merge(cm, Hx_agg_Daily, by.x = c("Date", "Region", "RowType"), by.y = c("Report_Date", "Region", "RowType"),all.x = TRUE, all.y = TRUE)
dim(cm) - dim(cm_newvar) #0, -5
names(cm) == names(cm_newvar)
dim(cm_newvar)
#reorder
cm_newvar <- cm_newvar[,c(4,1,5,2,3, 6:62)]
names(cm) == names(cm_newvar)
names(cm_newvar)
#combined.metrics.file <- read.csv("K:/DPH/COVID-19 Visualizations/Metrics Dashboard/data for combined file/merge-others-output/combined-metrics.csv")
dim(cm_newvar)
dim(combined.final)
setwd("K:/DPH/COVID-19 Visualizations/Metrics Dashboard/GIS OSDP Combined Metrics Files")
write.table(cm_newvar, "COVID-19 Data - Disease Activity and Hospital Capacity 06_02_21.csv",sep=',',row.names=F, na="")
setwd("K:/DPH/COVID-19 Visualizations/Metrics Dashboard/data for combined file/merge-others-output/")
write.table(cm_newvar, "combined-metrics.csv",sep=',',row.names=F, na="")
setwd("K:/DPH/COVID-19 Visualizations/Metrics Dashboard/GIS OSDP Combined Metrics Files")
write.table(cm_newvar, "COVID-19 Data - Disease Activity and Hospital Capacity 06_09_21.csv",sep=',',row.names=F, na="")
