Week 11: Reshape

[11a] Summarize / Group

Topics

  • Calculate summary statistics to describe an entire dataset
  • Extract extreme rows using the slice family of functions
  • Apply these operations to distinct subgroups within your data

Readings

  • R4DS (2E) Section 3.5: Groups

Slides

*Note that you can click the three-line (hamburger) icon on the bottom-left of the slides to access a navigation menu. You can click inside the slides region and press the left and right arrow keys on your keyboard to advance and reverse the slides and animations.

Practice

1) Summarizing Data

Using the mpg dataset (included with the tidyverse), calculate summary statistics for vehicle fuel efficiency.

  • First, use summarize() to calculate the overall average city mileage (cty) and highway mileage (hwy). Name these new columns avg_cty and avg_hwy.

  • Next, modify your code to calculate these same averages, but grouped by vehicle class using the .by argument.

  • Add a third summary column called n_cars that counts the number of observations in each class using n().

Answer key

library(tidyverse)

mpg |> 
  summarize(
    avg_cty = mean(cty),
    avg_hwy = mean(hwy),
    n_cars = n(),
    .by = class
  )
## # A tibble: 7 × 4
##   class      avg_cty avg_hwy n_cars
##   <chr>        <dbl>   <dbl>  <int>
## 1 compact       20.1    28.3     47
## 2 midsize       18.8    27.3     41
## 3 suv           13.5    18.1     62
## 4 2seater       15.4    24.8      5
## 5 minivan       15.8    22.4     11
## 6 pickup        13      16.9     33
## 7 subcompact    20.4    28.1     35

2) Extracting Extreme Rows

Using the msleep dataset (also included with the tidyverse), find the animals that sleep the most.

  • First, select just the name, vore, and sleep_total columns to make the output easier to read.

  • Use slice_max() to find the 3 animals with the highest total sleep (sleep_total) across the entire dataset.

  • Now, modify your slice_max() code to find the single animal with the highest total sleep within each feeding type (vore). Remember to use the by argument instead of .by inside slice functions!

Answer key

# Overall top 3 sleepers
msleep |> 
  select(name, vore, sleep_total) |> 
  slice_max(n = 3, order_by = sleep_total)
## # A tibble: 3 × 3
##   name                 vore    sleep_total
##   <chr>                <chr>         <dbl>
## 1 Little brown bat     insecti        19.9
## 2 Big brown bat        insecti        19.7
## 3 Thick-tailed opposum carni          19.4

# Top sleeper per feeding type
msleep |> 
  select(name, vore, sleep_total) |> 
  slice_max(n = 1, order_by = sleep_total, by = vore)
## # A tibble: 5 × 3
##   name                   vore    sleep_total
##   <chr>                  <chr>         <dbl>
## 1 Thick-tailed opposum   carni          19.4
## 2 North American Opossum omni           18  
## 3 Arctic ground squirrel herbi          16.6
## 4 Phalanger              <NA>           13.7
## 5 Little brown bat       insecti        19.9

3) Grouped Transformations

Let’s calculate how each animal’s sleep compares to others with the exact same diet using a grouped mutate.

  • Start with the msleep dataset and use mutate() with the .by argument to group by vore.

  • Calculate a new column called mean_sleep that represents the average sleep_total for that specific diet group. (Note: you will need to add na.rm = TRUE inside your mean function since some vore values are missing).

  • Calculate a second new column called diff_sleep that subtracts the group’s mean_sleep from the animal’s actual sleep_total.

  • Finally, select the name, vore, sleep_total, mean_sleep, and diff_sleep columns to view your work.

Answer key

msleep |> 
  mutate(
    .by = vore,
    mean_sleep = mean(sleep_total, na.rm = TRUE),
    diff_sleep = sleep_total - mean_sleep
  ) |> 
  select(name, vore, sleep_total, mean_sleep, diff_sleep)
## # A tibble: 83 × 5
##    name                       vore  sleep_total mean_sleep diff_sleep
##    <chr>                      <chr>       <dbl>      <dbl>      <dbl>
##  1 Cheetah                    carni        12.1      10.4       1.72 
##  2 Owl monkey                 omni         17        10.9       6.07 
##  3 Mountain beaver            herbi        14.4       9.51      4.89 
##  4 Greater short-tailed shrew omni         14.9      10.9       3.97 
##  5 Cow                        herbi         4         9.51     -5.51 
##  6 Three-toed sloth           herbi        14.4       9.51      4.89 
##  7 Northern fur seal          carni         8.7      10.4      -1.68 
##  8 Vesper mouse               <NA>          7        10.2      -3.19 
##  9 Dog                        carni        10.1      10.4      -0.279
## 10 Roe deer                   herbi         3         9.51     -6.51 
## # ℹ 73 more rows

[11b] Lengthen / Widen

Topics

  • Identify when “wide” or “long” format is needed
  • Reshape wide data into long data using pivot_longer()
  • Reshape long data into wide data using pivot_wider()

Readings

Slides

*Note that you can click the three-line (hamburger) icon on the bottom-left of the slides to access a navigation menu. You can click inside the slides region and press the left and right arrow keys on your keyboard to advance and reverse the slides and animations.

Practice

1) Lengthening Data

Using the billboard dataset (included with the tidyr package), reshape the song ranking data from wide to long format.

  • The dataset records the chart rank of songs over 76 weeks in columns wk1 through wk76.
  • Use pivot_longer() to collapse these week columns. Use the starts_with() helper to select them efficiently.
  • Name the new category column "week" and the new value column "rank".
  • Use the names_prefix and names_transform = parse_number arguments to strip the “wk” text and convert the week column into true numbers.

Answer key

library(tidyverse)
data("billboard", package = "tidyr")

billboard |> 
  pivot_longer(
    cols = starts_with("wk"),
    names_to = "week",
    values_to = "rank",
    names_prefix = "wk",
    names_transform = parse_number
  )
## # A tibble: 24,092 × 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
##  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

2) Widening Data

Using the us_rent_income dataset (included with the tidyr package), reshape the state data from long to wide format so that rent and income are presented side-by-side.

  • First, use select() to keep only the NAME, variable, and estimate columns. This prevents issues with duplicate rows caused by other identifiers.
  • Use pivot_wider() to create separate columns for each category stored in the variable column.
  • Pull the actual numerical values from the estimate column.

Answer key

data("us_rent_income", package = "tidyr")

us_rent_income |> 
  select(NAME, variable, estimate) |> 
  pivot_wider(
    names_from = variable,
    values_from = estimate
  )
## # A tibble: 52 × 3
##    NAME                 income  rent
##    <chr>                 <dbl> <dbl>
##  1 Alabama               24476   747
##  2 Alaska                32940  1200
##  3 Arizona               27517   972
##  4 Arkansas              23789   709
##  5 California            29454  1358
##  6 Colorado              32401  1125
##  7 Connecticut           35326  1123
##  8 Delaware              31560  1076
##  9 District of Columbia  43198  1424
## 10 Florida               25952  1077
## # ℹ 42 more rows

3) Pivoting Multiple Columns

Pivoting multiple columns simultaneously is a powerful tool for longitudinal or repeated-measures data. Let’s practice this on a simulated weather dataset.

  • First, copy the code below to create the weather dataset.
  • Use pivot_wider() to flatten this dataset so each station occupies only a single row.
  • Provide both category columns to the names_from argument to combine the season and measurement types.
  • Separate the combined names using a custom character with the names_sep argument.
weather <- tibble(
  station = rep(c("Station1", "Station2"), each = 4),
  season = rep(c("summer", "summer", "winter", "winter"), times = 2),
  measure = rep(c("temp", "precip"), times = 4),
  value = c(85, 2.1, 32, 5.5, 90, 1.8, 40, 6.2)
)

Answer key

weather |> 
  pivot_wider(
    names_from = c(season, measure),
    values_from = value,
    names_sep = "_"
  )
## # A tibble: 2 × 5
##   station  summer_temp summer_precip winter_temp winter_precip
##   <chr>          <dbl>         <dbl>       <dbl>         <dbl>
## 1 Station1          85           2.1          32           5.5
## 2 Station2          90           1.8          40           6.2