What “tidy” means

A dataset is tidy when:

1. Each variable is a column.
2. Each observation is a row.

The same numbers can be stored many different ways. Only one of the following is tidy:

table1   # tidy: country, year, cases, population each a column

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

The same data, untidy

table2   # cases and population are stacked into type/count

# A tibble: 12 × 4
   country      year type            count
   <chr>       <dbl> <chr>           <dbl>
 1 Afghanistan  1999 cases             745
 2 Afghanistan  1999 population   19987071
 3 Afghanistan  2000 cases            2666
 4 Afghanistan  2000 population   20595360
 5 Brazil       1999 cases           37737
 6 Brazil       1999 population  172006362
 7 Brazil       2000 cases           80488
 8 Brazil       2000 population  174504898
 9 China        1999 cases          212258
10 China        1999 population 1272915272
11 China        2000 cases          213766
12 China        2000 population 1280428583

table2 holds identical information, but type/count jams two variables into a name/value pair. Computing a rate is awkward here and natural in table1.

The dead giveaway that the data is not tidy here is that there are multiple rows corresponding to the same country / year observation.

Two layouts of one dataset

Reshaping refers to moving back and forth between wide and narrow data formats. For example, data on blood pressure (BP) in wide form:

bp_wide <- tribble(
  ~subject, ~before, ~after,
  "BHO",        160,    160,
  "GWB",        115,    135,
  "WJC",        105,    145
)
bp_wide

# A tibble: 3 × 3
  subject before after
  <chr>    <dbl> <dbl>
1 BHO        160   160
2 GWB        115   135
3 WJC        105   145

Here, we have row per subject, and before/after are separate columns.

The same BP data, narrow

bp_narrow <- bp_wide |>
  pivot_longer(
    cols      = c(before, after),
    names_to  = "when",
    values_to = "sbp"
  )
bp_narrow

# A tibble: 6 × 3
  subject when     sbp
  <chr>   <chr>  <dbl>
1 BHO     before   160
2 BHO     after    160
3 GWB     before   115
4 GWB     after    135
5 WJC     before   105
6 WJC     after    145

Now one row per measurement occasion (before/after): the case changed meaning, the numbers didn’t.

The idea

pivot_longer() takes several columns and stacks them into two new columns: one holding the old names, one holding the old values.

There are three arguments after the data:

• cols: which columns to gather (compatible with select() syntax: starts_with(), ranges with :, !)
• names_to: name for the new column that will hold the old column names
• values_to: name for the new column that will hold the cell values

bp_wide |>
  pivot_longer(cols = c(before, after), names_to = "when", values_to = "sbp")

A real one: when names ARE data

The billboard data (in tidyr) has one column per week (wk1, wk2, …). The week is a variable trapped in the column names:

billboard |>
  pivot_longer(
    cols      = starts_with("wk"),
    names_to  = "week",
    values_to = "rank",
    values_drop_na = TRUE
  )

# A tibble: 5,307 × 5
   artist  track                   date.entered week   rank
   <chr>   <chr>                   <date>       <chr> <dbl>
 1 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk1      87
 2 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk2      82
 3 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk3      72
 4 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk4      77
 5 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk5      87
 6 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk6      94
 7 2 Pac   Baby Don't Cry (Keep... 2000-02-26   wk7      99
 8 2Ge+her The Hardest Part Of ... 2000-09-02   wk1      91
 9 2Ge+her The Hardest Part Of ... 2000-09-02   wk2      87
10 2Ge+her The Hardest Part Of ... 2000-09-02   wk3      92
# ℹ 5,297 more rows

values_drop_na = TRUE drops the empty weeks (songs that had already fallen off the chart).

Now it’s plottable

In wide form you couldn’t put “week” on an axis, it wasn’t a variable. After pivoting, it is:

billboard |>
  pivot_longer(
    cols = starts_with("wk"),
    names_to = "week", values_to = "rank",
    values_drop_na = TRUE
  ) |>
  mutate(week = parse_number(week)) |> # "wk3" -> 3
  ggplot(aes(x = week, y = rank, group = track)) +
  geom_line(alpha = 0.25) +
  scale_y_reverse() # rank 1 on top

The opposite move

pivot_wider() spreads one column’s values across new columns. Its two key arguments are the opposite of before:

• names_from: the column whose values become new column names
• values_from: the column whose values fill the new cells

bp_narrow |>
  pivot_wider(names_from = when, values_from = sbp)

# A tibble: 3 × 3
  subject before after
  <chr>    <dbl> <dbl>
1 BHO        160   160
2 GWB        115   135
3 WJC        105   145

We’re back to bp_wide. pivot_longer() and pivot_wider() are inverses.

When you might use it

Previously we learned group_by() |> summarise() produces a long summary. Widening it makes a readable comparison table.

penguins |>
  filter(!is.na(sex)) |>
  group_by(species, sex) |>
  summarise(avg_mass = mean(body_mass_g, na.rm = TRUE), .groups = "drop") |>
  pivot_wider(names_from = sex, values_from = avg_mass)

# A tibble: 3 × 3
  species   female  male
  <fct>      <dbl> <dbl>
1 Adelie     3369. 4043.
2 Chinstrap  3527. 3939.
3 Gentoo     4680. 5485.

Comparison becomes a mutate operation

With female and male now side by side in each row, the difference is one mutate():

penguins |>
  filter(!is.na(sex)) |>
  group_by(species, sex) |>
  summarise(avg_mass = mean(body_mass_g, na.rm = TRUE)) |>
  pivot_wider(names_from = sex, values_from = avg_mass) |>
  mutate(diff = male - female)

# A tibble: 3 × 4
# Groups:   species [3]
  species   female  male  diff
  <fct>      <dbl> <dbl> <dbl>
1 Adelie     3369. 4043.  675.
2 Chinstrap  3527. 3939.  412.
3 Gentoo     4680. 5485.  805.

Which way do I pivot?

What we covered today

• Tidy data: variables in columns, observations in rows, values in cells
• Wide vs. narrow: the same values, different meaning of a “row”
• pivot_longer(): gather many columns into names_to + values_to; the fix when a variable is hiding in column names
• pivot_wider(): spread names_from + values_from into new columns; the way to a comparison table
• They’re inverses, and the right shape is whichever makes your next step easiest