8  Tidy data and pivoting

Objectives

• Define tidy data. Understand the three rules that make a dataset tidy: each variable must live in its own column, each observation must occupy its own row, and each value must appear in a single cell. Appreciate why a consistent data structure makes it easier to learn and use tidyverse tools.
• Lengthen data with pivot_longer(). Use pivot_longer() from the tidyr package to reshape untidy datasets by gathering column names into a new variable and their values into another variable. Learn how to select columns to pivot, specify the names of the new variables, and optionally drop missing values.
• Handle multiple variables in column names. Recognize when column headers encode multiple pieces of information (for example, method, gender, and age) and use the names_to/names_sep arguments of pivot_longer() to split them into separate variables, or names_pattern when a regular expression is a better fit than a fixed separator.
• Extract both a variable name and a value from a column header. Recognize the special .value sentinel for the (surprisingly common) case where column headers mix a variable name with an identifier, such as dob_child1 and name_child1.
• Widen data with pivot_wider(). Use pivot_wider() to spread rows into columns when each observation is represented across multiple rows. Learn how to choose the names_from, values_from, and id_cols arguments so that each row uniquely identifies an observation, and recognize what happens when it does not.

Notes

library(tidyverse)

What is tidy data?

Tidy data is a standard way to organize a dataset so that it works naturally with the tidyverse. In tidy data:

1. Each variable is a column, and each column is a variable. A dataset like table1 from R for Data Science has one column per variable and is the easiest shape to work with.
2. Each observation is a row. Every row corresponds to one observation of all the variables.
3. Each value is a cell. Every cell contains a single value for one variable in one observation.

The payoff is twofold. A single consistent structure makes it easier to learn a whole suite of tools, since they all assume the same underlying shape, and placing variables in columns lets R’s vectorized functions operate the way they are designed to.

Lengthening data with pivot_longer()

Most real datasets are not tidy, because they were organized for data entry or reporting rather than analysis. pivot_longer() lengthens data by gathering a set of columns into key-value pairs. Its most important arguments are cols (which columns to pivot, specified explicitly like bp1:bp2 or with a tidyselect helper like starts_with()), names_to (the name of the new variable built from the column names), values_to (the name of the new variable holding the pivoted values), and values_drop_na (set to TRUE to drop rows created only because a column happened to be empty for that observation).

The built-in billboard dataset records weekly Billboard chart positions: each row is a song, and each wk? column gives its rank in that week of its chart run.

billboard_long <- billboard |>
  pivot_longer(
    cols = starts_with("wk"),
    names_to = "week",
    values_to = "rank",
    values_drop_na = TRUE
  ) |>
  mutate(week = parse_number(week))

billboard_long |> head()

# A tibble: 6 × 5
  artist track                   date.entered  week  rank
  <chr>  <chr>                   <date>       <dbl> <dbl>
1 2 Pac  Baby Don't Cry (Keep... 2000-02-26       1    87
2 2 Pac  Baby Don't Cry (Keep... 2000-02-26       2    82
3 2 Pac  Baby Don't Cry (Keep... 2000-02-26       3    72
4 2 Pac  Baby Don't Cry (Keep... 2000-02-26       4    77
5 2 Pac  Baby Don't Cry (Keep... 2000-02-26       5    87
6 2 Pac  Baby Don't Cry (Keep... 2000-02-26       6    94

This call transforms the 317 by 79 billboard tibble into a 5307 by 5 tibble with one row per song-week combination, which is exactly why nrow() no longer answers “how many songs are there”: it now answers “how many song-week observations are there.”

Splitting multiple pieces of information out of column names

Sometimes a column header packs in more than one piece of information. The who2 dataset records tuberculosis cases with column names like sp_m_014, which combine the diagnosis method (sp), gender (m), and age range (014). Supplying a vector of names to names_to along with names_sep splits each column name at the separator into those separate variables in one step.

who2_long <- who2 |>
  pivot_longer(
    cols = !(country:year),
    names_to = c("diagnosis", "gender", "age"),
    names_sep = "_",
    values_to = "count"
  )

who2_long |> head()

# A tibble: 6 × 6
  country      year diagnosis gender age   count
  <chr>       <dbl> <chr>     <chr>  <chr> <dbl>
1 Afghanistan  1980 sp        m      014      NA
2 Afghanistan  1980 sp        m      1524     NA
3 Afghanistan  1980 sp        m      2534     NA
4 Afghanistan  1980 sp        m      3544     NA
5 Afghanistan  1980 sp        m      4554     NA
6 Afghanistan  1980 sp        m      5564     NA

Extracting a variable name and a value from a header at once

Occasionally a column header encodes a variable name and an identifier together, rather than several independent pieces of information. The household dataset records each child’s date of birth and name in separate columns per child:

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     

dob_child1 and name_child1 are not two unrelated categories the way sp, m, and 014 were; dob and name are themselves variable names that belong in their own columns, while child1/child2 identifies which child a row describes. The special sentinel ".value" inside names_to tells pivot_longer() to use that piece of the column name as a new column name instead of as a value:

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

The result has genuine dob and name columns, one row per family-child combination, with families that only have one child on record contributing only one row instead of a row with an NA child.

Widening data with pivot_wider()

Sometimes a single observation is spread across multiple rows instead, and you need to widen the data into new columns. pivot_wider() increases the number of columns and decreases the number of rows; it is especially useful when a variable identifies the type of measurement and another variable holds the corresponding value. The key arguments are names_from (the column whose unique values become new column names), values_from (the column supplying the values that fill those new columns), and id_cols (the columns that uniquely identify each resulting row).

The cms_patient_experience dataset from the Centers for Medicare & Medicaid Services records several performance measures per healthcare organization, with each organization spread across multiple rows, one per measure.

cms_wide <- cms_patient_experience |>
  pivot_wider(
    id_cols = starts_with("org"),
    names_from = measure_cd,
    values_from = prf_rate
  )

cms_wide |> head()

# A tibble: 6 × 8
  org_pac_id org_nm  CAHPS_GRP_1 CAHPS_GRP_2 CAHPS_GRP_3 CAHPS_GRP_5 CAHPS_GRP_8
  <chr>      <chr>         <dbl>       <dbl>       <dbl>       <dbl>       <dbl>
1 0446157747 USC CA…          63          87          86          57          85
2 0446162697 ASSOCI…          59          85          83          63          88
3 0547164295 BEAVER…          49          NA          75          44          73
4 0749333730 CAPE P…          67          84          85          65          82
5 0840104360 ALLIAN…          66          87          87          64          87
6 0840109864 REX HO…          73          87          84          67          91
# ℹ 1 more variable: CAHPS_GRP_12 <dbl>

Naming org_pac_id and org_nm as id_cols tells pivot_wider() exactly which columns identify a unique organization, so every organization lands on a single row with one column per measure code. id_cols is not optional in the way it looks; leaving it out only means pivot_wider() guesses which remaining columns identify a row, and a wrong guess produces duplicate or list-column results with no error at all (see Example 7.3).

Fringe cases and common pitfalls

ExampleExample 7.1

names_pattern does everything names_sep does, with regular expressions instead of a fixed character.

R4DS mentions names_pattern as an alternative to names_sep for splitting column names, but never actually shows it in action. Here it is, reproducing the who2 split from this session’s notes with a regular expression capturing the same three pieces:

who2_pattern <- who2 |>
  pivot_longer(
    cols = !(country:year),
    names_to = c("diagnosis", "gender", "age"),
    names_pattern = "(.*)_(.)_(.*)",
    values_to = "count"
  )

identical(who2_pattern, who2_long)

[1] TRUE

Each parenthesized group in the pattern becomes one of the names in names_to, in order. names_sep is really just a convenience wrapper for the common case where a single fixed character cleanly separates every piece; reach for names_pattern once a column name’s structure is too irregular for a single separator character to handle (for example, a fixed number of leading digits followed by free text).

ExampleExample 7.2

A column that looks like a unique identifier might not be.

n_distinct(billboard$track)         # fewer distinct titles than rows...

[1] 316

nrow(distinct(billboard, artist, track))  # ...because artist matters too

[1] 317

billboard has 317 rows but only 316 distinct track titles, because two different artists, Donell Jones and Shade Sheist, each charted a song called “Where I Wanna Be” the same year. track alone cannot serve as a unique identifier for a song; you need the combination of artist and track together. This is exactly the kind of column that looks safe to hand to pivot_wider()’s id_cols and is not, as the next example shows directly.

ExampleExample 7.3

When id_cols does not uniquely identify a row, pivot_wider() does not error; it silently builds list-columns.

messy <- tibble(
  id = c(1, 1, 2),
  measure = c("bp", "bp", "bp"),
  value = c(120, 125, 130)
)

messy |> pivot_wider(names_from = measure, values_from = value)

Warning: Values from `value` are not uniquely identified; output will contain list-cols.
• Use `values_fn = list` to suppress this warning.
• Use `values_fn = {summary_fun}` to summarise duplicates.
• Use the following dplyr code to identify duplicates.
  {data} |>
  dplyr::summarise(n = dplyr::n(), .by = c(id, measure)) |>
  dplyr::filter(n > 1L)

# A tibble: 2 × 2
     id bp       
  <dbl> <list>   
1     1 <dbl [2]>
2     2 <dbl [1]>

Because id = 1 has two different bp readings and nothing else distinguishes those two rows, pivot_wider() cannot put both values in one ordinary cell. Instead of raising an error, it warns you and packs every value for that combination into a list, so the resulting bp column holds <dbl [2]> for id = 1 and <dbl [1]> for id = 2, a fundamentally different (and much less convenient) kind of column than the plain numeric column you were probably expecting. If you ever see “values are not uniquely identified; output will contain list-cols,” your id_cols do not actually pin down one row per observation, exactly as in Example 7.2; decide whether you need an additional identifying column, or need to summarize the duplicates (with values_fn) before widening.

ExampleExample 7.4

pivot_wider() orders new columns by first appearance, not numeric order, which text sorting can quietly scramble.

long <- billboard |>
  select(artist, track, wk1:wk12) |>
  pivot_longer(cols = starts_with("wk"), names_to = "week", values_to = "rank", values_drop_na = TRUE)

# widening right away: columns come out in the sensible order wk1, wk2, ..., wk12
long |> pivot_wider(names_from = week, values_from = rank) |> names()

 [1] "artist" "track"  "wk1"    "wk2"    "wk3"    "wk4"    "wk5"    "wk6"   
 [9] "wk7"    "wk8"    "wk9"    "wk10"   "wk11"   "wk12"  

# widening after arranging by the (character) week column first
long |> arrange(week) |> pivot_wider(names_from = week, values_from = rank) |> names()

 [1] "artist" "track"  "wk1"    "wk10"   "wk11"   "wk12"   "wk2"    "wk3"   
 [9] "wk4"    "wk5"    "wk6"    "wk7"    "wk8"    "wk9"   

The first version looks correct purely by luck: the rows happened to still be in the order pivot_longer() produced them, which visits wk1 through wk12 in their original numeric order. Sorting the long data by the week column first, perhaps while cleaning it up for display, reorders those rows alphabetically as text, so pivot_wider() (which names columns in the order their values first appear) produces wk1, wk10, wk11, wk12, wk2, and so on. If you need a guaranteed column order after widening, use relocate() or select() afterward rather than relying on row order going in.

Recap

Term	Definition
Tidy data	Each variable is a column, each observation is a row, each value is a cell.
pivot_longer()	Gathers columns into key-value pairs, increasing the number of rows and decreasing the number of columns.
names_to / values_to	Name the new variable built from column names, and the new variable holding the pivoted values.
names_sep	Splits each pivoted column name into several new variables at a fixed character.
names_pattern	Splits each pivoted column name into several new variables using a regular expression, for cases names_sep can’t handle.
.value sentinel	Inside names_to, tells pivot_longer() to use part of the column name as a new column name rather than as a value.
pivot_wider()	Spreads values across new columns, increasing the number of columns and decreasing the number of rows.
id_cols	The columns that must, together, uniquely identify each resulting row when widening.
List-column	A column whose cells can each hold more than one value; pivot_wider() produces one when id_cols doesn’t uniquely identify a row.
Column order after widening	Determined by the order each names_from value first appears in the data, not by numeric or alphabetical order.

Check your understanding

NoteProblems

1. State the three rules that make a dataset tidy.
2. After running billboard |> pivot_longer(cols = starts_with("wk"), names_to = "week", values_to = "rank", values_drop_na = TRUE), does nrow() on the result still tell you how many songs are in the dataset? Explain.
3. A dataset has columns income_2020, income_2021, expenses_2020, and expenses_2021. What would you put in names_to to end up with separate income and expenses columns, plus a year column, in one pivot_longer() call?
4. You widen a dataset with pivot_wider(names_from = measure, values_from = value) and one of the resulting columns comes back as a list-column instead of an ordinary numeric column. What does that tell you about your id_cols?
5. Why might two pivot_longer() calls that use names_sep versus names_pattern produce identical results? When would you have to reach for names_pattern instead of names_sep?

TipSolutions

1. Each variable is a column, each observation is a row, and each value occupies a single cell.

2. No. Pivoting longer changes the unit of observation from one row per song to one row per song-week, so nrow() after pivoting tells you the number of song-week observations, not the number of distinct songs. Use n_distinct() on an identifying column (or combination of columns) to count the original units instead.

3. names_to = c(".value", "year") with names_sep = "_". The .value sentinel picks up income/expenses as new column names, and year becomes an ordinary column holding 2020/2021.

4. It tells you the named id_cols do not uniquely identify a row: at least one combination of your identifying columns has more than one value for that measure, so pivot_wider() had nowhere to put them except inside a list. You either need an additional identifying column, or need to summarize the duplicate values before widening.

5. names_sep is really a convenience shorthand for the common case where a single fixed character cleanly divides every piece of a column name; internally, splitting on a fixed separator can be expressed as a fairly simple regular expression, which is why the two approaches can produce identical results. You need names_pattern once the column names have a structure too irregular for one separator character to capture, such as a fixed-width prefix followed by free-form text, or pieces that aren’t consistently separated at all.