joon joon - 1 year ago 58
R Question

Extract all possible pairs within group in data.table

Currently, I am struggling with a problem related to obtaining all possible permutation within group using data.table.

To explain my problem, let me show you an example.

x <- c(1, 1, 1, 2, 2)
y <- c('red', 'blue', 'black', 'orange', 'red')
dt1 <-,y))
x y
1: 1 red
2: 1 blue
3: 1 black
4: 2 orange
5: 2 red

Now I want to see every possible pair of color(y) within group(x). So my ideal result would be....

x y1 y2
1 black blue
1 black red
1 blue black
1 blue red
1 red black
1 red blue
2 orange red
2 red orange

To find a solution for this, I did googling it and I found a function, permutation, which is what I am looking for but I find it hard to squeeze it into data.table framework.

y <- c('red', 'blue', 'black')
permutations(n=3, r=2, v=y, repeats.allowed=F)

[,1] [,2]
[1,] "black" "blue"
[2,] "black" "red"
[3,] "blue" "black"
[4,] "blue" "red"
[5,] "red" "black"
[6,] "red" "blue"

So I tried to do the following but obviously it has errors..

dt1[, .(j = lapply(.SD, permutations, n=.N, r=2, v=y, repeats.allowed=F)), by=x]

Any suggestion for this?
I will really appreciate it.

Answer Source

First off, don't use to create the data table. You will get unexpected column classes due to cbind coercing to matrix. Use

dt1 <- data.table(x, y)

That said, you can do

dt1[, {
        p <- gtools::permutations(.N, 2, y, repeats=FALSE)
        .(y1 = p[, 1], y2 = p[, 2])
    }, by = x]

which gives

   x     y1     y2
1: 1  black   blue
2: 1  black    red
3: 1   blue  black
4: 1   blue    red
5: 1    red  black
6: 1    red   blue
7: 2 orange    red
8: 2    red orange

There is no need to loop since we are operating on groups. permutations creates a matrix, so we create our desired result columns from the resulting matrix columns of permutations.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download