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")
CodePudding user response:
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