data.table time subset vs xts time subset

Question

Hi I am looking to subset some minutely data by time. I normally use xts doing something like:

subset.string <- 'T10:00/T13:00' 
xts.min.obj[subset.string]

to get all the rows which are between 10am and 1pm (inclusive) EACH DAY and have the output as an xts format. But is a bit slow for my purposes...e.g

j <- xts(rnorm(10e6),Sys.time()-(10e6:1))
system.time(j['T10:00/T16:00'])
   user  system elapsed 
  5.704   0.577  17.115

I know that data.table is v fast and at subsetting large datasets so am wondering if in conjunction with the fasttime package to deal with fast POSIXct creations, if it would be worth it to create a function like

dt.time.subset <- function(xts.min.obj, subset.string){
  require(data.table)
  require(fasttime)
  x.dt <- data.table(ts=format(index(xts.min.obj),"%Y-%m-%d %H:%M:%S %Z"),
                     coredata(xts.min.obj))
  out <- x.dt[,some.subsetting.operation.using."%between%"]
  xts(out,fastPOSIXct(out[,ts])
}

to convert the xts.min.obj into a data.table add some sort of character index and then use data.table to subset the relevant rows use the output row index with fasttime to recreate an xts output? or is this too many excess operations for something that is already highly optimised and written in C?

Sorry, what's your question? You know about %between% so what else do you want? — eddi
well not too sure how to use %between% with times first of all, and at the moment i am converting the index it into character strings rather than using POSIXct, as I was under the impression subsetting of POSIXct is slow...Plus I dont think it works for each day... — h.l.m
the each day part is solved by doing format(..., "%H:%M") %between% c("10:00", "13:00") but I still am not sure why you need data.table here for just subsetting - I highly doubt it's useful going back and forth between xts and data.table if your only purpose is subsetting (but you can always give us example data for which your operations are slow to play around with) — eddi

eddi eddi · Accepted Answer · 2013-06-27T16:22:40

If you're ok with specifying your range in UTC, you can do:

j[(.index(j) %% 86400) %between% c(10*3600, 16*3600 + 60)]
# +60 because xts includes that minute; you'll need to offset the times
# appropriately to match with xts unless you live in UTC :)

j <- xts(rnorm(10e6),Sys.time()-(10e6:1))
system.time(j[(.index(j) %% 86400) %between% c(10*3600, 16*3600 + 60)])
#  user  system elapsed 
#  1.17    0.08    1.25 
# likely faster on your machine as mine takes minutes to run the OP bench

data.table time subset vs xts time subset

1 Answers