Pairwise matrix to list of outcomes

user_5055647 0
Viewed 42

Suppose we have a matrix M

M       <- matrix(c(1:9),3,3)
diag(M) <- NA
M
     [,1] [,2] [,3]
[1,]   NA    4    7
[2,]    2   NA    8
[3,]    3    6   NA

where each entry describes the outcomes of pairwise interactions. Each interaction of row i with column j is interepreted as "object i outperformed object j X times". Examples: Object 2 performs better than object 1 in 2 cases. Object 1 performs better than object 3 in 7 cases.

Is there a quick way to transform this matrix into an object holding this information in a format where each row fully describes the interactions between two objects? The goal is something like this:

     [,1]   [,2]   [,3] [,4]
[1,] "OBJ1" "OBJ2" "N1" "N2"
[2,] "1"    "2"    "4"  "2" 
[3,] "1"    "3"    "7"  "3" 
[4,] "2"    "3"    "8"  "6"

where the first two columns give the objects that are compared while columns 3 and 4 describe how often OBJ1 outperformed OBJ2 and vice versa. The interpretation of the first row is: Object 1 has outperformed Object 2 4 times, whereas Object 2 has outperformed Object 1 2 times. I have been playing around with reshape2 and aggregating without useful results so far.

reshape2 r
2 Answers

Maybe you can try the code below

inds <- t(combn(dim(M)[1], 2))
Mout <- `colnames<-`(
    cbind(inds, M[inds], M[inds[, 2:1]]),
    do.call(paste0, rev(expand.grid(1:2, c("Obj", "N"))))
)

which gives

> Mout
     Obj1 Obj2 N1 N2
[1,]    1    2  4  2
[2,]    1    3  7  3
[3,]    2    3  8  6
user_121587570

Another solution could be:

M       <- matrix(c(1:9),3,3)
diag(M) <- NA
M1 <- M

M[upper.tri(M, diag=TRUE)] <- NA
M1[lower.tri(M1, diag=TRUE)] <- NA

R1 = reshape2::melt(M1, na.rm=TRUE, value.name="N1")
R2 = reshape2::melt(M, na.rm=TRUE, value.name="N2")

R1$N2 <- R2$N2
rownames(R1) <- NULL

Output:

> R1
  Var1 Var2 N1 N2
1    1    2  4  2
2    1    3  7  3
3    2    3  8  6
user_133280100
Related