Census data was acquired via the United States Census Bureau’s APIs. In this analyses we pull data from the 5-year American Community Census Data Profiles data.
This document outlines how to obtain the data from the API using R and the required information to do so successfully. Additionally, we aggregated county-level hospital data from American Hospital Association data, years 2017-2019. Currently have set all counties with NA for hospital as None for ICU beds.
aha2017_2019 <- readRDS(paste0(data_dir,
"/prepared_data/aha2017_2019.county.Rds"))
aha2019 <- readRDS(paste0(data_dir,
"/prepared_data/aha2019.county.Rds"))
#Bring in config file with new variable names and labels
#This will be used to create a labels list now and rename variables later.
acs.labels <- read.csv(paste0(data_dir,
"/acs/config.csv"), header = T)
# turn labels into list object.
# pre-allocate a list and fill it with human-readable var names a loop
acs.labels.list <- vector("list", nrow(acs.labels))
for (i in 1:nrow(acs.labels)) {
acs.labels.list[[i]] <- acs.labels[i,2]
}
#name elements using var name
acs.labels.list <- setNames(acs.labels.list, acs.labels[,1])# all population characteristics
acs0 <- get_acs(geography = "county",
variables = c(
"DP05_0001E", #Total pop
"DP05_0021E", # total pop 18+
"DP05_0021PE", # %pop 18+
"DP05_0024PE", # %pop 65%
"DP05_0019PE", # %pop < 18
"DP05_0003P", # %female
"DP05_0037PE", # %White
"DP05_0038PE", # %Black
"DP05_0039PE", # %AI/AN
"DP05_0044PE", # %Asian
"DP05_0052PE", # %NH/PI
"DP05_0057PE", # %Other
"DP05_0058PE", # 2+ races
"DP05_0071PE", # %Hispanic
"DP02_0059PE", # %25yr+ and <9th grade
"DP02_0066PE", # %25yr+ and at least HS graduate
"DP03_0027PE", # %16yr+ white collar jobs
"DP03_0099PE", # %wo health insurance
"DP03_0088E", # $per capita income
"DP03_0086E", # $median family income
"DP03_0062E", # $mean household income
"DP03_0052PE", # %household income <10K
"DP03_0053PE", # %household income 10-14K
"DP03_0054PE", # %household income 15-24K
"DP03_0055PE", # %household income 25-34K
"DP03_0056PE", # %household income 35-49K
"DP03_0057PE", # %household income 50-74K
"DP03_0058PE", # %household income 75-99K
"DP03_0059PE", # %household income 100-149K
"DP03_0060PE", # %household income 150-199K
"DP03_0061PE", # %household income 200K+
"DP04_0089E", # $Median home value
"DP04_0109E", # $Median rent
"DP04_0101E", # $Median mortgage
"DP04_0046PE", # %owner occupied
"DP03_0005PE", # %civilian labor force 16yr+ unemployed
"DP03_0119PE", # % families below poverty
"DP03_0128PE", # % individuals below poverty
"DP02_0007PE", # % male single parent family
"DP02_0009PE", # % female single parent family
"DP04_0058PE", # % households no car
"DP04_0075PE", # % households no phone service (inc cell)
"DP04_0073PE", # % households no plumbing
"DP04_0077PE", # % households 1 or less people per room
"DP05_0024PE", # % 65yr+
"DP05_0019PE", # % < 18yr
"DP02_0071PE", # % with a disability
"DP02_0113PE", # % 5yr+ speak English < very well
"DP04_0009PE", # % houses 2 units
"DP04_0010PE", # % houses 3-4 units
"DP04_0011PE", # % houses 5-9 units
"DP04_0012PE", # % houses 10-19 units
"DP04_0013PE", # % houses 20+ units
"DP04_0014PE", # % mobile homes
"DP02_0151PE" # % houses with computer
),
year = 2019,
survey = "acs5",
geometry = FALSE
)
acs1 <- left_join(acs0, acs.labels, by = "variable")
acs2 <- pivot_wider(acs1, id_cols = c(GEOID, NAME), names_from = name, values_from = estimate)
## Population characteristics that need wrangling
# % Minority - the complement of %White
acs3 <- acs2 %>%
group_by(NAME) %>%
mutate(
over50k = sum(perc.house.50_74k,
perc.house.75_99k,
perc.house.100_149k,
perc.house.150_199k,
perc.house.ge.200k), #Income disparity, create %under 50K
perc.singleparent = sum(perc.fam.singlemale,
perc.fam.singlefemale),# single parent households - add up male and female single parent homes
perc.multiunithouse = sum(perc.house.2unit,
perc.house.3_4unit,
perc.house.5_9unit,
perc.house.10_19unit,
perc.house.ge.20unit)#Multi unit housing, sum % of all relevant categories
)%>%
ungroup()%>%
mutate(
perc.minority = (100-perc.white), # % Minority - the complement of %White
perc.l.highschool = (100-perc.ge.highschool),# less than HS diploma - complement of %25yr+ and at least HS graduate
perc.house.g.1perroom = (100-perc.house.le.1perroom), # Houses >1 person per room - complement of 1 or less person per room
income.disparity = log(100*perc.house.l.10k/over50k),# Income disparity=log of 100 x ratio <10K/>50K
total.thousands = total/1000,#simple division for presentation
mean.indi.income.thousands = mean.indi.income/1000,
median.fam.income.thousands = median.fam.income/1000,
median.house.income.thousands = median.house.income/1000,
median.house.value.thousands = median.house.value/1000,
median.mortgage.thousands = median.mortgage/1000)%>%
select(-NAME)
#Join with hospital data
final2017_2019 <- left_join(acs3, aha2017_2019, by = "GEOID")
#Create per capita variables and dichotomize at median
final2017_2019 <- final2017_2019 %>%
mutate(adult_icubeds_per1k = 1000*mean.adult.icu.beds/total.ge.18,
adult_intensivists_per1k = 1000*mean.adult.fte/total.ge.18,
adult_icubeds_per1k_median =
if_else(is.na(adult_icubeds_per1k) == T, "None",
if_else(percent.missing>25, "Unreliable",
if_else(adult_icubeds_per1k<0.14, "Low",
if_else(adult_icubeds_per1k >= 0.14, "High",
NA_character_)))))Here we describe national-level summary of county characteristics. We have excluded counties with less than 1000 persons.
ICU Beds per 1000 adults
Intensivists per 1000 adults
Number of hospitals with eICU
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.0000 0.0000 0.1411 0.2019 0.2928 13.7694 751## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.0000 0.0000 0.0000 0.0349 0.0188 7.2546 751## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.0000 0.0000 0.0000 0.4066 1.0000 13.0000 751Here we describe county-level characteristics by per capita density of ICU beds. Specifically we dichotomize the number of Adult ICU beds per 1000 adults at the national median of 0.14 Adult beds per 1000 adults. We have excluded counties with less than 1000 persons.
High = Counties greater or equal to the median 0.14 ICU Beds per 1000 Adults
Low = Counties below the median 0.14 ICU Beds per 1000 Adults
None = Counteis without ICU Beds
Unreliable = Counties with > 25% of hospitals not reporting within 3-year span
| High (N=1123) | Low (N=874) | None (N=715) | Unreliable (N=472) | Total (N=3184) | |
|---|---|---|---|---|---|
| Total population, thousands | |||||
| - Mean (SD) | 190.1 (470.7) | 40.5 (100.9) | 18.2 (20.6) | 139.9 (382.8) | 103.0 (329.0) |
| - Range | 1.4 - 10081.6 | 1.0 - 2287.4 | 1.0 - 215.0 | 1.3 - 5198.3 | 1.0 - 10081.6 |
| Population 18+, % | |||||
| - Mean (SD) | 77.7 (3.0) | 77.5 (3.4) | 78.6 (3.9) | 77.3 (3.7) | 77.8 (3.5) |
| - Range | 66.1 - 87.7 | 64.0 - 87.6 | 59.6 - 92.7 | 58.2 - 86.6 | 58.2 - 92.7 |
| Persons 65+, % | |||||
| - Mean (SD) | 17.8 (4.1) | 19.3 (4.4) | 20.1 (4.9) | 18.0 (4.7) | 18.8 (4.6) |
| - Range | 6.6 - 39.6 | 7.2 - 36.4 | 5.5 - 56.7 | 3.2 - 38.6 | 3.2 - 56.7 |
| Female, % | |||||
| - Mean (SD) | 50.5 (1.6) | 49.6 (2.4) | 49.5 (3.0) | 50.2 (2.1) | 50.0 (2.3) |
| - Range | 37.0 - 55.4 | 34.0 - 54.2 | 27.3 - 57.2 | 34.9 - 55.3 | 27.3 - 57.2 |
| Any racial minority, % | |||||
| - Mean (SD) | 17.8 (15.2) | 13.8 (14.8) | 18.6 (20.0) | 22.8 (19.6) | 17.6 (17.2) |
| - Range | 0.7 - 82.5 | 0.4 - 88.8 | 0.2 - 96.4 | 0.5 - 91.7 | 0.2 - 96.4 |
| Hispanic, % | |||||
| - Mean (SD) | 10.0 (13.8) | 10.0 (14.7) | 15.1 (28.3) | 12.6 (20.9) | 11.6 (19.4) |
| - Range | 0.0 - 99.4 | 0.1 - 99.2 | 0.0 - 100.0 | 0.0 - 99.6 | 0.0 - 100.0 |
| Population below poverty level, % | |||||
| - Mean (SD) | 14.9 (5.8) | 14.3 (6.0) | 18.6 (11.5) | 17.4 (8.7) | 16.0 (8.1) |
| - Range | 3.1 - 51.5 | 3.3 - 42.4 | 2.4 - 64.5 | 3.4 - 55.9 | 2.4 - 64.5 |
| Families below poverty level, % | |||||
| - Mean (SD) | 10.6 (5.1) | 10.2 (5.2) | 14.3 (11.1) | 13.0 (8.1) | 11.7 (7.5) |
| - Range | 1.1 - 49.5 | 0.5 - 37.4 | 1.0 - 59.9 | 2.1 - 51.5 | 0.5 - 59.9 |