R data.table create from all combinations of multiple data.tables

Question

I have:

require(data.table)

tblDT1 <- data.table(ID_1 = 1:2)     # in reality, there are multiple columns per table
tblDT2 <- data.table(ID_2 = 3:4)
tblDT3 <- data.table(ID_3 = 5:6)

tblDT1
tblDT2
tblDT3

I want to create a new data.table, and it is all combinations of rows from multiple inputs:

resultDT <- data.table(ID_1 = rep(1:2, each = 4),
                       ID_2 = rep(3:4, times = 4),
                       ID_3 = rep(5:6, times = 2, each = 2))
resultDT
   ID_1 ID_2 ID_3
1:    1    3    5
2:    1    4    5
3:    1    3    6
4:    1    4    6
5:    2    3    5
6:    2    4    5
7:    2    3    6
8:    2    4    6

There are different number of rows and/or columns in each data.table that I have.

What is the correct & efficient way of doing this?

I have tried expand.grid, merge but they not working for my case.

Please note in reality I have multiple columns in each data.table input, so by explicitly calling out all column names inside either CJ or expand.grid e.g. CJ(DT1$col1, DT1$col2, DT2$col3...) it is not ideal.

@sindri_baldur, more like all combinations of all rows from multiple tables. — LeGeniusII, Oct 01 '19 at 08:28
r u looking for https://stackoverflow.com/questions/10600060/how-to-do-cross-join-in-r? — chinsoon12, Oct 01 '19 at 08:36

score 0 · Answer 1 · answered Oct 01 '19 at 08:32

We can use tidyr::crossing

tidyr::crossing(tblDT1, tblDT2, tblDT3)

#   ID_1  ID_2  ID_3
#  <int> <int> <int>
#1     1     3     5
#2     1     3     6
#3     1     4     5
#4     1     4     6
#5     2     3     5
#6     2     3     6
#7     2     4     5
#8     2     4     6

R data.table create from all combinations of multiple data.tables

1 Answers1