With the development version of cdata you can do this as follows (sorry the current CRAN version doesn't work on this example, it hit a corner-case I have now fixed in development).
I think the easy strategy with any method is going to be: add explicit rank information before trying to go to a wide form.
Also I think ID-group 4 is in order C A D in your example.
# remotes::install_github("WinVector/cdata")
library(cdata)
library(rqdatatable)
#> Loading required package: rquery
packageVersion("cdata")
#> [1] '1.1.4'
# 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 , "C" , "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" |
5 , "A" , "2010-10-10 00:00:00" |
5 , "B" , "2003-11-09 00:00:00" |
6 , "B" , "2004-01-09 00:00:00" )
# get the order based ranking
d$rowid <- seq_len(nrow(d))
d <- rquery::extend(d,
rank %:=% row_number(),
partitionby = "ID", orderby = "rowid")
# 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" )
# perform the transform
res <- blocks_to_rowrecs(d, keyColumns = "ID", controlTable = diagram)
knitr::kable(res)
| ID |
DATE1 |
OP1 |
DATE2 |
OP2 |
DATE3 |
OP3 |
DATE4 |
OP4 |
DATE5 |
OP5 |
| 1 |
2001-01-02 00:00:00 |
A |
2015-04-25 00:00:00 |
B |
NA |
NA |
NA |
NA |
NA |
NA |
| 2 |
2000-04-01 00:00:00 |
A |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
| 3 |
2014-04-07 00:00:00 |
C |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
| 4 |
2012-12-01 00:00:00 |
C |
2005-06-16 00:00:00 |
A |
2009-01-20 00:00:00 |
D |
NA |
NA |
NA |
NA |
| 5 |
2010-10-10 00:00:00 |
A |
2003-11-09 00:00:00 |
B |
NA |
NA |
NA |
NA |
NA |
NA |
| 6 |
2004-01-09 00:00:00 |
B |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |