Replacing only NA values in xts object column wise using specific formula

Question

I want to replace NA values in my xts object with formula Beta * Exposure * Index return. My xts object is suppose Position_SimPnl created below:

library(xts)    
df1 <- data.frame(Google = c(NA, NA, NA, NA, 500, 600, 700, 800),
                    Apple = c(10, 20,30,40,50,60,70,80),
                    Audi = c(1,2,3,4,5,6,7,8),
                    BMW = c(NA, NA, NA, NA, NA, 6000,7000,8000),
                    AENA = c(50,51,52,53,54,55,56,57))

Position_SimPnl <- xts(df1, order.by = Sys.Date() - 1:8)

For Beta there is a specific dataframe:

Beta_table <- data.frame (AENA = c(0.3,0.5,0.6), Apple = c(0.2,0.5,0.8), Google = c(0.1,0.3,0.5), Audi = c(0.4,0.6,0.7), AXP = c(0.5,0.7, 0.9), BMW = c(0.3,0.4, 0.5))
rownames(Beta_table) <- c(".SPX", ".FTSE", ".STOXX")

For exposure there is another dataframe:

Base <- data.frame (RIC = c("AENA","BMW","Apple","Audi","Google"), Exposure = c(100,200,300,400,500))

For Index return there is a xts object (Index_FX_Returns):

    df2 <- data.frame(.SPX = c(0.01, 0.02, 0.03, 0.04, 0.05, 0.06, 0.07, 0.08),
                    .FTSE = c(0.5, 0.4,0.3,0.2,0.3,0.4,0.3,0.4),
                    .STOXX = c(0.15,0.25,0.35,0.3,0.45,0.55,0.65,0.5))

Index_FX_Returns <- xts(df2,order.by = Sys.Date() - 1:8)

Also there is a dataframe which links RIC with Index:

RIC_Curr_Ind <- data.frame(RIC = c("AENA", "Apple", "Google", "Audi", "BMW"), Currency = c("EUR.","USD.","USD.","EUR.","EUR."), Index = c(".STOXX",".SPX",".SPX",".FTSE",".FTSE"))

What I want is for a particular position of NA value in Position_SimPnl it should look into the column name and get the corresponding index name from RIC_Curr_Ind dataframe and then look for the beta value from Beta_table by matching column name (column name of NA) and row name (index name derived from column name of NA). Then again by matching the column name from Position_SimPnl with the RIC column from 'Base' dataframe it would extract the corresponding exposure value. Then by matching column name from Position_SimPnl with RIC column from RIC_Curr_Ind dataframe, it would get the corresponding index name and from that index name it would look into the column name for xts object Index_FX_Returns and get the corresponding return value for the same date as of the NA value.

After getting the Beta, Exposure and Index return values I want the NA value to be replaced by formula: Beta * Exposure * Index return. Also I want only the NA values in Position_SimPnl to be replaced. the other values should remain as it was previously.I used the following formula for replacing the NA values:

do.call(merge, lapply(Position_SimPnl, function(y) {if(is.na(y)){y = (Beta_table[match(RIC_Curr_Ind$Index[match(colnames(y),RIC_Curr_Ind$RIC)],rownames(Beta_table)), match(colnames(y),colnames(Beta_table))]) * (Base$Exposure[match(colnames(y), Base$RIC)]) * (Index_FX_Returns[,RIC_Curr_Ind$Index[match(colnames(y),RIC_Curr_Ind$RIC)]])} else{y}}))

However in the output, if a particular column contains NA it is replacing all the values in the column (including which were not NA previously). Also I am getting multiple warning messages like "In if (is.na(y)) { ... : the condition has length > 1 and only the first element will be used". I think because of this all values of column are getting transformed including non-NA ones. Can anyone suggest how to effectively replace these NA values by the formula mentioned above, keeping the other values same. Any help would be appreciated

Thanks...I have taken your suggestions and edited my question accordingly. — ss003, Dec 05 '19 at 16:31
You need to make a decision of which daily `Beta * Exposure * Index` as there will be three values to replace position NA (since each RIC has 3 indices). Average/median the three? Min/max of three? — Parfait, Dec 05 '19 at 16:39
If you see the RIC_Curr_Ind dataframe, each RIC is mapped to only one index out of ".STOXX", ".SPX" or ".FTSE". So there will be only one unique value for Beta*Exposure*Index. — ss003, Dec 05 '19 at 16:59
@Parfait :Thank you very much for your response. This is working. I also wanted to ask if the the same output can be derived from tweaking my code which I mentioned in my post that uses lapply function. My code was working but it was converting all the values of a column (column which contained NAs) including the non-NA ones to Beta * Exposure * Index return — ss003, Dec 06 '19 at 13:09

score 0 · Answer 1 · answered Dec 05 '19 at 17:36

Because you need to combine all data sets to achieve your formula Beta * Exposure * Index, consider building a master data frame comprised of all needed components. However, you face two challenges:

different data types (xts objects and data frame)
different data formats (wide and long formats)

For proper merging and calculating, consider converting all data components into data frames and reshaping to long format (i.e., all but Base and RIC_Curr_Ind). Then, merge and calculate with ifelse to fill NA values. Of course, at the end, you will have to reshape back to wide and convert back to XTS.

Reshape

# USER-DEFINED METHOD GIVEN THE MULTIPLE CALLS 
proc_transpose <- function(df, col_pick, val_col, time_col) {
  reshape(df, 
          varying = names(df)[col_pick],
          times = names(df)[col_pick], ids = NULL,
          v.names = val_col, timevar = time_col,
          new.row.names = 1:1E4, direction = "long")
}

# POSITIONS
Position_SimPnl_wide_df <- data.frame(date = index(Position_SimPnl), 
                                      coredata(Position_SimPnl))

Position_SimPnl_long_df <- proc_transpose(Position_SimPnl_wide_df, col_pick = -1,
                                          val_col = "Position", time_col = "RIC")

# BETA
Beta_table_long_df <- proc_transpose(transform(Beta_table, Index = row.names(Beta_table)),
                                     col_pick = 1:ncol(Beta_table),
                                     val_col = "Beta", time_col = "RIC")

# INDEX
Index_FX_Returns_wide_df <- data.frame(date = index(Index_FX_Returns),
                                  coredata(Index_FX_Returns))

Index_FX_Returns_long_df <- proc_transpose(Index_FX_Returns_wide_df, col = -1,
                                           val_col = "Index_value", time_col = "Index")

Merge

# CHAIN MERGE
master_df <- Reduce(function(...) merge(..., by="RIC"), 
                    list(Position_SimPnl_long_df, 
                         Beta_table_long_df, 
                         Base)
                    )

# ADDITIONAL MERGES (NOT INCLUDED IN ABOVE CHAIN DUE TO DIFFERENT by)
master_df <- merge(master_df,
                   Index_FX_Returns_long_df, by=c("Index", "date"))

master_df <- merge(master_df,
                   RIC_Curr_Ind, by=c("Index", "RIC"))

Calculation

# FORMULA: Beta * Exposure * Index 
master_df$Position <- with(master_df, ifelse(is.na(Position),  
                                             Beta * Exposure * Index_value, 
                                             Position))

Final Preparation

# RE-ORDER ROWS AND SUBSET COLS
master_df <- data.frame(with(master_df, master_df[order(RIC, date), 
                                                  c("date", "RIC", "Position")]),
                        row.names = NULL)

# RESHAPE WIDE (REVERSE OF ABOVE)
Position_SimPnl_new <- setNames(reshape(master_df, idvar = "date",
                                        v.names = "Position", timevar = "RIC",
                                        direction = "wide"),
                                c("date", unique(master_df$RIC)))

# CONVERT TO XTS
Position_SimPnl_new <- xts(transform(Position_SimPnl_new, date = NULL),
                           order.by = Position_SimPnl_new$date)

Position_SimPnl_new 

#            AENA Apple Audi  BMW Google
# 2019-11-27   58    80    8 8000  800.0
# 2019-11-28   57    70    7 7000  700.0
# 2019-11-29   56    60    6 6000  600.0
# 2019-11-30   55    50    5   24  500.0
# 2019-12-01   54    40    4   16    2.0
# 2019-12-02   53    30    3   24    1.5
# 2019-12-03   52    20    2   32    1.0
# 2019-12-04   51    10    1   40    0.5

:Thank you very much for your response. This is working. I also wanted to ask if the the same output can be derived from tweaking my code which I mentioned in my post that uses lapply function. My code was working but it was converting all the values of a column (column which contained NAs) including the non-NA ones to Beta*Exposure*Index return. — ss003, Dec 06 '19 at 13:07
Forgive me but I don't recommend your attempted approach for readability, maintainability, and efficiency (as no loop is used here such as `lapply` but vectorized `ifelse`). — Parfait, Dec 06 '19 at 13:24

Replacing only NA values in xts object column wise using specific formula

1 Answers1