XTS conditional sum

Question

In the sample xts data below, I show a small subset of intra-day data.

my.temp <- structure(c(1134.65, 45665.2, 63639.8, 262817.8, 71898.4, 128737,
  641741.8, 6090, 7561.5, 3954.73, 15733.2, 274.88, 824.64, 1099.52, 1924.16,
  348715, 196425, 113975, 215825, 38340, 21138, 12020, 20200, 20200, 20200,
  20200, 20200, 20200, 23000, 12020, 12020, 12020, 12020, 12020, 12020, 12020,
  20200, 20200, 20200, 20200, 12020, 20000), .Dim = c(21L, 2L),
  .Dimnames = list(NULL, c("VALUE", "USAP")), index = structure(c(1378130401,
  1378130404, 1378130404, 1378130404, 1378130404, 1378130404, 1378130404,
  1378130404, 1378130406, 1378130409, 1378130411, 1378130415, 1378130415,
  1378130415, 1378130415, 1378130451, 1378130452, 1378130452, 1378130452,
  1378130455, 1378130501), tzone = "", tclass = c("POSIXct", "POSIXt")),
  .indexCLASS = c("POSIXct", "POSIXt"), tclass = c("POSIXct", "POSIXt"),
  .indexTZ = "", tzone = "", class = c("xts", "zoo"))

I am trying to show the total VALUE per USAP. I have used the aggregate function successfully if I want to show the results over the entire period. How would one show the total per USAP, per day?

Note that it's a lot easier for people to help if you provide the structure/output of your object via `dput` rather than just pasting the printed representation of the data. — Joshua Ulrich, Sep 03 '13 at 13:40

score 0 · Answer 1 · answered Sep 03 '13 at 13:37

Here's one way to do it. There may be more concise ways, but this is the best I can come up with at the moment.

# first, split your object into groups by USAP
temp.split <- split(my.temp, my.temp$USAP)
# define a simple helper function
f <- function(x) apply.daily(x, function(d) c(sum(d$VALUE),d$USAP[1]))
# then call apply.daily on each group;
# finally, rbind the results back together
do.call(rbind, lapply(temp.split, f))
#                          VALUE  USAP
# 2013-09-02 09:00:04    6090.00 23000
# 2013-09-02 09:00:52 2089440.00 20200
# 2013-09-02 09:00:55   70847.28 12020
# 2013-09-02 09:01:41   21138.00 20000

score 0 · Answer 2 · answered Sep 03 '13 at 14:08

aggregate(VALUE ~ USAP + as.Date(index(my.temp)), data = my.temp, sum)

   #    USAP as.Date(index(my.temp))      VALUE
   # 1 12020              2013-09-02   70847.28
   # 2 20000              2013-09-02   21138.00
   # 3 20200              2013-09-02 2089440.00
   # 4 23000              2013-09-02    6090.00

XTS conditional sum

2 Answers2