R xts and data.table

Question

I can convert a data.table to an xts object just as I do with a data.frame:

> df = data.frame(x = c("a", "b", "c", "d"), v = rnorm(4))
> dt = data.table(x = c("a", "b", "c", "d"), v = rnorm(4))
> xts(df, as.POSIXlt(c("2011-01-01 15:30:00", "2011-01-02 15:30:00", "2011-01-03 15:50:50", "2011-01-04 15:30:00")))
                    x   v           
2011-01-01 15:30:00 "a" "-1.2232283"
2011-01-02 15:30:00 "b" "-0.1654551"
2011-01-03 15:50:50 "c" "-0.4456202"
2011-01-04 15:30:00 "d" "-0.9416562"
> xts(dt, as.POSIXlt(c("2011-01-01 15:30:00", "2011-01-02 15:30:00", "2011-01-03 15:50:50", "2011-01-04 15:30:00")))
                    x   v           
2011-01-01 15:30:00 "a" " 1.3089579"
2011-01-02 15:30:00 "b" "-1.7681071"
2011-01-03 15:50:50 "c" "-1.4375100"
2011-01-04 15:30:00 "d" "-0.2467274"

Is there any issue in using data.table with xts?

There is no issue, but the fact it was a data.table is lost: the data is converted to a matrix (inside the xts object). In your example, it is even a matrix of strings. — Vincent Zoonekynd, Feb 15 '12 at 14:41
I thought xts was keeping a data.frame object in its internal implementation and only adding time indexes as attributes. Are the indexing queries I'm running on xts native rather than data.frame or data.table queries? — Robert Kubrick, Feb 15 '12 at 14:49
@RobertKubrick: xts, like its parent class (zoo), uses a matrix (not a data.frame) with an index attribute. — Joshua Ulrich, Feb 15 '12 at 16:29
I see. So whatever queries I am running on the xts columns, like myxts[myxts$Var1 == "ABC" & myxts$Var2 == "123",] are actually matrix queries? — Robert Kubrick, Feb 15 '12 at 16:38
If you would have data.table with key setup on posixct columns, isn't that cover the functionality of xts? of course there are tons of functions designed for xts, but in the long run wouldn't be better to use dt with key on posixct? — jangorecki, Aug 28 '13 at 13:55

jangorecki · Accepted Answer · 2015-02-08T21:52:15.803

19

Just to resolve an open question.

As Vincent point in the comment there is no issue about that.

It is included in data.table 1.9.5. Below is the similar content:

as.data.table.xts <- function(x, keep.rownames = TRUE){
  stopifnot(requireNamespace("xts") || !missing(x) || xts::is.xts(x))
  r = setDT(as.data.frame(x), keep.rownames = keep.rownames)
  if(!keep.rownames) return(r[])
  setnames(r,"rn","index")
  setkeyv(r,"index")[]
}

as.xts.data.table <- function(x){
  stopifnot(requireNamespace("xts") || !missing(x) || is.data.table(x) || any(class(x[[1]] %in% c("POSIXct","Date"))))
  colsNumeric = sapply(x, is.numeric)[-1] # exclude first col, xts index
  if(any(!colsNumeric)){
    warning(paste("Following columns are not numeric and will be omitted:",paste(names(colsNumeric)[!colsNumeric],collapse=", ")))
  }
  r = setDF(x[,.SD,.SDcols=names(colsNumeric)[colsNumeric]])
  rownames(r) <- x[[1]]
  xts::as.xts(r)
}

edited Feb 08 '15 at 21:52

answered Dec 08 '14 at 11:23

jangorecki

16,384
4
79
160

3

Nice -- +1'ed. Maybe Matt and Arun can pull this into data.table itself? – Dirk Eddelbuettel Dec 08 '14 at 13:21
1

The `as.data.table.xts` function converts the index to `character` and the `as.xts.data.table` function does not allow for `xts` objects that are not `numeric` (e.g. an all `character` `xts`) – GSee Dec 08 '14 at 13:46
1

@DirkEddelbuettel Not sure but link back to here added to [#882](https://github.com/Rdatatable/data.table/issues/882) to discuss... – Matt Dowle Dec 08 '14 at 22:54
Can I have a data.table with one column xts? – skan Feb 25 '21 at 20:41

score 8 · Answer 2 · answered Dec 08 '14 at 13:16

Because of quantmod, it is common to have an xts with the symbol embedded in all the column names. (e.g. "SPY.Open", "SPY.High", etc.). So, here is an alternative to Jan's as.data.table.xts that puts the symbol in a separate column, which is more natural in data.tables (since you're probably going to rbind a bunch of these before doing any analysis).

as.data.table.xts <- function(x, ...) {
  cn <- colnames(x)
  sscn <- strsplit(cn, "\\.")  
  indexClass(x) <- c('POSIXct', 'POSIXt') #coerce index to POSIXct
  DT <- data.table(time=index(x), coredata(x))
  #DT <- data.table(IDateTime(index(x)), coredata(x))

  ## If there is a Symbol embedded in the colnames, strip it out and make it a 
  ## column
  if (all(sapply(sscn, "[", 1) == sscn[[1]][1])) {
    Symbol <- sscn[[1]][1]
    setnames(DT, names(DT)[-1], sub(paste0(Symbol, "."), "", cn))
    DT <- DT[, Symbol:=Symbol]
    setkey(DT, Symbol, time)[]
  } else {
    setkey(DT, time)[]
  }
}

library(quantmod)
getSymbols("SPY")
as.data.table(SPY)
            time   Open   High    Low  Close   Volume Adjusted Symbol
   1: 2007-01-03 142.25 142.86 140.57 141.37 94807600   120.36    SPY
   2: 2007-01-04 141.23 142.05 140.61 141.67 69620600   120.61    SPY
   3: 2007-01-05 141.33 141.40 140.38 140.54 76645300   119.65    SPY
   4: 2007-01-08 140.82 141.41 140.25 141.19 71655000   120.20    SPY
   5: 2007-01-09 141.31 141.60 140.40 141.07 75680100   120.10    SPY
  ---                                                                
1993: 2014-12-01 206.30 206.60 205.38 205.64 12670100   205.64    SPY
1994: 2014-12-02 205.81 207.34 205.78 207.09 72105500   207.09    SPY
1995: 2014-12-03 207.30 208.15 207.10 207.89 69450000   207.89    SPY
1996: 2014-12-04 207.54 208.27 206.70 207.66 89928200   207.66    SPY
1997: 2014-12-05 207.87 208.47 207.55 208.00 85031000   208.00    SPY

R xts and data.table

2 Answers2

Linked