Dplyr in R – Pivot_wider with multiple columns R

September 5, 2022

I’m trying to reorder my dataset. It currently looks like this:

Grey.Reef.Shark    Silvertip.Shark monthyear   KUD
     0.01536934     0.006169693      2014/01   50
     0.07434824     0.067793605      2014/01   95
     0.01577231     0.009832403      2014/02   50
     0.07184520     0.092637823      2014/02   95

And I’d like it to look like this:

   50             95         monthyear         species
0.01536934    0.07434824      2014/01       Grey.Reef.Shark
0.006169693   0.067793605     2014/01       Silvertip.Shark
0.01577231    0.07184520      2014/02       Grey.Reef.Shark
0.009832403   0.092637823     2014/02       Silvertip.Shark

I think you can do this with tidyverse pivot_wider in dplyr package but I’m struggling to make it work.

A reproducible dataset can be found below.

structure(list(Grey.Reef.Shark = c(0.0153693415007032, 0.0743482431216283, 
0.0157723122886768, 0.0718452019326124, 0.0271587991640831, 0.139118848810864
), Silvertip.Shark = c(0.00616969268071902, 0.0677936053733444, 
0.00983240322136223, 0.0926378231217502, 0.0484117524071891, 
0.235938189749556), monthyear = c("2014/01", "2014/01", "2014/02", 
"2014/02", "2014/03", "2014/03"), KUD = structure(c(1L, 2L, 1L, 
2L, 1L, 2L), levels = c("50", "95"), class = "factor")), row.names = c("50", 
"95", "501", "951", "502", "952"), class = "data.frame")

>Solution :

We could use multiple columns in the values_from as a vector of column names or column index

library(tidyr)
pivot_wider(df1, names_from = KUD, values_from = names(df1)[1:2], names_sep = ".")

-output

# A tibble: 3 × 5
  monthyear Grey.Reef.Shark.50 Grey.Reef.Shark.95 Silvertip.Shark.50 Silvertip.Shark.95
  <chr>                  <dbl>              <dbl>              <dbl>              <dbl>
1 2014/01               0.0154             0.0743            0.00617             0.0678
2 2014/02               0.0158             0.0718            0.00983             0.0926
3 2014/03               0.0272             0.139             0.0484              0.236

For the updated format, reshape to ‘long’ with pivot_longer and then reshape back to ‘wide’ with pivot_wider

library(dplyr)
df1 %>%
   pivot_longer(cols = ends_with("Shark"), names_to = 'species') %>% 
   pivot_wider(names_from = KUD, values_from = value)

-output

# A tibble: 6 × 4
  monthyear species            `50`   `95`
  <chr>     <chr>             <dbl>  <dbl>
1 2014/01   Grey.Reef.Shark 0.0154  0.0743
2 2014/01   Silvertip.Shark 0.00617 0.0678
3 2014/02   Grey.Reef.Shark 0.0158  0.0718
4 2014/02   Silvertip.Shark 0.00983 0.0926
5 2014/03   Grey.Reef.Shark 0.0272  0.139 
6 2014/03   Silvertip.Shark 0.0484  0.236