Lab 03 Dataframe and Accessing Census Data

• Working Directory
• Access Census Data
• Dataframe
• Census Data Revisit
• 📚 TODO: Explore Census Demographic Data

Working Directory

# show the current working directory
getwd()

## [1] "/Users/yohaoyu/Repo/quantitative-method/labs/lab-03"

# modify the folder where this `.Rmd` is stored as the working directory
# setwd("/address") 

• .Rmd files use their current directory as a working directory by default, but we can change it by using setwd().
• For larger projects, instead of setting a working directory, it is usually better to use RStudio projects to manage working directories.
• Windows users: If you copy a path from Explorer, make sure to change backslashes (\) to forward slashes (/) for the file paths

Create a New Project

An R project enables your work to be bundled in a portable, self-contained folder. Within the project, all the relevant scripts, data files, figures/outputs, and history are stored in sub-folders, and importantly, the working directory is the project’s root folder. Unless you are working on a large project, using project may not be necessary.

Access Census Data

In this lab, we will explore datasets from American Community Survey (ACS) via DATA.CENSUS.GOV and R package tidycensus. A more detailed introduction on using R for census data can refer to Analyzing US Census Data: Methods, Maps, and Models in R.

We will use 2024 ACS 5-year estimate for Table B19001 Household Income in the Past 12 Months and Table B19013 Median Household Income in the Past 12 Months, as examples. Some intro to this data from Census Reporter: B19001 and B19013.

Remember ACS is the estimates from the population (census uses more inclusive term “universe”). Each table describes the specific universe of interest for that set of estimates. In terms of B19001, census defined the universe as the U.S. resident population and housing.

Remember that the ACS is a sample survey: the Census Bureau asks about 3.5 million addresses a year, not everyone, and uses their answers to estimate statistics for the whole population. The group a table describes is its universe by census, listed on data.census.gov and Census Reporter. For B19001 and B19013, the universe is households, so people living in group quarters, such as dorms or nursing homes, are not included.

You may be confused about how we can get the household income for the past 12 months through the 5-year estimate. The survey runs all year, every year, and each household reports its own income for the 12 months before it answered. The 2024 5-year estimate pools all responses collected from 2020 to 2024, with every income adjusted to 2024 dollars. It is one estimate from five years of responses, not an average of five 1-year estimates.

Detailed Data Table

There are more than 1000 detailed data tables. Let’s see what the meaning of this table name is: Table B19001:

There are various versions of the same table by race/ethnicity. For example, B19001A means Household Income in the Past 12 Months for households with a White alone householder. A–I are race/ethnicity versions; no letter is all households version. You can check detailed table code here from Census Reporter.

Variables in a Table

A table can contain more than one variable. For example, in Table B19001, there are 17 variables:

Therefore, another part will be used to decide variables. For example, B19001_001 will be the first variable of the number of total households, and B19001_002 will be the second variable of the number of household with annual income less than $10,000.

Oh, yes… The B19001 table just shows the number of households in each income range. How can we get the numbers we are interested in, such as the median household income for a census tract? The truth is, at least in this case, ACS provides us the direct access using Table B19013 Median Household Income in the Past 12 Months. There is only one variable in B19013, which is median household income in the past 12 months (in 2024 inflation-adjusted dollars).

Via Census API

To use tidycensus, we need to load the package and have a Census API key. A key can be obtained from https://api.census.gov/data/key_signup.html.

# remove the following '#' to install the `tidycensus` and `tidyverse` packages; 
# `tidyverse` will be most commonly used package for use in the future
# install.packages("tidycensus")
# install.packages("tidyverse")

# This loads packages into the current session; 
# however, it is best practice to load all packages at the start of an R Markdown document
library(tidycensus) 
library(tidyverse)

# For the sake of security, we shouldn't make API keys publicly available
# You only need to run the following one line of code once and the key will be saved in ~/.Renviron
# census_api_key("your key", install = TRUE)

Let’s first download the table B19013 Median Household Income.

hh_median_income_king <- get_acs(
  geography = "tract",
  state = "WA", # can also use FIPS code '53'
  county = "King", # can also use FIPS code '033'
  table =  "B19013",
  year = 2024)

## Getting data from the 2020-2024 5-year ACS

## Loading ACS5 variables for 2024 from table B19013. To cache this dataset for faster access to ACS tables in the future, run this function with `cache_table = TRUE`. You only need to do this once per ACS dataset.

We can save this hh_median_income_king tibble (just like a table) on our local machine as a .csv file (or in any other formats). .csv is one of the most common-used file types in the world of data science. But, what is a CSV file?

# The CSV file will be saved in your current working directory
# Here, we use the function `write_csv` from the `readr` package, which is in `tidyverse`
write_csv(hh_median_income_king, "hh_median_income_king.csv") 

Once you’ve set the working directory—or you’re in an RStudio project—you can refer to folders and files within the working directory using relative paths, like we’ve done in previous codes - saving the file in the current working directory. Then, we can also import this .csv again using read_csv():

another_hh_median_income_king <- read_csv("hh_median_income_king.csv")

## Rows: 495 Columns: 5
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (2): NAME, variable
## dbl (3): GEOID, estimate, moe
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Via DATA.CENSUS.GOV

DATA.CENSUS.GOV is the primary platform to access data from the U.S. Census Bureau. You can check How-to Materials for Using data.census.gov on its official website.

Let’s download the same 2024 ACS 5-year estimate for Table B19013 Median Household Income in the Past 12 Months for King County at census tract level.

1. Go to data.census.gov and search for the table code, e.g., B19013.
2. Choose the geography, the table, and the year. Geography is under Filters: Geography → Census Tract → Washington → King County → All Census Tracts within King County. The year is in the menu under the table title, e.g., “2024: ACS 5-Year Estimates Detailed Tables”.
3. Click Download and choose ZIP (flat, machine-readable .csv files). Do not choose CSV, which keeps the on-screen layout.

The ZIP file has three files:

• Table notes (...-Table-Notes.txt): notes about the table, such as its source and universe.
• Column metadata (...-Column-Metadata.csv): the dictionary, which says what each column code means.
• Data (...-Data.csv): the data itself, one row per census tract. This is the file we use.

The data file has two header rows. The first row has the column codes (GEO_ID, NAME, B19013_001E, B19013_001M). The second row describes each column (e.g., Estimate!!Median household income...). R uses only the first row as column names, so the second row is read as data, and the number columns become text. Remove that row after you read the file.

Before we talk more about the census data, we should talk about dataframe, the table we just downloaded was stored as dataframe (or a df in short).

Dataframe

Most data sets you will work with will be read into R and stored as a dataframe, just like the hh_median_income_king data. So this course will mainly focus on manipulating and visualizing these objects.

# check whether an object is a dataframe
is.data.frame(hh_median_income_king)

## [1] TRUE

You can check the dataframe by clicking the object name in the Environment Panel (right top) or using view() function.

# check the dataframe
view(hh_median_income_king)

# check the first three rows, default is five rows
head(hh_median_income_king, 3)

## # A tibble: 3 × 5
##   GEOID       NAME                                       variable estimate   moe
##   <chr>       <chr>                                      <chr>       <dbl> <dbl>
## 1 53033000101 Census Tract 1.01; King County; Washington B19013_…    69577 14341
## 2 53033000102 Census Tract 1.02; King County; Washington B19013_…    89840 23009
## 3 53033000201 Census Tract 2.01; King County; Washington B19013_…    88833 31370

# check the column names
colnames(hh_median_income_king)

## [1] "GEOID"    "NAME"     "variable" "estimate" "moe"

# number of rows and columns
dim(hh_median_income_king)

## [1] 495   5

# select GEOID column and elements with index 1 to 10
hh_median_income_king$GEOID[1:10]

##  [1] "53033000101" "53033000102" "53033000201" "53033000202" "53033000300"
##  [6] "53033000402" "53033000403" "53033000404" "53033000500" "53033000601"

# calculate the average of median estimate
# na.rm = TRUE helps to ignore missing values
mean(hh_median_income_king$estimate, na.rm = TRUE)

## [1] 135814

When you click the Knit button (top of this panel) a document (HTML or PDF) will be generated that includes both content as well as the output of any embedded R code chunks within the document.

Census Data Revisit

Wait, we just calculated the average of median estimate, but we shouldn’t do that because the average of median cannot give us the median of whole king county. What should we do? The easiest way is switching the geography from tract to county.

get_acs(
  geography = "county", # change to 'county'
  state = "WA", 
  county = "King", 
  table =  "B19013",
  year = 2024
)

## # A tibble: 1 × 5
##   GEOID NAME                    variable   estimate   moe
##   <chr> <chr>                   <chr>         <dbl> <dbl>
## 1 53033 King County, Washington B19013_001   124746   968

We find the estimate is $124,746, while our previous average is $135,814. The output also has a moe column next to estimate. The margin of error (moe) shows how uncertain an estimate is: because the ACS surveys only a sample of households, the true value is likely, with 90% confidence, to fall within the estimate ± moe. Here, King County’s median household income is likely between $123,778 and $125,714 ($124,746 ± $968).

However, what if we wish to get the median household income for UW Seattle, which I define as these 4 census tracts: 53033005303, 53033005304, 53033005305, 53033005202?

One way is to sum the number of households in each income range (from B19001) across these census tracts to obtain the aggregated income distribution. Then, we can identify the range where the cumulative number of households just exceeds the halfway point, so that the median falls within this range. At the end, perform an interpolation within this range. We will not do that here.

hh_income_king <- get_acs(
  geography = "tract",
  state = "WA",
  county = "King",
  table =  "B19001",
  year = 2024,
  output = "tidy" # try to use `wide` and see what the difference is
)
head(hh_income_king, 4)

## # A tibble: 4 × 5
##   GEOID       NAME                                       variable estimate   moe
##   <chr>       <chr>                                      <chr>       <dbl> <dbl>
## 1 53033000101 Census Tract 1.01; King County; Washington B19001_…     1746   283
## 2 53033000101 Census Tract 1.01; King County; Washington B19001_…       83    85
## 3 53033000101 Census Tract 1.01; King County; Washington B19001_…       59    52
## 4 53033000101 Census Tract 1.01; King County; Washington B19001_…       51    47

Different from normal way of organizing a table (each variable as a column), this table uses one column to indicate which variable they are.

That is because the output = "tidy" and we will get something called tidy data. Tidy is the principle that provides a standard way to organize data in a dataset, which is a preferred way to organize. And we can switch to untidy (wide) data by setting output = "wide". We will leave more discussion on tidy data in the future sessions.

Get Variables via API

We can simply get multiple variables at the same time and organize into one table. For example, B25058 is median contract rent and household income is generally associated with contract rent.

rent_income <- get_acs(
  geography = "tract",
  state = "WA",
  county = "King",
  # you can also get a single variable by using: variables =  "B19013_001"
  variables =  c(income = "B19013_001", rent = "B25058_001"),
  year = 2024,
  output = "wide"
)

Using R built-in function plot(), we can have a look at the relationship between these two variables using scatter plot.

plot(rent_income$incomeE, rent_income$rentE)

📚 TODO: Explore Census Demographic Data

Let’s do the similar step for other variables, select 1~2 interesting datasets from ACS, download them via API or via DATA.CENSUS.GOV. You may look up variables you’re interested in from this Census Reporter site or ACS Official Page.

Using a dataframe to store the data and calculate basic summary statistics such as mean, median, min/max, 25th/75th percentiles for your data. Make sure you understand the variable you choose before conducting analysis.

# TODO