I have to xts/zoo objects. each has measures of different variables over a different time span. I want to create a single time series comprising all measures at all times, with NAs for missing dates/variable combinations. how do I do that? 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:
a b c
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)
a b 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)
a b
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?
This is not a general solution. But works for this example:
cbind(rbind(xs[,1],ys[,1]), cbind(xs[,-1],ys[,-1]))
a b c
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
Just remind 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]))
a b c
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 incommon, you will have duplicated index in your final xts
object.For example, if we replace y :
rownames(y) = as.character(Sys.Date()-3:5)
You get , a duplicated index for 2013-03-14
, So im anot sure that it s a valid xts
object.
merge(rbind(xs[,1],ys[,1]), merge(xs[,-1],ys[,-1]))
a b c
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 a generalization of the solution:
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]))
a b c
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