[R] Aggregating 15 minute xts sequence to hourly

Joshua Ulrich josh.m.ulrich at gmail.com
Wed Jun 18 13:12:18 CEST 2014


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
>
>



More information about the R-help mailing list