Example #2. More Complex Tidycensus examples: multiple years, multiple geographies, multiple variables.

 This is a more complex example of a script to ���pull��� select variables from the ACS using my Census API key and the R package tidycensus.

 Step #0. Always need to load the relevant packages/libraries when starting up a new R-session. Otherwise, it won���t work.

# Step 0: Load relevant libraries into each R-session.

library(tidyverse)

library(tidycensus)

 In this set of examples, I���m extracting single year ACS variables (2005-2018) for all large (65,000+ population) places in the State of California.

# The get_acs function is run for each year of the single-year ACS data, from 2005 to 2018.

#  Note that group quarters data was not collected in 2005, but started in 2006.

#  Note the "_05_" included in the variable name in the first data "pull". That's a # #  mnemonic device that tells us it's for the year 2005.

#  Example 2.1 through 2.14: Run get_acs for large California Places, 2005-2018

#  Example 2.15:             Merge together data frames into a VERY wide database...lots of columns!

#  Example 2.16:             Merge in a file of Large San Francisco Bay Area places, and subset file.

#-------------------------------------------------------------------------------

place05  <- get_acs(survey="acs1", year=2005, geography = "place",   state = "CA",

                    show_call = TRUE, output="wide",

      variables = c(TotalPop_05_   = "B06001_001", # Total Population

                    Med_HHInc_05_  = "B19013_001", # Median Household Income

                    Agg_HHInc_05_  = "B19025_001", # Aggregate Household Income

                    HHldPop_05_    = "B11002_001", # Population in Households

                    Househlds_05_  = "B25003_001", # Total Households

                    Owner_OccDU_05_= "B25003_002", # Owner-Occupied Dwelling Units

                    Rent_OccDU_05_ = "B25003_003", # Renter-Occupied Dwelling Units

                    Med_HHVal_05_  = "B25077_001")) # Median Value of Owner-Occ DUs

place05$Avg_HHSize_05 <- place05$HHldPop_05_E / place05$Househlds_05_E

place05$MeanHHInc_05  <- place05$Agg_HHInc_05_E / place05$Househlds_05_E

#------------------------------------------------------------------------------------

place06  <- get_acs(survey="acs1", year=2006, geography = "place",   state = "CA",

                    show_call = TRUE, output="wide",

      variables = c(TotalPop_06_   = "B06001_001", # Total Population

                    Med_HHInc_06_  = "B19013_001", # Median Household Income

                    Agg_HHInc_06_  = "B19025_001", # Aggregate Household Income

                    HHldPop_06_    = "B11002_001", # Population in Households

                    Househlds_06_  = "B25003_001", # Total Households

                    Owner_OccDU_06_= "B25003_002", # Owner-Occupied Dwelling Units

                    Rent_OccDU_06_ = "B25003_003", # Renter-Occupied Dwelling Units

                    Med_HHVal_06_  = "B25077_001")) # Median Value of Owner-Occ DUs

place06$Avg_HHSize_06 <- place06$HHldPop_06_E / place06$Househlds_06_E

place06$MeanHHInc_06  <- place06$Agg_HHInc_06_E / place06$Househlds_06_E

#------------------------------------------------------------------------------------

These sets of codes are repeated for each single year ACS of interest, say for 2005 through 2018. Smarter ���R��� programmers will be able to tell me about ���do loops��� to make this process more efficient with magical wild cards.

The following step merges the data frames using the GEOID/NAME variables. This create a very ���wide��� database. One record per geography, and each column representing the variable/year combinations.

The ���merge��� function in ���R��� allows only two data frames to be joined by common columns at a time. I have yet to find a ���R��� function that allows me to merge all of the data frames at once.

#####################################################################################

#  Example 2.15: Merge together data frames into a VERY wide database...lots of columns!

# Merge the dataframes, adding a year in each step. All=TRUE is needed if # of places is different.

#

# (R-language newbie script...There are probably more terse/exotic ways of doing this!)

place0506 <- merge(place05,  place06, by = c('GEOID','NAME'), all=TRUE)

place0507 <- merge(place0506,place07, by = c('GEOID','NAME'), all=TRUE)

place0508 <- merge(place0507,place08, by = c('GEOID','NAME'), all=TRUE)

place0509 <- merge(place0508,place09, by = c('GEOID','NAME'), all=TRUE)

place0510 <- merge(place0509,place10, by = c('GEOID','NAME'), all=TRUE)

place0511 <- merge(place0510,place11, by = c('GEOID','NAME'), all=TRUE)

place0512 <- merge(place0511,place12, by = c('GEOID','NAME'), all=TRUE)

place0513 <- merge(place0512,place13, by = c('GEOID','NAME'), all=TRUE)

place0514 <- merge(place0513,place14, by = c('GEOID','NAME'), all=TRUE)

place0515 <- merge(place0514,place15, by = c('GEOID','NAME'), all=TRUE)

place0516 <- merge(place0515,place16, by = c('GEOID','NAME'), all=TRUE)

place0517 <- merge(place0516,place17, by = c('GEOID','NAME'), all=TRUE)

place0518 <- merge(place0517,place18, by = c('GEOID','NAME'), all=TRUE)

place_all <- place0518

View(place_all)

Sometimes you want to create smaller data frames with just a select number of columns. Here���s a good approach for that.

# The following functions output useful lists to the R-studio console which can then be edited

names(place_all)

dput(names(place_all)) # most useful for subsetting variables

# The purpose here is to re-order and select variables into a much more compact

#  database, for eventual exporting into a CSV file, and then into Excel for finishing touches.

selvars <- c("GEOID", "NAME",

             "TotalPop_05_E", "TotalPop_06_E", "TotalPop_07_E", "TotalPop_08_E",

             "TotalPop_09_E", "TotalPop_10_E", "TotalPop_11_E", "TotalPop_12_E",

             "TotalPop_13_E", "TotalPop_14_E", "TotalPop_15_E", "TotalPop_16_E",

             "TotalPop_17_E", "TotalPop_18_E")

# note the brackets for outputing new data frame from previous data frame....

place_all2 <- place_all[selvars]

# View the Selected Variables Table

View(place_all2)

# Set directory for exported data files, MacOS directory style

setwd("~/Desktop/tidycensus_work/output")

# Export the data frames to CSV files, for importing to Excel, and applying finishing touches

write.csv(place_all2,"ACS_AllYears_TotalPop_Calif_Places.csv")

write.csv(place_all, "ACS_AllYears_BaseVar_Calif_Places.csv")

In this last example, I���m reading in a file of large places in the Bay Area (manually derived from the CSV file created previous) in order to subset Bay Area ���large places��� from State of California ���large places���.

#####################################################################################

#  Example 2.16: Merge in a file of Large San Francisco Bay Area places, and subset file.

# Read in a file with the Large SF Bay Area Places, > 65,000 population

# and merge with the All Large California Places

bayplace <- read.csv("BayArea_Places_65K.csv")

Bayplace1 <- merge(bayplace,place_all,  by = c('NAME'))

Bayplace2 <- merge(bayplace,place_all2, by = c('NAME'))

write.csv(Bayplace1,"ACS_AllYears_BaseVar_BayArea_Places.csv")

write.csv(Bayplace2,"ACS_AllYears_TotalPop_BayArea_Places.csv")

This concludes Example #2: ���multiple geographies / multiple years/ multiple variables��� with only one record (row) per each geography.