How to replace values in an xts object based on conditions from other xts object

Question

I have two xts objects.

The first one "stocks_purchase_dates" contains the opening dates and purchase prices of 4 stocks.

stocks_purchase_dates             

           stock1 stock2 stock3 stock4
2018-03-19     NA     NA     NA 165.78
2018-03-21     NA   36.1     NA     NA
2018-03-23     23     NA     NA     NA
2018-03-26     NA     NA  48.81     NA

The second one "stocks_prices_mar15_mar28" contains the prices of the 4 stocks for the period March 15 - March 28, 2018.

stocks_prices_mar15_mar28                         

           stock1 stock2 stock3 stock4
2018-03-15  23.30  44.28  54.75 177.34
2018-03-16  23.06  45.12  55.10 176.72
2018-03-19  23.31  44.44  54.31 174.02
2018-03-20  23.75  44.82  54.06 173.96
2018-03-21  23.92  43.19  53.91 170.02
2018-03-22  23.47  41.27  51.68 167.61
2018-03-23  23.43  39.96  49.90 163.73
2018-03-26  24.16  38.27  51.68 171.50
2018-03-27  23.40  37.19  50.10 167.11
2018-03-28  23.27  36.99  50.94 165.26

In "stocks_prices_mar15_mar28", I want to replace the values of each stock before the opening date (given in "stocks_purchase_dates") with 0s.

One possible solution is to replace by column and dates:

stocks_prices_mar15_mar28[,"stock1"]["/2018-03-22", ] <- 0
stocks_prices_mar15_mar28[,"stock2"]["/2018-03-20", ] <- 0
stocks_prices_mar15_mar28[,"stock3"]["/2018-03-25", ] <- 0
stocks_prices_mar15_mar28[,"stock4"]["/2018-03-18", ] <- 0

The output is:

stocks_prices_mar15_mar28             

           stock1 stock2 stock3 stock4
2018-03-15   0.00   0.00   0.00   0.00
2018-03-16   0.00   0.00   0.00   0.00
2018-03-19   0.00   0.00   0.00 165.78
2018-03-20   0.00   0.00   0.00 173.96
2018-03-21   0.00  36.10   0.00 170.02
2018-03-22   0.00  41.27   0.00 167.61
2018-03-23  23.00  39.96   0.00 163.73
2018-03-26  24.16  38.27  48.81 171.50
2018-03-27  23.40  37.19  50.10 167.11
2018-03-28  23.27  36.99  50.94 165.26

It works, but if we have a lot more stocks and opening dates it will become hardwork and complicated.

Is there any way to accomplish the task more efficiently, for example with apply or for loop or a function from the purrr package?

phiver phiver · Accepted Answer · 2018-09-28T13:28:10

I used a for loop to loop over the column names. With !is.na(stocks_purchase_dates$stock1) you can find which stock1 record is not NA. With which you can find the position of this record. With .index you can filter inside an xts object. So what we can do is check if the index of stocks_prices_mar15_mar28 is lower than the index of record of stocks_purchase_dates we find looking with which and !is.na and then set those records to 0.

Now this only works if per stock there is only 1 purchase record in stocks_purchase_dates.

for(i in names(stocks_purchase_dates)) {
  stocks_prices_mar15_mar28[, i][.index(stocks_prices_mar15_mar28[, i]) < index(stocks_purchase_dates[, i])[which(!is.na(stocks_purchase_dates[, i]))]] <- 0
}

stocks_prices_mar15_mar28
           stock1 stock2 stock3 stock4
2018-03-15   0.00   0.00   0.00   0.00
2018-03-16   0.00   0.00   0.00   0.00
2018-03-19   0.00   0.00   0.00 174.02
2018-03-20   0.00   0.00   0.00 173.96
2018-03-21   0.00  43.19   0.00 170.02
2018-03-22   0.00  41.27   0.00 167.61
2018-03-23  23.43  39.96   0.00 163.73
2018-03-26  24.16  38.27  51.68 171.50
2018-03-27  23.40  37.19  50.10 167.11
2018-03-28  23.27  36.99  50.94 165.26

When copying and replacing values in the code, be careful of all brackets and braces.

How to replace values in an xts object based on conditions from other xts object

1 Answers