census R 5 states

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")