library(data.table)
library(dplyr)
library(lubridate)
library(ggplot2)
library(viridis)
library(reshape2)
library(knitr)
library(kableExtra)

New version: Uses DTs from Jeremy

Load conditional severity rates at county-level for CA counties.

Get expected proportion of hospital census in ICU by calculating LOS-weighted expected proportion of hospitalizations in ICU based on county demographics and hospitalization severity by age from Imperial report (calculated earlier in separate code).

# Constants from state request https://tinyurl.com/v4u5vj6
ICU_LOS = 9
ACUTE_LOS = 7

df <- read.csv('../data/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 = ICU_LOS*wtd_critical_case_rate/(ICU_LOS*wtd_critical_case_rate+ACUTE_LOS*wtd_acute_case_rate))

df = df %>% ungroup() %>% select(FIPS, County, wtd_prop_hosp_in_icu)
df = df %>% mutate(County = gsub(' County', '', County))
df[1:10,] %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
FIPS County wtd_prop_hosp_in_icu
6001 Alameda 0.3394973
6003 Alpine 0.3889375
6005 Amador 0.4021776
6007 Butte 0.3810086
6009 Calaveras 0.4016919
6011 Colusa 0.3484971
6013 Contra Costa 0.3532901
6015 Del Norte 0.3661183
6017 El Dorado 0.3686951
6019 Fresno 0.3439507

Join in bed (Acute, ICU) data

acute_beds_dt = fread('../data/acute_byFIPS.csv')
icu_beds_dt = fread('../data/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[1:10,] %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
FIPS County wtd_prop_hosp_in_icu num_acute_beds num_icu_beds
6001 Alameda 0.3394973 898 170
6003 Alpine 0.3889375 NA NA
6005 Amador 0.4021776 0 0
6007 Butte 0.3810086 290 43
6009 Calaveras 0.4016919 25 6
6011 Colusa 0.3484971 NA NA
6013 Contra Costa 0.3532901 698 142
6015 Del Norte 0.3661183 0 0
6017 El Dorado 0.3686951 118 28
6019 Fresno 0.3439507 859 221

Merge in current hospitalizations and DTs from Jeremy (4/4)

# CA Hospitalizations on 3/30
hosp = fread('data/hosp-est-dts-2020-04-04.csv')[,.(County, 
                                                    current_census = Hospitalized_4_4,
                                                    dt_short = bound_double_short,
                                                    dt_long = bound_double_long)]
df_w_beds_hosp = as.data.table(merge(df_w_beds, hosp, by = 'County'))

# break census into icu and floor
df_w_beds_hosp[,`:=`(
  est_current_icu_census = wtd_prop_hosp_in_icu * current_census,
  est_current_floor_census = (1-wtd_prop_hosp_in_icu) * current_census
)]

# estimate cumulative floor/icu hospitalizations with los and dt

df_w_beds_hosp[,`:=`(
  est_current_cum_icu_hosp_short = 
    est_current_icu_census/(1 - 2^(-1*ICU_LOS/dt_short)),
  est_current_cum_icu_hosp_long = 
    est_current_icu_census/(1 - 2^(-1*ICU_LOS/dt_long)),
  
  est_current_cum_floor_hosp_short = 
    est_current_floor_census/(1 - 2^(-1*ACUTE_LOS/dt_short)),
  est_current_cum_floor_hosp_long = 
    est_current_floor_census/(1 - 2^(-1*ACUTE_LOS/dt_long))
)]

df_w_beds_hosp %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
County FIPS wtd_prop_hosp_in_icu num_acute_beds num_icu_beds current_census dt_short dt_long est_current_icu_census est_current_floor_census est_current_cum_icu_hosp_short est_current_cum_icu_hosp_long est_current_cum_floor_hosp_short est_current_cum_floor_hosp_long
Alameda 6001 0.3394973 898 170 60 5.013039 8.062944 20.3698394 39.6301606 28.6136654 37.8130728 63.9079397 87.6466335
Butte 6007 0.3810086 290 43 3 3.910318 10.368000 1.1430259 1.8569741 1.4338682 2.5281739 2.6123100 4.9686950
Contra Costa 6013 0.3532901 698 142 30 4.907635 5.298419 10.5987033 19.4012967 14.7308507 15.3178310 30.8972937 32.3471459
El Dorado 6017 0.3686951 118 28 15 4.732575 8.526316 5.5304270 9.4695730 7.5513640 10.6582273 14.7664444 21.8220229
Fresno 6019 0.3439507 859 221 14 3.727761 4.942907 4.8153102 9.1846898 5.9272179 6.7165298 12.6180268 14.6885839
Humboldt 6023 0.3623859 126 24 1 4.284527 4.228714 0.3623859 0.6376141 0.4725730 0.4698537 0.9407688 0.9341795
Imperial 6025 0.3522828 48 8 9 3.772751 6.403827 3.1705451 5.8294549 3.9209080 5.0933347 8.0556730 10.9731791
Kern 6029 0.3260881 672 133 33 3.716334 3.156442 10.7609071 22.2390929 13.2300386 12.4919133 30.5067591 28.3295413
Los Angeles 6037 0.3446052 8967 1828 1007 3.908117 4.391539 347.0174581 659.9825419 435.2164324 457.5563432 928.1708994 986.9011028
Marin 6041 0.3805846 296 34 5 5.743212 10.660660 1.9029230 3.0970770 2.8723093 4.2956322 5.4299525 8.4703570
Merced 6047 0.3345757 164 24 5 3.654073 5.062500 1.6728784 3.3271216 2.0435059 2.3615960 4.5269962 5.3967570
Mono 6051 0.3012545 13 2 20 6.043203 7.800000 6.0250905 13.9749095 9.3585082 10.9432790 25.3183353 30.1729635
Monterey 6053 0.3515528 355 43 16 4.704930 6.169647 5.6248442 10.3751558 7.6586906 8.8414128 16.1244016 19.0532519
Napa 6055 0.3790017 156 66 1 4.833991 10.015860 0.3790017 0.6209983 0.5228546 0.8175477 0.9802798 1.6173740
Nevada 6057 0.3988101 0 0 2 4.975815 6.029448 0.7976201 1.2023799 1.1162369 1.2372981 1.9304363 2.1751200
Orange 6059 0.3510610 2403 441 143 4.725285 6.340033 50.2017166 92.7982834 68.4956510 80.1724899 144.5778293 173.5176983
Placer 6061 0.3804220 146 20 23 4.242217 6.302062 8.7497059 14.2502941 11.3603436 13.9242041 20.9139435 26.5395306
Riverside 6065 0.3544525 1584 280 128 4.701896 3.829003 45.3699212 82.6300788 61.7558046 56.4359076 128.3710490 115.0236176
Sacramento 6067 0.3474014 1090 236 69 4.843531 5.253471 23.9706986 45.0293014 33.1009303 34.4898548 71.1626989 74.6869354
San Benito 6069 0.3336361 33 8 2 4.576485 10.891530 0.6672722 1.3327278 0.8967022 1.5303073 2.0390056 3.7073000
San Bernardino 6071 0.3239550 1738 369 138 3.376067 2.581272 44.7057849 93.2942151 53.0684237 49.0846164 122.3684551 110.0992212
San Diego 6073 0.3494949 2524 505 177 4.200736 4.799354 61.8606042 115.1393958 79.9738748 85.0410217 168.0975116 180.9980207
San Francisco 6075 0.3587111 771 215 80 6.876255 9.975667 28.6968871 51.3031129 48.1202306 61.7232805 101.3498529 133.2008985
San Joaquin 6077 0.3409747 170 34 43 5.307315 10.173300 14.6619137 28.3380863 21.2088612 31.9858070 47.2956388 74.7078347
San Luis Obispo 6079 0.3804567 163 27 4 4.269195 18.586020 1.5218268 2.4781732 1.9814115 5.3374224 3.6493825 10.7857425
San Mateo 6081 0.3597095 561 89 66 4.731360 5.969825 23.7408245 42.2591755 32.4122209 36.6201333 65.8874307 75.9554796
Santa Barbara 6083 0.3730105 171 48 28 3.696195 5.163121 10.4442934 17.5557066 12.8140077 14.8931971 24.0189695 28.8141624
Santa Clara 6085 0.3426213 1318 266 159 6.041337 11.342250 54.4767896 104.5232104 84.6014799 128.7701088 189.3265305 300.3129606
Santa Cruz 6087 0.3478568 68 6 4 5.006336 10.829660 1.3914270 2.6085730 1.9532320 3.1776416 4.2032727 7.2236522
Shasta 6089 0.3861636 289 50 2 4.354308 2.804722 0.7723271 1.2276729 1.0144429 0.8659847 1.8272830 1.4922362
Solano 6095 0.3492218 314 70 16 4.499589 6.804773 5.5875483 10.4124517 7.4497498 9.3096861 15.7803626 20.4227835
Sonoma 6097 0.3694824 378 56 7 5.255950 9.378104 2.5863770 4.4136230 3.7222820 5.3236271 7.3226672 10.9270022
Stanislaus 6099 0.3440178 360 54 30 3.759954 4.951335 10.3205347 19.6794653 12.7461344 14.4076049 27.1495986 31.5038881
Tulare 6107 0.3382825 336 29 8 3.710286 3.503846 2.7062598 5.2937402 3.3251370 3.2549343 7.2560216 7.0619049
Ventura 6111 0.3553238 143 53 21 3.910345 7.135978 7.4618002 13.5381998 9.3604774 12.8032000 19.0450138 27.4412509
Yolo 6113 0.3475882 51 8 4 3.721132 10.580830 1.3903528 2.6096472 1.7102250 3.1212741 3.5820609 7.0950627
Yuba 6115 0.3352698 175 24 2 4.175449 8.284931 0.6705396 1.3294604 0.8646121 1.2674757 1.9347398 2.9993232

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.

Project 14-day time series of cumulative hospitalizations (Floor and ICU) with lower and upper doubling time estimates from Jeremy.

start_date = '2020-04-04'
num_days_to_project = 14

dates_dt = data.table(date = as.Date(start_date) + seq(0, num_days_to_project))

long_dt = merge(
  dates_dt[, i:=1],
  df_w_beds_hosp[,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_cum_floor_hosp_short = est_current_cum_floor_hosp_short * 2^(days_since_start_date/dt_short),
  est_cum_floor_hosp_long = est_current_cum_floor_hosp_long * 2^(days_since_start_date/dt_long),
  
  est_cum_icu_hosp_short = est_current_cum_icu_hosp_short * 2^(days_since_start_date/dt_short),
  est_cum_icu_hosp_long = est_current_cum_icu_hosp_long * 2^(days_since_start_date/dt_long)
)]
long_dt[County == "Santa Clara", .(County, date, est_cum_floor_hosp_short, est_cum_floor_hosp_long, est_cum_icu_hosp_short, est_cum_icu_hosp_long)] %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
County date est_cum_floor_hosp_short est_cum_floor_hosp_long est_cum_icu_hosp_short est_cum_icu_hosp_long
Santa Clara 2020-04-04 189.3265 300.3130 84.60148 128.7701
Santa Clara 2020-04-05 212.3439 319.2381 94.88692 136.8849
Santa Clara 2020-04-06 238.1597 339.3558 106.42281 145.5111
Santa Clara 2020-04-07 267.1140 360.7413 119.36117 154.6809
Santa Clara 2020-04-08 299.5884 383.4744 133.87253 164.4286
Santa Clara 2020-04-09 336.0109 407.6402 150.14810 174.7906
Santa Clara 2020-04-10 376.8615 433.3289 168.40237 185.8055
Santa Clara 2020-04-11 422.6784 460.6363 188.87591 197.5146
Santa Clara 2020-04-12 474.0656 489.6647 211.83853 209.9616
Santa Clara 2020-04-13 531.7002 520.5223 237.59283 223.1929
Santa Clara 2020-04-14 596.3418 553.3246 266.47821 237.2580
Santa Clara 2020-04-15 668.8421 588.1940 298.87534 252.2096
Santa Clara 2020-04-16 750.1567 625.2607 335.21115 268.1033
Santa Clara 2020-04-17 841.3571 664.6634 375.96449 284.9986
Santa Clara 2020-04-18 943.6452 706.5491 421.67243 302.9586

Estimate hospital census using LOS values from State

Main idea: Census[t] <- CumulativeHospitalizations[t] - CumulativeHospitalizations[t-LOS]

# function from Shiny app
get_hospitalizations = function(cumulative_cases, los, doubling_time) {
  
  days_to_hospitalization = 0 # not modeling infections so no need to account for this 
  
  # 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[, est_icu_census_short := get_hospitalizations(est_cum_icu_hosp_short, ICU_LOS, doubling_time=min(dt_short)), by = c('County')]
long_dt[, est_icu_census_long := get_hospitalizations(est_cum_icu_hosp_long, ICU_LOS, doubling_time=min(dt_long)), by = c('County')]

long_dt[, est_floor_census_short := get_hospitalizations(est_cum_floor_hosp_short, ACUTE_LOS, doubling_time=min(dt_short)), by = c('County')]
long_dt[, est_floor_census_long := get_hospitalizations(est_cum_floor_hosp_long, ACUTE_LOS, doubling_time=min(dt_long)), by = c('County')]

long_dt[County == "Santa Clara", .(County, date, est_icu_census_long, est_icu_census_short, est_floor_census_long, est_floor_census_short)] %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
County date est_icu_census_long est_icu_census_short est_floor_census_long est_floor_census_short
Santa Clara 2020-04-04 54.47679 54.47679 104.5232 104.5232
Santa Clara 2020-04-05 57.90980 61.09981 111.1100 117.2306
Santa Clara 2020-04-06 61.55916 68.52803 118.1120 131.4830
Santa Clara 2020-04-07 65.43849 76.85934 125.5551 147.4680
Santa Clara 2020-04-08 69.56229 86.20352 133.4674 165.3965
Santa Clara 2020-04-09 73.94596 96.68373 141.8782 185.5046
Santa Clara 2020-04-10 78.60588 108.43806 150.8191 208.0573
Santa Clara 2020-04-11 83.55946 121.62144 160.3234 233.3519
Santa Clara 2020-04-12 88.82520 136.40758 170.4266 261.7217
Santa Clara 2020-04-13 94.42279 152.99135 181.1666 293.5406
Santa Clara 2020-04-14 100.37311 171.59129 192.5833 329.2278
Santa Clara 2020-04-15 106.69842 192.45253 204.7195 369.2537
Santa Clara 2020-04-16 113.42233 215.84997 217.6205 414.1458
Santa Clara 2020-04-17 120.56997 242.09196 231.3345 464.4956
Santa Clara 2020-04-18 128.16804 271.52433 245.9127 520.9667

Example plot of ICU demand

ggplot(melt(long_dt[County %in% c('Alameda', 'Santa Clara', 'San Mateo', 'San Francisco'), 
                    .(County, date, est_icu_census_long, est_icu_census_short)],
            id.vars = c('County', 'date')),
       aes(x=date, y=value, color=factor(variable))) + geom_line() +
       facet_wrap(~County, ncol=2, scales="free")

Create wide views with time series by county

First, ICU time series by county

PROP_OF_AHA_BEDS_AVAILABLE = 0.3

icu_time_series_long = long_dt %>% group_by(County, date) %>%
  summarise(
    est_icu_beds_available = max(round(num_icu_beds * PROP_OF_AHA_BEDS_AVAILABLE)),
    icu_census_lb = round(est_icu_census_long),
    icu_census_m = round((est_icu_census_long+est_icu_census_short)/2),
    icu_census_ub = round(est_icu_census_short),
  ) %>% ungroup()

icu_time_series_long = icu_time_series_long %>% mutate(icu_census_str = paste0(icu_census_m, ' [', icu_census_lb, ', ', icu_census_ub, ']'))

# icu_time_series_long %>% filter(County == "Santa Clara")

wide_icu_str = as.data.table(dcast(icu_time_series_long, County + est_icu_beds_available ~ date, value.var = c('icu_census_str')))
wide_icu_str %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
County est_icu_beds_available 2020-04-04 2020-04-05 2020-04-06 2020-04-07 2020-04-08 2020-04-09 2020-04-10 2020-04-11 2020-04-12 2020-04-13 2020-04-14 2020-04-15 2020-04-16 2020-04-17 2020-04-18
Alameda 51 20 [20, 20] 23 [22, 23] 26 [24, 27] 29 [26, 31] 32 [29, 35] 36 [31, 41] 40 [34, 47] 45 [37, 54] 51 [41, 62] 57 [44, 71] 65 [48, 81] 73 [52, 93] 82 [57, 107] 93 [62, 123] 105 [68, 141]
Butte 13 1 [1, 1] 1 [1, 1] 1 [1, 2] 2 [1, 2] 2 [1, 2] 2 [2, 3] 3 [2, 3] 3 [2, 4] 3 [2, 5] 4 [2, 6] 4 [2, 7] 5 [2, 8] 6 [3, 10] 7 [3, 11] 8 [3, 14]
Contra Costa 43 11 [11, 11] 12 [12, 12] 14 [14, 14] 16 [16, 16] 18 [18, 19] 21 [20, 21] 24 [23, 25] 27 [26, 28] 31 [30, 33] 36 [34, 38] 41 [39, 44] 47 [45, 50] 54 [51, 58] 62 [58, 66] 71 [66, 77]
El Dorado 8 6 [6, 6] 6 [6, 6] 7 [7, 7] 8 [7, 9] 9 [8, 10] 10 [8, 12] 11 [9, 13] 13 [10, 15] 14 [11, 18] 16 [11, 21] 18 [12, 24] 21 [14, 28] 23 [15, 32] 27 [16, 37] 30 [17, 43]
Fresno 66 5 [5, 5] 6 [6, 6] 7 [6, 7] 8 [7, 8] 9 [8, 10] 11 [10, 12] 13 [11, 15] 15 [13, 18] 18 [15, 21] 21 [17, 26] 25 [20, 31] 30 [23, 37] 35 [26, 45] 42 [30, 54] 50 [34, 65]
Humboldt 7 0 [0, 0] 0 [0, 0] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 2 [2, 2] 2 [2, 2] 2 [2, 2] 3 [3, 3] 3 [3, 3] 4 [4, 3]
Imperial 2 3 [3, 3] 4 [4, 4] 4 [4, 5] 5 [4, 6] 6 [5, 7] 7 [5, 8] 8 [6, 10] 9 [7, 11] 11 [8, 14] 12 [8, 17] 15 [9, 20] 17 [10, 24] 20 [12, 29] 24 [13, 35] 28 [14, 42]
Kern 40 11 [11, 11] 13 [13, 13] 16 [17, 16] 20 [21, 19] 24 [26, 23] 30 [32, 27] 37 [40, 33] 45 [50, 40] 55 [62, 48] 68 [78, 58] 83 [97, 69] 102 [120, 84] 125 [150, 101] 154 [187, 122] 190 [233, 147]
Los Angeles 548 347 [347, 347] 410 [406, 414] 485 [476, 495] 574 [557, 591] 679 [652, 705] 803 [764, 842] 950 [895, 1006] 1124 [1048, 1201] 1330 [1227, 1434] 1574 [1436, 1712] 1863 [1682, 2045] 2206 [1970, 2441] 2611 [2306, 2915] 3091 [2701, 3481] 3659 [3162, 4156]
Marin 10 2 [2, 2] 2 [2, 2] 2 [2, 2] 3 [2, 3] 3 [2, 3] 3 [3, 3] 3 [3, 4] 4 [3, 4] 4 [3, 5] 5 [3, 6] 5 [4, 6] 6 [4, 7] 6 [4, 8] 7 [4, 9] 8 [5, 10]
Merced 7 2 [2, 2] 2 [2, 2] 2 [2, 2] 3 [3, 3] 3 [3, 4] 4 [3, 4] 5 [4, 5] 5 [4, 6] 6 [5, 8] 7 [6, 9] 9 [7, 11] 11 [8, 13] 12 [9, 16] 15 [10, 20] 18 [11, 24]
Mono 1 6 [6, 6] 7 [7, 7] 7 [7, 8] 8 [8, 8] 9 [9, 10] 10 [9, 11] 11 [10, 12] 12 [11, 13] 14 [12, 15] 15 [13, 17] 17 [15, 19] 19 [16, 21] 21 [18, 24] 23 [19, 27] 25 [21, 30]
Monterey 13 6 [6, 6] 6 [6, 7] 7 [7, 8] 8 [8, 9] 9 [9, 10] 11 [10, 12] 12 [11, 14] 14 [12, 16] 16 [14, 18] 18 [15, 21] 21 [17, 25] 24 [19, 28] 27 [22, 33] 31 [24, 38] 36 [27, 44]
Napa 20 0 [0, 0] 0 [0, 0] 0 [0, 1] 1 [0, 1] 1 [0, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 2] 1 [1, 2] 1 [1, 2] 2 [1, 2] 2 [1, 3]
Nevada 0 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 2 [1, 2] 2 [2, 2] 2 [2, 2] 2 [2, 2] 3 [2, 3] 3 [3, 3] 3 [3, 4] 4 [3, 4] 4 [4, 5] 5 [4, 6]
Orange 132 50 [50, 50] 57 [56, 58] 65 [62, 67] 74 [70, 78] 84 [78, 90] 96 [87, 105] 109 [97, 121] 124 [108, 140] 141 [120, 162] 161 [134, 188] 184 [150, 218] 210 [167, 252] 239 [186, 292] 273 [208, 338] 312 [232, 391]
Placer 6 9 [9, 9] 10 [10, 10] 12 [11, 12] 13 [12, 14] 15 [14, 17] 17 [15, 20] 20 [17, 23] 23 [19, 27] 27 [21, 32] 31 [24, 38] 36 [26, 45] 41 [29, 53] 47 [33, 62] 55 [37, 73] 63 [41, 86]
Riverside 84 45 [45, 45] 53 [54, 53] 63 [65, 61] 74 [78, 71] 88 [94, 82] 103 [112, 95] 122 [134, 110] 144 [161, 127] 170 [193, 148] 201 [231, 171] 238 [277, 198] 281 [332, 230] 332 [398, 266] 393 [477, 308] 465 [572, 357]
Sacramento 71 24 [24, 24] 28 [27, 28] 32 [31, 32] 36 [36, 37] 42 [41, 42] 48 [46, 49] 55 [53, 57] 63 [60, 65] 72 [69, 75] 83 [79, 87] 95 [90, 100] 109 [102, 116] 125 [117, 134] 144 [133, 154] 165 [152, 178]
San Benito 2 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 2] 1 [1, 2] 2 [1, 2] 2 [1, 3] 2 [1, 3] 2 [1, 4] 3 [1, 4] 3 [2, 5] 4 [2, 6]
San Bernardino 111 45 [45, 45] 57 [58, 55] 72 [76, 67] 91 [100, 83] 116 [131, 102] 148 [171, 125] 189 [224, 153] 241 [293, 188] 307 [383, 231] 392 [501, 284] 502 [655, 348] 643 [857, 428] 823 [1122, 525] 1056 [1467, 645] 1355 [1919, 792]
San Diego 152 62 [62, 62] 72 [71, 73] 84 [83, 86] 98 [95, 101] 115 [110, 120] 134 [127, 141] 157 [147, 166] 183 [170, 196] 214 [196, 232] 250 [227, 273] 292 [262, 322] 341 [303, 380] 399 [350, 448] 466 [404, 528] 545 [467, 623]
San Francisco 64 29 [29, 29] 31 [31, 32] 34 [33, 35] 37 [35, 39] 40 [38, 43] 44 [41, 48] 48 [44, 53] 52 [47, 58] 57 [50, 64] 62 [54, 71] 68 [57, 79] 74 [62, 87] 81 [66, 96] 89 [71, 106] 97 [76, 118]
San Joaquin 10 15 [15, 15] 16 [16, 17] 18 [17, 19] 20 [18, 22] 22 [19, 25] 24 [21, 28] 27 [22, 32] 30 [24, 37] 33 [25, 42] 37 [27, 47] 42 [29, 54] 46 [31, 62] 52 [33, 70] 58 [36, 80] 65 [38, 91]
San Luis Obispo 8 2 [2, 2] 2 [2, 2] 2 [2, 2] 2 [2, 2] 2 [2, 3] 3 [2, 3] 3 [2, 4] 3 [2, 5] 4 [2, 6] 4 [2, 7] 5 [2, 8] 6 [2, 9] 7 [2, 11] 8 [2, 13] 9 [3, 15]
San Mateo 27 24 [24, 24] 27 [27, 27] 31 [30, 32] 35 [34, 37] 40 [38, 43] 46 [42, 49] 52 [48, 57] 60 [54, 66] 68 [60, 77] 78 [68, 89] 89 [76, 103] 102 [85, 119] 117 [96, 138] 133 [107, 159] 153 [121, 185]
Santa Barbara 14 10 [10, 10] 12 [12, 13] 14 [14, 15] 17 [16, 18] 20 [18, 22] 24 [20, 27] 28 [23, 32] 33 [27, 39] 39 [31, 47] 46 [35, 56] 54 [40, 68] 64 [46, 82] 76 [52, 99] 90 [60, 120] 106 [68, 144]
Santa Clara 80 54 [54, 54] 60 [58, 61] 65 [62, 69] 71 [65, 77] 78 [70, 86] 85 [74, 97] 94 [79, 108] 103 [84, 122] 113 [89, 136] 124 [94, 153] 136 [100, 172] 150 [107, 192] 165 [113, 216] 181 [121, 242] 200 [128, 272]
Santa Cruz 2 1 [1, 1] 2 [1, 2] 2 [2, 2] 2 [2, 2] 2 [2, 2] 2 [2, 3] 3 [2, 3] 3 [2, 4] 3 [2, 4] 4 [2, 5] 4 [3, 6] 5 [3, 6] 5 [3, 7] 6 [3, 8] 7 [3, 10]
Shasta 15 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [2, 1] 2 [2, 1] 2 [3, 2] 3 [3, 2] 3 [4, 2] 4 [6, 3] 5 [7, 3] 6 [9, 4] 8 [12, 4] 10 [15, 5] 13 [19, 6] 16 [25, 7]
Solano 21 6 [6, 6] 6 [6, 7] 7 [7, 8] 8 [8, 9] 9 [8, 10] 11 [9, 12] 12 [10, 14] 14 [11, 16] 16 [13, 19] 18 [14, 22] 21 [15, 26] 24 [17, 30] 27 [19, 35] 31 [21, 41] 36 [23, 48]
Sonoma 17 3 [3, 3] 3 [3, 3] 3 [3, 3] 4 [3, 4] 4 [3, 4] 4 [4, 5] 5 [4, 6] 5 [4, 7] 6 [5, 7] 7 [5, 8] 8 [5, 10] 8 [6, 11] 9 [6, 13] 11 [7, 14] 12 [7, 16]
Stanislaus 16 10 [10, 10] 12 [12, 12] 14 [14, 15] 17 [16, 18] 20 [18, 22] 23 [21, 26] 28 [24, 31] 33 [27, 38] 38 [32, 45] 45 [36, 54] 54 [42, 65] 63 [48, 78] 75 [55, 94] 89 [64, 113] 105 [73, 136]
Tulare 9 3 [3, 3] 3 [3, 3] 4 [4, 4] 5 [5, 5] 6 [6, 6] 7 [7, 7] 9 [9, 8] 10 [11, 10] 13 [13, 12] 15 [16, 15] 19 [20, 18] 22 [24, 21] 27 [29, 25] 33 [35, 31] 40 [43, 37]
Ventura 16 7 [7, 7] 9 [8, 9] 10 [9, 11] 11 [10, 13] 13 [11, 15] 15 [12, 18] 17 [13, 22] 20 [15, 26] 24 [16, 31] 27 [18, 37] 32 [20, 44] 37 [22, 52] 43 [24, 63] 51 [26, 75] 59 [29, 89]
Yolo 2 1 [1, 1] 2 [1, 2] 2 [2, 2] 2 [2, 2] 2 [2, 3] 3 [2, 4] 3 [2, 4] 4 [2, 5] 4 [2, 6] 5 [3, 7] 6 [3, 9] 7 [3, 11] 8 [3, 13] 9 [3, 16] 11 [3, 19]
Yuba 7 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 2] 1 [1, 2] 2 [1, 2] 2 [1, 3] 2 [1, 3] 3 [2, 4] 3 [2, 4] 3 [2, 5] 4 [2, 6] 5 [2, 7]

Now create a version that just shows excess census over 30% of AHA ICU beds

icu_time_series_long_f = icu_time_series_long %>%
  mutate(excess_demand = paste0(pmax(0, icu_census_m - est_icu_beds_available), 
                                ' [', pmax(0, icu_census_lb - est_icu_beds_available), ', ', 
                                icu_census_ub - est_icu_beds_available, ']')) %>%
  mutate(excess_demand = ifelse(icu_census_ub>est_icu_beds_available, excess_demand, NA))

wide_icu_excess_demand = as.data.table(dcast(icu_time_series_long_f, County + est_icu_beds_available ~ date, value.var = c('excess_demand')))
wide_icu_excess_demand %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
County est_icu_beds_available 2020-04-04 2020-04-05 2020-04-06 2020-04-07 2020-04-08 2020-04-09 2020-04-10 2020-04-11 2020-04-12 2020-04-13 2020-04-14 2020-04-15 2020-04-16 2020-04-17 2020-04-18
Alameda 51 NA NA NA NA NA NA NA 0 [0, 3] 0 [0, 11] 6 [0, 20] 14 [0, 30] 22 [1, 42] 31 [6, 56] 42 [11, 72] 54 [17, 90]
Butte 13 NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 [0, 1]
Contra Costa 43 NA NA NA NA NA NA NA NA NA NA 0 [0, 1] 4 [2, 7] 11 [8, 15] 19 [15, 23] 28 [23, 34]
El Dorado 8 NA NA NA 0 [0, 1] 1 [0, 2] 2 [0, 4] 3 [1, 5] 5 [2, 7] 6 [3, 10] 8 [3, 13] 10 [4, 16] 13 [6, 20] 15 [7, 24] 19 [8, 29] 22 [9, 35]
Fresno 66 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Humboldt 7 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Imperial 2 1 [1, 1] 2 [2, 2] 2 [2, 3] 3 [2, 4] 4 [3, 5] 5 [3, 6] 6 [4, 8] 7 [5, 9] 9 [6, 12] 10 [6, 15] 13 [7, 18] 15 [8, 22] 18 [10, 27] 22 [11, 33] 26 [12, 40]
Kern 40 NA NA NA NA NA NA NA NA 15 [22, 8] 28 [38, 18] 43 [57, 29] 62 [80, 44] 85 [110, 61] 114 [147, 82] 150 [193, 107]
Los Angeles 548 NA NA NA 26 [9, 43] 131 [104, 157] 255 [216, 294] 402 [347, 458] 576 [500, 653] 782 [679, 886] 1026 [888, 1164] 1315 [1134, 1497] 1658 [1422, 1893] 2063 [1758, 2367] 2543 [2153, 2933] 3111 [2614, 3608]
Marin 10 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Merced 7 NA NA NA NA NA NA NA NA 0 [0, 1] 0 [0, 2] 2 [0, 4] 4 [1, 6] 5 [2, 9] 8 [3, 13] 11 [4, 17]
Mono 1 5 [5, 5] 6 [6, 6] 6 [6, 7] 7 [7, 7] 8 [8, 9] 9 [8, 10] 10 [9, 11] 11 [10, 12] 13 [11, 14] 14 [12, 16] 16 [14, 18] 18 [15, 20] 20 [17, 23] 22 [18, 26] 24 [20, 29]
Monterey 13 NA NA NA NA NA NA 0 [0, 1] 1 [0, 3] 3 [1, 5] 5 [2, 8] 8 [4, 12] 11 [6, 15] 14 [9, 20] 18 [11, 25] 23 [14, 31]
Napa 20 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Nevada 0 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 2 [1, 2] 2 [2, 2] 2 [2, 2] 2 [2, 2] 3 [2, 3] 3 [3, 3] 3 [3, 4] 4 [3, 4] 4 [4, 5] 5 [4, 6]
Orange 132 NA NA NA NA NA NA NA 0 [0, 8] 9 [0, 30] 29 [2, 56] 52 [18, 86] 78 [35, 120] 107 [54, 160] 141 [76, 206] 180 [100, 259]
Placer 6 3 [3, 3] 4 [4, 4] 6 [5, 6] 7 [6, 8] 9 [8, 11] 11 [9, 14] 14 [11, 17] 17 [13, 21] 21 [15, 26] 25 [18, 32] 30 [20, 39] 35 [23, 47] 41 [27, 56] 49 [31, 67] 57 [35, 80]
Riverside 84 NA NA NA NA NA 19 [28, 11] 38 [50, 26] 60 [77, 43] 86 [109, 64] 117 [147, 87] 154 [193, 114] 197 [248, 146] 248 [314, 182] 309 [393, 224] 381 [488, 273]
Sacramento 71 NA NA NA NA NA NA NA NA 1 [0, 4] 12 [8, 16] 24 [19, 29] 38 [31, 45] 54 [46, 63] 73 [62, 83] 94 [81, 107]
San Benito 2 NA NA NA NA NA NA NA NA NA 0 [0, 1] 0 [0, 1] 0 [0, 2] 1 [0, 2] 1 [0, 3] 2 [0, 4]
San Bernardino 111 NA NA NA NA NA 37 [60, 14] 78 [113, 42] 130 [182, 77] 196 [272, 120] 281 [390, 173] 391 [544, 237] 532 [746, 317] 712 [1011, 414] 945 [1356, 534] 1244 [1808, 681]
San Diego 152 NA NA NA NA NA NA 5 [0, 14] 31 [18, 44] 62 [44, 80] 98 [75, 121] 140 [110, 170] 189 [151, 228] 247 [198, 296] 314 [252, 376] 393 [315, 471]
San Francisco 64 NA NA NA NA NA NA NA NA NA 0 [0, 7] 4 [0, 15] 10 [0, 23] 17 [2, 32] 25 [7, 42] 33 [12, 54]
San Joaquin 10 5 [5, 5] 6 [6, 7] 8 [7, 9] 10 [8, 12] 12 [9, 15] 14 [11, 18] 17 [12, 22] 20 [14, 27] 23 [15, 32] 27 [17, 37] 32 [19, 44] 36 [21, 52] 42 [23, 60] 48 [26, 70] 55 [28, 81]
San Luis Obispo 8 NA NA NA NA NA NA NA NA NA NA NA 0 [0, 1] 0 [0, 3] 0 [0, 5] 1 [0, 7]
San Mateo 27 NA NA 4 [3, 5] 8 [7, 10] 13 [11, 16] 19 [15, 22] 25 [21, 30] 33 [27, 39] 41 [33, 50] 51 [41, 62] 62 [49, 76] 75 [58, 92] 90 [69, 111] 106 [80, 132] 126 [94, 158]
Santa Barbara 14 NA NA 0 [0, 1] 3 [2, 4] 6 [4, 8] 10 [6, 13] 14 [9, 18] 19 [13, 25] 25 [17, 33] 32 [21, 42] 40 [26, 54] 50 [32, 68] 62 [38, 85] 76 [46, 106] 92 [54, 130]
Santa Clara 80 NA NA NA NA 0 [0, 6] 5 [0, 17] 14 [0, 28] 23 [4, 42] 33 [9, 56] 44 [14, 73] 56 [20, 92] 70 [27, 112] 85 [33, 136] 101 [41, 162] 120 [48, 192]
Santa Cruz 2 NA NA NA NA NA 0 [0, 1] 1 [0, 1] 1 [0, 2] 1 [0, 2] 2 [0, 3] 2 [1, 4] 3 [1, 4] 3 [1, 5] 4 [1, 6] 5 [1, 8]
Shasta 15 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Solano 21 NA NA NA NA NA NA NA NA NA 0 [0, 1] 0 [0, 5] 3 [0, 9] 6 [0, 14] 10 [0, 20] 15 [2, 27]
Sonoma 17 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Stanislaus 16 NA NA NA 1 [0, 2] 4 [2, 6] 7 [5, 10] 12 [8, 15] 17 [11, 22] 22 [16, 29] 29 [20, 38] 38 [26, 49] 47 [32, 62] 59 [39, 78] 73 [48, 97] 89 [57, 120]
Tulare 9 NA NA NA NA NA NA NA 1 [2, 1] 4 [4, 3] 6 [7, 6] 10 [11, 9] 13 [15, 12] 18 [20, 16] 24 [26, 22] 31 [34, 28]
Ventura 16 NA NA NA NA NA 0 [0, 2] 1 [0, 6] 4 [0, 10] 8 [0, 15] 11 [2, 21] 16 [4, 28] 21 [6, 36] 27 [8, 47] 35 [10, 59] 43 [13, 73]
Yolo 2 NA NA NA NA 0 [0, 1] 1 [0, 2] 1 [0, 2] 2 [0, 3] 2 [0, 4] 3 [1, 5] 4 [1, 7] 5 [1, 9] 6 [1, 11] 7 [1, 14] 9 [1, 17]
Yuba 7 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

Write the ICU tables to CSV for now

fwrite(wide_icu_str, 'data/wide_icu_str_v5.csv')
fwrite(wide_icu_excess_demand, 'data/wide_icu_excess_demand_v5.csv')

And let’s do the same for Acute/Floor beds

acute_time_series_long = long_dt %>% group_by(County, date) %>%
  summarise(
    est_floor_beds_available = max(round(num_acute_beds * PROP_OF_AHA_BEDS_AVAILABLE)),
    acute_census_lb = round(est_floor_census_long),
    acute_census_m = round((est_floor_census_long + est_floor_census_short)/2),
    acute_census_ub = round(est_floor_census_short),
  ) %>% ungroup()

acute_time_series_long = acute_time_series_long %>% mutate(acute_census_str = paste0(acute_census_m, ' [', acute_census_lb, ', ', acute_census_ub, ']'))

# acute_time_series_long %>% filter(County == "Santa Clara")

wide_acute_str = as.data.table(dcast(acute_time_series_long, County + est_floor_beds_available ~ date, value.var = c('acute_census_str')))
wide_acute_str %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
County est_floor_beds_available 2020-04-04 2020-04-05 2020-04-06 2020-04-07 2020-04-08 2020-04-09 2020-04-10 2020-04-11 2020-04-12 2020-04-13 2020-04-14 2020-04-15 2020-04-16 2020-04-17 2020-04-18
Alameda 269 40 [40, 40] 44 [43, 46] 50 [47, 52] 56 [51, 60] 62 [56, 69] 70 [61, 79] 79 [66, 91] 88 [72, 104] 99 [79, 120] 112 [86, 138] 126 [94, 158] 142 [102, 181] 160 [111, 208] 180 [121, 239] 203 [132, 275]
Butte 87 2 [2, 2] 2 [2, 2] 2 [2, 3] 3 [2, 3] 3 [2, 4] 4 [3, 5] 4 [3, 5] 5 [3, 6] 5 [3, 8] 6 [3, 9] 7 [4, 11] 8 [4, 13] 10 [4, 16] 12 [4, 19] 13 [5, 22]
Contra Costa 209 19 [19, 19] 22 [22, 22] 25 [25, 26] 29 [29, 30] 33 [33, 34] 38 [37, 39] 44 [43, 45] 50 [48, 52] 58 [55, 60] 66 [63, 69] 76 [72, 80] 87 [82, 92] 99 [93, 106] 114 [106, 122] 131 [121, 140]
El Dorado 35 9 [9, 9] 11 [10, 11] 12 [11, 13] 13 [12, 15] 15 [13, 17] 17 [14, 20] 19 [15, 23] 22 [17, 26] 24 [18, 31] 28 [20, 35] 31 [21, 41] 35 [23, 47] 40 [25, 55] 45 [27, 64] 52 [30, 74]
Fresno 258 9 [9, 9] 11 [11, 11] 13 [12, 13] 15 [14, 16] 18 [16, 19] 21 [19, 23] 25 [21, 28] 29 [25, 34] 34 [28, 41] 41 [32, 49] 48 [37, 59] 57 [43, 71] 67 [49, 86] 80 [57, 103] 95 [65, 124]
Humboldt 38 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 2 [2, 2] 2 [2, 2] 2 [2, 2] 3 [3, 3] 3 [3, 3] 4 [4, 4] 5 [5, 4] 5 [5, 5] 6 [6, 6]
Imperial 14 6 [6, 6] 7 [6, 7] 8 [7, 8] 9 [8, 10] 11 [9, 12] 12 [10, 15] 14 [11, 18] 17 [12, 21] 20 [14, 25] 23 [15, 30] 27 [17, 37] 32 [19, 44] 37 [21, 53] 44 [24, 64] 51 [27, 76]
Kern 202 22 [22, 22] 27 [28, 27] 33 [35, 32] 41 [43, 39] 50 [54, 47] 62 [67, 57] 76 [83, 68] 93 [103, 82] 114 [129, 99] 140 [160, 119] 172 [200, 144] 211 [249, 173] 259 [310, 209] 319 [386, 251] 392 [481, 303]
Los Angeles 2690 660 [660, 660] 780 [773, 788] 923 [905, 941] 1092 [1060, 1124] 1291 [1241, 1342] 1528 [1453, 1602] 1807 [1701, 1913] 2138 [1992, 2284] 2530 [2333, 2727] 2994 [2732, 3257] 3544 [3199, 3889] 4195 [3746, 4643] 4965 [4386, 5544] 5878 [5136, 6620] 6960 [6015, 7905]
Marin 89 3 [3, 3] 3 [3, 3] 4 [4, 4] 4 [4, 4] 5 [4, 5] 5 [4, 6] 5 [5, 6] 6 [5, 7] 7 [5, 8] 7 [6, 9] 8 [6, 10] 9 [6, 12] 10 [7, 13] 11 [7, 15] 12 [8, 17]
Merced 49 3 [3, 3] 4 [4, 4] 5 [4, 5] 5 [5, 6] 6 [6, 7] 8 [7, 9] 9 [8, 10] 11 [9, 13] 13 [10, 15] 15 [11, 18] 18 [13, 22] 21 [15, 27] 25 [17, 32] 29 [20, 39] 35 [23, 47]
Mono 4 14 [14, 14] 15 [15, 16] 17 [17, 18] 19 [18, 20] 21 [20, 22] 23 [22, 25] 26 [24, 28] 29 [26, 31] 32 [28, 35] 35 [31, 39] 39 [34, 44] 43 [37, 49] 48 [41, 55] 53 [44, 62] 59 [48, 70]
Monterey 106 10 [10, 10] 12 [12, 12] 13 [13, 14] 15 [15, 16] 17 [16, 19] 20 [18, 22] 23 [20, 25] 26 [23, 29] 30 [25, 34] 34 [29, 39] 39 [32, 45] 44 [36, 52] 50 [40, 61] 58 [45, 70] 66 [50, 82]
Napa 47 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 1] 1 [1, 2] 2 [1, 2] 2 [1, 2] 2 [1, 3] 2 [1, 3] 2 [1, 3] 3 [2, 4] 3 [2, 5]
Nevada 0 1 [1, 1] 1 [1, 1] 2 [2, 2] 2 [2, 2] 2 [2, 2] 2 [2, 2] 3 [2, 3] 3 [3, 3] 3 [3, 4] 4 [3, 4] 4 [4, 5] 5 [4, 6] 6 [5, 6] 6 [5, 7] 7 [6, 8]
Orange 721 93 [93, 93] 105 [104, 107] 120 [115, 124] 136 [129, 144] 155 [144, 167] 177 [160, 193] 201 [179, 224] 229 [199, 259] 261 [223, 300] 298 [248, 347] 340 [277, 402] 387 [309, 466] 442 [345, 540] 505 [384, 625] 576 [429, 723]
Placer 44 14 [14, 14] 16 [16, 17] 19 [18, 20] 22 [20, 23] 25 [22, 27] 28 [25, 32] 33 [28, 38] 38 [31, 45] 44 [34, 53] 50 [38, 62] 58 [43, 73] 67 [48, 86] 77 [53, 101] 89 [60, 119] 103 [66, 140]
Riverside 475 83 [83, 83] 97 [99, 96] 115 [119, 111] 135 [142, 129] 160 [170, 149] 188 [204, 173] 222 [245, 200] 263 [293, 232] 310 [352, 269] 366 [421, 311] 433 [505, 361] 512 [605, 418] 605 [725, 485] 715 [869, 562] 846 [1042, 651]
Sacramento 327 45 [45, 45] 52 [51, 52] 59 [59, 60] 68 [67, 69] 78 [76, 80] 90 [87, 92] 103 [99, 106] 118 [113, 123] 135 [129, 141] 155 [148, 163] 178 [168, 188] 205 [192, 217] 235 [219, 251] 270 [250, 289] 310 [286, 334]
San Benito 10 1 [1, 1] 1 [1, 2] 2 [2, 2] 2 [2, 2] 2 [2, 2] 2 [2, 3] 3 [2, 3] 3 [2, 4] 3 [2, 4] 4 [2, 5] 4 [3, 6] 5 [3, 7] 6 [3, 8] 6 [3, 10] 7 [3, 11]
San Bernardino 521 93 [93, 93] 118 [122, 115] 150 [160, 141] 191 [209, 173] 243 [273, 212] 309 [357, 260] 394 [467, 320] 502 [611, 393] 641 [800, 482] 819 [1046, 592] 1047 [1368, 727] 1341 [1789, 893] 1718 [2340, 1096] 2204 [3061, 1346] 2829 [4004, 1653]
San Diego 757 115 [115, 115] 134 [133, 136] 157 [154, 160] 183 [178, 189] 214 [205, 223] 250 [237, 263] 292 [274, 310] 341 [316, 365] 398 [366, 431] 465 [422, 508] 544 [488, 600] 635 [564, 707] 743 [651, 834] 868 [753, 984] 1015 [870, 1160]
San Francisco 231 51 [51, 51] 56 [55, 57] 61 [59, 63] 66 [63, 69] 72 [68, 77] 79 [73, 85] 86 [78, 94] 94 [83, 104] 102 [89, 115] 111 [96, 127] 122 [103, 141] 133 [110, 155] 145 [118, 172] 158 [127, 190] 173 [136, 210]
San Joaquin 51 28 [28, 28] 31 [30, 32] 35 [32, 37] 38 [35, 42] 42 [37, 48] 47 [40, 54] 52 [43, 62] 58 [46, 71] 65 [49, 81] 72 [52, 92] 80 [56, 105] 90 [60, 119] 100 [64, 136] 112 [69, 155] 125 [74, 176]
San Luis Obispo 49 2 [2, 2] 3 [3, 3] 3 [3, 3] 3 [3, 4] 4 [3, 5] 4 [3, 6] 5 [3, 7] 5 [3, 8] 6 [3, 9] 7 [3, 11] 8 [4, 13] 9 [4, 15] 11 [4, 17] 12 [4, 20] 14 [4, 24]
San Mateo 168 42 [42, 42] 48 [47, 49] 55 [53, 57] 63 [60, 66] 72 [67, 76] 82 [76, 88] 93 [85, 102] 107 [95, 118] 122 [107, 136] 139 [120, 158] 159 [135, 183] 182 [152, 212] 208 [170, 245] 238 [191, 284] 272 [215, 329]
Santa Barbara 51 18 [18, 18] 21 [20, 21] 24 [23, 26] 29 [26, 31] 34 [30, 37] 40 [34, 45] 47 [39, 54] 55 [45, 65] 65 [51, 79] 77 [59, 95] 91 [67, 115] 108 [77, 138] 127 [88, 167] 151 [101, 201] 179 [115, 242]
Santa Clara 395 105 [105, 105] 114 [111, 117] 125 [118, 131] 137 [126, 147] 149 [133, 165] 164 [142, 186] 179 [151, 208] 197 [160, 233] 216 [170, 262] 237 [181, 294] 261 [193, 329] 287 [205, 369] 316 [218, 414] 348 [231, 464] 383 [246, 521]
Santa Cruz 20 3 [3, 3] 3 [3, 3] 3 [3, 3] 4 [3, 4] 4 [3, 5] 4 [4, 5] 5 [4, 6] 5 [4, 7] 6 [4, 8] 7 [5, 9] 8 [5, 10] 9 [5, 12] 10 [6, 14] 11 [6, 16] 12 [6, 18]
Shasta 87 1 [1, 1] 2 [2, 1] 2 [2, 2] 2 [3, 2] 3 [3, 2] 3 [4, 3] 4 [5, 3] 5 [7, 4] 7 [9, 4] 8 [11, 5] 10 [15, 6] 13 [19, 7] 16 [24, 8] 20 [31, 10] 25 [39, 11]
Solano 94 10 [10, 10] 12 [12, 12] 13 [13, 14] 15 [14, 17] 17 [16, 19] 20 [17, 22] 23 [19, 26] 26 [21, 31] 30 [24, 36] 34 [26, 42] 39 [29, 49] 44 [32, 57] 51 [35, 66] 58 [39, 77] 67 [43, 90]
Sonoma 113 4 [4, 4] 5 [5, 5] 5 [5, 6] 6 [6, 7] 7 [6, 7] 7 [6, 9] 8 [7, 10] 9 [7, 11] 10 [8, 13] 12 [9, 14] 13 [9, 17] 14 [10, 19] 16 [11, 21] 18 [12, 25] 20 [12, 28]
Stanislaus 108 20 [20, 20] 23 [23, 24] 27 [26, 28] 32 [30, 34] 38 [34, 41] 45 [40, 49] 53 [46, 59] 62 [52, 72] 73 [60, 86] 86 [69, 103] 102 [80, 124] 121 [92, 150] 143 [106, 180] 169 [121, 216] 200 [140, 260]
Tulare 101 5 [5, 5] 6 [6, 6] 8 [8, 8] 9 [10, 9] 11 [12, 11] 14 [14, 13] 17 [17, 16] 20 [21, 20] 25 [26, 24] 30 [31, 28] 36 [38, 34] 44 [47, 41] 53 [57, 50] 65 [69, 60] 78 [84, 72]
Ventura 43 14 [14, 14] 16 [15, 16] 18 [16, 19] 21 [18, 23] 24 [20, 28] 27 [22, 33] 32 [24, 39] 37 [27, 47] 43 [29, 56] 50 [32, 67] 58 [36, 80] 67 [39, 95] 79 [43, 114] 92 [48, 136] 107 [53, 162]
Yolo 15 3 [3, 3] 3 [3, 3] 3 [3, 4] 4 [3, 5] 4 [3, 5] 5 [4, 7] 6 [4, 8] 7 [4, 10] 8 [4, 12] 9 [5, 14] 11 [5, 17] 13 [5, 20] 15 [6, 24] 18 [6, 29] 21 [7, 35]
Yuba 52 1 [1, 1] 2 [1, 2] 2 [2, 2] 2 [2, 2] 2 [2, 3] 3 [2, 3] 3 [2, 4] 3 [2, 4] 4 [3, 5] 4 [3, 6] 5 [3, 7] 6 [3, 8] 7 [4, 10] 8 [4, 12] 9 [4, 14] 
acute_time_series_long_f = acute_time_series_long %>%
  mutate(excess_demand = paste0(pmax(0, acute_census_m - est_floor_beds_available), 
                                ' [', pmax(0, acute_census_lb - est_floor_beds_available), ', ', 
                                acute_census_ub - est_floor_beds_available, ']')) %>%
  mutate(excess_demand = ifelse(acute_census_ub > est_floor_beds_available, excess_demand, NA))

wide_acute_excess_demand = as.data.table(dcast(acute_time_series_long_f, County + est_floor_beds_available ~ date, value.var = c('excess_demand')))
wide_acute_excess_demand %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover"))
County est_floor_beds_available 2020-04-04 2020-04-05 2020-04-06 2020-04-07 2020-04-08 2020-04-09 2020-04-10 2020-04-11 2020-04-12 2020-04-13 2020-04-14 2020-04-15 2020-04-16 2020-04-17 2020-04-18
Alameda 269 NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 [0, 6]
Butte 87 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Contra Costa 209 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
El Dorado 35 NA NA NA NA NA NA NA NA NA NA 0 [0, 6] 0 [0, 12] 5 [0, 20] 10 [0, 29] 17 [0, 39]
Fresno 258 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Humboldt 38 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Imperial 14 NA NA NA NA NA 0 [0, 1] 0 [0, 4] 3 [0, 7] 6 [0, 11] 9 [1, 16] 13 [3, 23] 18 [5, 30] 23 [7, 39] 30 [10, 50] 37 [13, 62]
Kern 202 NA NA NA NA NA NA NA NA NA NA NA NA 57 [108, 7] 117 [184, 49] 190 [279, 101]
Los Angeles 2690 NA NA NA NA NA NA NA NA 0 [0, 37] 304 [42, 567] 854 [509, 1199] 1505 [1056, 1953] 2275 [1696, 2854] 3188 [2446, 3930] 4270 [3325, 5215]
Marin 89 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Merced 49 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Mono 4 10 [10, 10] 11 [11, 12] 13 [13, 14] 15 [14, 16] 17 [16, 18] 19 [18, 21] 22 [20, 24] 25 [22, 27] 28 [24, 31] 31 [27, 35] 35 [30, 40] 39 [33, 45] 44 [37, 51] 49 [40, 58] 55 [44, 66]
Monterey 106 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Napa 47 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Nevada 0 1 [1, 1] 1 [1, 1] 2 [2, 2] 2 [2, 2] 2 [2, 2] 2 [2, 2] 3 [2, 3] 3 [3, 3] 3 [3, 4] 4 [3, 4] 4 [4, 5] 5 [4, 6] 6 [5, 6] 6 [5, 7] 7 [6, 8]
Orange 721 NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 [0, 2]
Placer 44 NA NA NA NA NA NA NA 0 [0, 1] 0 [0, 9] 6 [0, 18] 14 [0, 29] 23 [4, 42] 33 [9, 57] 45 [16, 75] 59 [22, 96]
Riverside 475 NA NA NA NA NA NA NA NA NA NA NA NA 130 [250, 10] 240 [394, 87] 371 [567, 176]
Sacramento 327 NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 [0, 7]
San Benito 10 NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 [0, 1]
San Bernardino 521 NA NA NA NA NA NA NA NA NA 298 [525, 71] 526 [847, 206] 820 [1268, 372] 1197 [1819, 575] 1683 [2540, 825] 2308 [3483, 1132]
San Diego 757 NA NA NA NA NA NA NA NA NA NA NA NA 0 [0, 77] 111 [0, 227] 258 [113, 403]
San Francisco 231 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
San Joaquin 51 NA NA NA NA NA 0 [0, 3] 1 [0, 11] 7 [0, 20] 14 [0, 30] 21 [1, 41] 29 [5, 54] 39 [9, 68] 49 [13, 85] 61 [18, 104] 74 [23, 125]
San Luis Obispo 49 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
San Mateo 168 NA NA NA NA NA NA NA NA NA NA 0 [0, 15] 14 [0, 44] 40 [2, 77] 70 [23, 116] 104 [47, 161]
Santa Barbara 51 NA NA NA NA NA NA 0 [0, 3] 4 [0, 14] 14 [0, 28] 26 [8, 44] 40 [16, 64] 57 [26, 87] 76 [37, 116] 100 [50, 150] 128 [64, 191]
Santa Clara 395 NA NA NA NA NA NA NA NA NA NA NA NA 0 [0, 19] 0 [0, 69] 0 [0, 126]
Santa Cruz 20 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Shasta 87 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Solano 94 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Sonoma 113 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Stanislaus 108 NA NA NA NA NA NA NA NA NA NA 0 [0, 16] 13 [0, 42] 35 [0, 72] 61 [13, 108] 92 [32, 152]
Tulare 101 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Ventura 43 NA NA NA NA NA NA NA 0 [0, 4] 0 [0, 13] 7 [0, 24] 15 [0, 37] 24 [0, 52] 36 [0, 71] 49 [5, 93] 64 [10, 119]
Yolo 15 NA NA NA NA NA NA NA NA NA NA 0 [0, 2] 0 [0, 5] 0 [0, 9] 3 [0, 14] 6 [0, 20]
Yuba 52 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 
fwrite(wide_acute_str, 'data/wide_acute_str_v5.csv')
fwrite(wide_acute_excess_demand, 'data/wide_acute_excess_demand_v5.csv')