Created
September 29, 2021 19:10
-
-
Save jimcrozier/894291a58abb641ae92cba10d64c4268 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
library(tidyverse) | |
library(tidycensus) | |
#install your acs api key | |
#census_api_key(YOUR_KEY, install=T) | |
pops <- get_acs(geography = "county", | |
variables = c("B19013_001", | |
"B01002_001", | |
"B01003_001","B01001_002", #sex, male | |
"B01001A_001", #swhite | |
"B15003_001","B15003_017",#education, HS | |
"B08303_001","B08303_013",#travel more than 90min, | |
"B05001_001", "B05001_006" #US citizens | |
), | |
state = c("MI", "WI", "OH", "IN","IL"), | |
geometry = FALSE,year = 2019,survey = "acs5" | |
) | |
pops$state = "" | |
pops$county = "" | |
#dbWriteTable(con, "acs_dat", pops) | |
for(i in 1:NROW(pops)){ | |
pops$county[i] = strsplit(pops$NAME[i],",")[[1]][1] | |
pops$state[i] = strsplit(pops$NAME[i],",")[[1]][2] | |
} | |
library(sqldf) | |
pop_out = sqldf("with dat as ( | |
SELECT state, county, | |
sum(case when variable = 'B19013_001' then estimate end) as median_income, | |
sum(case when variable = 'B01002_001' then estimate end) as median_age, | |
sum(case when variable = 'B01003_001' then estimate end) as pop_tot, | |
sum(case when variable = 'B01001_002' then estimate end) as pop_male, | |
sum(case when variable = 'B01001A_001' then estimate end) as pop_white, | |
sum(case when variable = 'B15003_001' then estimate end) as educ_tot, | |
sum(case when variable = 'B15003_017' then estimate end) as educ_hs, | |
sum(case when variable = 'B08303_001' then estimate end) as travel_tot, | |
sum(case when variable = 'B08303_013' then estimate end) as travel_gt90, | |
sum(case when variable = 'B05001_001' then estimate end) as citizen_tot, | |
sum(case when variable = 'B05001_006' then estimate end) as citizen_non_us | |
FROM pops | |
group by state, county) | |
select state, county, | |
median_income, | |
median_age, | |
pop_tot, | |
educ_tot, | |
travel_tot, | |
citizen_tot, | |
pop_male/pop_tot pop_male_pct, | |
pop_white/pop_tot pop_white_pct, | |
educ_hs/educ_tot educ_hs_pct, | |
travel_gt90/travel_tot travel_gt90_pct, | |
citizen_non_us/citizen_tot citizen_non_us_pct | |
from dat ") | |
write_csv(pop_out, "pop_out.csv") | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment