Pivot wider with pairs of rows

Question

My data looks like this:

id1   id2  date1        date2         len
1      3   2021-01-01   2020-12-01     2
2      3   2021-02-02   2020-12-31     1
3      5   2021-03-01   2020-09-30     3
4      5   2021-03-03   2021-01-30     4
...

For each id2 there is exactly 2 rows. If we let A represent the first of these 2 rows and B the second, I would like to obtain this:

id2   A.date1     A.date2     B.date1      B.date2     A.len     B.len
3     2021-01-01  2020-12-01  2021-02-02   2020-12-31    2         1
5     2021-03-01  2020-09-30  2021-03-03   2021-01-30    3         4

I thought there might be a way to do this with pivot_wider but I haven't been able to do it.

Here is the toy data:

data.frame(id1 = c(1, 2, 3, 4), id2 = c(3, 3, 5, 5), date1 = as.Date(c("2021-01-01", "2021-02-02", "2021-03-01", "2021-03-03")), 
         date2 = as.Date(c("2020-12-01", "2020-12-31", "2020-09-30", "2021-01-30")), len = c(2,1,3,4))

Answer 1

We can create a sequence column grouped by 'id2' (rowid from data.table does that in a single step). Use that column as the names_from and specify the multiple columns in values_from

library(dplyr)
library(tidur)
library(data.table)
df1 %>% 
   # // or do a group by 
   # group_by(id2) %>%
   # mutate(rn = LETTERS[row_number()]) %>%
   mutate(rn = LETTERS[rowid(id2)]) %>% 
   select(-id1) %>% 
   pivot_wider(names_from = rn, values_from = c(date1, date2, len)) 

Answer 2

a data.table approach

library( data.table )
DT[, AB := LETTERS[ rowid( id2 ) ] ][]
dcast( DT, id2 ~ AB, value.var = c("date1", "date2", "len") )

#    id2    date1_A    date1_B    date2_A    date2_B len_A len_B
# 1:   3 2021-01-01 2021-02-02 2020-12-01 2020-12-31     2     1
# 2:   5 2021-03-01 2021-03-03 2020-09-30 2021-01-30     3     4

sample data

DT <- data.table::fread("id1   id2  date1        date2         len
1      3   2021-01-01   2020-12-01     2
2      3   2021-02-02   2020-12-31     1
3      5   2021-03-01   2020-09-30     3
4      5   2021-03-03   2021-01-30     4")