R: Multiply values in one column to those in another if the dates coincide

Question

I've been trying this for some days but I can't seem to find a way to do it. I have a two dataframes. One named "df1" with two variables: date, price

         date        price
        2019-12-21    140
        2019-10-25     91
        2019-10-24     91    
        2019-12-13     70

Another one, named "df2" with two variables: date, exchange rate

         date        exchange rate
        2019-12-21    1.1097
        2019-12-22    1.117
        2019-12-23    1.1075  
           ...         ...
        2019-12-13    1.108
           ...         ...
        2019-10-25    1.1074
        2019-10-24    1.1073

I want to multiply the exchange rates from df2 by the prices in df1 WHEN the dates coincide. And add that into a column in df1. So the result would be like this:

         date        price    priceEUR
1       2019-12-21    140      155.358
2       2019-10-25     91      100.7734
3       2019-10-24     91      100.7643
4       2019-12-13     70      77.56

Thank you very much.

score 1 · Accepted Answer · answered Feb 09 '20 at 12:08

1

One way is to:

library(tidyverse)

df3 <- inner_join(df1, df2)

df3 <- df3 %>%
    mutate(priceEUR = price * rate)

answered Feb 09 '20 at 12:08

novica

655
4
11

score 1 · Answer 2 · answered Feb 09 '20 at 13:06

We can also use base R to do this.

Using match :

df1$priceEUR <- df1$price * df2$exchange_rate[match(df1$date, df2$date)]
df1
#        date price priceEUR
#1 2019-12-21   140 155.3580
#2 2019-10-25    91 100.7734
#3 2019-10-24    91 100.7643
#4 2019-12-13    70  77.5600

Or using merge and transform

transform(merge(df1, df2, by = "date"), priceEUR = price * exchange_rate)

data

df1 <- structure(list(date = structure(c(4L, 2L, 1L, 3L), .Label = c("2019-10-24", 
"2019-10-25", "2019-12-13", "2019-12-21"), class = "factor"), 
price = c(140L, 91L, 91L, 70L)), row.names = c(NA, -4L), class = "data.frame")

df2 <- structure(list(date = structure(c(4L, 5L, 6L, 3L, 2L, 1L), 
.Label = c("2019-10-24", "2019-10-25", "2019-12-13", "2019-12-21", "2019-12-22", 
"2019-12-23"), class = "factor"), exchange_rate = c(1.1097, 1.117, 1.1075, 1.108, 
1.1074, 1.1073)), class = "data.frame", row.names = c(NA, -6L))

R: Multiply values in one column to those in another if the dates coincide

2 Answers2