Applying a user-defined function to perform row-wise calculations in a data table in R

Question

I’m trying to use a Currency Converter function (custom function) to convert amounts of each Order in a data table from one specific currency to another. I have two tables:

1. Orders (dfOrders)
2. Exchange Rates (dfXRate)

Ultimately, I need to convert all amounts for each order (Price, VAT, and Fees) from the “FromCUR” currency to the “ToCUR” currency associated with each order using the Exchange Rate table by matching the FromCUR field to the “From” field in the Exchange Rate table, and the same for the “ToCUR” with the “To” field to get the “Rate” and the “Action” type. Action will be used in the function to either Multiply or Divide the Amount from the Order by the Rate.

My complete code to re-produce the Function and tables is shown below.

Yet, I could not even manage to convert the Price as a start although I need to convert all amounts for each order. When I run the code, R throws the following error: “Error in switch(rate$Action, Multiply = Amount * rate$Rate, Divide = Amount/rate$Rate, : EXPR must be a length 1 vector”

I’m aware that .SDCols might help here, but not sure how to use it either.

Would be grateful if someone can fix the code or suggest an alternative more efficient one. Thanks.

CurConverter <- function(FromCUR, ToCUR, Amount, exchange_rate_table) {
  # Look up exchange rate from the provided table
  rate <- exchange_rate_table[From == FromCUR & To == ToCUR, .(Rate, Action)]
  
  # if (nrow(rate) == 0) {
  #   warning("Exchange rate not found for the specified currencies. Returning original amount.")
  #   return(Amount)
  # }
  
  result <- switch(rate$Action,
                   Multiply = Amount * rate$Rate,
                   Divide = Amount / rate$Rate,
                   Amount) |>
    as.numeric()

    return(result)
}

# Example data tables
dfOrders <- data.table(
  OrderID=c("A","B","C"),
  FromCUR = c("USD", "AED", "GBP"),
  ToCUR = c("EUR", "USD", "USD"),
  Price = c(100, 200, 150),
  VAT=c(10,20,15),
  Fees=c(1,2,3)
)

dfXRate <- data.table(
  From = c("USD", "AED", "GBP"),
  To = c("EUR", "USD", "USD"),
  Rate = c(1.06, 3.6725, 1.25),
  Action = c("Multiply", "Divide", "Divide")
)

# Merge data tables based on currency columns
merged_df <- merge(dfOrders, dfXRate, by.x = c("FromCUR", "ToCUR"), by.y = c("From", "To"), all.x = TRUE)

# Apply the CurConverter function to each row
merged_df[, ConvertedPrice := CurConverter(FromCUR, ToCUR, Price, dfXRate)]

Answer 1

Here is how I would do it:

library(data.table)

convertCurrenciesWithSymbol <- function(orders, exchangeRates) {
 
  # Merge orders with exchange rates
  mergedData <- merge(orders, exchangeRates, by.x = c("FromCUR", "ToCUR"), by.y = c("From", "To"), all.x = TRUE)
  
  # currency conversion 
  mergedData[, Converted := fifelse(Action == "Multiply" & ToCUR == "EUR", 
                                    paste0(as.character(round(Price * Rate, 2)), " €"),
                                    fifelse(Action == "Divide" & ToCUR == "USD", 
                                            paste0(as.character(round(Price / Rate, 2)), " $"),
                                            paste0("No Change, Original: ", Price)
                                    )
  )]
  return(mergedData)
}

convertedOrders <- convertCurrencies(dfOrders, dfXRate)

convertedOrders

With switch

library(data.table)

convertCurrenciesWithSwitch <- function(orders, exchangeRates) {
  # Merge orders and rates
  mergedData <- merge(orders, exchangeRates, by.x = c("FromCUR", "ToCUR"), by.y = c("From", "To"), all.x = TRUE)
  
  # Define a helper function for conversion
  convertWithSwitch <- function(action, toCurrency, price, rate) {
    caseKey <- paste(action, toCurrency)
    switch(caseKey,
           "Multiply EUR" = paste0(as.character(round(price * rate, 2)), " €"),
           "Divide USD" = paste0(as.character(round(price / rate, 2)), " $"),
           paste0("No Change, Original: ", price))
  }
  
  # Currency conversion
  mergedData[, Converted := mapply(convertWithSwitch, Action, ToCUR, Price, Rate)]
  return(mergedData)
}


convertedOrdersWithSwitch <- convertCurrenciesWithSwitch(dfOrders, dfXRate)
convertedOrdersWithSwitch

   FromCUR ToCUR OrderID Price VAT Fees   Rate   Action Converted
1:     AED   USD       B   200  20    2 3.6725   Divide   54.46 $
2:     GBP   USD       C   150  15    3 1.2500   Divide     120 $
3:     USD   EUR       A   100  10    1 1.0600 Multiply     106 €

Answer 2

With the help of @TarJae answer, I modified the code to produce the expected results.

# Define a function for conversion
CurConverter <- function(action, amount, rate) {
  
  switch(action,
     "Multiply" = round(amount * rate, 2),
     "Divide" = round(amount / rate, 2),
     NA)
}
  

# Example data tables
dfOrders <- data.table(
  OrderID = c("A", "B", "C"),
  FromCUR = c("USD", "AED", "GBP"),
  ToCUR = c("SAR", "USD", "USD"),
  Price = c(100, 200, 150),
  VAT = c(10, 20, 15),
  Fees = c(1, 2, 3)
)

dfXRate <- data.table(
  From = c("USD", "AED", "GBP"),
  To = c("EUR", "USD", "USD"),
  Rate = c(1.06, 3.6725, 0.8),
  Action = c("Multiply", "Divide", "Divide")
)

# Merge orders and rates
mergedData <- merge(dfOrders, dfXRate, by.x = c("FromCUR", "ToCUR"), by.y = c("From", "To"), all.x = TRUE)

# Currency conversion
mergedData[, ConvertedPrice := mapply(CurConverter, Action, Price, Rate)]
mergedData[, ConvertedVAT := mapply(CurConverter, Action, VAT, Rate)]
mergedData[, ConvertedFees := mapply(CurConverter, Action, Fees, Rate)]

print(mergedData)

Answer 3

You could apply some highschool math, x/y = x*y^-1, and lapply over the cols you wish.

> library(data.table)
> merged_df[, .f := c(-1, 1)[match(Action, c("Divide", "Multiply"))]]
> cols <- c('Price', 'VAT', 'Fees')
> merged_df[, (paste0('Converted', cols)) := lapply(.SD, \(x) x*Rate^.f), .SDcols=cols]
> merged_df
   FromCUR ToCUR OrderID Price VAT Fees   Rate   Action .f ConvertedPrice ConvertedVAT ConvertedFees
1:     AED   USD       B   200  20    2 3.6725   Divide -1       54.45882     5.445882     0.5445882
2:     GBP   USD       C   150  15    3 1.2500   Divide -1      120.00000    12.000000     2.4000000
3:     USD   EUR       A   100  10    1 1.0600 Multiply  1      106.00000    10.600000     1.0600000

---

Data:

> dput(merged_df)
structure(list(FromCUR = c("AED", "GBP", "USD"), ToCUR = c("USD", 
"USD", "EUR"), OrderID = c("B", "C", "A"), Price = c(200, 150, 
100), VAT = c(20, 15, 10), Fees = c(2, 3, 1), Rate = c(3.6725, 
1.25, 1.06), Action = c("Divide", "Divide", "Multiply")), class = c("data.table", 
"data.frame"), row.names = c(NA, -3L), .internal.selfref = <pointer: 0x560122094ac0>, sorted = c("FromCUR", 
"ToCUR"))