est_county_fatalities

1. Load Data

Conditional Fatality Rates by Comorbidity and Age

These numbers are based on data from the Chinese CDC (http://weekly.chinacdc.cn/en/article/id/e53946e2-c6c4-41e9-9a9b-fea8db1a8f51).

I assume we can use these rates to estimate a lower bound of US hospitalizations by comorbidity and an upper bound for the number of fatalities.

I use the conditional fatality rates by cormobidity for a county’s 65+ population, while I use the conditional fatality rates by age group for the population aged <65 where fatality rates are lower and we do not have CMS comorbidity statistics.

cond_fatality_rates = fread('data/ccdc_rates_v4.csv')
cond_fatality_rates  %>% kable() %>% kable_styling()

age_decade	comorbidity	case_fatality_rate
65+	Hypertension	0.0600000
65+	Diabetes	0.0730000
65+	Cardiovascular disease	0.1050000
65+	Chronic respiratory disease	0.0630000
65+	Cancer	0.0560000
65+	None	0.0090000
0–9	Any	0.0000000
10–19	Any	0.0018215
20–29	Any	0.0019342
30–39	Any	0.0023684
40–49	Any	0.0044336
50–59	Any	0.0129896
60–69	Any	0.0360014

County Age Distribution

Data source:

Census County Population by Characteristics: 2010-2018 (https://www.census.gov/data/datasets/time-series/demo/popest/2010s-counties-detail.html?)

Using 2018 population estimates.

I just need to have the population under 65, and 65+ for each county to join with the comorbidities and fatality rates.

age_buckets_by_county = fread('data/county_age_populations_v4.csv')
age_buckets_by_county[CTYNAME == "Santa Clara County"]  %>% kable() %>% kable_styling()

FIPS	STNAME	CTYNAME	age_decade	pop
6085	California	Santa Clara County	0–9	231605
6085	California	Santa Clara County	10–19	237135
6085	California	Santa Clara County	20–29	278072
6085	California	Santa Clara County	30–39	302185
6085	California	Santa Clara County	40–49	270163
6085	California	Santa Clara County	50–59	252777
6085	California	Santa Clara County	60–69	104502
6085	California	Santa Clara County	65+	261131

County Comorbidity Distribution for 65+ Medicare Population

Data from 2017 for FFS Medicare beneficiaries aged 65+ From CMS https://www.cms.gov/Research-Statistics-Data-and-Systems/Statistics-Trends-and-Reports/Chronic-Conditions/CC_Main

I had to make some assumptions about how comorbidities overlap to get the rates in the table below. Code used to generate this data can be found here: https://rpubs.com/jferstad/covid-cms-comorbidity-data-load-v01

county_ccs = fread('data/output_cms_county_ccs_v3.csv')
county_ccs[County == "Santa Clara"]  %>% kable() %>% kable_styling()

State	County	FIPS	comorbidity	value
California	Santa Clara	6085	Cardiovascular disease	0.0820000
California	Santa Clara	6085	Diabetes	0.2423520
California	Santa Clara	6085	Chronic respiratory disease	0.0418902
California	Santa Clara	6085	Hypertension	0.3320891
California	Santa Clara	6085	Cancer	0.0253402
California	Santa Clara	6085	None	0.2763286

2. Estimating Severe COVID-19 Cases in each County

I assume everyone in each county will be infected by COVID-19 here. You can scale the estimate down by your expected infection rate.

Steps to get simple estimate of severe cases:

Join the county population numbers for 65+ population with CMS comorbidity distribution for 65+ population. Keep the age distribution of <65 population.
Join the result from (1) with CCDC conditional fatality rates by comorbidity (for 65+ population) and by age (for <65 population).
Calculate and report the expected number of critical cases within each subset of the population.

Step 1: Join the county population numbers for 65+ population with CMS comorbidity distribution for 65+ population. Keep the age distribution of <65 population.

merged_old_pop_ccs = merge(
  age_buckets_by_county[age_decade == "65+", .(FIPS, State = STNAME, County = CTYNAME, age_decade, population_in_age_group = pop)],
  county_ccs[, .(FIPS, comorbidity, p_with_cormobidity = value)],
  by = 'FIPS')

unioned_pop_ccs = rbind(
  merged_old_pop_ccs,
  age_buckets_by_county[age_decade != "65+", 
                      .(FIPS, State = STNAME, County = CTYNAME, age_decade, population_in_age_group = pop, comorbidity = "Any", p_with_cormobidity = 1)]
)

# filter and subset
unioned_pop_ccs[County == "Santa Clara County"]  %>% kable() %>% kable_styling()

FIPS	State	County	age_decade	population_in_age_group	comorbidity	p_with_cormobidity
6085	California	Santa Clara County	65+	261131	Cardiovascular disease	0.0820000
6085	California	Santa Clara County	65+	261131	Diabetes	0.2423520
6085	California	Santa Clara County	65+	261131	Chronic respiratory disease	0.0418902
6085	California	Santa Clara County	65+	261131	Hypertension	0.3320891
6085	California	Santa Clara County	65+	261131	Cancer	0.0253402
6085	California	Santa Clara County	65+	261131	None	0.2763286
6085	California	Santa Clara County	0–9	231605	Any	1.0000000
6085	California	Santa Clara County	10–19	237135	Any	1.0000000
6085	California	Santa Clara County	20–29	278072	Any	1.0000000
6085	California	Santa Clara County	30–39	302185	Any	1.0000000
6085	California	Santa Clara County	40–49	270163	Any	1.0000000
6085	California	Santa Clara County	50–59	252777	Any	1.0000000
6085	California	Santa Clara County	60–69	104502	Any	1.0000000

Step 2: Join the result from (1) with CCDC conditional fatality rates by comorbidity (for 65+ population) and by age (for <65 population).

# Join on comorbidity fatality rates for 65+ population
merged_pop_ccs_fatality_rate = merge(
  unioned_pop_ccs, 
  cond_fatality_rates,
  by=c('age_decade','comorbidity'))

merged_pop_ccs_fatality_rate[County == "Santa Clara County"]  %>% kable() %>% kable_styling()

age_decade	comorbidity	FIPS	State	County	population_in_age_group	p_with_cormobidity	case_fatality_rate
0–9	Any	6085	California	Santa Clara County	231605	1.0000000	0.0000000
10–19	Any	6085	California	Santa Clara County	237135	1.0000000	0.0018215
20–29	Any	6085	California	Santa Clara County	278072	1.0000000	0.0019342
30–39	Any	6085	California	Santa Clara County	302185	1.0000000	0.0023684
40–49	Any	6085	California	Santa Clara County	270163	1.0000000	0.0044336
50–59	Any	6085	California	Santa Clara County	252777	1.0000000	0.0129896
60–69	Any	6085	California	Santa Clara County	104502	1.0000000	0.0360014
65+	Cancer	6085	California	Santa Clara County	261131	0.0253402	0.0560000
65+	Cardiovascular disease	6085	California	Santa Clara County	261131	0.0820000	0.1050000
65+	Chronic respiratory disease	6085	California	Santa Clara County	261131	0.0418902	0.0630000
65+	Diabetes	6085	California	Santa Clara County	261131	0.2423520	0.0730000
65+	Hypertension	6085	California	Santa Clara County	261131	0.3320891	0.0600000
65+	None	6085	California	Santa Clara County	261131	0.2763286	0.0090000

Step 3: Calculate and report the expected number of fatalities within each subset of the population.

These estimates assume a 100% infection rate!

merged_pop_ccs_fatality_rate[,`:=`(
  est_fatalities = population_in_age_group * p_with_cormobidity * case_fatality_rate, 
  pop_within_comorbidity = population_in_age_group * p_with_cormobidity)]

Numbers for Santa Clara County:

merged_pop_ccs_fatality_rate[
  County == "Santa Clara County", 
  .(County, age_decade, comorbidity, est_fatalities_ub = round(est_fatalities), 
    population = round(pop_within_comorbidity),
    pct_fatality_rate = round(est_fatalities/pop_within_comorbidity*100, 2)
    )][
      order(age_decade, est_fatalities_ub, decreasing = TRUE)]  %>%
  kable() %>% kable_styling()

County	age_decade	comorbidity	est_fatalities_ub	population	pct_fatality_rate
Santa Clara County	65+	Hypertension	5203	86719	6.00
Santa Clara County	65+	Diabetes	4620	63286	7.30
Santa Clara County	65+	Cardiovascular disease	2248	21413	10.50
Santa Clara County	65+	Chronic respiratory disease	689	10939	6.30
Santa Clara County	65+	None	649	72158	0.90
Santa Clara County	65+	Cancer	371	6617	5.60
Santa Clara County	60–69	Any	3762	104502	3.60
Santa Clara County	50–59	Any	3283	252777	1.30
Santa Clara County	40–49	Any	1198	270163	0.44
Santa Clara County	30–39	Any	716	302185	0.24
Santa Clara County	20–29	Any	538	278072	0.19
Santa Clara County	10–19	Any	432	237135	0.18
Santa Clara County	0–9	Any	0	231605	0.00

fwrite(merged_pop_ccs_fatality_rate[,.(FIPS, State, County, age_decade, comorbidity, pop_within_comorbidity, case_fatality_rate, est_fatalities)], 'data/merged_pop_ccs_fatality_rate_v4.csv')

3. Hospitalization for Santa Clara County given additional assumptions and different infection rates

Assumptions:

# of critical cases / # of fatalities = 2087/1023 = 2.04 (from CCDC data)
# of severe cases / # of fatalities = 6168/1023 = 6.03 (from CCDC data)

Estimating the number of fatalities, critical cases, and severe cases per case in Santa Clara County

COUNTY = "Santa Clara County"
CRITICAL_CASES_PER_FATALITY = 2.04
SEVERE_CASES_PER_FATALITY = 6.03

est_county_fatalities_per_case = merged_pop_ccs_fatality_rate[County == COUNTY, sum(est_fatalities)/sum(pop_within_comorbidity)]
est_county_critical_cases_per_case = est_county_fatalities_per_case*CRITICAL_CASES_PER_FATALITY
est_county_severe_cases_per_case = est_county_fatalities_per_case*SEVERE_CASES_PER_FATALITY

county_population = merged_pop_ccs_fatality_rate[County == COUNTY, sum(pop_within_comorbidity)]

as.data.table(rbind(
  c('Fatalities per Case', round(est_county_fatalities_per_case,3)),
  c('Critical Cases per Case', round(est_county_critical_cases_per_case, 3)),
  c('Severe Cases per Case', round(est_county_severe_cases_per_case, 3))
)) %>%
  kable() %>% kable_styling()

V1	V2
Fatalities per Case	0.012
Critical Cases per Case	0.025
Severe Cases per Case	0.074

Estimating the number of fatalities, critical cases, severe cases in Santa Clara County with infection rates of: `0.1%, 1%, 10%, and 50%`.

INFECTION_RATES = c(0.001, 0.01, 0.1, 0.5)
county_estimates = data.table(infection_rate = INFECTION_RATES)
county_estimates[, cases := round(infection_rate * county_population)]
county_estimates[, `:=`(
  severe_cases = round(cases * est_county_severe_cases_per_case),
  critical_cases = round(cases * est_county_critical_cases_per_case),
  fatalities = round(cases * est_county_fatalities_per_case))]
county_estimates %>%
  kable() %>% kable_styling()

infection_rate	cases	severe_cases	critical_cases	fatalities
0.001	1938	143	48	24
0.010	19376	1430	484	237
0.100	193757	14297	4837	2371
0.500	968785	71484	24184	11855

4. Estimates of Fatalities for Top 100 US Counties

These are estimates given 100% infection rate.

merged_pop_ccs_fatality_rate[, .(
  est_fatalities_ub = round(sum(est_fatalities)),
  pct_fatalities_county_pop = round(sum(est_fatalities)/sum(pop_within_comorbidity)*100, 2)
  ), by = c('State', 'County')][order(est_fatalities_ub, decreasing = TRUE)][1:100] %>%
  kable() %>% kable_styling()

State	County	est_fatalities_ub	pct_fatalities_county_pop
California	Los Angeles County	130594	1.29
Illinois	Cook County	69605	1.34
Arizona	Maricopa County	58046	1.32
Texas	Harris County	50401	1.07
California	Orange County	43293	1.36
Florida	Miami-Dade County	41452	1.50
California	San Diego County	40941	1.22
New York	Kings County	35240	1.36
New York	Queens County	34126	1.50
California	Riverside County	31142	1.27
Nevada	Clark County	29266	1.31
Texas	Dallas County	28649	1.09
Florida	Broward County	28429	1.46
Florida	Palm Beach County	27523	1.85
Washington	King County	26090	1.17
Michigan	Wayne County	25740	1.47
California	San Bernardino County	24336	1.12
Texas	Tarrant County	23738	1.14
California	Santa Clara County	23709	1.22
Texas	Bexar County	22787	1.15
New York	Suffolk County	22776	1.54
New York	New York County	22065	1.35
New York	Nassau County	21458	1.58
Massachusetts	Middlesex County	21371	1.32
California	Alameda County	21147	1.27
Pennsylvania	Philadelphia County	20524	1.30
California	Sacramento County	19655	1.28
Ohio	Cuyahoga County	18971	1.53
Florida	Pinellas County	18959	1.94
Florida	Hillsborough County	18937	1.32
Pennsylvania	Allegheny County	18766	1.54
Michigan	Oakland County	18738	1.49
New York	Bronx County	17991	1.26
Florida	Orange County	16289	1.18
California	Contra Costa County	15870	1.38
Arizona	Pima County	15819	1.52
Florida	Lee County	15529	2.06
Ohio	Franklin County	15251	1.16
Missouri	St. Louis County	15040	1.51
Hawaii	Honolulu County	14856	1.52
Minnesota	Hennepin County	14795	1.17
Maryland	Montgomery County	14456	1.37
New Jersey	Bergen County	14354	1.53
Virginia	Fairfax County	14322	1.24
New York	Erie County	14203	1.54
New York	Westchester County	14011	1.45
Michigan	Macomb County	13462	1.54
Connecticut	Hartford County	13223	1.48
Connecticut	Fairfield County	13186	1.40
Connecticut	New Haven County	12951	1.51
Illinois	DuPage County	12621	1.36
Florida	Duval County	12590	1.33
North Carolina	Wake County	12517	1.15
Texas	Travis County	12490	1.00
Pennsylvania	Montgomery County	12470	1.50
Maryland	Baltimore County	12386	1.50
Tennessee	Shelby County	12187	1.30
North Carolina	Mecklenburg County	12049	1.10
Wisconsin	Milwaukee County	12027	1.27
Maryland	Prince George’s County	12002	1.32
California	Ventura County	11911	1.40
California	San Francisco County	11891	1.35
Georgia	Fulton County	11829	1.13
Florida	Polk County	11783	1.66
New Jersey	Middlesex County	11769	1.42
Utah	Salt Lake County	11688	1.01
Indiana	Marion County	11612	1.22
California	Fresno County	11573	1.16
Massachusetts	Essex County	11556	1.46
Florida	Brevard County	11513	1.93
Massachusetts	Worcester County	11476	1.38
Ohio	Hamilton County	11431	1.40
Texas	Collin County	11293	1.12
New Jersey	Ocean County	11146	1.85
New York	Monroe County	11104	1.50
Washington	Pierce County	11047	1.24
Kentucky	Jefferson County	10849	1.41
New Jersey	Essex County	10658	1.33
Florida	Volusia County	10550	1.93
Florida	Sarasota County	10491	2.46
California	San Mateo County	10416	1.35
Florida	Collier County	10251	2.71
Massachusetts	Norfolk County	10094	1.43
Oklahoma	Oklahoma County	10085	1.27
Washington	Snohomish County	10057	1.23
Pennsylvania	Bucks County	10023	1.60
Georgia	Gwinnett County	9980	1.08
Florida	Pasco County	9870	1.83
New Jersey	Monmouth County	9840	1.58
California	Kern County	9828	1.10
Florida	Marion County	9773	2.71
Texas	El Paso County	9669	1.15
Missouri	Jackson County	9489	1.35
Alabama	Jefferson County	9485	1.44
Texas	Hidalgo County	9381	1.08
Massachusetts	Suffolk County	9373	1.16
Oregon	Multnomah County	9324	1.15
Illinois	Lake County	9309	1.33
Texas	Denton County	9184	1.07
California	San Joaquin County	9149	1.22

Prototype Forecast of COVID-19 fatalities in US Counties