i'm trying to use an if function (unless there's a better way!) to convert EUR and USD amounts into GBP. I have two tables, one is of currency conversation rates, and one is of people's holiday spending.
Holiday Spending
Date<- c('01/01/2021', '02/01/2021','01/01/2021', '02/01/2021','01/01/2021', '02/01/2021','01/01/2021', '02/01/2021' )
Person <- c('Person A', 'Person B', 'Person C', 'Person A', 'Person B', 'Person C', 'Person A', 'Person B')
Amount <- c('100', '150', '200', '250', '300', '350', '400', '450')
Currency <- c('GBP', 'EUR', 'USD', 'GBP', 'GBP', 'GBP', 'EUR', 'USD')
DF <- data.frame(Date, Person, Currency, Amount)
Conversation Rates
Date<- c('01/01/2021', '01/01/2021','02/01/2021', '02/01/2021','03/01/2021', '03/01/2021','04/01/2021', '04/01/2021' )
Country <- c('EUR', 'USD', 'EUR', 'USD', 'EUR', 'USD', 'EUR', 'USD')
Rate <- c('2', '4', '6', '8', '10', '12', '14', '16')
XR <- data.frame(Date, Country, Rate)
What I'm looking to do is search the currency list of the holiday spending, and if the Currency column says anything not GBP, then to find the currency conversation rate of that day and re calculate the Amount column to that.
So for example, person B spend 150 Euros on the 02/10/2021, the conversation rate that day was 6 so then that 150 should be recalculated to 900, I don't need the currency column to change as it gets filtered out anyway when I rejig the tables later in the process.
I can do it in excel with vlookups and an if function, but i'm struggling to translate this to R. In Excel I have the EU and USD exchange rates on different tabs but in R it's one big table. I could split it into small tables but still not sure how to get the if and sum to work in an R format.
Excel function
=IF(D2="EUR",SUM(E2 * VLOOKUP(A2,EU!A:D,3,FALSE)),IF(D2="USD",SUM(E2* VLOOKUP(A2,USD!A:D,3,FALSE)),Spending!E2))
grateful for your advice.
You can perform a join and multiply the Rate to Amount.
library(dplyr)
DF %>%
left_join(XR, by = c('Date', 'Currency' = 'Country')) %>%
mutate(New_Amount = as.numeric(Amount) * as.numeric(Rate),
New_Amount = coalesce(New_Amount, as.numeric(Amount)))
# Date Person Currency Amount Rate New_Amount
#1 01/01/2021 Person A GBP 100 <NA> 100
#2 02/01/2021 Person B EUR 150 6 900
#3 01/01/2021 Person C USD 200 4 800
#4 02/01/2021 Person A GBP 250 <NA> 250
#5 01/01/2021 Person B GBP 300 <NA> 300
#6 02/01/2021 Person C GBP 350 <NA> 350
#7 01/01/2021 Person A EUR 400 2 800
#8 02/01/2021 Person B USD 450 8 3600