An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20140616/90b0936c/attachment.pl>
Aggregating 15 minute xts sequence to hourly
5 messages · Joshua Ulrich, Costas Vorlow
On Mon, Jun 16, 2014 at 3:41 AM, Costas Vorlow <costas.vorlow at gmail.com> wrote:
Dear all, Why aggregation of 15 minute xts data happens on the 45th (3rd quarter) and not the exact hour close (i.e., 00) time?
The "00" time is the beginning of the hour, not the end. E.g., 10:00:00 is the beginning of the 10-o'clock hour.
For example, temp below is an xts sequence with 15-minute frequency:
quarters <- ISOdatetime(2012,05,02,9,0,0) + seq(0:39)*15*60; set.seed(42); observation <- xts(1:40, order.by=as.POSIXct(quarters)); head(observation);
[,1] 2012-05-02 09:15:00 1 2012-05-02 09:30:00 2 2012-05-02 09:45:00 3 2012-05-02 10:00:00 4 2012-05-02 10:15:00 5 2012-05-02 10:30:00 6
ends<-endpoints(observation,'hours'); temp<-period.apply(observation, ends,sum); temp
[,1] 2012-05-02 09:45:00 6 2012-05-02 10:45:00 22 2012-05-02 11:45:00 38 2012-05-02 12:45:00 54 2012-05-02 13:45:00 70 2012-05-02 14:45:00 86 2012-05-02 15:45:00 102 2012-05-02 16:45:00 118 2012-05-02 17:45:00 134 2012-05-02 18:45:00 150 2012-05-02 19:00:00 40
I get the sum of every quarter within the hour on the third quarter. How can I implicitly calculate the sum of the quarterly data on the hour's close (10:00, 11:00, 12:00 and so on) ?
Again, those are the beginnings of the hours. endpoints() and period.apply() only use the timestamps in your data. If you want to round up to the beginning of the next hour, use align.time().
align.time(temp, 3600)
[,1] 2012-05-02 10:00:00 6 2012-05-02 11:00:00 22 2012-05-02 12:00:00 38 2012-05-02 13:00:00 54 2012-05-02 14:00:00 70 2012-05-02 15:00:00 86 2012-05-02 16:00:00 102 2012-05-02 17:00:00 118 2012-05-02 18:00:00 134 2012-05-02 19:00:00 150 2012-05-02 20:00:00 40
Many thanks in advance, Costas
__________________________________________________________________ *Costas Vorlow <http://www.gravatar.com/avatar/49a9dee59073b1ed4a36440a06aeb81b> * *http://www.linkedin.com/in/costasvorlow <http://www.linkedin.com/in/costasvorlow>* *http://www.vorlow.com* <http://www.vorlow.com> ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
Best, -- Joshua Ulrich | about.me/joshuaulrich FOSS Trading | www.fosstrading.com
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20140616/790ba172/attachment.pl>
1 day later
On Mon, Jun 16, 2014 at 7:04 AM, Costas Vorlow <costas.vorlow at gmail.com> wrote:
Dear Joshua, Thanks for your reply. As I see, the solution you suggest aligns the time stamps as required but leaves the aggregation results as is. Hence, the last quarter data of every hour are not aggregated still... Am I right or am I understanding something wrongly?
You still seem to think the "00" time is the end of the hour, but it's not; it's the beginning. The first 3 rows of 'observation' contain data for the first hour (9 o'clock). The fourth row is the beginning of the second hour (10 o'clock).
I tried to "move" ahead ends by one (ends<-ends+1) but this does not work either. It seems that if you change the endpoints, still aggregation happens every 45 minutes as you pointed out, although the ends variable points to the round hour time stamp...
ends <- endpoints(observation,'hours')+1 The above doesn't work because endpoints always includes the last observation, which is now out of bounds. You would need to adjust the first and last 'ends' values. ends <- endpoints(observation,'hours')+1 ends[1] <- 0 ends[length(ends)] <- nrow(observation) ends <- unique(ends) temp <- period.apply(observation, ends, sum) Be very careful with the results in this 'temp' object though. If you merge it with another xts object, you will have a look-ahead bias because you will know the aggregate for the time period before it has occurred.
__________________________________________________________________ Costas Vorlow http://www.linkedin.com/in/costasvorlow http://www.vorlow.com ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? On 16 June 2014 13:31, Joshua Ulrich <josh.m.ulrich at gmail.com> wrote: On Mon, Jun 16, 2014 at 3:41 AM, Costas Vorlow <costas.vorlow at gmail.com> wrote: Dear all, Why aggregation of 15 minute xts data happens on the 45th (3rd quarter) and not the exact hour close (i.e., 00) time? The "00" time is the beginning of the hour, not the end. E.g., 10:00:00 is the beginning of the 10-o'clock hour. For example, temp below is an xts sequence with 15-minute frequency: quarters <- ISOdatetime(2012,05,02,9,0,0) + seq(0:39)*15*60; set.seed(42); observation <- xts(1:40, order.by=as.POSIXct(quarters)); head(observation); [,1] 2012-05-02 09:15:00 1 2012-05-02 09:30:00 2 2012-05-02 09:45:00 3 2012-05-02 10:00:00 4 2012-05-02 10:15:00 5 2012-05-02 10:30:00 6 ends<-endpoints(observation,'hours'); temp<-period.apply(observation, ends,sum); temp [,1] 2012-05-02 09:45:00 6 2012-05-02 10:45:00 22 2012-05-02 11:45:00 38 2012-05-02 12:45:00 54 2012-05-02 13:45:00 70 2012-05-02 14:45:00 86 2012-05-02 15:45:00 102 2012-05-02 16:45:00 118 2012-05-02 17:45:00 134 2012-05-02 18:45:00 150 2012-05-02 19:00:00 40 I get the sum of every quarter within the hour on the third quarter. How can I implicitly calculate the sum of the quarterly data on the hour's close (10:00, 11:00, 12:00 and so on) ? Again, those are the beginnings of the hours. endpoints() and period.apply() only use the timestamps in your data. If you want to round up to the beginning of the next hour, use align.time(). align.time(temp, 3600) [,1] 2012-05-02 10:00:00 6 2012-05-02 11:00:00 22 2012-05-02 12:00:00 38 2012-05-02 13:00:00 54 2012-05-02 14:00:00 70 2012-05-02 15:00:00 86 2012-05-02 16:00:00 102 2012-05-02 17:00:00 118 2012-05-02 18:00:00 134 2012-05-02 19:00:00 150 2012-05-02 20:00:00 40 Many thanks in advance, Costas __________________________________________________________________ *Costas Vorlow <http://www.gravatar.com/avatar/49a9dee59073b1ed4a36440a06aeb81b> * *http://www.linkedin.com/in/costasvorlow <http://www.linkedin.com/in/costasvorlow>* *http://www.vorlow.com* <http://www.vorlow.com> ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Best, -- Joshua Ulrich | about.me/joshuaulrich FOSS Trading | www.fosstrading.com
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20140618/56865565/attachment.pl>