CODEWITHSUNDEEP
rdplyrapplydata-manipulation
L. Tucker
L. Tucker

Reputation: 543

Find cell values in matrix using values from another dataframe

I have a matrix with all cell values being from -1:1 and another dataframe that identifies row/column cells for each cell I need to find.

I want to add columns to the data frame that contain the values in the cells identified in the matrix. Example of what I have and what I want below:

HAVE MATRIX:

      1   2   3   4   5   6   7   8   9  10

 1    0   0   0   1   0   1   1   0   0   0
 2    0   0  -1  -1   1   1  -1  -1   0   0
 3    0   0  -1   0   0  -1   0  -1   0   0
 4   -1   1   0   0   1   1   0  -1   1  -1
 5   -1   1   0  -1  -1   0   0   0   0   1
 6    1  -1   1   1   0   0  -1  -1   0   1
 7    0  -1   1   0   1   1   0   1  -1   0
 8    0   0  -1  -1  -1   0   1  -1   0   1
 9   -1   1   0   1   1  -1   0   1  -1  -1
10   -1   1  -1  -1  -1  -1   1   0   1  -1

HAVE DATAFRAME:

     i   j   k

 1   3   4   2
 2   4   8  10
 3  10   7   5
 4   2   6   8
 5   9  10   7
 6   2  10   4
 7   7   8  10
 8   6  10   8
 9   2   9   5
10   9   7   1

WANT DATAFRAME:

     i    j    k    j,i    k,i    k,j

 1   3    4    2      0     -1     -1
 2   4    8   10     -1     -1      0 
 3  10    7    5      0      1      0
 4   2    6    8     -1      0      0
 5   9   10    7      1     -1      0   
 6   2   10    4      1      1     -1
 7   7    8   10      1      1      0
 8   6   10    8     -1      0      1
 9   2    9    5      1      1      0
10   9    7    1     -1      0      1

Upvotes: 3

Views: 87

Answers (1)

akrun
akrun

Reputation: 886998

One option would be to use combn or sapply(if the combination needs to be in a specific order, loop through the list, extract the columns of second dataset, use that as row/column index of extracting the corresponding elements from first dataset and cbind

indList <- list(ji = c('j', 'i'), ki = c('k', 'i'), kj = c('k', 'j'))
cbind(df2, sapply(indList, function(x) m1[as.matrix(df2[x])]))
#    i  j  k ji ki kj
#1   3  4  2  0 -1 -1
#2   4  8 10 -1 -1  0
#3  10  7  5  0  1  0
#4   2  6  8 -1  0  0
#5   9 10  7  1 -1  0
#6   2 10  4  1  1 -1
#7   7  8 10  1  1  0
#8   6 10  8 -1  0  1
#9   2  9  5  1  1  0
#10  9  7  1 -1  0  1

It is also possible with combn)

cbind(df2, combn(df2, 2, FUN = function(x) m1[as.matrix(x[2:1])]))

data

df2 <- structure(list(i = c(3L, 4L, 10L, 2L, 9L, 2L, 7L, 6L, 2L, 9L), 
    j = c(4L, 8L, 7L, 6L, 10L, 10L, 8L, 10L, 9L, 7L), k = c(2L, 
    10L, 5L, 8L, 7L, 4L, 10L, 8L, 5L, 1L)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10"))

m1 <- structure(c(0L, 0L, 0L, -1L, -1L, 1L, 0L, 0L, -1L, -1L, 0L, 0L, 
0L, 1L, 1L, -1L, -1L, 0L, 1L, 1L, 0L, -1L, -1L, 0L, 0L, 1L, 1L, 
-1L, 0L, -1L, 1L, -1L, 0L, 0L, -1L, 1L, 0L, -1L, 1L, -1L, 0L, 
1L, 0L, 1L, -1L, 0L, 1L, -1L, 1L, -1L, 1L, 1L, -1L, 1L, 0L, 0L, 
1L, 0L, -1L, -1L, 1L, -1L, 0L, 0L, 0L, -1L, 0L, 1L, 0L, 1L, 0L, 
-1L, -1L, -1L, 0L, -1L, 1L, -1L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 
0L, -1L, 0L, -1L, 1L, 0L, 0L, 0L, -1L, 1L, 1L, 0L, 1L, -1L, -1L
), .Dim = c(10L, 10L), .Dimnames = list(c("1", "2", "3", "4", 
"5", "6", "7", "8", "9", "10"), c("1", "2", "3", "4", "5", "6", 
"7", "8", "9", "10")))

Upvotes: 1

Related Questions