Data manipulation examples

A tidy table: dat2b

dat2 consists of county income and educational attainment for both the male and female population. A codebook available here provides descriptions for the different codes. We will remove the cases (rows) from dat2 for which values are missing (i.e. cases having a NA designation) since these rows will serve no purpose (such cases may be associated with counties having no year-round residents or a resident population too small for data dissemination).

dat2 <- na.omit(dat2)

As with dat1, we will create a tidy version of dat2 for use with packages such as ggplot2.

The dat2 dataset has income data broken down by educational attainment and gender aggregated at the county level. It would therefore be convenient for plot operations if two variables, Gender and (educational) Level, were added to the long table version of dat2.

We will first generate a lookup table, Edu.Gend, of variable elements that will match each census category (e.g. B20004001, B20004002, …) to its matching pair of Level and Gender types.

We will also create a State/Region lookup table, st.reg, that will store two variables: the two letter state abbreviation variable State and its matching region variable Region. R has a built-in vector called state.region that assigns a region to each state. However, you’ll note that this vector only has region names but makes no reference to states. It’s intended to be used with another built-in data vector called state.abb or state.name. We will combine state.abb with state.region to create st.reg. We will also need to convert the uppercase state abbreviations to lower case using tolower so that they match the dat2’s lowercase state abbreviations. Note that D.C. is not included in the built-in states dataset, so we will add this record the st.reg table and assign D.C. to the South region.

Finally, the two tables, Edu.gend and st.reg, will be joined to the long version of dat2 such that each observation will be assigned a Level, Gender and Region.

library(stringr)
# Create a variable/Level/Gender join table
Edu.Gend <- data.frame( 
               variable   = paste("B200040", str_pad(1:18, width=2 , pad="0"),sep="" ),
               Level      = rep(c("All", "NoHS","HS","AD","BD","Grad"), times=3),
               Gender     = rep(c("All", "M","F"), each=6) )

# Create a region/state join table
st.reg <- data.frame(State = tolower(state.abb), Region = state.region)
st.reg <- rbind(st.reg , data.frame(State="dc", Region="South") )

# Start the piping operations
dat2b <- dat2 %>%
  gather(key = "variable", value = "value", -1:-2)  %>%
  left_join(Edu.Gend, by="variable" )              %>%
  select(State, County, Level, Gender, value)       %>%
  mutate(Level = factor(Level, 
                        levels = c("All","NoHS","HS", "AD", "BD", "Grad"))) %>%
  left_join(st.reg , by="State")
head(dat2b)

  State  County Level Gender value Region
1    al Autauga   All    All 35881  South
2    al Baldwin   All    All 31439  South
3    al Barbour   All    All 25201  South
4    al    Bibb   All    All 29016  South
5    al  Blount   All    All 32035  South
6    al Bullock   All    All 26408  South

tail(dat2b)

      State     County Level Gender value Region
55201    wy   Sublette  Grad      F 59683   West
55202    wy Sweetwater  Grad      F 63681   West
55203    wy      Teton  Grad      F 48357   West
55204    wy      Uinta  Grad      F 52321   West
55205    wy   Washakie  Grad      F 50341   West
55206    wy     Weston  Grad      F 56765   West

Note that we have eliminated references to variable names such as “B20004001” from dat2b making it easier to interpret the variable names/values. Also note that we have re-leveled the educational attainment factor Level to reflect the implied order in educational attainment levels.

Spreading gender across columns: dat2c

In dat2b the gender values are lumped under the variable called Gender. In some upcoming lectures, we will want to compare male and female incomes requiring that they be assigned their own columns. We will therefore widen dat2b. We will use the spread function from the tidyr package to create a new data frame called dat2c.

dat2c <- spread(dat2b, key = Gender, value = value )

head(dat2c)

  State                 County Level Region   All     F     M
1    ak Aleutians East Borough   All   West 21953 20164 22940
2    ak Aleutians East Borough  NoHS   West 21953 19250 22885
3    ak Aleutians East Borough    HS   West 20770 19671 21192
4    ak Aleutians East Borough    AD   West 26383 26750 26352
5    ak Aleutians East Borough    BD   West 22431 19592 27875
6    ak Aleutians East Borough  Grad   West 74000 74000 71250