0

I have two dataframes in R:

Dates<-as.Date(c("01/01/14","01/02/14","01/03/14","01/04/14","01/05/14","01/06/14","01/07/14","01/08/14","01/09/14","01/10/14","01/11/14","01/12/14"), "%d/%m/%y")
SGD<-c(0.710123, 0.706803,0.694468, 0.703793, 0.709672, 0.715876,0.721752,0.728214,0.740987,0.747695,0.744376,0.741504)
EUR<-c(1.230716,1.221853,1.217159,1.219232,1.220381,1.218245,1.215026,1.211793,1.207671,1.20807,1.202782,1.202307)
ExchangeRate <- data.frame(Dates,SGD,EUR)

and

Order_Date<-as.Date(c("01/01/14","11/01/14","02/02/14","10/03/14","01/05/14","01/06/14","22/07/14","01/08/14","18/09/14","01/10/14","23/11/14","01/12/14"), "%d/%m/%y")
Currency <-c("SGD","SGD","EUR","EUR","EUR","EUR","SGD","SGD","SGD","SGD","EUR","EUR")
Revenue<-c(10,20,30,40,50,60,70,80,90,100,110,120)
Customer<-data.frame(Order_Date,Currency,Revenue)

What I want to achieve is to create another column next to revenues with the revenue in EUR corresponding to the correct Date looking only at month and year (since the exchange rate is on a monthly basis).

Ex: in the first row of the table Customer, it should look in the table ExchangeRate what is the exchange rate corresponding to January 2014 for SGD and return a new column with the total revenue in EUR -> 10*0.71 = 7.1 and do the same for each row.

divibisan
  • 11,659
  • 11
  • 40
  • 58
Nico3007
  • 61
  • 1
  • 5

1 Answers1

1

We can do this in a few steps by joining Customers with the ExchangeRate dataframe:

  1. Use lubridate::floor_date to get the first day of the month in Customers, so we can match with the dates in the ExchangeRate table. See this question for a discussion of how to do this: First day of the month from a POSIXct date time using lubridate
  2. Then use dplyr::left_join to add the appropriate exchange rate into the Customers table. Here's a broader discussion of joining data frames: How to join (merge) data frames (inner, outer, left, right)
  3. Then, we can multiply Revenue by the SGD exchange rate when Currency == 'SGD'. Here's a question about making a new variable based on a conditional: How to add column into a dataframe based on condition?
  4. Finally, we'd probably want to use dplyr::select to drop the unnecessary variables (not shown)

See below:

library(tidyverse)
library(lubridate)

Customer %>%
    mutate(md = floor_date(Order_Date, 'month')) %>%
    left_join(ExchangeRate, by = c('md' = 'Dates')) %>%
    mutate(Revenue = if_else(Currency == 'SGD', SGD * Revenue, Revenue))

  
   Order_Date Currency   Revenue         md      SGD      EUR
1  2014-01-01      SGD   7.10123 2014-01-01 0.710123 1.230716
2  2014-01-11      SGD  14.20246 2014-01-01 0.710123 1.230716
3  2014-02-02      EUR  30.00000 2014-02-01 0.706803 1.221853
4  2014-03-10      EUR  40.00000 2014-03-01 0.694468 1.217159
5  2014-05-01      EUR  50.00000 2014-05-01 0.709672 1.220381
6  2014-06-01      EUR  60.00000 2014-06-01 0.715876 1.218245
7  2014-07-22      SGD  50.52264 2014-07-01 0.721752 1.215026
8  2014-08-01      SGD  58.25712 2014-08-01 0.728214 1.211793
9  2014-09-18      SGD  66.68883 2014-09-01 0.740987 1.207671
10 2014-10-01      SGD  74.76950 2014-10-01 0.747695 1.208070
11 2014-11-23      EUR 110.00000 2014-11-01 0.744376 1.202782
12 2014-12-01      EUR 120.00000 2014-12-01 0.741504 1.202307
divibisan
  • 11,659
  • 11
  • 40
  • 58