0

A simple query returns 3 columns: instrument, date, price

Data as follows:

library("xts")
dta = data.frame(
  sample(x=c("a", "b", "c", "d", "e", "f"), size=367, replace=TRUE),
  c(as.Date('2016-01-01') + 0:366), 
  c(0:366))
names(dta) <- c("instr", "date", "price")

What I want is a pivoted xts-object that looks as follows:

            "a"   "b"   "c"   "d"   "e"   "f"
2016-01-01  1     NA    NA    NA    NA    NA
2016-01-02  NA    2     NA    NA    NA    NA
2016-01-03  3     NA    NA    NA    NA    NA
2016-01-04  NA    NA    NA    NA    4     NA
...

I was thinking about splitting the data into "instrument"-vectors:

list_of_instr_vectors <- split(dta, dta$instr)

Turn all the data.frames of the list into xts-objects and merge.xts those.

Maybe it would be simpler to turn the data.frame from the start into an xts:

xts_dta = as.xts(dta, order.by = dta$date)

And pivot that data... but how?

I am sure, there is a simple way to accomplish this common task... but how?

Thanks for any hint! Marco

Marco
  • 31
  • 3
  • Possible duplicate of [How to reshape data from long to wide format?](http://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) – nrussell Jan 20 '17 at 20:54
  • This is not a duplicate. It might be possible to use the methods in the link but since we are dealing with xts series those are not really the best solutions. – G. Grothendieck Jan 21 '17 at 12:16

1 Answers1

0

Use read.zoo with the split argument:

z <- read.zoo(dta, split = 1, index = 2)
as.xts(z)
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341