Single sheet cleaning

census_sheet1 <-
  read_excel(path, sheet = 1)

View(census_sheet1)

Immediately, we see that the top lines are superfluous rows (the headers from the original dataset). We can use skip in read_excel() to not have them read in.

census_sheet1 <-
  read_excel(path, sheet = 1, skip = 3)

# View(census_sheet1)

Now that the unnecessary rows are gone, we see that the column names aren’t reading in super well because of the merged cells in the original sheet. In this case, we manually create a vector of the column names and replace the old ones with set_names().

new_names <-
  c("select_characteristics", "total", "using_online_resources", "using_paper_materials_sent_home", "where_classes_were_cancelled", "where_classes_changed_in_another_way", "where_no_change_to_classes", "did_not_respond")

census_example <-
  census_sheet1 %>% 
  set_names(new_names)

We still have some empty rows (and also a row at the very bottom which is a note in the original dataset). We can eliminate these rows using slice(). Here, we’re saying to ‘slice’ rows 1 through 3 and 60.

census_example <-
  census_example %>% 
  slice(-1:-3, -60:-61)

Now to deal with the fact that the category names are embedded within the first column select_characteristics. There may be other ways to do this, but again I manually create a vector with all the values that I want to get rid of and use filter() to keep only the rows that do not contain the items in the vector.

filter_var <- 
  c("Age", "Sex", "Hispanic origin and Race", "Education", "Marital status", "Presence of children under 18 years old", "Respondent or household member experienced loss of employment income", "Mean weekly hours spent on…", "Respondent currently employed", "Food sufficiency for households prior to March 13, 2020", "Household income")

census_example <-
  census_example %>% 
  filter(!select_characteristics %in% filter_var) 

Even though we removed the characteristic names from the rows, they contain very useful information. Also, we run into an issue in which two of the characteristic categories had the same options (“yes” and “no”). If we don’t address this, we’ll forget which rows are for which characteristic. To fix this, we manually create a column with the characteristics for each of the response options and append it to the data.

category_column <-
  c("age", "age", "age", "age", "age", "sex", "sex", "race", "race", "race", "race", "race", "education", "education", "education", "education", "marital_status", "marital_status", "marital_status", "marital_status", "marital_status", "children", "children", "loss_employment", "loss_employment", "loss_employment", "hours_spent", "hours_spent", "employed", "employed", "employed", "food_sufficiency", "food_sufficiency", "food_sufficiency", "food_sufficiency", "food_sufficiency", "income", "income", "income", "income", "income", "income", "income", "income", "income")

census_example <-
  census_example %>% 
  add_column(category_column)

Finally, you may have noticed that some of the rows did not read in as numbers but as characters.

We can use mutate_at() and specify which variables we want to be numeric.

census_example <-
  census_example %>% 
  mutate_at(vars(total, using_online_resources:did_not_respond), list(~ as.numeric(.)))

Hooray - now we have a tidy dataset we could use for analysis! Which is great, but it’s only one sheet. How do we do this for the additional 66?

Multi-sheet cleaning

We will now download the data and store it in a list, where each sheet (which represents a state) is saved as a tibble within the list. To work across all the lists, we use the tidyverse package {purrr} and its handy functions.

You may notice that the multi-sheet cleaning looks a lot like the single sheet cleaning but everything is wrapped in the function map(). That’s true! The wonderful thing about {purrr} being in the tidyverse is that it’s really easy to integrate with all the tidyverse functions.

Reading the data into one list is slightly more complicated than reading in a single sheet. We begin with the file path from before and then use excel_sheets() to create a vector of the sheet names. set_names() ensures that we have a named list that contains the state names, which will be important later. If we don’t use set_names(), then the tibbles have generic names instead of ‘US’, ‘AL’, etc. Then using purrr::map(), we ask R to download each of the sheets of the dataset and store it together in a list (map() always returns a list).

census_list <-
  path %>% 
  excel_sheets() %>% 
  set_names() %>% 
  map(~ read_excel(path = path, sheet = .x, skip = 3), .id = "Sheet")

If you take a look at the list using View(census_list), you can see the data is stored as tibbles within the list. If you expand US, you’ll see the same data as when we did the single sheet example. You can also see the same data if you run census_list[["US"]].

Using the same thinking as we did with the single sheet example, let’s go through and clean up this list - without having to go into each tibble!

First, we set the names within each list using set_names(). We tell map() the names of the columns by defining nm.

census_list <- 
  census_list %>% 
  map(., set_names, nm = new_names)

For each tibble in the list (.x), remove the rows 1 through 3 and 60.

census_list <- 
  census_list %>% 
  map(~ slice(.x, -1:-3, -60:-61))

Now for each tibble, filter out the rows in select_characteristics that contain the items in filter_var.

census_list <- 
  census_list %>% 
  map(~ filter(.x, !select_characteristics %in% filter_var))

Like before, we want a new column that lets us know the category for each of the characteristic options.

census_list <- 
  census_list %>% 
  map(~ add_column(.x, category_column))

And like before, we want to make sure our numeric columns are actually numeric.

census_list <- 
  census_list %>% 
  map(~ mutate_at(.x, vars(total, using_online_resources:did_not_respond), list(~ as.numeric(.))))

Now that our tibbles are all clean and uniform, let’s make this a single, 2D data frame! As I mentioned before, our list should contain the state abbreviations. We can use map_df() to create a data frame with an ID column called state that stores each of the sheet names. With this column, we’ll easily know which column is for which state/geography.

census_df <- 
  census_list %>% 
  map_df(~ as.data.frame(.x), .id = "state")

Congrats! We have successfully tidied a Census dataset!