23

I have a data frame containing multiple time series of returns, stored in columns.

The first column contains dates, and subsequent columns are independent time series each with a name. The column headers are the variable names.

## I have a data frame like this
t <- seq(as.Date('2009-01-01'),by='days',length=10)
X <- rnorm(10,0,1)
Y <- rnorm(10,0,2)
Z <- rnorm(10,0,4)

dat <- data.frame(t,X,Y,Z)

## which appears as
           t          X          Y         Z
1 2009-01-01 -1.8763317 -0.1885183 -6.655663
2 2009-01-02 -1.3566227 -2.1851226 -3.863576
3 2009-01-03 -1.3447188  2.4180249 -1.543931

I want to plot each time series as a line on a separate plot, in a lattice, with each plot labeled by the variable names.

To plot this with lattice, the data must be in a tall format, as such: 

           t symbol       price
1 2009-01-01      X -1.8763317
2 2009-01-02      Y -0.1885183
2 2009-01-02      Z -6.655663

What is a good function call to do this?

Rob Hyndman
  • 30,301
  • 7
  • 73
  • 85
medriscoll
  • 26,995
  • 17
  • 40
  • 36
  • 1
    Just in case someone would like to find here an answer not mentioned below, this is the solution using `reshape` function in base `R`: `reshape(dat, direction = "long", varying = 2:4, v.names = "price", idvar = "t", timevar = "symbol", times = names(dat)[2:4], new.row.names = 1:30)` – josep maria porrà Apr 19 '20 at 16:55

5 Answers5

18

you can also use melt() from the 'reshape' library (I think it's easier to use than reshape() itself) - that'll save you the extra step of having to add the time column back in...

> library(reshape)
> m <- melt(dat,id="t",variable_name="symbol")
> names(m) <- sub("value","price",names(m))
> head(m)
           t symbol       price
1 2009-01-01      X -1.14945096
2 2009-01-02      X -0.07619870
3 2009-01-03      X  0.01547395
4 2009-01-04      X -0.31493143
5 2009-01-05      X  1.26985167
6 2009-01-06      X  1.31492397
> class(m$t)
[1] "Date"
> library(lattice)                                                              
> xyplot( price ~ t | symbol, data=m ,type ="l", layout = c(1,3) )

For this particular task, however, I would consider using the 'zoo' library, which would not require you to reshape the data frame:

> library(zoo)                                                                  
> zobj <- zoo(dat[,-1],dat[,1])                                                 
> plot(zobj,col=rainbow(ncol(zobj)))

R developers/contributors (Gabor and Hadley in this case) have blessed us with many great choices. (and can't forget Deepayan for the lattice package)

hatmatrix
  • 42,883
  • 45
  • 137
  • 231
  • Melt is exactly what I was looking for. Stephen, could you edit your answer to include the last plotting step? – medriscoll Jul 30 '09 at 04:21
  • 1
    Having wasted an hour with the "stats::reshape()" function, I have to wonder what drugs some of the people who wrote R functions were taking. melt() is great. (once again, thanks Hadley) – forkandwait Oct 22 '09 at 19:46
18

From tidyr gather help page:

Examples

library(tidyr)
library(dplyr)
# From http://stackoverflow.com/questions/1181060
stocks <- data.frame(
  time = as.Date('2009-01-01') + 0:9,
  X = rnorm(10, 0, 1),
  Y = rnorm(10, 0, 2),
  Z = rnorm(10, 0, 4)
)

gather(stocks, stock, price, -time)
stocks %>% gather(stock, price, -time)
zx8754
  • 52,746
  • 12
  • 114
  • 209
11

If it is a multivariate time series, consider storing it as a zoo object by using the package of the same name. This makes indexing, merging, subseting a lot easier --- see the zoo vignettes.

But as you asked about lattice plots -- and this can also be done. In this example, we construct a simple 'long' data.frame with a date column, as well as a value column 'val' and a variable id column 'var':

> set.seed(42)
> D <- data.frame(date=rep(seq(as.Date("2009-01-01"),Sys.Date(),by="week"),2),\
                  val=c(cumsum(rnorm(30)), cumsum(rnorm(30))), \
                  var=c(rep("x1",30), rep("x2",30)))

Given that dataset, plotting per your description is done by xyplot from the lattice package by asking for a plot of 'value given data grouped by variable' where we turn on lines in each panel:

> library(lattice)
> xyplot(val ~ date | var, data=D, panel=panel.lines)
Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
5

For a dataframe 'temp' with the date in the first column and values in each of the other columns:

> par(mfrow=c(3,4)) # 3x4 grid of plots
> mapply(plot,temp[,-1],main=names(temp)[-1],MoreArgs=list(x=temp[,1],xlab="Date",type="l",ylab="Value") )
MRE
  • 61
  • 1
3

Many thanks for the answers folks - Dirk's answer was on mark.

The missing step turned out to be using "stack()" function to convert the data frame from a wide to a long format. I'm aware there may be an easier way to do this with the reshape() function, happy to see an example if someone wants to post it.

So here's what I ended up doing, using the 'dat' dataframe mentioned in the question:

## use stack() to reshape the data frame to a long format
## <time> <stock> <price>
stackdat <- stack(dat,select=-t) 
names(stackdat) <- c('price','symbol')

## create a column of date & bind to the new data frame
nsymbol <- length(levels(stackdat$symbol))  
date <- rep(dat$t, nsymbol)  
newdat <- cbind(date,stackdat)

## plot it with lattice
library(lattice)
xyplot(price ~ date | symbol,  ## model conditions on 'symbol' to lattice
       data=newdat,            ## data source
       type='l',               ## line
       layout=c(nsymbol,1))    ## put it on a single line

## or plot it with ggplot2
library(ggplot2)
qplot(date, price, data = newdat, geom="line") + facet_grid(. ~ symbol)
medriscoll
  • 26,995
  • 17
  • 40
  • 36