Elements of Data Science
SDS 322E

H. Sherry Zhang
Department of Statistics and Data Sciences
University of Texas at Austin

Get the repository for today:
library(usethis)
create_from_github("SDS322E-26FALL/0502-pivot", fork = FALSE)

Learning objectives

  • Data reshaping:
    • Refresh on the concept of tidy data
    • New functions: pivot_longer() and pivot_wider()
  • Practice combining pivot_longer() and pivot_wider() with other tidyr functions such as separate()
  • Application: the anscombe example

Artwork by @allison_horst

Is this tidy?

The Billboard dataset: the date a song first entered the Billboard Top 100

❌: No, because wk1, wk2, … are values, not variables - they should be recorded in cells rather than in column names

Is this tidy?

✅ Yes, because 1) The variables are: year, artist, time, track, date, week, and rank, 2) The observation is a recorded rank of a song in a particular week

Pivot longer

Pivot longer

Pivot longer

Pivot longer

Basic syntax:

pivot_longer(cols = columns_to_pivot, 
             names_to = new_colname_to_store_old_column_names, 
             values_to = new_colname_to_store_values))

Example:

df <- tibble(
  id = c("A", "B", "C"),
  bp1 = c(100, 140, 120),
  bp2 = c(120, 115, 125)
)

df
# A tibble: 3 × 3
  id      bp1   bp2
  <chr> <dbl> <dbl>
1 A       100   120
2 B       140   115
3 C       120   125
df |> 
  pivot_longer(
    cols = bp1:bp2,
    names_to = "measurement",
    values_to = "value"
  )
# A tibble: 6 × 3
  id    measurement value
  <chr> <chr>       <dbl>
1 A     bp1           100
2 A     bp2           120
3 B     bp1           140
4 B     bp2           115
5 C     bp1           120
6 C     bp2           125

Your time

usethis::create_from_github("SDS322E-26FALL/0502-pivot", fork = FALSE)

You can use the full billboard dataset from the tidyr package, but we make it easier by consider only 4 columns and 5 rows.

df <- billboard |> 
  select(artist, track,  wk1, wk2) |> 
  head(5)

df
# A tibble: 5 × 4
  artist       track                     wk1   wk2
  <chr>        <chr>                   <dbl> <dbl>
1 2 Pac        Baby Don't Cry (Keep...    87    82
2 2Ge+her      The Hardest Part Of ...    91    87
3 3 Doors Down Kryptonite                 81    70
4 3 Doors Down Loser                      76    76
5 504 Boyz     Wobble Wobble              57    34

Fill in the blanks to pivot df longer:

df |> 
  pivot_longer(
    cols = ..., 
    names_to = ..., 
    values_to = ...
  )

Your result should look like this:

# A tibble: 10 × 4
   artist       track                   week   rank
   <chr>        <chr>                   <chr> <dbl>
 1 2 Pac        Baby Don't Cry (Keep... wk1      87
 2 2 Pac        Baby Don't Cry (Keep... wk2      82
 3 2Ge+her      The Hardest Part Of ... wk1      91
 4 2Ge+her      The Hardest Part Of ... wk2      87
 5 3 Doors Down Kryptonite              wk1      81
 6 3 Doors Down Kryptonite              wk2      70
 7 3 Doors Down Loser                   wk1      76
 8 3 Doors Down Loser                   wk2      76
 9 504 Boyz     Wobble Wobble           wk1      57
10 504 Boyz     Wobble Wobble           wk2      34

Example - billboard

billboard |> 
  select(artist, track,  wk1, wk2) |> 
  head(5) |> 
  pivot_longer(cols = c(wk1, wk2), names_to = "week", values_to = "rank")
# A tibble: 10 × 4
   artist       track                   week   rank
   <chr>        <chr>                   <chr> <dbl>
 1 2 Pac        Baby Don't Cry (Keep... wk1      87
 2 2 Pac        Baby Don't Cry (Keep... wk2      82
 3 2Ge+her      The Hardest Part Of ... wk1      91
 4 2Ge+her      The Hardest Part Of ... wk2      87
 5 3 Doors Down Kryptonite              wk1      81
 6 3 Doors Down Kryptonite              wk2      70
 7 3 Doors Down Loser                   wk1      76
 8 3 Doors Down Loser                   wk2      76
 9 504 Boyz     Wobble Wobble           wk1      57
10 504 Boyz     Wobble Wobble           wk2      34

You can do exactly the same with all the rows (remove head(5)).

What would you do if you want to pivot all the week columns (wk1, wk2, …, wk76) longer?

Example - billboard

In the full billboard dataset there are 76 weeks: wk1: wk76, we can use starts_with("wk") to select all the columns that start with “wk”.

billboard |> 
  pivot_longer(
    cols = starts_with("wk"), 
    names_to = "week", 
    values_to = "rank"
  ) 
# A tibble: 24,092 × 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 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk8      NA
 9 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk9      NA
10 2 Pac  Baby Don't Cry (Keep... 2000-02-26   wk10     NA
# ℹ 24,082 more rows

Select syntax

billboard |> select(wk1: wk20) # all the variables in between variable a and b (inclusive) 

billboard |> select(starts_with("wk")) # variables start with "wk"
billboard |> select(!starts_with("wk")) # everything other than the ones start with "wk"

penguins |> select(contains("bill")) # bill_len and bill_dep

# `|` and `&` is used in the same way as in the predicate functions in `filter()`
penguins |> select(contains("bill") | "species") # bill_len, bill_dep, species
penguins |> select(contains("bill") & contains("len")) # bill_len


penguins |> select(-species) # deselect the column

Furter cleaning with separate()

Use separate() to split week into two columns: “wk” and (1, 2, 3, 4, …).

  • sep = 2 means split at the second position
billboard |> 
  pivot_longer(cols = starts_with("wk"), names_to = "week", values_to = "rank") |>  
  separate(week, into = c("wk", "week"), sep = 2) |> 
  select(-wk)
# A tibble: 24,092 × 5
   artist track                   date.entered week   rank
   <chr>  <chr>                   <date>       <chr> <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
 7 2 Pac  Baby Don't Cry (Keep... 2000-02-26   7        99
 8 2 Pac  Baby Don't Cry (Keep... 2000-02-26   8        NA
 9 2 Pac  Baby Don't Cry (Keep... 2000-02-26   9        NA
10 2 Pac  Baby Don't Cry (Keep... 2000-02-26   10       NA
# ℹ 24,082 more rows

Pivot wider

The inverse of pivot longer

Pivot wider

Basic syntax:

pivot_wider(names_from = old_colnames_whose_values_become_the_new_colnames, 
            values_from = old_colnames_whose_values_fill_in_the_cells)

Example:

df <- tibble(
  id = c("A", "B", "B", "A", "A"),
  measurement = c("bp1", "bp1", "bp2", "bp2", "bp3"),
  value = c(100, 140, 115, 120, 105)
)

df
# A tibble: 5 × 3
  id    measurement value
  <chr> <chr>       <dbl>
1 A     bp1           100
2 B     bp1           140
3 B     bp2           115
4 A     bp2           120
5 A     bp3           105
df |> 
  pivot_wider(
    names_from = measurement,
    values_from = value
  )
# A tibble: 2 × 4
  id      bp1   bp2   bp3
  <chr> <dbl> <dbl> <dbl>
1 A       100   120   105
2 B       140   115    NA

Recall from visualization lecture 1 …

Anscombe’s quartet

anscombe
   x1 x2 x3 x4    y1   y2    y3    y4
1  10 10 10  8  8.04 9.14  7.46  6.58
2   8  8  8  8  6.95 8.14  6.77  5.76
3  13 13 13  8  7.58 8.74 12.74  7.71
4   9  9  9  8  8.81 8.77  7.11  8.84
5  11 11 11  8  8.33 9.26  7.81  8.47
6  14 14 14  8  9.96 8.10  8.84  7.04
7   6  6  6  8  7.24 6.13  6.08  5.25
8   4  4  4 19  4.26 3.10  5.39 12.50
9  12 12 12  8 10.84 9.13  8.15  5.56
10  7  7  7  8  4.82 7.26  6.42  7.91
11  5  5  5  8  5.68 4.74  5.73  6.89

Summary of average for x and y:

# A tibble: 4 × 3
  set   mean_y mean_x
  <chr>  <dbl>  <dbl>
1 1       7.50      9
2 2       7.50      9
3 3       7.5       9
4 4       7.50      9

Your time

To produce the plot on the right from the anscombe data, you should

  • sketch in your head what the clean data to produce this plot would look like, so that you can directly use the ggplot syntax you know.

  • You will need all three (pivot_wider(), pivot_longer(), and separate()), in the proper order, to reshape the anscombe data into the proper shape.

Solution

If we can structure the data into this format…

# A tibble: 44 × 4
      id panel     x     y
   <dbl> <dbl> <dbl> <dbl>
 1     1     1    10  8.04
 2     2     1     8  6.95
 3     3     1    13  7.58
 4     4     1     9  8.81
 5     5     1    11  8.33
 6     6     1    14  9.96
 7     7     1     6  7.24
 8     8     1     4  4.26
 9     9     1    12 10.8 
10    10     1     7  4.82
# ℹ 34 more rows

then it is easy to map them into x and y axes and facet by set.

dt |> 
  ggplot(aes(x = x, y = y)) + 
  geom_point() + 
  facet_wrap(vars(panel))

Solution

Should we use pivot_wider() or pivot_longer() first?

pivot_longer()!

Start from a tibble:

dt <- rownames_to_column(anscombe, var = "id") |> 
  as_tibble()
dt
# A tibble: 11 × 9
   id       x1    x2    x3    x4    y1    y2    y3    y4
   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1 1        10    10    10     8  8.04  9.14  7.46  6.58
 2 2         8     8     8     8  6.95  8.14  6.77  5.76
 3 3        13    13    13     8  7.58  8.74 12.7   7.71
 4 4         9     9     9     8  8.81  8.77  7.11  8.84
 5 5        11    11    11     8  8.33  9.26  7.81  8.47
 6 6        14    14    14     8  9.96  8.1   8.84  7.04
 7 7         6     6     6     8  7.24  6.13  6.08  5.25
 8 8         4     4     4    19  4.26  3.1   5.39 12.5 
 9 9        12    12    12     8 10.8   9.13  8.15  5.56
10 10        7     7     7     8  4.82  7.26  6.42  7.91
11 11        5     5     5     8  5.68  4.74  5.73  6.89
dt |> 
  pivot_longer(cols = x1: y4, 
               names_to = "variable", 
               values_to = "value") 
# A tibble: 88 × 3
   id    variable value
   <chr> <chr>    <dbl>
 1 1     x1       10   
 2 1     x2       10   
 3 1     x3       10   
 4 1     x4        8   
 5 1     y1        8.04
 6 1     y2        9.14
 7 1     y3        7.46
 8 1     y4        6.58
 9 2     x1        8   
10 2     x2        8   
# ℹ 78 more rows

Solution

Use separate() to split variable into two columns: type (x or y) and panel (1, 2, 3, 4).

  • sep = 1 means split at the first position
dt |> 
  pivot_longer(cols = x1: y4, names_to = "variable", values_to = "value") |> 
  separate(variable, into = c("type", "panel"), sep = 1)
# A tibble: 88 × 4
   id    type  panel value
   <chr> <chr> <chr> <dbl>
 1 1     x     1     10   
 2 1     x     2     10   
 3 1     x     3     10   
 4 1     x     4      8   
 5 1     y     1      8.04
 6 1     y     2      9.14
 7 1     y     3      7.46
 8 1     y     4      6.58
 9 2     x     1      8   
10 2     x     2      8   
# ℹ 78 more rows

Should we use pivot_wider() or pivot_longer() now?

Solution

pivot_wider()!

dt |> 
  pivot_longer(cols = x1: y4, names_to = "variable", values_to = "value") |> 
  separate(variable, into = c("type", "panel"), sep = 1) |> 
  pivot_wider(names_from = type, values_from = value)
# A tibble: 44 × 4
   id    panel     x     y
   <chr> <chr> <dbl> <dbl>
 1 1     1        10  8.04
 2 1     2        10  9.14
 3 1     3        10  7.46
 4 1     4         8  6.58
 5 2     1         8  6.95
 6 2     2         8  8.14
 7 2     3         8  6.77
 8 2     4         8  5.76
 9 3     1        13  7.58
10 3     2        13  8.74
# ℹ 34 more rows

Solution

Combine everything together:

dt2 <- dt |> 
  pivot_longer(cols = x1: y4, names_to = "variable", values_to = "value") |> 
  separate(variable, into = c("type", "panel"), sep = 1) |> 
  pivot_wider(names_from = type, values_from = value)

dt2 |> 
  ggplot(aes(x = x, y = y)) + 
  geom_point() + 
  geom_smooth(method = "lm") + 
  facet_wrap(vars(panel), ncol = 2)