replace values of dataframe with search in another dataframe in R

Question

I have two dataframes:

Ranking Text (picture as example)

structure(list(`1` = c("GLD", "GLD", "GLD", "GLD", "GLD", "GLD"
), `2` = c("VT", "VT", "VT", "VT", "BIL", "VT"), `3` = c("BIL", 
"BIL", "BIL", "BIL", "VT", "BIL"), `4` = c("UUP", "UUP", "UUP", 
"UUP", "UUP", "UUP"), `5` = c("RSP", "RSP", "RSP", "RSP", "RSP", 
"RSP")), row.names = c("2008-02-01", "2008-03-01", "2008-04-01", 
"2008-05-01", "2008-06-01", "2008-07-01"), class = "data.frame")

Returns Text (picture as example)

structure(list(BIL = c(0.04, -0.22, -0.02, 0.13, 0.07, -0.07), 
    GLD = c(-6, -4.16, 0.92, 4.52, -1.44, -9.29), RSP = c(-2.24, 
    5.72, 2.65, -10.16, -0.5, 2.96), UUP = c(-2.4, 0.94, 0.44, 
    -0.97, 1.02, 5.37), VT = c(0, 0, 0, 0, 0, -1.85)), row.names = c("2008-02-01", 
"2008-03-01", "2008-04-01", "2008-05-01", "2008-06-01", "2008-07-01"
), class = "data.frame")

What I want is create a new dataframe with the same structure than Ranking but with the value of Returns.

Something like this Text

Right now I´m using two For Loop:

for (i in rownames(Ranking)){
  
  for (x in colnames(Ranking)){
    
      stock = as.character(Ranking[i,x])
    
      Solution[i,x] = Returns[i,stock]
    }
    
  }

But I would like to use something more efficient. Any idea?

sorry, what is the dput? about dates: yes, both dataframe has the same nrow and rownames. — Alberto Aguilera, Aug 12 '22 at 15:08
Please [do not post code or data in images](https://meta.stackoverflow.com/q/285551/2372064). Use an alternative [reproducible format](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — MrFlick, Aug 12 '22 at 15:14
structure(list(`1` = c("GLD", "GLD", "GLD", "GLD", "GLD", "GLD" ), `2` = c("VT", "VT", "VT", "VT", "BIL", "VT"), `3` = c("BIL", "BIL", "BIL", "BIL", "VT", "BIL"), `4` = c("UUP", "UUP", "UUP", "UUP", "UUP", "UUP"), `5` = c("RSP", "RSP", "RSP", "RSP", "RSP", "RSP")), row.names = c("2008-02-01", "2008-03-01", "2008-04-01", "2008-05-01", "2008-06-01", "2008-07-01"), class = "data.frame") — Alberto Aguilera, Aug 12 '22 at 15:21
structure(list(BIL = c(0.04, -0.22, -0.02, 0.13, 0.07, -0.07), GLD = c(-6, -4.16, 0.92, 4.52, -1.44, -9.29), RSP = c(-2.24, 5.72, 2.65, -10.16, -0.5, 2.96), UUP = c(-2.4, 0.94, 0.44, -0.97, 1.02, 5.37), VT = c(0, 0, 0, 0, 0, -1.85)), row.names = c("2008-02-01", "2008-03-01", "2008-04-01", "2008-05-01", "2008-06-01", "2008-07-01" ), class = "data.frame") — Alberto Aguilera, Aug 12 '22 at 15:22

akrun · Accepted Answer · 2022-08-12T15:28:27.600

Create a matrix of row/column indexes - row from the row index (or from row.names, matched - if the row names are the same, then we don't need to match), and column index from matching the values from 'Ranking' with the column names of 'Returns', extract the 'Returns' value and assign it to 'Solution' new data created

Solution <- Ranking
Solution[] <- type.convert(Returns[cbind(c(row(Ranking)), 
      match(as.matrix(Ranking), colnames(Returns)))], as.is = TRUE)

-output

> Solution
               1     2     3     4      5
2008-02-01 -6.00  0.00  0.04 -2.40  -2.24
2008-03-01 -4.16  0.00 -0.22  0.94   5.72
2008-04-01  0.92  0.00 -0.02  0.44   2.65
2008-05-01  4.52  0.00  0.13 -0.97 -10.16
2008-06-01 -1.44  0.07  0.00  1.02  -0.50
2008-07-01 -9.29 -1.85 -0.07  5.37   2.96

replace values of dataframe with search in another dataframe in R

1 Answers1