1

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  ...
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
Sergey777
  • 59
  • 1
  • 1
  • 8
  • What is the larger problem you're trying to solve? There's probably a better way to solve it than trying to do what you're asking for in your question. – Joshua Ulrich Mar 08 '14 at 19:27
  • I want to calculate an average price change (and other statistics) over the last 30 days for every minute during the day. – Sergey777 Mar 08 '14 at 19:30

1 Answers1

2

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
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418