How to execute operation on a dataframe according to another dataframe?

Question

I've got a dataframe named df on which I want to convert amounts in certain columns in euros (given by the variable $currency.Code). For this, I've got another dataframe named taux_change_vers_EUR with two variables: (1) the name of the currency, (2) the exchange rate to convert in euros.

I would like to apply the coefficients of the second column of my dataframe taux_change_vers_EUR to several columns of my dataframe df. The problem is that my df is quite large and so the operation has to be optimized (nor for loop). Do you have an idea?

Here is a part of code. This code does not work but does show you what I'm expecting:

for (devise in unique(df$currency.Code)){
    df[df$currency.Code==devise,c(4:37,44:48)] <- df[df$currency.Code==devise,c(4:37,44:48)]*rep(as.numeric(taux_change_vers_EU    R[taux_change_vers_EUR[,1]==devise,2]),39)
}

Here is how my second dataframe looks like:

taux_change_vers_EUR
     V1        V2
1   USD   1.14720
2   CAD   1.48836
3   GBP   0.87869
4   EUR   1.00000
5  <NA>   1.00000
6   DKK   6.50221
7   SEK   9.10235
8   PLN   3.76455
9   CZK  22.49280
10  NOK   8.28273
11  TRY   6.12973
12  TWD  30.98320
13  CNY   6.92256
14  HKD   7.83296
15  JPY 113.16000

Provide example input dataframes, and expected output. I am guessing `merge` should be a solution. Merge 2 dataframes on `V1`(Currency), then multiply `valueCol` with `V2`. — zx8754, Oct 23 '18 at 09:48
Hey there, could you please post a code example that properly illustrates your situation and that we can run? I recommend reading [this excellent guide](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and then editing your question! — Oliver Baumann, Oct 23 '18 at 09:48
Thx @zx8754 it worked perfectly!. Sorry Oliver Baumann I'll read it! — T. Ciffréo, Oct 23 '18 at 09:59
@T.Ciffréo, if you found an answer to be useful and solve your problem, please consider marking it as accepted! This will award both you and the person who wrote the answer [reputation](https://stackoverflow.com/help/whats-reputation) — Oliver Baumann, Oct 23 '18 at 14:33

score 1 · Answer 1 · answered Oct 23 '18 at 09:51

You should be able to merge the two data.frames by the actual currency then convert. Below is a reproducible examples using dplyr.

library(dplyr)

# create dummy data
currencies <- data.frame(
    currency = c("USD", "GBP", "JPY", "CAD"),
    rate = c(1.2, .9, .8, 1.5)
)

moneyz <- data.frame(
    value = runif(100, 5, 100),
    currency = sample(currencies$currency, 100, replace = TRUE)
)

# merge and convert
moneyz %>% 
    left_join(currencies, by = "currency") %>% # merge
    mutate(
        converted = value * rate # convert
    )

We simply merge the two tables together to obtain one table with the value, the currency it is in and the appropriate rate to convert to EUR.

score 1 · Accepted Answer · answered Oct 23 '18 at 10:25

1

We can use match:

# example data borrowed from @JohnCoene
df1 <- data.frame(
  currency = c("USD", "GBP", "JPY", "CAD"),
  rate = c(1.2, 0.9, 0.8, 1.5))

set.seed(1); df2 <- data.frame(
  value = 1,
  currency = sample(df1$currency, 5, replace = TRUE))

df2$velueNew <- df2$value * df1$rate[ match(df2$currency, df1$currency) ]
df2
#   value currency velueNew
# 1     1      GBP      0.9
# 2     1      GBP      0.9
# 3     1      JPY      0.8
# 4     1      CAD      1.5
# 5     1      USD      1.2

answered Oct 23 '18 at 10:25

zx8754

52,746
12
114
209

well done, didnt know that -- how fast is it compared to merge? – gaut Oct 23 '18 at 11:26
1

@gpier depends on input datasets, but I would guess about 10 times faster than merge. – zx8754 Oct 23 '18 at 11:44

score 0 · Answer 3 · answered Oct 23 '18 at 09:52

I created an example which you can reuse by running this:

df <- structure(list(c("EUR", "EUR", "USD", "CAD"), c(1654, 68797, 
                                                6546, 736), c("CNY", "HKD", "HKD", "HKD"), c(6876, 63, 687, 354
                                                )), .Names = c("currency1", "amount1", "currency2", "amount2"
                                                ), class = "data.frame", row.names = c(NA, -4L))

So a fast way to do this, provided you have unique currencies in taux_change_vers_EUR is using a merge for each one of your columns of interest in df, which you can even loop through.

cur1.rate  <- merge(df, taux_change_vers_EUR, by.x="currency1", by.y="V1", all.y=FALSE)
cur2.rate  <- merge(cur1.rate, taux_change_vers_EUR, by.x="currency2", by.y="V1", all.y=FALSE)
result <- cur2.rate
result$amount1.eur <- result$amount1*result$V2.x
result$amount2.eur <- result$amount2*result$V2.y
result
  currency2 currency1 amount1 amount2    V2.x    V2.y amount1.eur amount2.eur
1       CNY       EUR    1654    6876 1.00000 6.92256    1654.000  47599.5226
2       HKD       CAD     736     354 1.48836 7.83296    1095.433   2772.8678
3       HKD       EUR   68797      63 1.00000 7.83296   68797.000    493.4765
4       HKD       USD    6546     687 1.14720 7.83296    7509.571   5381.2435

How to execute operation on a dataframe according to another dataframe?

3 Answers3