Tidy Data and Pivoting

The Basics: Column Names Contain Variable Values

Here is a snippet of WHO data on the number of tuberculosis cases in different years in different countries.

table4a

## # A tibble: 3 × 3
##   country     `1999` `2000`
##   <chr>        <dbl>  <dbl>
## 1 Afghanistan    745   2666
## 2 Brazil       37737  80488
## 3 China       212258 213766

If we wanted to compare tuberculosis cases over time by country (e.g. by plotting the year on the x-axis and case count on the y-axis with a line for each country), then this format is not tidy. We want to (graphically) compare years within countries, so there should be one observation per unit within each population (country-years). In this case, we do not observe units within each country-year, so each observation is a country-year. The variables then fall into place: the country and year labels, and the case counts.

(Aside: if we had measured more data, then perhaps there would be more units within each population! Imagine if we had case-level information, like severity. Then we could view cases as observations within the country-year populations, and we would have variables like country, year, case ID, and severity.)

So the tidy format here puts the variables (the year, the country, and the case counts) on the columns. There are 6 rows, one for each unique country-year combination.

## # A tibble: 6 × 3
##   country     year   cases
##   <chr>       <chr>  <dbl>
## 1 Afghanistan 1999     745
## 2 Afghanistan 2000    2666
## 3 Brazil      1999   37737
## 4 Brazil      2000   80488
## 5 China       1999  212258
## 6 China       2000  213766

Notice that compared to table4a, the tidy format is longer. That means to produce it using table4a, we need to lengthen it by moving some information in the column names (the info about the measurement year) into new rows.

We can solve this problem using pivot_longer, which needs three pieces of information.

• Which are the columns that we want to expand into more rows? Put their names in the cols argument.

• We want to save the information in the names of those columns as values in new column(s) of our dataset. What should we name these new column(s)? This is the names_to argument.

• We also want to preserve the information in the values of those columns - so we should save them as values in a new column of our dataset. What should we name it? This is the values_to argument.

table4a %>% pivot_longer(cols = c(`1999`, `2000`), 
                         names_to = "year", 
                         values_to = "cases")

## # A tibble: 6 × 3
##   country     year   cases
##   <chr>       <chr>  <dbl>
## 1 Afghanistan 1999     745
## 2 Afghanistan 2000    2666
## 3 Brazil      1999   37737
## 4 Brazil      2000   80488
## 5 China       1999  212258
## 6 China       2000  213766

Challenge 1: Column Names Contain Multiple Variable Values

Here’s a more realistic (but still simplified!!) look at the WHO Tuberculosis data.

who_demo <- who2 %>% 
  select(country, year, starts_with("sp")) %>%
  rename_with(function(x) 
    str_remove(x, pattern="sp_"), 
    starts_with("sp")) %>% 
  filter(year %in% c(1999, 2000)) %>% 
  filter(country %in% c("Afghanistan", "Brazil", "China"))

head(who_demo)

## # A tibble: 6 × 16
##   country      year m_014 m_1524 m_2534 m_3544 m_4554 m_5564  m_65 f_014 f_1524
##   <chr>       <dbl> <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl> <dbl> <dbl>  <dbl>
## 1 Afghanistan  1999     8     55     55     47     34     21     8    25    139
## 2 Afghanistan  2000    52    228    183    149    129     94    80    93    414
## 3 Brazil       1999   301   3662   5401   5827   4630   2634  2121   372   2909
## 4 Brazil       2000  1894   7268  11568  11906   8623   5085  4494  1859   6719
## 5 China        1999  1247  18961  29328  25095  24239  21564 21367  1431  15178
## 6 China        2000  1131  19111  29399  25206  25593  21429 21771  1420  14536
## # ℹ 5 more variables: f_2534 <dbl>, f_3544 <dbl>, f_4554 <dbl>, f_5564 <dbl>,
## #   f_65 <dbl>

This time, cases are broken down by gender (f/m) and by age range (014\1524\2534\3544\4554\5564\65).

Suppose now that we are interested in comparing tuberculosis rates over time across (potentially) gender, age, and country. Then the most granular population we are trying to describe is a country, gender, age, and year combination, and like in the last example, we have no measured sub-units within that population, so an observation is a unique combination of country, gender, age, and year. (What a mouthful!)

Once we’ve sorted that out, the variables fall into place: country, year, gender, age range, and case count. Values for gender and age range are currently located in the column names of who_demo, and values for case count are currently spread across multiple columns. So to tidy who_demo up, we need to use pivot_longer() to move the info in the columns into new rows.

Conceptually, this is pretty similar to the last example: we want to use the information in m_014, m_1524, etc. to create new rows. So we should put those column names into the cols argument. But now, we want the information in their column names - the gender and age - to go into two new columns: gender and age. We can do this by specifying two column names in the names_to argument: gender and age.

But how is pivot_longer() to know which part of the column name m_014 corresponds to the gender, and which part corresponds to the age? You need to tell it that the pieces of information are separated by the “_” character using the names_sep argument.

Finally, we can specify the name of the new column we want the values in the m_014, m_1524, etc. columns to go into with the values_to argument.

who_demo %>% pivot_longer(cols = !(country:year),
                          names_to = c("gender", "age"),
                          names_sep = "_",
                          values_to = "cases")

## # A tibble: 84 × 5
##    country      year gender age   cases
##    <chr>       <dbl> <chr>  <chr> <dbl>
##  1 Afghanistan  1999 m      014       8
##  2 Afghanistan  1999 m      1524     55
##  3 Afghanistan  1999 m      2534     55
##  4 Afghanistan  1999 m      3544     47
##  5 Afghanistan  1999 m      4554     34
##  6 Afghanistan  1999 m      5564     21
##  7 Afghanistan  1999 m      65        8
##  8 Afghanistan  1999 f      014      25
##  9 Afghanistan  1999 f      1524    139
## 10 Afghanistan  1999 f      2534    160
## # ℹ 74 more rows

Challenge 2: Column Names Contain Variable Names And Values

So far we have seen examples where the column names contain variable values. But what if they contain names AND values?

Let’s have a look at the household dataset (loaded with the tidyr package), which has the date of birth and names of two children in families. Let’s say that we wanted to investigate how children names relate to their date of birth.

head(household)

## # A tibble: 5 × 5
##   family dob_child1 dob_child2 name_child1 name_child2
##    <int> <date>     <date>     <chr>       <chr>      
## 1      1 1998-11-26 2000-01-29 Susan       Jose       
## 2      2 1996-06-22 NA         Mark        <NA>       
## 3      3 2002-07-11 2004-04-05 Sam         Seth       
## 4      4 2004-10-10 2009-08-27 Craig       Khai       
## 5      5 2000-12-05 2005-02-28 Parker      Gracie

We are trying to learn about the population from which these children belong; it is hard to say precisely what that is without having more information about how this data was collected, but it is likely something like “all children living in a particular place in a particular year”. The units in this population are children. So to tidy this data, we’d want “date of birth” and “name” to be two variables/columns associated with an observation/row (a child). We know we want to use pivot_longer(), because we want to make household longer by creating new variables. But wait! The names of the “date of birth”/“name” variables AND the values of the “child” variable are BOTH in the column names of household!

Inspecting the documentation for pivot_longer() very carefully reveals that you can use a special specification of the names_to argument to resolve this problem.

household %>% pivot_longer(cols = -family, 
                           names_to = c(".value", "child"), 
                           names_sep = "_")

## # A tibble: 10 × 4
##    family child  dob        name  
##     <int> <chr>  <date>     <chr> 
##  1      1 child1 1998-11-26 Susan 
##  2      1 child2 2000-01-29 Jose  
##  3      2 child1 1996-06-22 Mark  
##  4      2 child2 NA         <NA>  
##  5      3 child1 2002-07-11 Sam   
##  6      3 child2 2004-04-05 Seth  
##  7      4 child1 2004-10-10 Craig 
##  8      4 child2 2009-08-27 Khai  
##  9      5 child1 2000-12-05 Parker
## 10      5 child2 2005-02-28 Gracie

The special ".value" specification says that we want to use the first component of the pivoted column name as a variable name, and make a new column with values coming from the second component of the pivoted column name. The second thing we pass into names_to names that new column.

This process is best described by Figure 6.7 from R4DS.

But wait! Row 4 is a bunch of NAs! Does that mean this data isn’t tidy??

The fact that there is an NA does not necessarily mean that this data is untidy. To be clear: for the purpose of the tidy data definition, an indicator for a missing value is a value.

Whether this data is untidy depends on the data context. Essentially, the question we should ask is: “Is row 4 an observation that we are missing information about? Or is it simply an artifact of our pivoting procedure?”

Suppose this study was designed to only sample families with two children. Then, row 4 could be a real observation that we are missing information about: family 2 should have only been included if they had two children. Perhaps this reflects family 2 filling out a survey that asks them the number of children (in which they listed 2), but then getting distracted and forgetting to fill out the information for their second child. In this case, our data is tidy, and the tidy data format is a real advantage: it reveals missing information in our data set that was not obvious from the original untidy format.

Now suppose this study just samples families at large. We know from experience about the world that some families have one children, some families have two, and some families have more. Then, it seems possible that row 4 is not a real observation: family 2 might just have a single child. In this case, we have a row for something that is not an observation, so we would like to tidy up by dropping it. We could actually have done this by altering our original pivot_wider() call as follows:

household %>% pivot_longer(cols = -family, 
                           names_to = c(".value", "child"), 
                           names_sep = "_", 
                           values_drop_na = TRUE)

## # A tibble: 9 × 4
##   family child  dob        name  
##    <int> <chr>  <date>     <chr> 
## 1      1 child1 1998-11-26 Susan 
## 2      1 child2 2000-01-29 Jose  
## 3      2 child1 1996-06-22 Mark  
## 4      3 child1 2002-07-11 Sam   
## 5      3 child2 2004-04-05 Seth  
## 6      4 child1 2004-10-10 Craig 
## 7      4 child2 2009-08-27 Khai  
## 8      5 child1 2000-12-05 Parker
## 9      5 child2 2005-02-28 Gracie

This discussion highlights the importance of knowing the context in which your data is collected for tidying (and for your analysis at large). Here and elsewhere, it really pays to be in close contact with the people who generated your data.