Class Meeting 9 Tidy Data and Pivoting
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()
andtidyr::pivot_wider()
. - Making tibbles using
tibble::tibble()
andtidyr::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()
andgather()
functions.
- the repo this links to has some useful exercises too, but uses the older
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:
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()
:
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:
## # 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.
## # 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()
:
## # 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:
## # 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?
## # 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
orpetal
. - “Dimension”, either
length
orwidth
.
The resulting tidy data frame would then be:
## # 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:
## # 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.
## # 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 usualnames_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:
## # 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:
## 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()
:
## # A tibble: 3 x 2
## Day Breakfast
## <dbl> <chr>
## 1 1 Apple
## 2 2 Yogurt
## 3 3 Yogurt
List columns are easy with tibbles!
## # 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()
:
## # 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]>
## # 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:
## # 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()
:
## # 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):
## # 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
## # 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:
## # 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
## # 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.