mercredi 21 mars 2018

How to calculate a column with different currencies to a new column with the same currency

I'm in a bit of a R-dilemma.

I am working with a dataset, which is a project portfolio, where I have a column named: Currency (which lists a total of 60 different currenices worldwide fx. USD, Euro, GBR) and another column named: Total Amount, which is the actual total amount of that currency on a given project.

My dilemma, is that i want to re-calculate all the projetcs into 1 specific currency, Euro.

I have been able to do this, one currency at a time, by first creating a new dataframe, where I subset my data (Projects) with a specific currency fx GBR:

Projects_GBR <- subset(Projects, Currency=="Pound Sterling") 

I then create another dataframe, where I create a new column using mutate, where I then calculate the project from GBR to Euro.

Projects_GBR_Euro <- Projects_GBR %>% mutate(Total_Amount_Euro = Total_Amount*1.4)

(1.4 is the currency to Euro)

This works, but there must be a more straightforward way, where I won't need to create a new dataframe for each currency.

I have tried creating a For Loop also using if-functions, but nothing really seems to work as I recieve a whole bunch of warnings.

My For-Loop/If attempt:

Projects$Total_Amount_Euro <- 0

for (i in 1:nrow(Projects)){
  if(i == (Projects$Currency=="Pound Sterling")){
    Projects$Total_Amount_Euro <- Projects$Total_Amount*1.4 
  }
  i <- i+1
}  

... and so on with the rest of the currencies.

I hope that someone is able to help me through this R-dilemma - Thank you

Aucun commentaire:

Enregistrer un commentaire