I have the following code that converts relevant columns in my dataframe to USD from whatever currency it is:
df %>%
mutate_at(vars(contains('cost_AUD')), list(~ . * ER_AUD_USD )) %>%
mutate_at(vars(contains('cost_GBP')), list(~ . * ER_GBP_USD )) %>%
mutate_at(vars(contains('cost_EUR')), list(~ . * ER_EUR_USD ))
My dataframe looks like this (but with more columns):
date cost_AUD_d cost_CAD_e cost_AUD_f ER_AUD_USD ER_CAD_USD
1 2016-01-01 80.18 5.95 4.83 0.70 0.69
2 2016-02-01 85.72 5.12 3.98 0.71 0.67
3 2016-03-01 67.33 5.12 5.02 0.75 0.72
4 2016-04-01 77.42 5.11 4.55 0.77 0.73
5 2016-05-01 75.40 5.54 4.92 0.73 0.70
Is there a better way to do this? Since the columns are named appropriately, it only needs to match the currency each price is in with the middle part of the Exchange rate columns (i.e. cost_*** and ER_***_USD). Is there a way to incorporate a switching statement with mutate.
Here is one possible way :
#Please include all currencies that you have
currency <- c('AUD', 'GBP', 'EUR')
#Loop over each of them
do.call(cbind, lapply(currency, function(x) {
#Find all the columns with that currency
group_cols <- grep(paste0('cost_', x), names(df))
#Get the exhange rate column
col_to_multiply <- grep(paste0('ER_', x), names(df))
#Repeat the exchange rate column same as total columns and multiply
df[group_cols] * df[rep(col_to_multiply, length(group_cols))]
}))
Or similar with purrr::map_dfc
purrr::map_dfc(currency, ~{
group_cols <- grep(paste0('cost_', .x), names(df))
col_to_multiply <- grep(paste0('ER_', .x), names(df))
df[group_cols] * df[rep(col_to_multiply, length(group_cols))]
})