If I have a 10-year daily xts time series, starting on 1985-01-01 and ending on 1994-12-31, how can I calculate the sum of values for the interval starting on Nov 1 and ending on Mar 31 of the next year, through out the series?
dates <- seq(from = as.Date("1985-01-01"), to = as.Date("1994-12-31"), by = 1)
data <- rnorm(length(dates))
my.ts <- xts(x=data, order.by=dates)
I need to get
my.ts2[1] = sum(my.ts[1985-11-01 to 1986-03-31])
my.ts2[2] = sum(my.ts[1986-11-01 to 1987-03-31])
my.ts2[3] = sum(my.ts[1987-11-01 to 1988-03-31])
and so on
Note: There are no Feb-29 in the original time series.
This aggregates by successive groups, separated at Nov 1. and Apr 1. The first row of the results is an incomplete "in" group, then an "out" group, then an "in", and so on.
library(xts)
set.seed(1)
dates <- seq(from = as.Date("1985-01-01"), to = as.Date("1994-12-31"), by = 1)
data <- round(rnorm(length(dates)), 2)
my.ts <- xts(x=data, order.by=dates)
d <- as.POSIXlt(index(my.ts))
nov1 <- d$mon == 10 & d$mday == 1
apr1 <- d$mon == 3 & d$mday == 1
id <- cumsum(nov1+apr1)
agg <- cbind(
aggregate(index(my.ts), list(id), function(x) as.character(range(x))),
aggregate(my.ts, id, sum)
)
agg <- data.frame(
id=agg[[1]],
start=agg[[2]][,1],
end=agg[[2]][,2],
sum=agg[[3]]
)
# To filter for only the "in" groups
agg[endsWith(agg$end, "-03-31") | endsWith(agg$start, "-11-01"),]
# id start end sum
# 1 0 1985-01-01 1985-03-31 9.77
# 3 2 1985-11-01 1986-03-31 -5.76
# 5 4 1986-11-01 1987-03-31 -10.54
# 7 6 1987-11-01 1988-03-31 -5.81
# 9 8 1988-11-01 1989-03-31 9.26
# 11 10 1989-11-01 1990-03-31 -6.42
# 13 12 1990-11-01 1991-03-31 -5.78
# 15 14 1991-11-01 1992-03-31 -17.57
# 17 16 1992-11-01 1993-03-31 20.03
# 19 18 1993-11-01 1994-03-31 16.06
# 21 20 1994-11-01 1994-12-31 -5.76