CODEWITHSUNDEEP
r
Maylo
Maylo

Reputation: 562

match multiple column with row value from another frame based on date

I have the below tables:

test<- read.table(text = "        var1 var2 var3 var4
                  1 12/12/2017  CAD   10    3
                  2 20/02/2018  CAD   30    8
                  3 14/03/2016  USD   44    9
                  4 10/05/2000  KOR   56   11
                  5 04/04/2016  GBP   45   22")

test2 <- read.table(text = "        date USD CAD KOR GBP
1 12/12/2017   1   2 0.1   2
2 20/02/2018   2   1 0.8   1
3 14/03/2016   3   4 1.0   4
4 10/05/2000   4   5 0.6   5
5 04/04/2016   5   1 1.0   1")

I want to multiply the values of test$var3 and test$var4 with the corresponding values in the test2 table depending on the date. So for example if test$var2 is CAD and test$var1 is 12/12/2017, it should look into table test2 to find the spot rate of CAD at that date and multiply it with test$var3 and test$var4.

Is there a way to do that?

Upvotes: 2

Views: 45

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388817

We can use mapply on var1 and var2 of test to filter row and column respectively of test2 to get the spot rate of that particular currency on that particular day. Once we get the spot_rate we can multiply it to var3 and var4.

spot_rate  = mapply(function(x, y) test2[x == test2$date, y == names(test2)],
                                      test$var1, test$var2)
spot_rate
#[1] 2.0 1.0 3.0 0.6 1.0

test$var3 * spot_rate
#[1]  20.0  30.0 132.0  33.6  45.0

test$var4 * spot_rate
#[1]  6.0  8.0 27.0  6.6 22.0

We can also get spot_rate using match which is more or less the same operation.

mapply(function(i, j) test2[i, j], match(test$var1, test2$date), 
                                   match(test$var2, names(test2)))

#[1] 2.0 1.0 3.0 0.6 1.0

Upvotes: 2

phiver
phiver

Reputation: 23608

If you want to have the currency value available in your data.frame without looking you could join the tables together after transforming test2 in long format.

If you just want a lookup Ronak Shah's answer works very good.

library(dplyr)
library(tidyr)

test %>% 
  inner_join((test2 %>% gather(key = "currency", value = "curency_value", -date)), by = c("var1" = "date", "var2" = "currency") ) %>% 
  mutate(var3 = var3 * curency_value,
         var4 = var4 * curency_value)

        var1 var2  var3 var4 curency_value
1 12/12/2017  CAD  20.0  6.0           2.0
2 20/02/2018  CAD  30.0  8.0           1.0
3 14/03/2016  USD 132.0 27.0           3.0
4 10/05/2000  KOR  33.6  6.6           0.6
5 04/04/2016  GBP  45.0 22.0           1.0

Upvotes: 0

Related Questions