Join each row with each other row

Asked by At

Assume that we have a simple data table:

d <- data.table(a=c(1,2,3),b=c("A","B","C"))

It's content is:

   a b
1: 1 A
2: 2 B
3: 3 C

Now, I want to join/merge each row of the data table with each other row. The result should look like this:

   a b c d
1: 1 A 1 A
2: 1 A 2 B
3: 1 A 3 C
4: 2 B 1 A
5: 2 B 2 B
6: 2 B 3 C
7: 3 C 1 A
8: 3 C 2 B
9: 3 C 3 C

So, each row is basically cbind'ed with the whole data table. What is the most efficient way to do this? Could this be done only using data.table syntax?

Thank you!

Edit: The CJ-Method of data.table does build a cartesion product but only for vectors, not tables.

2 Answers

5
Jaap On

Three possible solutions:

# one
d[, .(c = d$a, d = d$b), by = .(a, b)]

# two
d[, setnames(d, c("c","d")), by = .(a, b)]

# three
d[, setNames(as.list(d), c("c","d")), by = .(a, b)]

which both give:

   a b c d
1: 1 A 1 A
2: 1 A 2 B
3: 1 A 3 C
4: 2 B 1 A
5: 2 B 2 B
6: 2 B 3 C
7: 3 C 1 A
8: 3 C 2 B
9: 3 C 3 C

The second option will also change the column names of the original data.table d. If you don't want that, you adapt the second option to:

d[, setnames(copy(d), c("c","d")), by = .(a, b)]

Additionaly (including my comment under bJust's answer) you can also use merge, but that only works on data.table's when explicitly calling the data.frame-method:

merge.data.frame(d, d, by = NULL)
0
bJust On

My naive solution using data.frame (if that is an alternative), not data.table:

merge(d,d,by=NULL)

by=NULL ensures to get all combinations