Calling different dataframes from within R function

Question

I am trying to create a function which lets me go through each row of a data frame and convert the currency this row is in to euro, using the exchange rate of that year using the following code

Valuta <- function(Code, Year, Exchange) {
  for (i in 1:nrow(DataFilter)) {
    if (DataFilter[i,'curcd'] == deparse(substitute(Code))) {
      if (DataFilter[i,'fyear']== deparse(substitute(Year))) {
          DataFilter[i,'ebitda'] <- DataFilter[i,'ebitda'] / Exchange[,'Value'][Exchange[,'Year'==Year]]
          DataFilter[i,'invt'] <- DataFilter[i,'invt'] / Exchange[,'Value'][Exchange[,'Year'==Year]]
          DataFilter[i,'sale'] <- DataFilter[i,'sale'] / Exchange[,'Value'][Exchange[,'Year'==Year]]
      }
    }
  }
}

Using this code to call the function:

Valuta(GBP, 1999 , EURGBPTMP)

R returns:

Error in Exchange[, "Value"][Exchange[, "Year" == Year]] : 
  invalid subscript type 'list'

GBP is the call sign in the data frame DataFilter

EURGBPTMP is a data frame containing 2 columns: Year and Value, where each Year is unique

Using unlist() returns the same result, even though the result of Exchange[,'Value'][Exchange[,'Year'==Year]] should not be a list

Added Sample Data

Sample for DataFilter:

DataFilter <- data.frame(curcd = c("EUR", "EUR", "GBP", "USD"), fyear
= c("1999", "2000", "2001", "2001"), ebitda = c(63842000, 248290000, 67014000, 34089000), invt = c(107280000, 188001000, 206027000, 185752000), sale = c(414495000, 935212000, 561064000, 518802000))

Sample for EURGBPTMP:

EURGBPTMP <- data.frame(Year = c("1999", "2000", "2001","2002"), Value
    = c(0.62170, 0.62410, 0.60850, 0.65050))

With this sample Valuta(GBP, 1999 , EURGBPTMP) should only edit row 3

Can you add a reproducible sample? https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Mohanasundaram, Apr 24 '20 at 14:19
@Mohanasundaram Of course, sorry. I added them in the question — Emaasland, Apr 24 '20 at 14:38
do you want to convert the GPB rows alone to EURO, ignoring the USD and existing Euro? — Mohanasundaram, Apr 24 '20 at 15:04
The idea is to use the function to parse all currencies not EURO in the data frame (around 40 currencies). Every currency has a data frame with year/value exchange rates. I've tried other loops, but using a function seemed to be the most efficient way/ — Emaasland, Apr 24 '20 at 16:23

Mohanasundaram · Accepted Answer · 2020-04-25T11:47:58.220

Try this function

conv <- function(df, fxdata, currency) {
  df$fyear <- as.character(df$fyear)
  fxdata$Year <- as.character(fxdata$Year)
  fxdata$Value <- as.numeric(fxdata$Value)
  rn= rownames(df[df$curcd == currency,])
  for(i in rn) {
    y = df[i,]$fyear
    fx = fxdata[fxdata$Year == y,]$Value
    df[i,]$ebitda <- df[i,]$ebitda * fx
    df[i,]$invt <- df[i,]$invt * fx
    df[i,]$sale <- df[i,]$sale * fx
  }
  out <<- df
}

You can use it for all the currencies with different file for FX rate.

> conv(df = DataFilter, fxdata = EURGBPTMP, currency = "GBP")
> out
  curcd fyear    ebitda      invt      sale
1   EUR  1999  63842000 107280000 414495000
2   EUR  2000 248290000 188001000 935212000
3   GBP  2001  40778019 125367430 341407444
4   USD  2001  34089000 185752000 518802000

Edit:

An addition to the above code. This will change the currency name from the original currency to EUR, after applying the Forex rate.

conv <- function(df, fxdata, currency) {
  df$fyear <- as.character(df$fyear)
  fxdata$Year <- as.character(fxdata$Year)
  fxdata$Value <- as.numeric(fxdata$Value)
  rn= rownames(df[df$curcd == currency,])
  for(i in rn) {
    y = df[i,]$fyear
    fx = fxdata[fxdata$Year == y,]$Value
    df[i,]$ebitda <- df[i,]$ebitda * fx
    df[i,]$invt <- df[i,]$invt * fx
    df[i,]$sale <- df[i,]$sale * fx
  }
  out <<- df
  out[out$curcd==currency,]$curcd <<- "EUR"
}

> conv(df = DataFilter, fxdata = EURGBPTMP, currency = "GBP")
> out
  curcd fyear    ebitda      invt      sale
1   EUR  1999  63842000 107280000 414495000
2   EUR  2000 248290000 188001000 935212000
3   EUR  2001  40778019 125367430 341407444
4   USD  2001  34089000 185752000 518802000

This works perfectly, doing exactly what I needed, thanks! For my understandig, was my error trying to call a variable in the calculation before I defined it? Or was my code just poor to begin with? — Emaasland, Apr 25 '20 at 09:30
The variables you are calling inside the curly brackets should be from the data frames that you have mentioned in the round brackets. So, the names of the items in the round brackets should be used inside the curly brackets and filters should be applied inside the function. — Mohanasundaram, Apr 25 '20 at 09:49
@Emoeloe I have added the code to my answer which will change the currency name as well, while converting the rate. — Mohanasundaram, Apr 25 '20 at 11:49

Calling different dataframes from within R function

1 Answers1