Something like this?
packageVersion("cdata")
#> [1] '1.1.4'
library(cdata)
library(rqdatatable)
#> Loading required package: rquery
# example data
d <- wrapr::build_frame(
"ID" , "OP", "DATE" |
1 , "A" , "2001-01-02 00:00:00" |
1 , "B" , "2015-04-25 00:00:00" |
2 , "A" , "2000-04-01 00:00:00" |
3 , "D" , "2014-04-07 00:00:00" |
4 , "C" , "2012-12-01 00:00:00" |
4 , "A" , "2005-06-16 00:00:00" |
4 , "D" , "2009-01-20 00:00:00" |
4 , "B" , "2009-01-20 00:00:00" |
5 , "A" , "2010-10-10 00:00:00" |
5 , "B" , "2003-11-09 00:00:00" |
6 , "B" , "2004-01-09 00:00:00" )
# draw a picture of the record format
diagram <- wrapr::build_frame(
"rank", "DATE", "OP" |
"1", "DATE1", "OP1" |
"2", "DATE2", "OP2" |
"3", "DATE3", "OP3" |
"4", "DATE4", "OP4" |
"5", "DATE5", "OP5" )
# get the time based ranking, and grouped ops
# and then transform
ops <- local_td(d) %.>%
project(.,
OP = list(sort(OP)),
groupby = c("ID", "DATE")) %.>%
extend(.,
rank %:=% row_number(),
partitionby = "ID", orderby = "DATE") %.>%
blocks_to_rowrecs(.,
keyColumns = "ID",
controlTable = diagram)
res <- d %.>% ops
print(res)
#> ID DATE1 OP1 DATE2 OP2 DATE3 OP3
#> 1: 1 2001-01-02 00:00:00 A 2015-04-25 00:00:00 B <NA> NA
#> 2: 2 2000-04-01 00:00:00 A <NA> NA <NA> NA
#> 3: 3 2014-04-07 00:00:00 D <NA> NA <NA> NA
#> 4: 4 2005-06-16 00:00:00 A 2009-01-20 00:00:00 B,D 2012-12-01 00:00:00 C
#> 5: 5 2003-11-09 00:00:00 B 2010-10-10 00:00:00 A <NA> NA
#> 6: 6 2004-01-09 00:00:00 B <NA> NA <NA> NA
#> DATE4 OP4 DATE5 OP5
#> 1: <NA> NA <NA> NA
#> 2: <NA> NA <NA> NA
#> 3: <NA> NA <NA> NA
#> 4: <NA> NA <NA> NA
#> 5: <NA> NA <NA> NA
#> 6: <NA> NA <NA> NA