Conditional insertion of extra row in data.table

Question

library(data.table)
library(tibble)

dt1 <- setDT(tribble(~ID,~time,~val1,~val2,
                    1,2,4,NA,
        1,2,3,"O",
        1,2,3,"A",
        1,3,3,"F",
        2,1,3,"A",
        2,2,2,"B",
        2,2,1,"L",
         3,2,2,"B",
        3,3,2,"O"),key = c("ID","time"))

Each ID should have a row for time 1,2 and 3 with NA in col val1 and val2 in case the timepoint is missing. Any suggestions?

desired output:

(setDT(tribble(~ID,~time,~val1,~val2,
              1,1,NA,NA,
              1,2,4,NA,
              1,2,3,"O",
              1,2,3,"A",
              1,3,3,"F",
              2,1,3,"A",
              2,2,2,"B",
              2,2,1,"L",
              2,3,NA,NA,
              3,1,NA,NA,
              3,2,2,"B",
              3,3,2,"O"),key = c("ID","time")))

Add your expected output pls. – cdcarrion Sep 18 '22 at 18:59 — cdcarrion, Sep 18 '22 at 18:59

score 2 · Accepted Answer · answered Sep 18 '22 at 19:13

2

We could use crossjoin

library(data.table)
dt1[CJ(ID, time, unique = TRUE)]

-output

Key: <ID, time>
       ID  time  val1   val2
    <num> <num> <num> <char>
 1:     1     1    NA   <NA>
 2:     1     2     4   <NA>
 3:     1     2     3      O
 4:     1     2     3      A
 5:     1     3     3      F
 6:     2     1     3      A
 7:     2     2     2      B
 8:     2     2     1      L
 9:     2     3    NA   <NA>
10:     3     1    NA   <NA>
11:     3     2     2      B
12:     3     3     2      O

answered Sep 18 '22 at 19:13

akrun

874,273
37
540
662

1

Excellent-- I havent understood the use of CJ, but have to look into it - exactly what I was looking for – Misha Sep 18 '22 at 19:19
In case I wanted to add rows indicating time 4 and 5 in addition to the set of times (1,2,3) in time. How could I do that? – Misha Sep 18 '22 at 19:21
1

@Misha in that case, you may need `dt1[CJ(ID, time = 1:5, unique = TRUE)]` – akrun Sep 18 '22 at 19:21
1

impressive--thx – Misha Sep 18 '22 at 19:22

Conditional insertion of extra row in data.table

1 Answers1