Assume I have a matrix:

> mat1
  A B
A 1 3
B 2 4

And a data frame:

> df
  First Second
1    A    A
2    A    B
3    B    A
4    B    B

How do I do a lookup for the value of First and Second in the matrix?

My desired output in this case is:

> df
  First Second result
1    A    A      1
2    A    B      3
3    B    A      2
4    B    B      4

I figured out that I can do df <- cbind(df, result = diag(mat1[df$First, df$Second])) but is there a way to do it without the diag()? I figured that if I have a long dataframe, then mat1[df$First, df$Second] will make the code run slower if it generates a larger matrix.

CodePudding user response：

Alternative:

df$result <- mat1[as.matrix(df)]
df
#   First Second result
# 1     A      A      1
# 2     A      B      3
# 3     B      A      2
# 4     B      B      4

If df has more columns, you'll need to subset them explicitly, as in

mat1[as.matrix(df[c("First","Second")])]
# [1] 1 3 2 4

CodePudding user response：

We may do this directly from the mat1

as.data.frame(as.table(mat1))

-output

  Var1 Var2 Freq
1    A    A    1
2    B    A    2
3    A    B    3
4    B    B    4

Or convert to a matrix

df$result <- mat1[do.call(cbind, df)]

Or use cbind

df$result <- mat1[cbind(df$First, df$Second)]

-output

> df
  First Second result
1     A      A      1
2     A      B      3
3     B      A      2
4     B      B      4

Or use simplify2array

df$result <- mat1[simplify2array(df)]

data

mat1 <- structure(1:4, .Dim = c(2L, 2L), .Dimnames = list(c("A", "B"), 
    c("A", "B")))
df <- structure(list(First = c("A", "A", "B", "B"), Second = c("A", 
"B", "A", "B")), class = "data.frame", row.names = c("1", "2", 
"3", "4"))