CODEWITHSUNDEEP
r
Seen
Seen

Reputation: 4194

Look up value in a matrix based on the strings in a dataframe

I have a data frame like

 X     Y    VALUE    
"AAA" "BBB"
"CCC" "AAA"

I also have a matrix as: 

     AAA BBB CCC
 AAA  1   13  2
 BBB  27  11  4
 CCC  6   12  3

with AAA, BBB and CCC as the column names and row names. How can I fill the VALUE column in my data frame with the values in the matrix?

Upvotes: 0

Views: 1102

Answers (2)

nassimhddd
nassimhddd

Reputation: 8510

You can also get the dataframe directly from the matrix with melt:

library(reshape2)
melt(mat)

Upvotes: 0

flodel
flodel

Reputation: 89097

You can do

df$VALUE <- mat[cbind(df$X, df$Y)]

Using your example:

df <- data.frame(X = c("AAA", "CCC"),
                 Y = c("BBB", "AAA"), stringsAsFactors = FALSE)
df
#     X   Y
# 1 AAA BBB
# 2 CCC AAA

mat <- matrix(c(1, 27, 6, 13, 11, 12, 2, 4, 3),
              nrow = 3, ncol = 3,
              dim = list(c("AAA", "BBB", "CCC"),
                         c("AAA", "BBB", "CCC")))
mat
#     AAA BBB CCC
# AAA   1  13   2
# BBB  27  11   4
# CCC   6  12   3

df$VALUE <- mat[cbind(df$X, df$Y)]
df
#     X   Y VALUE
# 1 AAA BBB    13
# 2 CCC AAA     6

Upvotes: 2

Related Questions