r - Match row names and column names to values in another data frame

Question

I have two data frames as follows :

df1 <- t(data.frame(seq(1,6,by=1),seq(6,1,by=-1)))    
colnames(df1) <- c("A","B","C","D","E","F)    
rownames(df1) <- c("a","b")    
df2 <- data.frame(rep(colnames(df1),2),rep(rownames(df1),6))    
colnames(df2) <- c("Vector1","Vector2")

Such that

df1

     A   B   C   D   E   F
  a  1   2   3   4   5   6
  b  6   5   4   3   2   1

df2

   Vector1    Vector2
     A           a
     B           b
     C           a
     D           b
     E           a
     F           b
     A           a
     B           b
     C           a
     D           b
     E           a
     F           b

I want to match the column values of df2 to column names and row names of df1, and fill the corresponding value to a new column in df2 as follows:

 Vector1 Vector2   Newcol
   A       a         1
   B       b         5
   C       a         3
   D       b         3
   E       a         5
   F       b         1
   A       a         1
   B       b         5
   C       a         3
   D       b         3
   E       a         5
   F       b         1

Any suggestions would be much appreciated. Thanks.

Answer 1

We can use merge with melt. The melt returns a three column data.frame, merge it with the second dataset to create the new column

library(reshape2)
merge(df2, melt(df1), by.x = c("Vector1", "Vector2"), by.y = c("Var2", "Var1"))

---

Or a base R option would be to get the numeric index with match after pasteing the 'df2' rowwise (do.call(paste) and get the pasted column names and row names of 'df1' using outer. Using the numeric index, we get the values in 'df1' to create the 'Newcol'

df2$Newcol <- df1[match(do.call(paste, df2), 
                       t(outer(colnames(df1), rownames(df1), FUN = paste)))]
df2$Newcol
#[1] 1 5 3 3 5 1 1 5 3 3 5 1