Search code examples
rdata.tablexts

R data.table to xts with grouping

Does anybody know how to convert this data.table

library(data.table)
library(xts)
library(lubridate)
dt <- data.table(date=c(today()+months(0:4),today()+months(0:4)),price=c(100,102,104,106,108,100,99,101,98,102),ticker=c(rep("A",5),rep("B",5)))

into an xts like this

xts(cbind(A=c(100,102,104,106,108),B=c(100,99,101,98,102)),c(today()+months(0:4)))

I tried using dcast or create a new data.table where the columns are the unique(ticker) and the prices are bellow, but I can't figure out how to do it.

I would appreciate any help. Thank you!

Solution

  • Using dcast from data.table:

    dt <- data.table(date=c(today()+months(0:4),today()+months(0:4)),price=c(100,102,104,106,108,100,99,101,98,102),ticker=c(rep("A",5),rep("B",5)))

dt <- dcast(dt, date ~ ticker,  value.var = "price")
my_xts <- xts(dt[, -1], order.by = dt$date)

             A   B
2018-09-11 100 100
2018-10-11 102  99
2018-11-11 104 101
2018-12-11 106  98
2019-01-11 108 102