CODEWITHSUNDEEP
rxts
Sergey777
Sergey777

Reputation: 59

Aggregate xts object by time of day

I have a xts object with minute prices for each day, as below. I would like to aggregate these time series so I have a table with rowname equal to time hh:mm:ss (no day, month, year) and multiple columns for each statistic I want to calculate.

                    ESH4.Open ESH4.High ESH4.Low ESH4.Close ESH4.Volume
2014-03-05 18:00:00   1873.75   1874.00  1873.50    1873.50         452
2014-03-05 18:01:00   1873.50   1873.75  1873.25    1873.50         453
2014-03-05 18:02:00   1873.25   1873.50  1873.25    1873.25          61
2014-03-05 18:03:00   1873.50   1873.50  1873.25    1873.25          21
2014-03-05 18:04:00   1873.50   1873.50  1873.00    1873.25         456
2014-03-05 18:05:00   1873.00   1873.25  1872.25    1872.25        1004
2014-03-05 18:06:00   1872.50   1872.75  1872.25    1872.75          98
2014-03-05 18:07:00   1872.50   1872.75  1872.25    1872.50         361
2014-03-05 18:08:00   1872.25   1872.25  1871.75    1871.75         476
2014-03-05 18:09:00   1871.75   1872.25  1871.75    1872.25         177

Need for ESH4.Close column:

          stat1 stat2 stat3
18:01:00  ...
18:02:00  ... 
18:03:00  ...

Upvotes: 1

Views: 1808

Answers (1)

Joshua Ulrich
Joshua Ulrich

Reputation: 176648

This can be done with the aggregate function. Note that xts does not define an aggregate method, so aggregate.zoo will be dispatched.

# create some sample data
set.seed(21)
i <- Sys.time()+seq_len(60*24*30)*60
x <- xts(rnorm(length(i)),i)
# call aggregate.zoo
z <- aggregate(x, format(index(x),"%H:%M"), function(d) c(mean(d),sd(d)))
# note that 'z' is a zoo object, not xts
head(z)                   
# 00:00 21422 12673.5
# 00:01 21423 12673.5
# 00:02 21424 12673.5
# 00:03 21425 12673.5
# 00:04 21426 12673.5
# 00:05 21427 12673.5

Upvotes: 3

Related Questions