dataframe - Need help reshaping an R dataset

Question

My dataset currently looks something like this:

id  date00  var1_00 var2_00 date01  var1_01 var2_01
1   1/1/2019    1   2       1/1/2020    3   4
2   2/2/2019    1   2       2/2/2020    3   4
3   3/3/2019    1   2       3/3/2020    3   4

The code for the table:

structure(list(id = c(1, 2, 3), date00 = structure(c(1546300800, 
1549065600, 1551571200), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    var1_00 = c(1, 1, 1), var2_00 = c(2, 2, 2), date01 = structure(c(1577836800, 
    1580601600, 1583193600), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    var1_01 = c(3, 3, 3), var2_01 = c(4, 4, 4)), row.names = c(NA, 
-3L), class = c("tbl_df", "tbl", "data.frame"))

How can I reshape it so it looks like this:

id  date    var1_00 var2_00 var1_01 var2_01
1   1/1/2019    1   2       NA      NA
2   2/2/2019    1   2       NA      NA
3   3/3/2019    1   2       NA      NA
1   1/1/2020    NA  NA      3       4
2   1/1/2020    NA  NA      3       4
3   1/1/2020    NA  NA      3       4

Thank you!

question from:https://stackoverflow.com/questions/65601619/need-help-reshaping-an-r-dataset

Answer 1

Here is a data.table option using rbindlist

setDT(df)
dt1 <- setnames(df[,.SD,.SDcols = grep("^id|00$",names(df))],"date00","date")
dt2 <- setnames(df[,.SD,.SDcols = grep("^id|01$",names(df))],"date01","date")
out <- rbindlist(list(dt1,dt2),fill = TRUE)

or

dt <- as.data.table(df)
out <- rbindlist(
  lapply(
    split.default(dt[,-1],gsub(".*(\d+$)","\1",names(dt)[-1])),
    function(x) cbind(dt[,1],setnames(x,1,"date"))),
  fill = TRUE
)

such that

> out
   id       date var1_00 var2_00 var1_01 var2_01
1:  1 2019-01-01       1       2      NA      NA
2:  2 2019-02-02       1       2      NA      NA
3:  3 2019-03-03       1       2      NA      NA
4:  1 2020-01-01      NA      NA       3       4
5:  2 2020-02-02      NA      NA       3       4
6:  3 2020-03-03      NA      NA       3       4