R Data Table add rows to each group if not existing

Question

I have a data table with multiple groups. Each group I'd like to fill with rows containing the values in vals if they are not already present. Additional columns should be filled with NAs.

DT = data.table(group = c(1,1,1,2,2,3,3,3,3), val = c(1,2,4,2,3,1,2,3,4), somethingElse = rep(1,9)) 
vals = data.table(val = c(1,2,3,4))

What I want:

    group val somethingElse
 1:     1   1             1
 2:     1   2             1
 3:     1   3            NA
 4:     1   4             1
 5:     2   1            NA
 6:     2   2             1
 7:     2   3             1
 8:     2   4            NA
 9:     3   1             1
10:     3   2             1
11:     3   3             1
12:     3   4             1

The order of val does not necessarily have to be increasing, the values may also be appened at the beginning/end of each group.

I don't know how to approach this problem. I've thought about using rbindlist(...,fill = TRUE), but then the values will be simply appended. I think some expression with DT[, lapply(...), by = c("group")] might be useful here but I have no idea how to check if a value already exists.

score 2 · Accepted Answer · answered Jan 20 '23 at 11:03

You can use a cross-join:

setDT(DT)[
  CJ(group = group, val = val, unique = TRUE), 
  on = .(group, val)
]

    group val somethingElse
 1:     1   1             1
 2:     1   2             1
 3:     1   3            NA
 4:     1   4             1
 5:     2   1            NA
 6:     2   2             1
 7:     2   3             1
 8:     2   4            NA
 9:     3   1             1
10:     3   2             1
11:     3   3             1
12:     3   4             1

score 1 · Answer 2 · answered Jan 22 '23 at 08:53

Another way to solve your problem:

DT[, .SD[vals, on="val"], by=group]

    group   val somethingElse
 1:     1     1             1
 2:     1     2             1
 3:     1     3            NA
 4:     1     4             1
 5:     2     1            NA
 6:     2     2             1
 7:     2     3             1
 8:     2     4            NA
 9:     3     1             1
10:     3     2             1
11:     3     3             1
12:     3     4             1

# or
DT[CJ(group, val, unique=TRUE), on=.NATURAL]

score 0 · Answer 3 · answered Jan 20 '23 at 14:49

I will just add this answer for a slightly more complex case:

#Raw Data
DT = data.table(group = c(1,1,2,2,2,3,3,3,3),
                  x = c(1,2,1,3,4,1,2,3,4),
                  y = c(2,4,2,6,8,2,4,6,8),
                  somethingElse = rep(1,9))

#allowed combinations of x and y
DTxy = data.table(x = c(1,2,3,4), y = c(2,4,6,8))

Here, I want to add all x,y combinations from DTxy to each group from DT, if not already present.

I've wrote a function to work for subsets.

#function to join subsets on two columns (here: x,y)
DTxyJoin = function(.SD, xy){
  .SD = .SD[xy, on = .(x,y)]
  return(.SD)
}

I then applied the function to each group:

#add x and y to each group if missing
DTres = DT[, DTxyJoin(.SD, DTxy), by = c("group")]

The Result:

    group x y somethingElse
 1:     1 1 2             1
 2:     1 2 4             1
 3:     1 3 6            NA
 4:     1 4 8            NA
 5:     2 1 2             1
 6:     2 2 4            NA
 7:     2 3 6             1
 8:     2 4 8             1
 9:     3 1 2             1
10:     3 2 4             1
11:     3 3 6             1
12:     3 4 8             1

R Data Table add rows to each group if not existing

3 Answers3