R convert a data.frame to a daily time series object

Question

I have my data.frame some what like this

  name units_sold order_date
1 obj1         10 2013-09-21
2 obj1         10 2013-09-22
3 obj1         10 2013-09-23
4 obj2        100 2013-09-21
5 obj2        200 2013-09-22
6 obj2        300 2013-09-23
7 obj3         70 2013-09-21
8 obj3        200 2013-09-22
9 obj3         50 2013-09-23

I want to convert it to a time series object such that it should have values in below format:

       2013-09-21  2013-09-22 2013-09-23
obj1      10            10         10
obj2      100           200        300
obj3      70            200        50

... for a week

Welcome to Stack Overflow and thank you for trying to include data with your question. Unfortunately it's very difficult to see how your data is structured based on the above, or what new format you are aiming for. Please read the help page for `dput` (enter `?dput`) which will explain how to convert an object into text. Better yet, read [this post](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) carefully for hints as to how to produce a good reproducible example. Finally, you should include what code you have written, even if it doesn't work. — SlowLearner, Sep 23 '13 at 11:57

G. Grothendieck · Answer 1 · 2013-09-25T14:18:46.527

In R a multivariate series is normally represented by one series per column, not row. Using the zoo package one can read it in like this (to keep the example self contained we have read it in as a character string but you would want to replace text=Lines with something like file="myfile.dat"):

Lines <- "name units_sold order_date
1 obj1         10 2013-09-21
2 obj1         10 2013-09-22
3 obj1         10 2013-09-23
4 obj2        100 2013-09-21
5 obj2        200 2013-09-22
6 obj2        300 2013-09-23
7 obj3         70 2013-09-21
8 obj3        200 2013-09-22
9 obj3         50 2013-09-23
"

library(zoo)
z <- read.zoo(text = Lines, header = TRUE, index = 3, split = 1)

which gives:

> z
           obj1 obj2 obj3
2013-09-21   10  100   70
2013-09-22   10  200  200
2013-09-23   10  300   50

From this point on you can plot it (plot(z)), convert it to a ts series (as.ts(z) although daily time series are not normally used with ts) and do many other operations. See the 5 zoo vignettes (pdfs) and the zoo help pages at the same link.

(Note that in this case header=TRUE is not actually necessary since it will figure out that the first line is a header by virtue of the fact that the remaining lines have one more field, i.e. they have row names whereas the first line does not.)

agstudy · Answer 2 · 2013-09-23T12:14:45.890

I don't think that the expected output is a ts object. I understand your question as a reshaping problem from the long to the wide format. Here 2 methods:

Using dcast from reshape2 package:

library(reshape2)
dcast(dat,name~order_date,value.var="units_sold")

 name 2013-09-21 2013-09-22 2013-09-23
1 obj1         10         10         10
2 obj2        100        200        300
3 obj3         70        200         50

Using reshape from base package:

reshape(dat,direction='wide',idvar='name',timevar='order_date')

 name units_sold.2013-09-21 units_sold.2013-09-22 units_sold.2013-09-23
1 obj1                    10                    10                    10
4 obj2                   100                   200                   300
7 obj3                    70                   200                    50

That doesn't result in a time series object, though. – Roland Sep 23 '13 at 12:03 — Roland, Sep 23 '13 at 12:03

R convert a data.frame to a daily time series object

2 Answers2