Mutate a dataframe based on column name/currency

Question

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.

I would (a) convert to long format, using `melt`, `pivot_longer`, or whatever, (b) `left_join` to a look-up table with conversion rates, (c) do a single multiplication to covert all the values, (d) (optional), convert back to wide format using `dcast`, `pivot_wider`, or whatever. — Gregor Thomas, Jan 06 '20 at 02:42
As far as deriving the exchange rates, it would be helpful if you'd share some sample data applicable to that part of the code. — Gregor Thomas, Jan 06 '20 at 02:44
@RonakShah 6 total, 4 plus GBP and USD, but I may decide to add more, so ideally it could take in a list or vector of the currencies I want to use. — Undroid, Jan 06 '20 at 03:03

Ronak Shah · Accepted Answer · 2020-01-06T04:10:34.743

0

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

edited Jan 06 '20 at 04:10

answered Jan 06 '20 at 03:29

Ronak Shah

377,200
20
156
213

I like the purrr solution, I think it only works with the example df I provided, I've changed it to better highlight what I am trying to do, as my dataframe has the cost of items in different currencies that are not grouped together, so it will need to pick the right exchange rate as my code is doing at the top. – Undroid Jan 06 '20 at 03:56
@Undroid I should have used `map_dfc` instead of `map_df`. I have updated the answer, can you check now ? – Ronak Shah Jan 06 '20 at 04:11
What is `currency` supposed to contain? If it's all the currencies I get this error: `Error in Ops.data.frame(df[group_cols], df[rep(col_to_multiply, : ‘*’ only defined for equally-sized data frames` How does it select which exchange rate to multiply a column by? `col_to_multiply` will contain strings like `AUD_USD` – Undroid Jan 06 '20 at 05:06
@Undroid It takes value from `currency` say `AUD`, `paste` `'cost'` to get all the columns with that currency which start with 'cost', so `group_cols` would have columns `cost_AUD_d ` and `cost_AUD_f`. `col_to_multiply` is 'ER' followed by currency which is `ER_AUD_USD`. Since I expect `group_cols` to be multiple and `col_to_multiply` to be only 1 I repeat the same values of `col_to_multiply` in multiple columns so that both the dataframe are of equal columns and we can multiply them. So for the given example, `df[c(2, 4)] * df[5]` gives error so I repeat 5th column `df[c(2, 4)] * df[c(5, 5)]` – Ronak Shah Jan 06 '20 at 05:28
Ah I see, my df will inevitably have other exchange rates, that I may need, but If I specify a `to_currency` and use `col_to_multiply <- grep(paste('ER', .x, to_currency, sep = '_'), names(df))` it works. Is there a way to include the date column in the output within the expression? And is there a way to use map_df to create new columns? - for Part 2 – Undroid Jan 07 '20 at 00:11

Mutate a dataframe based on column name/currency

1 Answers1