Combining rows based on value, creating new columns as needed-CodePudding

I have a dataframe like this:

data <- data.frame(Site= c("a","a","a","b","b","c","c","c"),
                   Start=c("2017-11-29","2018-09-24","2018-05-01","2018-09-23","2019-10-06","2020-09-07","2018-09-17","2019-10-08"),
                   End=c("2018-09-26","2019-09-11","2018-09-23","2019-06-28","2020-09-07","2021-08-26","2019-10-08","2020-09-02"))

  Site      Start        End
1    a 2017-11-29 2018-09-26
2    a 2018-09-24 2019-09-11
3    a 2018-05-01 2018-09-23
4    b 2018-09-23 2019-06-28
5    b 2019-10-06 2020-09-07
6    c 2020-09-07 2021-08-26
7    c 2018-09-17 2019-10-08
8    c 2019-10-08 2020-09-02

I would like to combine rows with similar Sites, to look like this:

   Site    Start        End      Start2      End2      End3       End3
1    a 2017-11-29 2018-09-26  2018-09-24  2019-09-11  2018-05-01 2018-09-23
2    b 2018-09-23 2019-06-28  2019-10-06 2020-09-07      NA         NA
3    c 2020-09-07 2021-08-26  2018-09-17 2019-10-08   2019-10-08 2020-09-02

Thanks!

CodePudding user response：

data <- data.frame(Site= c("a","a","a","b","b","c","c","c"),
                   Start=c("2017-11-29","2018-09-24","2018-05-01","2018-09-23","2019-10-06","2020-09-07","2018-09-17","2019-10-08"),
                   End=c("2018-09-26","2019-09-11","2018-09-23","2019-06-28","2020-09-07","2021-08-26","2019-10-08","2020-09-02"))

library(tidyr)
library(dplyr)

data %>% group_by(Site) %>% mutate(id = 1:n()) %>% 
pivot_wider(id_cols = Site, names_from = id, values_from = c(Start, End) )

#> # A tibble: 3 × 7
#> # Groups:   Site [3]
#>   Site  Start_1    Start_2    Start_3    End_1      End_2      End_3     
#>   <chr> <chr>      <chr>      <chr>      <chr>      <chr>      <chr>     
#> 1 a     2017-11-29 2018-09-24 2018-05-01 2018-09-26 2019-09-11 2018-09-23
#> 2 b     2018-09-23 2019-10-06 <NA>       2019-06-28 2020-09-07 <NA>      
#> 3 c     2020-09-07 2018-09-17 2019-10-08 2021-08-26 2019-10-08 2020-09-02