rdplyrswitch-statementcurrency-exchange-rates

Mutate a dataframe based on column name/currency


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.


Solution

  • 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))]
    })