Concatenating / merging time series (in R)

Question

I have xts / zoo objects. each of them has different variables over a different period of time. I want to create a single time series that includes all measures at all times, with NA for missing dates / variable combinations. how to do it? artificial example:

library(xts) x<-cbind(a=1:3,b=3:1) rownames(x) = as.character(Sys.Date()-1:3) y<-cbind(a=5:7,c=3:1) rownames(y) = as.character(Sys.Date()-5:7) xs=as.xts(x) ys=as.xts(y) #now what? #desired outcome looks like: abc 2013-03-10 7 NA 1 2013-03-11 6 NA 2 2013-03-12 5 NA 3 2013-03-14 3 1 NA 2013-03-15 2 2 NA 2013-03-16 1 3 NA # regular merge looks like that (adding an a.1 variable) merge(xs,ys) ab a.1 c 2013-03-10 NA NA 7 1 2013-03-11 NA NA 6 2 2013-03-12 NA NA 5 3 2013-03-14 3 1 NA NA 2013-03-15 2 2 NA NA 2013-03-16 1 3 NA NA # simple concatenation ignores variable names and looks like that c(xs,ys) ab 2013-03-10 7 1 2013-03-11 6 2 2013-03-12 5 3 2013-03-14 3 1 2013-03-15 2 2 2013-03-16 1 3 # so what should I do? 

Answer 1

This is not a general solution. But it works for this example:

 cbind(rbind(xs[,1],ys[,1]), cbind(xs[,-1],ys[,-1])) abc 2013-03-10 7 NA 1 2013-03-11 6 NA 2 2013-03-12 5 NA 3 2013-03-14 3 1 NA 2013-03-15 2 2 NA 2013-03-16 1 3 NA 

Remember that cbind.xts is just merge.xts . S you can get the same result using merge

 merge(rbind(xs[,1],ys[,1]), merge(xs[,-1],ys[,-1])) abc 2013-03-10 7 NA 1 2013-03-11 6 NA 2 2013-03-12 5 NA 3 2013-03-14 3 1 NA 2013-03-15 2 2 NA 2013-03-16 1 3 NA 

The problem with this solution is that if ys and xs have some dates, you will have a duplicate index in your xts . For example, if we replace y:

 rownames(y) = as.character(Sys.Date()-3:5) 

You get a duplicate index for 2013-03-14 , so im anot sure this is a valid xts object.

 merge(rbind(xs[,1],ys[,1]), merge(xs[,-1],ys[,-1])) abc 2013-03-12 7 NA 1 2013-03-13 6 NA 2 2013-03-14 3 1 3 2013-03-14 5 NA NA 2013-03-15 2 2 NA 2013-03-16 1 3 NA 

EDIT solution generalization:

 inter <- intersect(names(ys), names(xs)) diffx <- setdiff(names(xs),inter) diffy <- setdiff(names(ys),inter) merge(rbind(xs[,inter],ys[,inter]), merge(xs[,diffx],ys[,diffy])) abc 2013-03-10 7 NA 1 2013-03-11 6 NA 2 2013-03-12 5 NA 3 2013-03-14 3 1 NA 2013-03-15 2 2 NA 2013-03-16 1 3 NA 

Answer 2

What you want => merge(data.frame(x,d),data.frame(y,d),by=c("d","a"),all=T )

You should use data.frame not names vectors / matrix, here is a general solution, you only need one liner with a full external connection (look at? Merge)

 x<-cbind(a=1:3,b=3:1) d= as.character(Sys.Date()-1:3) DT1 = data.frame(x,d) #DT1 # abd #1: 1 3 2013-03-16 #2: 2 2 2013-03-15 #3: 3 1 2013-03-14 y<-cbind(a=5:7,c=3:1) d = as.character(Sys.Date()-5:7) DT2 = data.frame(y,d) #DT2 # abd #1: 1 3 2013-03-12 #2: 2 2 2013-03-11 #3: 3 1 2013-03-10 merge(DT1,DT2,by=c("d","a"),all=T) # dabc #1 2013-03-10 7 NA 1 #2 2013-03-11 6 NA 2 #3 2013-03-12 5 NA 3 #4 2013-03-14 3 1 NA #5 2013-03-15 2 2 NA #6 2013-03-16 1 3 NA 

Answer 3

OK. thought about it for a while. because in the end I need to “merge” many such dataframes / xts into one, and not just merge two of them, I thought it would make sense to do all this in one step: create a large matrix of all date / var combinations. then inserting into this large matrix all the observed data, the object by object. the code is as follows (I will be glad to receive comments on it and, of course, without problems, without any guarantees):

 alltogether = function(dlist) { all.vars = unique(unlist(lapply(dlist,colnames))) all.obs = unique(unlist(lapply(dlist,rownames))) res = array(NA,dim=c(length(all.obs),length(all.vars)), dimnames=list(all.obs,all.vars)) for(d in dlist) { res[rownames(d),colnames(d)]=d } return(res) } alltogether.xts = function(xlist) { dlist = lapply(xlist,as.matrix) res = alltogether(dlist) xres = as.xts(res) return(xres) } 

Answer 4

I would say to convert it to a numeric array (as.numeric (ts)), combine it with cbind (ts1, ts2), and then go back to the time series, ts (c (as.numeric (ts1), as.numeric (TS2))