I have a Base dataframe consisting of the following columns:
Base <- data.frame(Currency = c("EUR.", "EUR.", "USD.", "USD.", "GBP."), Quantity = c(393858,597184,673522,110204,38051625), Price_local = c(168,119.2,168.29,1221.14,1.45), FX_rate = c(1.0898, 1.0898, 1, 1, 1.2287), Exposure.USD = c(72110043,77576686,113347017,134574513,67723215))
I have another dataframe by the name FX_Stress consisting of following columns:
FX_Stress <- data.frame(V1 = c("FXDown5", FXDown10", "FXUp5", "FXUp10"), V2 = c(0.95,0.90,1.05,1.10))
What I want is to add new columns to Base data frame having the column names of the first column of FX_Stress data frame i.e. FXDown5, FXDown10, FXUp5 and FXUp10. Also I want these new columns to be filled by a specific formula. For ex- For new column Base$FXDown5 the formula would be:
Base$FXDown5 <- ifelse(Base$Currency == "USD.", 0, ((1/((1/Base$FX_rate)*0.95))*(Base$Price_local)*Base$Quantity)-Base$Exposure.USD)
The 0.95 used in the above formula is obtained from the column V2 in FX_Stress corresponding to FXDown5 in column V1. This is working like this and the correct values under new column Base$FXDown5 are coming as 3795265.77, 4082983.35, 0, 0, 3638201.71. But I have to fill lot of columns and I cannot write a new formula for each new column. Therefore I tried to use lapply function to fill all the new columns at once. These are mentioned below:
Base[FX_Stress[,1]] <- 0
Base[FX_Stress[,1]] <- lapply(Base[FX_Stress[,1]], function(x) {ifelse(Base$Currency == "USD.", 0, ((1/((1/Base$FX_rate)* FX_Stress$V2[match(names(x), FX_Stress$V1)]))*(Base$Price_local)*Base$Quantity)-Base$Exposure.USD)})
But in the output I am getting NA values for the currencies which are not USD. (for USD. currency its coming as 0 which is correct). If I do individually for each column its coming correctly but when I use lapply to match column names NA values are coming for currencies which are not USD. Could anyone help regarding the lapply formula which I have used to get the correct values for the new columns at once. This can be done through using dplyr and functions like gather and spread but I would request help regarding how it can be done using lapply/sapply.
Maybe you can use sapply() in this way
Base <- cbind(Base,`colnames<-`(sapply(FX_Stress$V2, function(x) with(Base,ifelse(Currency == "USD.", 0, FX_rate/x * Price_local * Quantity - Exposure.USD))),FX_Stress$V1))
such that
> Base
Currency Quantity Price_local FX_rate Exposure.USD FXDown5 FXDown10 FXUp5 FXUp10
1 EUR. 393858 168.00 1.0898 72110043 3795266 8012227 -3433811 -6555458
2 EUR. 597184 119.20 1.0898 77576686 4082983 8619632 -3694128 -7052426
3 USD. 673522 168.29 1.0000 113347017 0 0 0 0
4 USD. 110204 1221.14 1.0000 134574513 0 0 0 0
5 GBP. 38051625 1.45 1.2287 67723215 3638202 7602725 -3158124 -6092901
DATA
Base <- data.frame(Currency = c("EUR.", "EUR.", "USD.", "USD.", "GBP."), Quantity = c(393858,597184,673522,110204,38051625), Price_local = c(168,119.2,168.29,1221.14,1.45), FX_rate = c(1.0898, 1.0898, 1, 1, 1.2287), Exposure.USD = c(72110043,77576686,113347017,134574513,67723215))
FX_Stress <- data.frame(V1 = c("FXDown5", "FXDown10", "FXUp5", "FXUp10"), V2 = c(0.95,0.90,1.05,1.10))
with OP's original formula
Base <- data.frame(Currency = c("EUR.", "EUR.", "USD.", "USD.", "GBP."), Quantity = c(393858,597184,673522,110204,38051625), Price_local = c(168,119.2,168.29,1221.14,1.45), FX_rate = c(1.0898, 1.0898, 1, 1, 1.2287), Exposure.USD = c(72110043,77576686,113347017,134574513,67723215))
FX_Stress <- data.frame(V1 = c("FXDown5", "FXDown10", "FXUp5", "FXUp10"), V2 = c(0.95,0.90,1.05,1.10))
Base <- cbind(Base,`colnames<-`(sapply(FX_Stress$V2, function(x) ifelse(Base$Currency == "USD.", 0, ((1/((1/Base$FX_rate)*0.95))*(Base$Price_local)*Base$Quantity)-Base$Exposure.USD)),FX_Stress$V1))