Reshape a table from long format to wide

You want to reshape your data frame from a long format to a wide format. In other words, you want to expand two columns into several columns: changing the values of one column into new column names, and redistributing the values of the second column beneath those names.

Step 1 - Pass your data to tidyr::pivot_wider(). pivot_wider() will return a modified copy of the data frame with a new layout.

Step 2 - Identify the column to borrow new column names_from. pivot_wider() will add a new column for each unique value in this column.

Step 3 - Identify the column to borrow new column values_from. pivot_wider() will redistribute these values across the new columns, recreating the relationships that existed in the original data frame.

data %>% 
  pivot_wider(
    names_from = col_A,
    values_from = col_B
  )

Example

vaccine_long describes the number of doses of three types of vaccine that were allocated to Virginia in March, April, and May of 2021.

vaccine_long

# A tibble: 9 × 3
  vaccine month num_doses
  <chr>   <fct>     <dbl>
1 Janssen March    137900
2 Janssen April    139500
3 Janssen May       34000
4 Moderna March    828000
5 Moderna April    719200
6 Moderna May      946080
7 Pfizer  March   1137240
8 Pfizer  April    968760
9 Pfizer  May     1262880

We’d like to display this data more concisely for publication. One way to do that is to reorganize the table. We use pivot_wider() to create a separate column for each month and to redistribute the dose numbers across the new columns.

library(tidyr)
vaccine_long %>% 
  pivot_wider(
    names_from = month,
    values_from = num_doses
  )

# A tibble: 3 × 4
  vaccine   March  April     May
  <chr>     <dbl>  <dbl>   <dbl>
1 Janssen  137900 139500   34000
2 Moderna  828000 719200  946080
3 Pfizer  1137240 968760 1262880

Reshape table to long format in SAS

pivot_wider() is the equivalent of SAS’s TRANSPOSE procedure where the inputted data set is sorted by the BY variables.

For example, we have a long data frame with three columns: student_id, day, and hours indicating the number of hours each student studied each day with multiple observations per student.

In SAS:

PROC TRANSPOSE DATA = data_long OUT = data_wide;
  BY student_id;
  ID day;
  VAR hours;
RUN;

In R:

data_wide <- 
  data_long %>% 
  pivot_wider(
    names_from = day,
    values_from = hours
  )