2

So I have a xts time serie over the year with time zone "UTC". The time interval between each row is 15 minutes.

                       x1    x2
2014-12-31 23:15:00 153.0   0.0 
2014-12-31 23:30:00 167.1   5.4 
2014-12-31 23:45:00 190.3   4.1 
2015-01-01 00:00:00 167.1   9.7

As I want data over one hour to allow for comparison with other data sets, I tried to use period.apply:

dat <- period.apply(dat, endpoints(dat,on="hours",k=1), colSums)

The problem is that the first row in my new data set is 2014-12-31 23:45:00 and not 2015-01-01 00:00:00. I tried changing the endpoint vector but somehow it keeps saying that it is out of bounds. I also thought this was my answer: https://stats.stackexchange.com/questions/5305/how-to-re-sample-an-xts-time-series-in-r/19003#19003 but it was not. I don't want to change the names of my columns, I want to sum over a different interval.

Here a reproducible example:

library(xts)
seq<-seq(from=ISOdate(2014,12,31,23,15),length.out = 100, by="15 min", tz="UTC")
xts<-xts(rep(1,100),order.by = seq)
period.apply(xts, endpoints(xts,on="hours",k=1), colSums)

And the result looks like this:

2014-12-31 23:45:00    3
2015-01-01 00:45:00    4
2015-01-01 01:45:00    4
2015-01-01 02:45:00    4

and ends up like this:

2015-01-01 21:45:00    4
2015-01-01 22:45:00    4
2015-01-01 23:45:00    4
2015-01-02 00:00:00    1

Whereas I would like it to always sum over the same interval, meaning I would like only 4s. (I am using RStudio 0.99.903 with R x64 3.3.2)

Community
  • 1
  • 1
Daldal
  • 73
  • 5
  • Someone can probably help you if you provide some sample data that reproduces your problem. See http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example or http://meta.stackexchange.com/questions/176460/how-to-paste-data-from-r-to-stackoverflow – FXQuantTrader Nov 21 '16 at 21:40
  • Thanks for your suggestions. I tried to make it clearer! – Daldal Nov 22 '16 at 07:55

1 Answers1

2

The problem is that you're using endpoints, but you want to align by the start of the interval, not the end. I thought you might be able to use this startpoints function, but that produced weird results.

The basic idea of the work-around below is to subtract a small amount from all index values, then use endpoints and period.apply to aggregate. Then call align.time on the result. I'm not sure if this is a general solution, but it seems to work for your example.

library(xts)
seq<-seq(from=ISOdate(2014,12,31,23,15),length.out = 100, by="15 min", tz="UTC")
xts<-xts(rep(1,100),order.by = seq)
# create a temporary object
tmp <- xts
# subtract a small amount of time from each index value
.index(tmp) <- .index(tmp)-0.001
# aggregate to hourly
agg <- period.apply(tmp, endpoints(tmp, "hours"), colSums)
# round index up to next hour
agg_aligned <- align.time(agg, 3600)
Community
  • 1
  • 1
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418