Class Meeting 9 Tidy Data and Pivoting

library(tidyverse)

9.1 Orientation (5 min)

9.1.1 Worksheet

You can find a worksheet template for today here.

9.1.2 Announcements

Sorry that we’re late posting the “assignment box” on canvas for Assignment 3. It’s up now.

9.1.3 Today

Today’s concept is tidy data and the tidyr package.

In fact tidyr Version 1.0.0 just came out 19 days ago with some great new additions that we’ll be looking at. We’ll focus on:

• Reshaping data by pivoting with tidyr::pivot_longer() and tidyr::pivot_wider().
• Making tibbles using tibble::tibble() and tidyr::expand_grid().

9.1.4 Resources

For concepts of tidy data:

• Jenny’s intro to tidy data is short and sweet.
  • the repo this links to has some useful exercises too, but uses the older spread() and gather() functions.
• tidyr vignette on tidy data.
• Hadley’s paper on tidy data provides a thorough investigation.

For pivoting with tidyr, check out the pivot vignette.

I also recommend reading the new additions that come with the new tidyr Version 1.0.0 in this tidyverse article. We won’t be covering all of it in STAT 545A, but things like nesting and rectangling are covered in STAT 547M.

9.2 Tidy Data (10 min)

A data set is tidy if:

• Each row is an observation;
• Each column is a variable;
• Each cell is a value.

This means that each value belongs to exactly one variable and one observation.

Why bother? Because doing computations with untidy data can be a nightmare. Computations become simple with tidy data.

This also means that tidy data is relative, as it depends on how you define your observational unit and variables.

As an example, consider this example derived from the datasets::HairEyeColor dataset, containing the number of people having a certain hair and eye colour.

If one observation is identified by a hair-eye colour combination, then the tidy dataset is:

haireye %>% 
  DT::datatable(rownames = FALSE)

If one observation is identified by a single person, then the tidy dataset has one pair of values per person, and one row for each person. We can use the handy tidyr::uncount() function, the opposite of dplyr::count():

haireye %>% 
  tidyr::uncount(n) %>% 
  DT::datatable(rownames = FALSE)

9.2.1 Untidy Examples

The following are examples of untidy data. They’re untidy for either of the cases considered above, but for discussion, let’s take a hair-eye colour combination to be one observational unit.

Note that untidy does not always mean “bad”, especially when the data set is too wide.

Untidy Example 1: The following table is untidy because there are multiple observations per row. It’s too wide.

Imagine calculating the total number of people with each hair colour. You can’t just group_by() and summarize(), here!

hair	Blue_eyed	Brown_eyed	Green_eyed	Hazel_eyed
Black	20	68	5	15
Blond	94	7	16	10
Brown	84	119	29	54
Red	17	26	14	14

Untidy Example 2: The following table is untidy for the same reason as Example 1 – multiple observations are contained per row. It’s too wide.

eye	Black_haired	Blond_haired	Brown_haired	Red_haired
Blue	20	94	84	17
Brown	68	7	119	26
Green	5	16	29	14
Hazel	15	10	54	14

Untidy Example 3: This is untidy because each observational unit is spread across multiple columns. It’s too long. In fact, we needed to add an identifier for each observation, otherwise we would have lost which row belongs to which observation!

Does red hair ever occur with blue eyes? Can’t just filter(hair == "red", eye == "blue")!

Untidy Example 4: Just when you thought a data set couldn’t get any longer! Now, each variable has its own row: hair colour, eye colour, and n. This demonstrates that there’s no such thing as “long” and “wide” format, since these terms are relative.

9.2.2 Pivoting tools

The task of making tidy data is about making data either longer, by stacking two or more rows, or wider, by putting one or more columns alongside each other based on groups. This is called pivoting (or, reshaping).

Sometimes, tidy data is incorrectly referred to as data in long format as opposed to wide format, where “length” refers to the number of rows, and “width” the number of columns. But Example 3 of untidy data (above) is in fact too long and needs to be made wider! However, usually the task of tidying data involves lengthening, and usually the task of widening is useful for turning data into something more friendly for human eyes.

The (new!) easiest and most powerful way to widen or lengthen data are with the functions tidyr::pivot_wider() and tidyr::pivot_longer().

History: R has seen many attempts at reshaping, all that’s progressively gotten better. First came the reshape package. Then the reshape2 package. Both were finicky. Then, the tidyr::spread() and tidyr::gather() functions provided a simple interface (and are still part of the tidyr package!), but used awkward terminology and weren’t as flexible as they ought to be.

9.3 Univariate Pivoting (20 min)

Let’s start with pivoting in the simplest case where only one variable is “out of place”. We’ll use the hair and eye colour example from before, using the untidy data version from Example 1:

haireye_untidy

## # A tibble: 4 x 5
##   hair  Blue_eyed Brown_eyed Green_eyed Hazel_eyed
##   <chr>     <dbl>      <dbl>      <dbl>      <dbl>
## 1 Black        20         68          5         15
## 2 Blond        94          7         16         10
## 3 Brown        84        119         29         54
## 4 Red          17         26         14         14

The eye colour variable is spread out across columns. To fix this, we need to convert the eye colour columns to two columns:

• one column to hold the eye colour (column names),
• one column to hold the values.

Doing this, we obtain:

## # A tibble: 16 x 3
##    hair  eye            n
##    <chr> <chr>      <dbl>
##  1 Black Blue_eyed     20
##  2 Black Brown_eyed    68
##  3 Black Green_eyed     5
##  4 Black Hazel_eyed    15
##  5 Blond Blue_eyed     94
##  6 Blond Brown_eyed     7
##  7 Blond Green_eyed    16
##  8 Blond Hazel_eyed    10
##  9 Brown Blue_eyed     84
## 10 Brown Brown_eyed   119
## 11 Brown Green_eyed    29
## 12 Brown Hazel_eyed    54
## 13 Red   Blue_eyed     17
## 14 Red   Brown_eyed    26
## 15 Red   Green_eyed    14
## 16 Red   Hazel_eyed    14

For the reverse operation, we take the column eye and make each unique entry a new column, and the values of those columns take on n.

9.3.1 pivot_longer()

pivot_longer() takes a data frame, and returns a data frame. The arguments after the data argument that we’ll need are:

• cols for the column names that we want to turn into a single column.
• names_to: the old column names are going to a new column. What should this new column be named? (optional, but highly recommended)
• values_to: the values underneath the old columns are going to a new column. What should this new column be named? (optional, but highly recommended)

Possibly the trickiest bit is in identifying the column names. We could list all of them, but it’s not robust to changes:

haireye_untidy %>% 
  pivot_longer(cols      = c(Blue_eyed, Brown_eyed, Green_eyed, Hazel_eyed),
               names_to  = "eye",
               values_to = "n")

## # A tibble: 16 x 3
##    hair  eye            n
##    <chr> <chr>      <dbl>
##  1 Black Blue_eyed     20
##  2 Black Brown_eyed    68
##  3 Black Green_eyed     5
##  4 Black Hazel_eyed    15
##  5 Blond Blue_eyed     94
##  6 Blond Brown_eyed     7
##  7 Blond Green_eyed    16
##  8 Blond Hazel_eyed    10
##  9 Brown Blue_eyed     84
## 10 Brown Brown_eyed   119
## 11 Brown Green_eyed    29
## 12 Brown Hazel_eyed    54
## 13 Red   Blue_eyed     17
## 14 Red   Brown_eyed    26
## 15 Red   Green_eyed    14
## 16 Red   Hazel_eyed    14

We could identify a range. This is more robust, but still not very robust.

haireye_untidy %>% 
  pivot_longer(cols      = Blue_eyed:Hazel_eyed,
               names_to  = "eye",
               values_to = "n")

## # A tibble: 16 x 3
##    hair  eye            n
##    <chr> <chr>      <dbl>
##  1 Black Blue_eyed     20
##  2 Black Brown_eyed    68
##  3 Black Green_eyed     5
##  4 Black Hazel_eyed    15
##  5 Blond Blue_eyed     94
##  6 Blond Brown_eyed     7
##  7 Blond Green_eyed    16
##  8 Blond Hazel_eyed    10
##  9 Brown Blue_eyed     84
## 10 Brown Brown_eyed   119
## 11 Brown Green_eyed    29
## 12 Brown Hazel_eyed    54
## 13 Red   Blue_eyed     17
## 14 Red   Brown_eyed    26
## 15 Red   Green_eyed    14
## 16 Red   Hazel_eyed    14

Better is to use helper functions from the tidyselect package. In this case, we know the columns contain the text “eyed”, so let’s use tidyselect::contains():

haireye_untidy %>% 
  pivot_longer(cols      = contains("eyed"),
               names_to  = "eye",
               values_to = "n")

## # A tibble: 16 x 3
##    hair  eye            n
##    <chr> <chr>      <dbl>
##  1 Black Blue_eyed     20
##  2 Black Brown_eyed    68
##  3 Black Green_eyed     5
##  4 Black Hazel_eyed    15
##  5 Blond Blue_eyed     94
##  6 Blond Brown_eyed     7
##  7 Blond Green_eyed    16
##  8 Blond Hazel_eyed    10
##  9 Brown Blue_eyed     84
## 10 Brown Brown_eyed   119
## 11 Brown Green_eyed    29
## 12 Brown Hazel_eyed    54
## 13 Red   Blue_eyed     17
## 14 Red   Brown_eyed    26
## 15 Red   Green_eyed    14
## 16 Red   Hazel_eyed    14

Yet another way is to indicate everything except the hair column:

haireye_untidy %>% 
  pivot_longer(cols      = -hair,
               names_to  = "eye",
               values_to = "n")

## # A tibble: 16 x 3
##    hair  eye            n
##    <chr> <chr>      <dbl>
##  1 Black Blue_eyed     20
##  2 Black Brown_eyed    68
##  3 Black Green_eyed     5
##  4 Black Hazel_eyed    15
##  5 Blond Blue_eyed     94
##  6 Blond Brown_eyed     7
##  7 Blond Green_eyed    16
##  8 Blond Hazel_eyed    10
##  9 Brown Blue_eyed     84
## 10 Brown Brown_eyed   119
## 11 Brown Green_eyed    29
## 12 Brown Hazel_eyed    54
## 13 Red   Blue_eyed     17
## 14 Red   Brown_eyed    26
## 15 Red   Green_eyed    14
## 16 Red   Hazel_eyed    14

9.3.2 pivot_wider()

Like pivot_longer(), pivot_wider() takes a data frame and returns a data frame. The arguments after the data argument that we’ll need are:

• id_cols: The columns you would like to keep. If widening to make data tidy, then this is an identifier for an observation.
• names_from: the new column names are coming from an old column. Which column is this?
• values_from: the values under the new columns are coming from an old column. Which column is this?

haireye %>% 
  pivot_wider(id_cols     = hair, 
              names_from  = eye, 
              values_from = n)

## # A tibble: 4 x 5
##   hair   Blue Brown Green Hazel
##   <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Black    20    68     5    15
## 2 Blond    94     7    16    10
## 3 Brown    84   119    29    54
## 4 Red      17    26    14    14

9.3.3 Activity

Fill out Exercise 1: Univariate Pivoting in the worksheet.

9.4 Multivariate Pivoting (20 min)

Now let’s consider the case when more than one variable are “out of place” – perhaps there are multiple variables per row, and/or multiple observations per row.

For example, consider the (lightly modified) iris data set that we’ll call iris2:

Although we probably wouldn’t, we could view this as having two variables bundled into the column names:

• “Plant part”, either sepal or petal.
• “Dimension”, either length or width.

The resulting tidy data frame would then be:

iris2_longest

## # A tibble: 600 x 5
##       id species part  dimension measurement
##    <int> <fct>   <chr> <chr>           <dbl>
##  1     1 setosa  sepal length            5.1
##  2     1 setosa  sepal width             3.5
##  3     1 setosa  petal length            1.4
##  4     1 setosa  petal width             0.2
##  5     2 setosa  sepal length            4.9
##  6     2 setosa  sepal width             3  
##  7     2 setosa  petal length            1.4
##  8     2 setosa  petal width             0.2
##  9     3 setosa  sepal length            4.7
## 10     3 setosa  sepal width             3.2
## # … with 590 more rows

More realistic is the situation where there are multiple observations per row:

• An observation of (length, width) of the sepal.
• An observation of (length, width) of the petal.

The resulting tidy data frame has a length that’s in between the above two:

iris2_longer

## # A tibble: 300 x 5
##       id species part  length width
##    <int> <fct>   <chr>  <dbl> <dbl>
##  1     1 setosa  sepal    5.1   3.5
##  2     1 setosa  petal    1.4   0.2
##  3     2 setosa  sepal    4.9   3  
##  4     2 setosa  petal    1.4   0.2
##  5     3 setosa  sepal    4.7   3.2
##  6     3 setosa  petal    1.3   0.2
##  7     4 setosa  sepal    4.6   3.1
##  8     4 setosa  petal    1.5   0.2
##  9     5 setosa  sepal    5     3.6
## 10     5 setosa  petal    1.4   0.2
## # … with 290 more rows

9.4.1 pivot_longer()

To obtain the case where two (or more) variables are contained in column names, here’s how we specify the arguments of pivot_longer():

• cols: As usual.
• names_sep: What is separating the variables in the column names?
• names_to: The old columns are going to be put into new columns, after being separated. What should those columns be named?
• values_to: As usual.

Here is the code:

iris2 %>% 
  pivot_longer(cols      = c(-species, -id), 
               names_to  = c("part", "dimension"),
               names_sep = "_",
               values_to = "measurement")

## # A tibble: 600 x 5
##       id species part  dimension measurement
##    <int> <fct>   <chr> <chr>           <dbl>
##  1     1 setosa  sepal length            5.1
##  2     1 setosa  sepal width             3.5
##  3     1 setosa  petal length            1.4
##  4     1 setosa  petal width             0.2
##  5     2 setosa  sepal length            4.9
##  6     2 setosa  sepal width             3  
##  7     2 setosa  petal length            1.4
##  8     2 setosa  petal width             0.2
##  9     3 setosa  sepal length            4.7
## 10     3 setosa  sepal width             3.2
## # … with 590 more rows

To obtain the case where multiple observations are contained in one row, here’s how to specify the arguments of pivot_longer():

• cols: As usual.
• names_sep: As above.
• names_to: As above, except this time, one part of the old column names are going to stay as columns (in this case, “length” and “width”). Indicate ".value" instead of a new column name.
  
• values_to: Not needed! You’ve already indicated that using the ".value" placeholder.

iris2 %>% 
  pivot_longer(cols      = c(-id, -species), 
               names_to  = c("part", ".value"), 
               names_sep = "_")

## # A tibble: 300 x 5
##       id species part  length width
##    <int> <fct>   <chr>  <dbl> <dbl>
##  1     1 setosa  sepal    5.1   3.5
##  2     1 setosa  petal    1.4   0.2
##  3     2 setosa  sepal    4.9   3  
##  4     2 setosa  petal    1.4   0.2
##  5     3 setosa  sepal    4.7   3.2
##  6     3 setosa  petal    1.3   0.2
##  7     4 setosa  sepal    4.6   3.1
##  8     4 setosa  petal    1.5   0.2
##  9     5 setosa  sepal    5     3.6
## 10     5 setosa  petal    1.4   0.2
## # … with 290 more rows

9.4.2 pivot_wider()

If two or more columns contain parts of a variable name (i.e., each unique combination of these columns gives rise to a new variable), here’s how we can use pivot_wider():

• id_cols: as usual.
• names_from: the new variable names are coming from old columns. Which old columns?
• names_sep: What character should you separate the entries of the old columns by?
• values_from: as usual.

Here is the code to go from the longest form to the original:

iris2_longest %>% 
  pivot_wider(id_cols     = c(id, species),
              names_from  = c(part, dimension), 
              names_sep   = "_", 
              values_from = measurement)

## # A tibble: 150 x 6
##       id species sepal_length sepal_width petal_length petal_width
##    <int> <fct>          <dbl>       <dbl>        <dbl>       <dbl>
##  1     1 setosa           5.1         3.5          1.4         0.2
##  2     2 setosa           4.9         3            1.4         0.2
##  3     3 setosa           4.7         3.2          1.3         0.2
##  4     4 setosa           4.6         3.1          1.5         0.2
##  5     5 setosa           5           3.6          1.4         0.2
##  6     6 setosa           5.4         3.9          1.7         0.4
##  7     7 setosa           4.6         3.4          1.4         0.3
##  8     8 setosa           5           3.4          1.5         0.2
##  9     9 setosa           4.4         2.9          1.4         0.2
## 10    10 setosa           4.9         3.1          1.5         0.1
## # … with 140 more rows

If variables are spread out amongst rows and columns (for example, “sepal width” has “sepal” in a column, and “width” as a column name), here’s how we can use pivot_wider():

• id_cols: as usual
• names_from: Which column contains the part of the variable?
• names_sep: As before, what character should you separate the entries of the old columns by?
• values_from: Which column names contain the other part of the variable?

Here is the code to go from the “semi-long” form to the original:

iris2_longer %>% 
  pivot_wider(id_cols     = c(id, species), 
              names_from  = part, 
              names_sep   = "_",
              values_from = c(length, width))

## # A tibble: 150 x 6
##       id species length_sepal length_petal width_sepal width_petal
##    <int> <fct>          <dbl>        <dbl>       <dbl>       <dbl>
##  1     1 setosa           5.1          1.4         3.5         0.2
##  2     2 setosa           4.9          1.4         3           0.2
##  3     3 setosa           4.7          1.3         3.2         0.2
##  4     4 setosa           4.6          1.5         3.1         0.2
##  5     5 setosa           5            1.4         3.6         0.2
##  6     6 setosa           5.4          1.7         3.9         0.4
##  7     7 setosa           4.6          1.4         3.4         0.3
##  8     8 setosa           5            1.5         3.4         0.2
##  9     9 setosa           4.4          1.4         2.9         0.2
## 10    10 setosa           4.9          1.5         3.1         0.1
## # … with 140 more rows

9.4.3 Activity

Fill out Exercise 2: Multivariate Pivoting in the worksheet.

9.5 Making tibbles (5 min)

In base R, we can make data frames using the data.frame() function. The tidyverse version is tibble::tibble(), which also has backwards referencing to variables you make on the fly. It’s also stricter by not allowing recycling unless the vector is of length 1:

Good:

tibble(x = 1:6,
       y = min(x))

## # A tibble: 6 x 2
##       x     y
##   <int> <int>
## 1     1     1
## 2     2     1
## 3     3     1
## 4     4     1
## 5     5     1
## 6     6     1

Bad:

tibble(x = 1:6,
       y = 1:2)

## Error: Tibble columns must have compatible sizes.
## * Size 6: Existing data.
## * Size 2: Column `y`.
## ℹ Only values of size one are recycled.

Truly manual construction of tibbles is easy with tibble::tribble():

tribble(
  ~Day, ~Breakfast,
  1, "Apple",
  2, "Yogurt",
  3, "Yogurt"
)

## # A tibble: 3 x 2
##     Day Breakfast
##   <dbl> <chr>    
## 1     1 Apple    
## 2     2 Yogurt   
## 3     3 Yogurt

List columns are easy with tibbles!

(list_col <- tibble(n = 1:2,
                    y = list(iris, mtcars)))

## # A tibble: 2 x 2
##       n y                  
##   <int> <list>             
## 1     1 <df[,5] [150 × 5]> 
## 2     2 <df[,11] [32 × 11]>

Often obtained with nest() and unnest():

(iris_nest <- iris %>% 
  group_by(Species) %>% 
  nest())

## # A tibble: 3 x 2
## # Groups:   Species [3]
##   Species    data             
##   <fct>      <list>           
## 1 setosa     <tibble [50 × 4]>
## 2 versicolor <tibble [50 × 4]>
## 3 virginica  <tibble [50 × 4]>

iris_nest %>% 
  unnest(data)

## # A tibble: 150 x 5
## # Groups:   Species [3]
##    Species Sepal.Length Sepal.Width Petal.Length Petal.Width
##    <fct>          <dbl>       <dbl>        <dbl>       <dbl>
##  1 setosa           5.1         3.5          1.4         0.2
##  2 setosa           4.9         3            1.4         0.2
##  3 setosa           4.7         3.2          1.3         0.2
##  4 setosa           4.6         3.1          1.5         0.2
##  5 setosa           5           3.6          1.4         0.2
##  6 setosa           5.4         3.9          1.7         0.4
##  7 setosa           4.6         3.4          1.4         0.3
##  8 setosa           5           3.4          1.5         0.2
##  9 setosa           4.4         2.9          1.4         0.2
## 10 setosa           4.9         3.1          1.5         0.1
## # … with 140 more rows

expand_grid() to obtain all combinations:

expand_grid(x = 1:2, y = 1:2, z = 1:2)

## # A tibble: 8 x 3
##       x     y     z
##   <int> <int> <int>
## 1     1     1     1
## 2     1     1     2
## 3     1     2     1
## 4     1     2     2
## 5     2     1     1
## 6     2     1     2
## 7     2     2     1
## 8     2     2     2

In conjunction with nesting():

expand_grid(nesting(x = 1:2, y = 1:2), z = 1:2)

## # A tibble: 4 x 3
##       x     y     z
##   <int> <int> <int>
## 1     1     1     1
## 2     1     1     2
## 3     2     2     1
## 4     2     2     2

9.6 Implicit NA’s (5 min)

Sometimes there’s “hidden” missing data in a tibble. Here’s an example straight from the documentation of tidyr::expand():

(df <- tibble(
  year   = c(2010, 2010, 2010, 2010, 2012, 2012, 2012),
  qtr    = c(   1,    2,    3,    4,    1,    2,    3),
  return = rnorm(7)
))

## # A tibble: 7 x 3
##    year   qtr return
##   <dbl> <dbl>  <dbl>
## 1  2010     1  1.30 
## 2  2010     2  1.05 
## 3  2010     3  1.20 
## 4  2010     4 -2.12 
## 5  2012     1  1.39 
## 6  2012     2 -1.35 
## 7  2012     3  0.669

We can consider all existing combinations by invoking the column names in expand() or complete() (which either drops or keeps all other columns):

df %>% 
  expand(year, qtr)

## # A tibble: 8 x 2
##    year   qtr
##   <dbl> <dbl>
## 1  2010     1
## 2  2010     2
## 3  2010     3
## 4  2010     4
## 5  2012     1
## 6  2012     2
## 7  2012     3
## 8  2012     4

df %>% 
  complete(year, qtr)

## # A tibble: 8 x 3
##    year   qtr return
##   <dbl> <dbl>  <dbl>
## 1  2010     1  1.30 
## 2  2010     2  1.05 
## 3  2010     3  1.20 
## 4  2010     4 -2.12 
## 5  2012     1  1.39 
## 6  2012     2 -1.35 
## 7  2012     3  0.669
## 8  2012     4 NA

We can consider new combinations by specifying an expectation of possible values:

df %>% 
  expand(year = 2010:2012, qtr)

## # A tibble: 12 x 2
##     year   qtr
##    <int> <dbl>
##  1  2010     1
##  2  2010     2
##  3  2010     3
##  4  2010     4
##  5  2011     1
##  6  2011     2
##  7  2011     3
##  8  2011     4
##  9  2012     1
## 10  2012     2
## 11  2012     3
## 12  2012     4

df %>% 
  complete(year = 2010:2012, qtr)

## # A tibble: 12 x 3
##     year   qtr return
##    <dbl> <dbl>  <dbl>
##  1  2010     1  1.30 
##  2  2010     2  1.05 
##  3  2010     3  1.20 
##  4  2010     4 -2.12 
##  5  2011     1 NA    
##  6  2011     2 NA    
##  7  2011     3 NA    
##  8  2011     4 NA    
##  9  2012     1  1.39 
## 10  2012     2 -1.35 
## 11  2012     3  0.669
## 12  2012     4 NA

Want to link two or more columns when looking for combinations? Use nesting().

9.7 Activity (10 min)

Fill out Exercise 3: Making tibbles in the worksheet.