tidying data - reorganizing tibble

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
1 Like

Thanks again, worked fine!

1 Like