library(data.table)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:data.table':
##
## between, first, last## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(lubridate)##
## Attaching package: 'lubridate'## The following objects are masked from 'package:data.table':
##
## hour, isoweek, mday, minute, month, quarter, second, wday, week,
## yday, year## The following object is masked from 'package:base':
##
## datelibrary(ggplot2)
library(viridis)## Loading required package: viridisLitelibrary(reshape2)##
## Attaching package: 'reshape2'## The following objects are masked from 'package:data.table':
##
## dcast, meltLoad conditional severity rates at county-level for CA counties
df <- read.csv('county_age_severity_rates_v6.csv', stringsAsFactors = FALSE)
df$County <- gsub('city', 'City', df$County)
df = df %>% filter(State == "California") %>% group_by(FIPS, County) %>%
summarise(
total_population = sum(population_in_age_group),
wtd_critical_case_rate = weighted.mean(critical_case_rate, population_in_age_group),
wtd_acute_case_rate = weighted.mean(severe_cases_rate, population_in_age_group)) %>%
mutate(
wtd_prop_hosp_in_icu = wtd_critical_case_rate/(wtd_critical_case_rate+wtd_acute_case_rate)) %>%
select(County, total_population, wtd_prop_hosp_in_icu)## Adding missing grouping variables: `FIPS`df = df %>% mutate(County = gsub(' County', '', County))
df## # A tibble: 58 x 4
## # Groups: FIPS [58]
## FIPS County total_population wtd_prop_hosp_in_icu
## <int> <chr> <int> <dbl>
## 1 6001 Alameda 1643700 0.286
## 2 6003 Alpine 1146 0.331
## 3 6005 Amador 37829 0.344
## 4 6007 Butte 227075 0.324
## 5 6009 Calaveras 45235 0.343
## 6 6011 Colusa 21464 0.294
## 7 6013 Contra Costa 1133247 0.298
## 8 6015 Del Norte 27424 0.310
## 9 6017 El Dorado 186661 0.312
## 10 6019 Fresno 978130 0.290
## # … with 48 more rowsJoin in bed data
acute_beds_dt = fread('acute_byFIPS.csv')
icu_beds_dt = fread('icu_byFIPS.csv')
bed_dt = merge(acute_beds_dt, icu_beds_dt, by = "FIPS")
df_w_beds = merge(df, bed_dt, by="FIPS", all.x = TRUE)
df_w_beds## FIPS County total_population wtd_prop_hosp_in_icu num_acute_beds
## 1 6001 Alameda 1643700 0.2856003 898
## 2 6003 Alpine 1146 0.3311264 NA
## 3 6005 Amador 37829 0.3435048 0
## 4 6007 Butte 227075 0.3237516 290
## 5 6009 Calaveras 45235 0.3430493 25
## 6 6011 Colusa 21464 0.2938068 NA
## 7 6013 Contra Costa 1133247 0.2981919 698
## 8 6015 Del Norte 27424 0.3099784 0
## 9 6017 El Dorado 186661 0.3123548 118
## 10 6019 Fresno 978130 0.2896567 859
## 11 6021 Glenn 27897 0.3041234 0
## 12 6023 Humboldt 135768 0.3065415 126
## 13 6025 Imperial 180216 0.2972695 48
## 14 6027 Inyo 18085 0.3455569 0
## 15 6029 Kern 883053 0.2734385 672
## 16 6031 Kings 150075 0.2680675 0
## 17 6033 Lake 64148 0.3205475 17
## 18 6035 Lassen 31185 0.2844235 20
## 19 6037 Los Angeles 10098052 0.2902536 8967
## 20 6039 Madera 155013 0.2977537 73
## 21 6041 Marin 260295 0.3233580 296
## 22 6043 Mariposa 17540 0.3417553 0
## 23 6045 Mendocino 87422 0.3189308 52
## 24 6047 Merced 269075 0.2811274 164
## 25 6049 Modoc 8938 0.3364453 4
## 26 6051 Mono 14174 0.2511204 13
## 27 6053 Monterey 433212 0.2966013 355
## 28 6055 Napa 140530 0.3218895 156
## 29 6057 Nevada 99092 0.3403488 0
## 30 6059 Orange 3164182 0.2961512 2403
## 31 6061 Placer 380077 0.3232071 146
## 32 6063 Plumas 18699 0.3337423 9
## 33 6065 Riverside 2383286 0.2992569 1584
## 34 6067 Sacramento 1510023 0.2928058 1090
## 35 6069 San Benito 59416 0.2802747 33
## 36 6071 San Bernardino 2135413 0.2715110 1738
## 37 6073 San Diego 3302833 0.2947189 2524
## 38 6075 San Francisco 870044 0.3031638 771
## 39 6077 San Joaquin 732212 0.2869450 170
## 40 6079 San Luis Obispo 281455 0.3232393 163
## 41 6081 San Mateo 765935 0.3040809 561
## 42 6083 Santa Barbara 443738 0.3163412 171
## 43 6085 Santa Clara 1922200 0.2884449 1318
## 44 6087 Santa Cruz 273765 0.2932217 68
## 45 6089 Shasta 179085 0.3285431 289
## 46 6091 Sierra 2930 0.3552292 NA
## 47 6093 Siskiyou 43540 0.3355520 25
## 48 6095 Solano 438530 0.2944692 314
## 49 6097 Sonoma 501317 0.3130815 378
## 50 6099 Stanislaus 539301 0.2897179 360
## 51 6101 Sutter 95872 0.3127271 0
## 52 6103 Tehama 63373 0.3203325 0
## 53 6105 Trinity 12862 0.3332495 0
## 54 6107 Tulare 460477 0.2844952 336
## 55 6109 Tuolumne 53932 0.3444248 59
## 56 6111 Ventura 848112 0.3000556 143
## 57 6113 Yolo 214977 0.2929764 51
## 58 6115 Yuba 75493 0.2817576 175
## num_icu_beds
## 1 170
## 2 NA
## 3 0
## 4 43
## 5 6
## 6 NA
## 7 142
## 8 0
## 9 28
## 10 221
## 11 0
## 12 24
## 13 8
## 14 0
## 15 133
## 16 0
## 17 4
## 18 0
## 19 1828
## 20 10
## 21 34
## 22 0
## 23 10
## 24 24
## 25 0
## 26 2
## 27 43
## 28 66
## 29 0
## 30 441
## 31 20
## 32 0
## 33 280
## 34 236
## 35 8
## 36 369
## 37 505
## 38 215
## 39 34
## 40 27
## 41 89
## 42 48
## 43 266
## 44 6
## 45 50
## 46 NA
## 47 4
## 48 70
## 49 56
## 50 54
## 51 0
## 52 0
## 53 0
## 54 29
## 55 6
## 56 53
## 57 8
## 58 24Merge in current hospitalizations (3/30)
# CA Hospitalizations on 3/30
hosp = fread('data/nigam_hospital_data_0330.csv')[,.(County, original_hosp = `COVID-19 Positive Patients March 30 2020`)]
df_w_beds_hosp = as.data.table(merge(df_w_beds, hosp, by = 'County', all.x = TRUE))
df_w_beds_hosp## County FIPS total_population wtd_prop_hosp_in_icu num_acute_beds
## 1: Alameda 6001 1643700 0.2856003 898
## 2: Alpine 6003 1146 0.3311264 NA
## 3: Amador 6005 37829 0.3435048 0
## 4: Butte 6007 227075 0.3237516 290
## 5: Calaveras 6009 45235 0.3430493 25
## 6: Colusa 6011 21464 0.2938068 NA
## 7: Contra Costa 6013 1133247 0.2981919 698
## 8: Del Norte 6015 27424 0.3099784 0
## 9: El Dorado 6017 186661 0.3123548 118
## 10: Fresno 6019 978130 0.2896567 859
## 11: Glenn 6021 27897 0.3041234 0
## 12: Humboldt 6023 135768 0.3065415 126
## 13: Imperial 6025 180216 0.2972695 48
## 14: Inyo 6027 18085 0.3455569 0
## 15: Kern 6029 883053 0.2734385 672
## 16: Kings 6031 150075 0.2680675 0
## 17: Lake 6033 64148 0.3205475 17
## 18: Lassen 6035 31185 0.2844235 20
## 19: Los Angeles 6037 10098052 0.2902536 8967
## 20: Madera 6039 155013 0.2977537 73
## 21: Marin 6041 260295 0.3233580 296
## 22: Mariposa 6043 17540 0.3417553 0
## 23: Mendocino 6045 87422 0.3189308 52
## 24: Merced 6047 269075 0.2811274 164
## 25: Modoc 6049 8938 0.3364453 4
## 26: Mono 6051 14174 0.2511204 13
## 27: Monterey 6053 433212 0.2966013 355
## 28: Napa 6055 140530 0.3218895 156
## 29: Nevada 6057 99092 0.3403488 0
## 30: Orange 6059 3164182 0.2961512 2403
## 31: Placer 6061 380077 0.3232071 146
## 32: Plumas 6063 18699 0.3337423 9
## 33: Riverside 6065 2383286 0.2992569 1584
## 34: Sacramento 6067 1510023 0.2928058 1090
## 35: San Benito 6069 59416 0.2802747 33
## 36: San Bernardino 6071 2135413 0.2715110 1738
## 37: San Diego 6073 3302833 0.2947189 2524
## 38: San Francisco 6075 870044 0.3031638 771
## 39: San Joaquin 6077 732212 0.2869450 170
## 40: San Luis Obispo 6079 281455 0.3232393 163
## 41: San Mateo 6081 765935 0.3040809 561
## 42: Santa Barbara 6083 443738 0.3163412 171
## 43: Santa Clara 6085 1922200 0.2884449 1318
## 44: Santa Cruz 6087 273765 0.2932217 68
## 45: Shasta 6089 179085 0.3285431 289
## 46: Sierra 6091 2930 0.3552292 NA
## 47: Siskiyou 6093 43540 0.3355520 25
## 48: Solano 6095 438530 0.2944692 314
## 49: Sonoma 6097 501317 0.3130815 378
## 50: Stanislaus 6099 539301 0.2897179 360
## 51: Sutter 6101 95872 0.3127271 0
## 52: Tehama 6103 63373 0.3203325 0
## 53: Trinity 6105 12862 0.3332495 0
## 54: Tulare 6107 460477 0.2844952 336
## 55: Tuolumne 6109 53932 0.3444248 59
## 56: Ventura 6111 848112 0.3000556 143
## 57: Yolo 6113 214977 0.2929764 51
## 58: Yuba 6115 75493 0.2817576 175
## County FIPS total_population wtd_prop_hosp_in_icu num_acute_beds
## num_icu_beds original_hosp
## 1: 170 59
## 2: NA NA
## 3: 0 0
## 4: 43 4
## 5: 6 0
## 6: NA 0
## 7: 142 24
## 8: 0 0
## 9: 28 6
## 10: 221 10
## 11: 0 0
## 12: 24 1
## 13: 8 19
## 14: 0 NA
## 15: 133 15
## 16: 0 2
## 17: 4 0
## 18: 0 0
## 19: 1828 559
## 20: 10 1
## 21: 34 10
## 22: 0 NA
## 23: 10 2
## 24: 24 1
## 25: 0 0
## 26: 2 0
## 27: 43 11
## 28: 66 3
## 29: 0 26
## 30: 441 91
## 31: 20 19
## 32: 0 0
## 33: 280 60
## 34: 236 40
## 35: 8 NA
## 36: 369 67
## 37: 505 129
## 38: 215 42
## 39: 34 20
## 40: 27 1
## 41: 89 53
## 42: 48 19
## 43: 266 153
## 44: 6 1
## 45: 50 1
## 46: NA NA
## 47: 4 0
## 48: 70 15
## 49: 56 7
## 50: 54 17
## 51: 0 0
## 52: 0 0
## 53: 0 0
## 54: 29 5
## 55: 6 1
## 56: 53 25
## 57: 8 2
## 58: 24 3
## num_icu_beds original_hospprint(paste("Counties with nonzero hospitalizations: ", df_w_beds_hosp[original_hosp>0, .N]))## [1] "Counties with nonzero hospitalizations: 39"For now, only project hospitalizations for counties with nonzero hospitalizations. Projecting hospitalizations for other counties requires stronger assumptions about testing and/or fatality rates in order to estimate hospitalizations.
Will now project 21-day time series of cumulative hospitalizations with doubling times of 6, 10, 14
start_date = '2020-03-30'
num_days_to_project = 21
doubling_times = c(7,10,14)
dts_cross_dates = as.data.table(expand.grid(
doubling_times,
as.Date('2020-03-30') + seq(0, num_days_to_project)
))
setnames(dts_cross_dates, c('doubling_time', 'date'))
long_dt = merge(
df_w_beds_hosp[original_hosp>0][, i:=1],
dts_cross_dates[,i:=1],
by = 'i',
allow.cartesian = TRUE
)[,i:=NULL]
long_dt[, days_since_start_date := as.numeric(date - as.Date(start_date)), by=.I]
long_dt[, est_hosp := original_hosp * 2^(days_since_start_date/doubling_time)]
long_dt[, `:=`(
est_acute_hosp = (1-wtd_prop_hosp_in_icu) * est_hosp,
est_icu_hosp = wtd_prop_hosp_in_icu * est_hosp
)]
long_dt## County FIPS total_population wtd_prop_hosp_in_icu num_acute_beds
## 1: Alameda 6001 1643700 0.2856003 898
## 2: Alameda 6001 1643700 0.2856003 898
## 3: Alameda 6001 1643700 0.2856003 898
## 4: Alameda 6001 1643700 0.2856003 898
## 5: Alameda 6001 1643700 0.2856003 898
## ---
## 2570: Yuba 6115 75493 0.2817576 175
## 2571: Yuba 6115 75493 0.2817576 175
## 2572: Yuba 6115 75493 0.2817576 175
## 2573: Yuba 6115 75493 0.2817576 175
## 2574: Yuba 6115 75493 0.2817576 175
## num_icu_beds original_hosp doubling_time date days_since_start_date
## 1: 170 59 7 2020-03-30 0
## 2: 170 59 10 2020-03-30 0
## 3: 170 59 14 2020-03-30 0
## 4: 170 59 7 2020-03-31 1
## 5: 170 59 10 2020-03-31 1
## ---
## 2570: 24 3 10 2020-04-19 20
## 2571: 24 3 14 2020-04-19 20
## 2572: 24 3 7 2020-04-20 21
## 2573: 24 3 10 2020-04-20 21
## 2574: 24 3 14 2020-04-20 21
## est_hosp est_acute_hosp est_icu_hosp
## 1: 59.000000 42.149585 16.850415
## 2: 59.000000 42.149585 16.850415
## 3: 59.000000 42.149585 16.850415
## 4: 65.141281 46.536915 18.604367
## 5: 63.234634 45.174806 18.059828
## ---
## 2570: 12.000000 8.618909 3.381091
## 2571: 8.075401 5.800096 2.275305
## 2572: 24.000000 17.237818 6.762182
## 2573: 12.861282 9.237518 3.623763
## 2574: 8.485281 6.094489 2.390792Estimate hospital census using LOS values
ICU_LOS = 9
ACUTE_LOS = 7
# function from Shiny app
get_hospitalizations = function(cumulative_cases, los, doubling_time) {
days_to_hospitalization = 0
# project back los + days to hospitalization days
back_vec = c(rep(NA, los + days_to_hospitalization), cumulative_cases)
for (i in (los + days_to_hospitalization):1) {
back_vec[i] = back_vec[i + 1]/2^(1/doubling_time)
}
# get indices of original vectors
original_start = los + days_to_hospitalization + 1
original_end = los + days_to_hospitalization + length(cumulative_cases)
stopifnot(all.equal(back_vec[original_start:original_end], cumulative_cases))
stopifnot(length(back_vec) == original_end)
# get indices of vectors shifted by days to hospitalization
shifted_start = original_start - days_to_hospitalization
shifted_end = original_end - days_to_hospitalization
# subtract off for length of stay
return_vec = back_vec[shifted_start:shifted_end] - back_vec[(shifted_start - los):(shifted_end - los)]
return(list(result = return_vec, back_vec = back_vec[1:los]))
}
# use on our data
long_dt[,icu_census := get_hospitalizations(est_icu_hosp, ICU_LOS, doubling_time=6), by = c('County', 'doubling_time')]
long_dt[,acute_census := get_hospitalizations(est_acute_hosp, ACUTE_LOS, doubling_time=6), by = c('County', 'doubling_time')]long_dt[County == "Santa Clara" & doubling_time == 7]## County FIPS total_population wtd_prop_hosp_in_icu num_acute_beds
## 1: Santa Clara 6085 1922200 0.2884449 1318
## 2: Santa Clara 6085 1922200 0.2884449 1318
## 3: Santa Clara 6085 1922200 0.2884449 1318
## 4: Santa Clara 6085 1922200 0.2884449 1318
## 5: Santa Clara 6085 1922200 0.2884449 1318
## 6: Santa Clara 6085 1922200 0.2884449 1318
## 7: Santa Clara 6085 1922200 0.2884449 1318
## 8: Santa Clara 6085 1922200 0.2884449 1318
## 9: Santa Clara 6085 1922200 0.2884449 1318
## 10: Santa Clara 6085 1922200 0.2884449 1318
## 11: Santa Clara 6085 1922200 0.2884449 1318
## 12: Santa Clara 6085 1922200 0.2884449 1318
## 13: Santa Clara 6085 1922200 0.2884449 1318
## 14: Santa Clara 6085 1922200 0.2884449 1318
## 15: Santa Clara 6085 1922200 0.2884449 1318
## 16: Santa Clara 6085 1922200 0.2884449 1318
## 17: Santa Clara 6085 1922200 0.2884449 1318
## 18: Santa Clara 6085 1922200 0.2884449 1318
## 19: Santa Clara 6085 1922200 0.2884449 1318
## 20: Santa Clara 6085 1922200 0.2884449 1318
## 21: Santa Clara 6085 1922200 0.2884449 1318
## 22: Santa Clara 6085 1922200 0.2884449 1318
## County FIPS total_population wtd_prop_hosp_in_icu num_acute_beds
## num_icu_beds original_hosp doubling_time date days_since_start_date
## 1: 266 153 7 2020-03-30 0
## 2: 266 153 7 2020-03-31 1
## 3: 266 153 7 2020-04-01 2
## 4: 266 153 7 2020-04-02 3
## 5: 266 153 7 2020-04-03 4
## 6: 266 153 7 2020-04-04 5
## 7: 266 153 7 2020-04-05 6
## 8: 266 153 7 2020-04-06 7
## 9: 266 153 7 2020-04-07 8
## 10: 266 153 7 2020-04-08 9
## 11: 266 153 7 2020-04-09 10
## 12: 266 153 7 2020-04-10 11
## 13: 266 153 7 2020-04-11 12
## 14: 266 153 7 2020-04-12 13
## 15: 266 153 7 2020-04-13 14
## 16: 266 153 7 2020-04-14 15
## 17: 266 153 7 2020-04-15 16
## 18: 266 153 7 2020-04-16 17
## 19: 266 153 7 2020-04-17 18
## 20: 266 153 7 2020-04-18 19
## 21: 266 153 7 2020-04-19 20
## 22: 266 153 7 2020-04-20 21
## num_icu_beds original_hosp doubling_time date days_since_start_date
## est_hosp est_acute_hosp est_icu_hosp icu_census acute_census
## 1: 153.0000 108.8679 44.13207 28.52903 60.37278
## 2: 168.9257 120.1999 48.72575 31.21193 65.76598
## 3: 186.5091 132.7115 53.79759 34.13899 71.61144
## 4: 205.9227 146.5254 59.39736 37.33132 77.94287
## 5: 227.3571 161.7771 65.58000 40.81171 84.79587
## 6: 251.0226 178.6164 72.40619 44.60473 92.20789
## 7: 277.1514 197.2085 79.94292 48.73683 100.21822
## 8: 306.0000 217.7359 88.26413 53.23649 108.86793
## 9: 337.8514 240.3999 97.45151 58.13430 120.19994
## 10: 373.0182 265.4230 107.59519 63.46312 132.71150
## 11: 411.8455 293.0507 118.79472 70.06896 146.52537
## 12: 454.7143 323.5543 131.16000 77.36241 161.77713
## 13: 502.0452 357.2329 144.81238 85.41502 178.61643
## 14: 554.3029 394.4171 159.88583 94.30583 197.20853
## 15: 612.0000 435.4717 176.52827 104.12208 217.73587
## 16: 675.7028 480.7998 194.90301 114.96010 240.39989
## 17: 746.0364 530.8460 215.19037 126.92624 265.42299
## 18: 823.6909 586.1015 237.58943 140.13793 293.05074
## 19: 909.4285 647.1085 262.32000 154.72481 323.55425
## 20: 1004.0905 714.4657 289.62476 170.83005 357.23286
## 21: 1108.6058 788.8341 319.77166 188.61166 394.41705
## 22: 1224.0000 870.9435 353.05654 208.24416 435.47173
## est_hosp est_acute_hosp est_icu_hosp icu_census acute_censusExample plot
ggplot(long_dt[County %in% c('Alameda', 'Santa Clara', 'San Mateo', 'San Francisco')],
aes(x=date, y=icu_census, color=factor(doubling_time))) + geom_line() +
facet_wrap(~County, ncol=2)
Create county-level output
# Filter to rows for Apr 7 and Apr 14
date1 = '2020-04-07'
date2 = '2020-04-14'
filtered_dt = long_dt[date %in% c(as.Date(date1), as.Date(date2))]
#Subset columns
filtered_dt = filtered_dt[,.(County, num_acute_beds, num_icu_beds, doubling_time, date, acute_census = round(acute_census), icu_census = round(icu_census))]
filtered_dt## County num_acute_beds num_icu_beds doubling_time date acute_census
## 1: Alameda 898 170 7 2020-04-07 47
## 2: Alameda 898 170 10 2020-04-07 28
## 3: Alameda 898 170 14 2020-04-07 18
## 4: Alameda 898 170 7 2020-04-14 93
## 5: Alameda 898 170 10 2020-04-14 46
## ---
## 230: Yuba 175 24 10 2020-04-07 1
## 231: Yuba 175 24 14 2020-04-07 1
## 232: Yuba 175 24 7 2020-04-14 5
## 233: Yuba 175 24 10 2020-04-14 2
## 234: Yuba 175 24 14 2020-04-14 1
## icu_census
## 1: 22
## 2: 14
## 3: 10
## 4: 44
## 5: 22
## ---
## 230: 1
## 231: 1
## 232: 2
## 233: 1
## 234: 1# pivot ICU data to wide table
icu_wide = as.data.table(dcast(filtered_dt, County + num_icu_beds ~ date + doubling_time, value.var = c('icu_census')))
icu_wide = icu_wide[,.(
County,
April_7_ICU_LB = get(paste(date1, doubling_times[3], sep='_')),
April_7_ICU_M = get(paste(date1, doubling_times[2], sep='_')),
April_7_ICU_UB = get(paste(date1, doubling_times[1], sep='_')),
April_14_ICU_LB = get(paste(date2, doubling_times[3], sep='_')),
April_14_ICU_M = get(paste(date2, doubling_times[2], sep='_')),
April_14_ICU_UB = get(paste(date2, doubling_times[1], sep='_')),
ICU_Utilization_UB = get(paste(date2, doubling_times[1], sep='_'))/num_icu_beds
)]
icu_wide## County April_7_ICU_LB April_7_ICU_M April_7_ICU_UB April_14_ICU_LB
## 1: Alameda 10 14 22 13
## 2: Butte 1 1 2 1
## 3: Contra Costa 4 6 9 5
## 4: El Dorado 1 2 2 1
## 5: Fresno 2 2 4 2
## 6: Humboldt 0 0 0 0
## 7: Imperial 3 5 7 4
## 8: Kern 2 3 5 3
## 9: Kings 0 0 1 0
## 10: Los Angeles 97 138 214 123
## 11: Madera 0 0 0 0
## 12: Marin 2 3 4 2
## 13: Mendocino 0 1 1 0
## 14: Merced 0 0 0 0
## 15: Monterey 2 3 4 2
## 16: Napa 1 1 1 1
## 17: Nevada 5 8 12 7
## 18: Orange 16 23 36 20
## 19: Placer 4 5 8 5
## 20: Riverside 11 15 24 14
## 21: Sacramento 7 10 15 9
## 22: San Bernardino 11 15 24 14
## 23: San Diego 23 32 50 29
## 24: San Francisco 8 11 17 10
## 25: San Joaquin 3 5 8 4
## 26: San Luis Obispo 0 0 0 0
## 27: San Mateo 10 14 21 12
## 28: Santa Barbara 4 5 8 5
## 29: Santa Clara 26 38 58 33
## 30: Santa Cruz 0 0 0 0
## 31: Shasta 0 0 0 0
## 32: Solano 3 4 6 3
## 33: Sonoma 1 2 3 2
## 34: Stanislaus 3 4 6 4
## 35: Tulare 1 1 2 1
## 36: Tuolumne 0 0 0 0
## 37: Ventura 4 6 10 6
## 38: Yolo 0 0 1 0
## 39: Yuba 1 1 1 1
## County April_7_ICU_LB April_7_ICU_M April_7_ICU_UB April_14_ICU_LB
## April_14_ICU_M April_14_ICU_UB ICU_Utilization_UB
## 1: 22 44 0.25882353
## 2: 2 3 0.06976744
## 3: 9 19 0.13380282
## 4: 2 5 0.17857143
## 5: 4 8 0.03619910
## 6: 0 1 0.04166667
## 7: 7 15 1.87500000
## 8: 5 11 0.08270677
## 9: 1 1 Inf
## 10: 213 423 0.23140044
## 11: 0 1 0.10000000
## 12: 4 8 0.23529412
## 13: 1 2 0.20000000
## 14: 0 1 0.04166667
## 15: 4 8 0.18604651
## 16: 1 3 0.04545455
## 17: 12 23 Inf
## 18: 35 70 0.15873016
## 19: 8 16 0.80000000
## 20: 24 47 0.16785714
## 21: 15 31 0.13135593
## 22: 24 47 0.12737127
## 23: 50 99 0.19603960
## 24: 17 33 0.15348837
## 25: 8 15 0.44117647
## 26: 0 1 0.03703704
## 27: 21 42 0.47191011
## 28: 8 16 0.33333333
## 29: 58 115 0.43233083
## 30: 0 1 0.16666667
## 31: 0 1 0.02000000
## 32: 6 12 0.17142857
## 33: 3 6 0.10714286
## 34: 6 13 0.24074074
## 35: 2 4 0.13793103
## 36: 0 1 0.16666667
## 37: 10 20 0.37735849
## 38: 1 2 0.25000000
## 39: 1 2 0.08333333
## April_14_ICU_M April_14_ICU_UB ICU_Utilization_UB# pivot Acute data to wide table
acute_wide = as.data.table(dcast(filtered_dt, County + num_acute_beds ~ date + doubling_time, value.var = c('acute_census')))
acute_wide = acute_wide[,.(
County,
April_7_Acute_LB = get(paste(date1, doubling_times[3], sep='_')),
April_7_Acute_M = get(paste(date1, doubling_times[2], sep='_')),
April_7_Acute_UB = get(paste(date1, doubling_times[1], sep='_')),
April_14_Acute_LB = get(paste(date2, doubling_times[3], sep='_')),
April_14_Acute_M = get(paste(date2, doubling_times[2], sep='_')),
April_14_Acute_UB = get(paste(date2, doubling_times[1], sep='_')),
Acute_Utilization_UB = get(paste(date2, doubling_times[1], sep='_'))/num_acute_beds
)]
acute_wide## County April_7_Acute_LB April_7_Acute_M April_7_Acute_UB
## 1: Alameda 18 28 47
## 2: Butte 1 2 3
## 3: Contra Costa 7 11 19
## 4: El Dorado 2 3 5
## 5: Fresno 3 5 8
## 6: Humboldt 0 0 1
## 7: Imperial 6 9 15
## 8: Kern 5 7 12
## 9: Kings 1 1 2
## 10: Los Angeles 173 266 438
## 11: Madera 0 0 1
## 12: Marin 3 5 7
## 13: Mendocino 1 1 2
## 14: Merced 0 0 1
## 15: Monterey 3 5 9
## 16: Napa 1 1 2
## 17: Nevada 7 11 19
## 18: Orange 28 43 71
## 19: Placer 6 9 14
## 20: Riverside 18 28 46
## 21: Sacramento 12 19 31
## 22: San Bernardino 21 33 54
## 23: San Diego 40 61 100
## 24: San Francisco 13 20 32
## 25: San Joaquin 6 10 16
## 26: San Luis Obispo 0 0 1
## 27: San Mateo 16 25 41
## 28: Santa Barbara 6 9 14
## 29: Santa Clara 47 73 120
## 30: Santa Cruz 0 0 1
## 31: Shasta 0 0 1
## 32: Solano 5 7 12
## 33: Sonoma 2 3 5
## 34: Stanislaus 5 8 13
## 35: Tulare 2 2 4
## 36: Tuolumne 0 0 1
## 37: Ventura 8 12 19
## 38: Yolo 1 1 2
## 39: Yuba 1 1 2
## County April_7_Acute_LB April_7_Acute_M April_7_Acute_UB
## April_14_Acute_LB April_14_Acute_M April_14_Acute_UB Acute_Utilization_UB
## 1: 26 46 93 0.103563474
## 2: 2 3 6 0.020689655
## 3: 10 18 37 0.053008596
## 4: 3 4 9 0.076271186
## 5: 4 8 16 0.018626310
## 6: 0 1 2 0.015873016
## 7: 8 15 29 0.604166667
## 8: 7 12 24 0.035714286
## 9: 1 2 3 Inf
## 10: 244 431 876 0.097691536
## 11: 0 1 2 0.027397260
## 12: 4 7 15 0.050675676
## 13: 1 1 3 0.057692308
## 14: 0 1 2 0.012195122
## 15: 5 8 17 0.047887324
## 16: 1 2 4 0.025641026
## 17: 11 19 38 Inf
## 18: 39 70 141 0.058676654
## 19: 8 14 28 0.191780822
## 20: 26 46 93 0.058712121
## 21: 17 31 62 0.056880734
## 22: 30 53 108 0.062140391
## 23: 56 99 201 0.079635499
## 24: 18 32 65 0.084306096
## 25: 9 16 31 0.182352941
## 26: 0 1 1 0.006134969
## 27: 23 40 81 0.144385027
## 28: 8 14 29 0.169590643
## 29: 67 118 240 0.182094082
## 30: 0 1 2 0.029411765
## 31: 0 1 1 0.003460208
## 32: 7 12 23 0.073248408
## 33: 3 5 11 0.029100529
## 34: 7 13 27 0.075000000
## 35: 2 4 8 0.023809524
## 36: 0 1 1 0.016949153
## 37: 11 19 39 0.272727273
## 38: 1 2 3 0.058823529
## 39: 1 2 5 0.028571429
## April_14_Acute_LB April_14_Acute_M April_14_Acute_UB Acute_Utilization_UBCombine the Acute and ICU data
combined_data = merge(icu_wide, acute_wide, by = 'County')
combined_data## County April_7_ICU_LB April_7_ICU_M April_7_ICU_UB April_14_ICU_LB
## 1: Alameda 10 14 22 13
## 2: Butte 1 1 2 1
## 3: Contra Costa 4 6 9 5
## 4: El Dorado 1 2 2 1
## 5: Fresno 2 2 4 2
## 6: Humboldt 0 0 0 0
## 7: Imperial 3 5 7 4
## 8: Kern 2 3 5 3
## 9: Kings 0 0 1 0
## 10: Los Angeles 97 138 214 123
## 11: Madera 0 0 0 0
## 12: Marin 2 3 4 2
## 13: Mendocino 0 1 1 0
## 14: Merced 0 0 0 0
## 15: Monterey 2 3 4 2
## 16: Napa 1 1 1 1
## 17: Nevada 5 8 12 7
## 18: Orange 16 23 36 20
## 19: Placer 4 5 8 5
## 20: Riverside 11 15 24 14
## 21: Sacramento 7 10 15 9
## 22: San Bernardino 11 15 24 14
## 23: San Diego 23 32 50 29
## 24: San Francisco 8 11 17 10
## 25: San Joaquin 3 5 8 4
## 26: San Luis Obispo 0 0 0 0
## 27: San Mateo 10 14 21 12
## 28: Santa Barbara 4 5 8 5
## 29: Santa Clara 26 38 58 33
## 30: Santa Cruz 0 0 0 0
## 31: Shasta 0 0 0 0
## 32: Solano 3 4 6 3
## 33: Sonoma 1 2 3 2
## 34: Stanislaus 3 4 6 4
## 35: Tulare 1 1 2 1
## 36: Tuolumne 0 0 0 0
## 37: Ventura 4 6 10 6
## 38: Yolo 0 0 1 0
## 39: Yuba 1 1 1 1
## County April_7_ICU_LB April_7_ICU_M April_7_ICU_UB April_14_ICU_LB
## April_14_ICU_M April_14_ICU_UB ICU_Utilization_UB April_7_Acute_LB
## 1: 22 44 0.25882353 18
## 2: 2 3 0.06976744 1
## 3: 9 19 0.13380282 7
## 4: 2 5 0.17857143 2
## 5: 4 8 0.03619910 3
## 6: 0 1 0.04166667 0
## 7: 7 15 1.87500000 6
## 8: 5 11 0.08270677 5
## 9: 1 1 Inf 1
## 10: 213 423 0.23140044 173
## 11: 0 1 0.10000000 0
## 12: 4 8 0.23529412 3
## 13: 1 2 0.20000000 1
## 14: 0 1 0.04166667 0
## 15: 4 8 0.18604651 3
## 16: 1 3 0.04545455 1
## 17: 12 23 Inf 7
## 18: 35 70 0.15873016 28
## 19: 8 16 0.80000000 6
## 20: 24 47 0.16785714 18
## 21: 15 31 0.13135593 12
## 22: 24 47 0.12737127 21
## 23: 50 99 0.19603960 40
## 24: 17 33 0.15348837 13
## 25: 8 15 0.44117647 6
## 26: 0 1 0.03703704 0
## 27: 21 42 0.47191011 16
## 28: 8 16 0.33333333 6
## 29: 58 115 0.43233083 47
## 30: 0 1 0.16666667 0
## 31: 0 1 0.02000000 0
## 32: 6 12 0.17142857 5
## 33: 3 6 0.10714286 2
## 34: 6 13 0.24074074 5
## 35: 2 4 0.13793103 2
## 36: 0 1 0.16666667 0
## 37: 10 20 0.37735849 8
## 38: 1 2 0.25000000 1
## 39: 1 2 0.08333333 1
## April_14_ICU_M April_14_ICU_UB ICU_Utilization_UB April_7_Acute_LB
## April_7_Acute_M April_7_Acute_UB April_14_Acute_LB April_14_Acute_M
## 1: 28 47 26 46
## 2: 2 3 2 3
## 3: 11 19 10 18
## 4: 3 5 3 4
## 5: 5 8 4 8
## 6: 0 1 0 1
## 7: 9 15 8 15
## 8: 7 12 7 12
## 9: 1 2 1 2
## 10: 266 438 244 431
## 11: 0 1 0 1
## 12: 5 7 4 7
## 13: 1 2 1 1
## 14: 0 1 0 1
## 15: 5 9 5 8
## 16: 1 2 1 2
## 17: 11 19 11 19
## 18: 43 71 39 70
## 19: 9 14 8 14
## 20: 28 46 26 46
## 21: 19 31 17 31
## 22: 33 54 30 53
## 23: 61 100 56 99
## 24: 20 32 18 32
## 25: 10 16 9 16
## 26: 0 1 0 1
## 27: 25 41 23 40
## 28: 9 14 8 14
## 29: 73 120 67 118
## 30: 0 1 0 1
## 31: 0 1 0 1
## 32: 7 12 7 12
## 33: 3 5 3 5
## 34: 8 13 7 13
## 35: 2 4 2 4
## 36: 0 1 0 1
## 37: 12 19 11 19
## 38: 1 2 1 2
## 39: 1 2 1 2
## April_7_Acute_M April_7_Acute_UB April_14_Acute_LB April_14_Acute_M
## April_14_Acute_UB Acute_Utilization_UB
## 1: 93 0.103563474
## 2: 6 0.020689655
## 3: 37 0.053008596
## 4: 9 0.076271186
## 5: 16 0.018626310
## 6: 2 0.015873016
## 7: 29 0.604166667
## 8: 24 0.035714286
## 9: 3 Inf
## 10: 876 0.097691536
## 11: 2 0.027397260
## 12: 15 0.050675676
## 13: 3 0.057692308
## 14: 2 0.012195122
## 15: 17 0.047887324
## 16: 4 0.025641026
## 17: 38 Inf
## 18: 141 0.058676654
## 19: 28 0.191780822
## 20: 93 0.058712121
## 21: 62 0.056880734
## 22: 108 0.062140391
## 23: 201 0.079635499
## 24: 65 0.084306096
## 25: 31 0.182352941
## 26: 1 0.006134969
## 27: 81 0.144385027
## 28: 29 0.169590643
## 29: 240 0.182094082
## 30: 2 0.029411765
## 31: 1 0.003460208
## 32: 23 0.073248408
## 33: 11 0.029100529
## 34: 27 0.075000000
## 35: 8 0.023809524
## 36: 1 0.016949153
## 37: 39 0.272727273
## 38: 3 0.058823529
## 39: 5 0.028571429
## April_14_Acute_UB Acute_Utilization_UBWrite to CSV
fwrite(combined_data, 'data/ca_county_projections.csv')