How can I merge 3 time series in R?

Question

I'm really new here and I'm really new in programming language like R. I would like to download 3 time series, choose 2 columns (dates and closing price) from each of them and then merge them into one. Merging them is my problem -- some dates are copied like here:

http://prntscr.com/avz7o6.jpg

I tried to use merge.xts() instead of merge() and change .xts into data.frame but it doesn't work.

My code:

#install packages quantmod i Quandl
install.packages(c("quantmod","Quandl"))
library('quantmod')
library('Quandl')

#download data (.xts) using Quandl
Wine<-Quandl("LSE/WINE",type="xts")

#download data from Yahoo!Finance (.xts)
Rennova.Health<-getSymbols("RNVA")

#download data from stooq.pl and change them into xts
data<-read.csv("http://stooq.pl/q/d/l/?s=EUR&i=d",header=TRUE)   
EUR<-xts(data[,-1],order.by=as.POSIXct(data[,1])) 

#choose closing price and name them 
zamkniecie1<-Wine$"Last Close"
zamkniecie2<-RNVA$RNVA.Close
zamkniecie3<-EUR$Zamkniecie

#MARGE! into one
all.in.one<-merge(zamkniecie1, zamkniecie2, zamkniecie3)

Thanks for your help!

`merge(merge(zamkniecie1, zamkniecie2, by = "dates") , zamkniecie3, by = "dates")` is the way I can think of, assuming the three time series all have the `dates` column — Psidom, Apr 23 '16 at 22:03

score 1 · Answer 1 · answered Apr 23 '16 at 22:07

I think the problem you are having is that your xts time index for zamkniecie3 has a "PST" timezone appended to it. You can convert it with as.Date and then merge successfully.

index(zamkniecie3) <- as.Date(index(zamkniecie3))

#MERGE! into one
all.in.one <- merge(zamkniecie1, zamkniecie2, zamkniecie3)

score 1 · Answer 2 · answered Apr 25 '16 at 00:04

The problem is that Quandl and getSymbols both return xts objects with a Date classed index, but you create EUR with a POSIXct classed index.

The Date class does not have a timezone, and is therefore treated as if it is in the UTC timezone. as.POSIXct uses your local timezone by default, so the EUR object has a different timezone than the other two objects.

Here's a simplified version of your code that avoids this issue by using read.zoo, which correctly infers that the CSV downloaded from stooq.pl has a Date classed index.

library('quantmod')
library('Quandl')
# download data (.xts) using Quandl
Wine <- Quandl("LSE/WINE", type="xts")
# download data from Yahoo!Finance (.xts)
RNVA <- getSymbols("RNVA", auto.assign=FALSE)
# download data from stooq.pl and change them into xts
EUR <- as.xts(read.zoo("http://stooq.pl/q/d/l/?s=EUR&i=d", header=TRUE, sep=","))
# merge into one
all.in.one <- merge(Wine[,"Last Close"], RNVA[,"RNVA.Close"], EUR$Zamkniecie)

If you just want to update the offending line, you need to change your call to as.POSIXct to as.Date, or add tz="UTC" to your as.POSIXct call.

# download data from stooq.pl and change them into xts
data <- read.csv("http://stooq.pl/q/d/l/?s=EUR&i=d",header=TRUE)
EUR <- xts(data[,-1], order.by=as.Date(data[,1]))
# or:
EUR <- xts(data[,-1], order.by=as.POSIXct(data[,1], tz="UTC"))

Thanks for your detailed comment! It really helps when you understand the problem. — Baska, Apr 25 '16 at 21:17
@Baska: it's not a comment, it's an answer. This is a comment. Also, please read, [What should I do when someone answers my question?](http://stackoverflow.com/help/someone-answers). — Joshua Ulrich, Apr 26 '16 at 00:23

How can I merge 3 time series in R?

2 Answers2