CODEWITHSUNDEEP
rdataframe
T. Ciffréo
T. Ciffréo

Reputation: 126

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

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

Upvotes: 1

Views: 61

Answers (3)

zx8754
zx8754

Reputation: 56189

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

Upvotes: 1

gaut
gaut

Reputation: 5958

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

Upvotes: 0

JohnCoene
JohnCoene

Reputation: 2261

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.

Upvotes: 1

Related Questions