How to convert matrix with tick data into xts?

Question

I have the data which i am trying to convert into xts format:

> dput(data)
structure(list(50370788L, 50370777L, 50370694L, 50370620L, 50370504L, 
    620639L, 620639L, 592639L, 592639L, 592639L, "2015-10-24", 
    "2015-10-24", "2015-09-04", "2015-09-04", "2015-09-04", structure(list(
        id = 12544L, symbol = "GBSN", title = "Great Basin Scientific, Inc."), .Names = c("id", 
    "symbol", "title"), class = "data.frame", row.names = 1L), 
    structure(list(id = 12544L, symbol = "GBSN", title = "Great Basin Scientific, Inc."), .Names = c("id", 
    "symbol", "title"), class = "data.frame", row.names = 1L), 
    structure(list(id = 12544L, symbol = "GBSN", title = "Great Basin Scientific, Inc."), .Names = c("id", 
    "symbol", "title"), class = "data.frame", row.names = 1L), 
    structure(list(id = 12544L, symbol = "GBSN", title = "Great Basin Scientific, Inc."), .Names = c("id", 
    "symbol", "title"), class = "data.frame", row.names = 1L), 
    structure(list(id = 12544L, symbol = "GBSN", title = "Great Basin Scientific, Inc."), .Names = c("id", 
    "symbol", "title"), class = "data.frame", row.names = 1L), 
    "$GBSN Still sticking with my prediction of FDA coming sometime in March..", 
    "$GBSN Last time I check NASDAQ gave them till sometime in April to get it together or else they&#39;ll see pink. Correct me if in wrong?", 
    "$GBSN time for retailers to get knocked out of the ring with a 25 to 30 % gain", 
    "$GBSN market cap will end up around 65 million not enough to comply rs takes it to 21 dollars pps 26$ by august", 
    "$GBSN shorts are going to attack the sell off"), .Dim = c(5L, 
5L), .Dimnames = list(c("2016-02-28 16:59:53", "2016-02-28 16:58:58", 
"2016-02-28 16:51:36", "2016-02-28 16:46:09", "2016-02-28 16:34:34"
), c("GBSN.Message_ID", "GBSN.User_ID", "GBSN.User_Join_Date", 
"GBSN.Message_Symbols", "GBSN.Message_Body")))

I have been trying to use :

 Message_series <- xts(zoo(data, format='%Y-%m-%d %H:%M:%S'))

i get this error:

Error in zoo(data, format = "%Y-%m-%d %H:%M:%S") : 
  unused argument (format = "%Y-%m-%d %H:%M:%S")

kaliczp · Answer 1 · 2016-02-28T20:15:35.417

Your matrix is not tidy. Look at the fourth column (data[,4]). zoo and hence xts not support so complicated object only simple matrix with all element in the same type.

First and second columns are OK. They inherited the list properties so the conversion is not so straightforward.

data.mat <- matrix(as.numeric(data[,1:2]), ncol = 2)
colnames(data.mat) <- colnames(data)[1:2]
xts(data.mat, order.by = as.POSIXct(rownames(data)))

Join data can be converted and included:

data.mat <- cbind(data.mat, as.numeric(as.Date(as.character(data[,3]))))
colnames(data.mat) <- colnames(data)[1:3]
data.xts <- xts(data.mat, order.by = as.POSIXct(rownames(data)))

and transformable back:

as.Date(coredata(data.xts['2016-02-28 16:59:53',3]))

You can code variables id, symbol, title from Message_Symbols too in the same way.

I recommend you store Message_Body in a separate object (e.g. data.frame).

WaltS · Answer 2 · 2016-02-28T21:48:14.817

Based on the column names of data, it appears that all of your data is or could be of character type. However, data[,4], GBSN.Message_Symbols, contains lists, not an atomic vector so we'll have to flatten using rbind. apply is then used to convert each column to a character vector and combine to form a character matrix. The xts object is formed by converting the rownames to POSIX date/time types and using them as the index. Code would look like

# flatten list data in column 4 to a data frame
  mat4 <- do.call(rbind, data[,4])
# convert all data to character type
  data.mat  <- apply(cbind(data[,-4], mat4), 2, as.character)    
# create xts time series
  data.xts <- xts(data.mat, order.by = as.POSIXct(rownames(data)))

How to convert matrix with tick data into xts?

2 Answers2