R - Subsetting XTS via Time and Which

Question

Currently, I have an xts time series, called Data, which contains a Date, and two value columns, Value1 and Value2 which are both numbers. I would like to get a single number as output from my code which would be the mean of Value1 in the time period from a point where Value2 < mean(Value2) and going forward 14 data points, weeks in this particular data set.

In order to get the dates where Value2 < mean(Value2), I wrote the below code

Data[which(Data$Value2 < mean(Data$Value2)),"Date"]

However, I am not sure how to get the mean of Value1 in the period, going 14 days forward from each of the resultant dates from the above code.

Example Dataset:

                Value1  Value2
1   2009-01-02  22.6500 17
2   2009-01-09  21.4700 56
3   2009-01-16  20.6100 -50
4   2009-01-23  19.6800 13
5   2009-01-30  19.2800 172
6   2009-02-06  20.1300 -120
7   2009-02-13  18.9900 17

The mean of Value2 is 12.57. Therefore the selected dates would be 2009-01-16 and 2009-02-06 since Value2 < mean(Value2) there. I would then like the mean of Value1 in the time period from 2009-01-16 to 2009-01-30 and from 2009-02-06 to 2009-02-20, in matrix form with the start date followed by the mean(Value1).

Do you want to calculate the mean of `Value1` in the original data or in the subsetted data, where all `Value2` are less than `mean(Value2)`? — talat, May 27 '14 at 15:56
xts objects store the date/times in the `index` attribute and do not have a `"Date"` column (unless you purposefully create one yourself). Please provide a [reproducible example](http://stackoverflow.com/q/5963269/271616). — Joshua Ulrich, May 27 '14 at 16:02
Sorry, I did mean the index attribute. Will add example momentarily — user2844947, May 27 '14 at 16:03

score 0 · Accepted Answer · answered May 27 '14 at 16:05

I'm not sure whether this is exactly what you were asking for. It will calculate the mean of Value1 in the subsetted data. Note however, that it is not clear what you want to do in the cases where there are less than 14 more days ahead. I include two versions of the my.func definition, one will produce NAs in the final output, the other wont. You can check this out yourself. Also, if I understood the question correct, it is not necessary to use xts for this task (so I dont use it in my example).

set.seed(99)
Data <- data.frame(Date = seq(as.Date("2000-01-01"), as.Date("2000-03-30"), by = 1), 
               Value1 = rnorm(90), 
               Value2 = runif(90))

strt <- which(Data$Value2 < mean(Data$Value2))

subData <- Data[which(Data$Value2 < mean(Data$Value2)),]

my.func <- function(x) mean(subData$Value1[x:(x+14)])       #will produce 14 NA

#my.func <- function(x) mean(subData$Value1[x:(x+14)], na.rm = TRUE)   #wont produce NA

sapply(seq_along(strt), my.func)

R - Subsetting XTS via Time and Which

1 Answers1