How to merge xts objects with slightly different columns?

Question

Given various one-row xts objects:

z1 = xts(t(c("9902"=0,"9903"=0,"9904"=0,"9905"=2,"9906"=2)),as.Date("2015-01-01"))
z2 = xts(t(c("9902"=3,"9903"=4,"9905"=6,"9906"=5,"9908"=8)),as.Date("2015-01-02"))
z3 = xts(t(c("9901"=1,"9903"=3,"9905"=5,"9906"=6,"9907"=7,"9909"=9)),as.Date("2015-01-03"))

I want to merge them into a single xts object. But cbind(z1,z2,z3) gives:

           X9902 X9903 X9904 X9905 X9906 X9902.1 X9903.1 X9905.1 X9906.1 X9908 X9901 X9903.2 X9905.2 X9906.2 X9907 X9909
2015-01-01     0     0     0     2     2      NA      NA      NA      NA    NA    NA      NA      NA      NA    NA    NA
2015-01-02    NA    NA    NA    NA    NA       3       4       6       5     8    NA      NA      NA      NA    NA    NA
2015-01-03    NA    NA    NA    NA    NA      NA      NA      NA      NA    NA     1       3       5       6     7     9

Whereas what I expect is:

           9901 9902 9903 9904 9905 9906 9907 9908 9909
2015-01-01    0    0    0    0    2    2    0    0    0
2015-01-02    0    3    4    0    6    5    0    8    0
2015-01-03    1    0    3    0    5    6    7    0    9

(I can get the NAs changed to zeroes with giving fill=0, i.e. cbind(z1,z2,z3,fill=0).)

rbind(z1,z2,z3) complains that the rows have different number of columns. But, I believe if if the missing columns were added to each xts object in advance that this would be a good approach?

The real data may have 1000s of rows, and a few hundred columns (once merged), so I've got one eye on efficiency.

`merge.xts` and `merge.zoo` only merge by index, so you can't get your desired result using `merge` (or `cbind`). So it looks like you do need `rbind`, but (as you say) it will require all the objects have the same number of columns in the same order. — Joshua Ulrich, Jan 03 '16 at 18:23

score 7 · Accepted Answer · answered Jan 03 '16 at 18:43

As I mentioned in my comment, merge.xts (and merge.zoo) only merge by index, so you can't get your desired result using merge (or cbind). So it looks like you do need rbind, but (as you say) it will require all the objects have the same number of columns in the same order.

I've created two functions below to help process the objects so you can rbind them to create your desired result.

# put all xts objects in a list for easier processing
x <- list(z1, z2, z3)

# function to create template xts object
template <- function(xlist) {
  # find set of unique column names from all objects
  cn <- unique(unlist(lapply(xlist, colnames)))
  # create template xts object
  # using a date that doesn't occur in the actual data
  minIndex <- do.call(min, lapply(xlist, function(x) index(x[1L,])))
  # template object
  xts(matrix(0,1,length(cn)), minIndex-1, dimnames=list(NULL, sort(cn)))
}

# function to apply to each xts object
proc <- function(x, template) {
  # columns we need to add
  neededCols <- !(colnames(template) %in% colnames(x))
  # merge this object with template object, filling w/zeros
  out <- merge(x, template[,neededCols], fill=0)
  # reorder columns (NB: merge.xts always uses make.names)
  # and remove first row (from template)
  out <- out[-1L,make.names(colnames(template))]
  # set column names back to desired values
  # (using attr<- because dimnames<-.xts copies)
  attr(out, "dimnames") <- list(NULL, colnames(template))
  # return object
  out
}
(res <- do.call(rbind, lapply(x, proc, template=template(x))))
#            9901 9902 9903 9904 9905 9906 9907 9908 9909
# 2015-01-01    0    0    0    0    2    2    0    0    0
# 2015-01-02    0    3    4    0    6    5    0    8    0
# 2015-01-03    1    0    3    0    5    6    7    0    9

Thanks; I think I will go with this. Do you think there would be a significant difference in performance to make one huge empty matrix up front, and copy in to that, compared to re-making each xts object, then calling rbind? (As matrices in R are column-major ordered, I assume rbind needs to do that kind of copy, anyway?) — Darren Cook, Jan 03 '16 at 20:26
@DarrenCook: Not off the top of my head. It may be faster, but I'd have to profile to verify either way. `rbind` does do that kind of copy, but it can use `memcopy`, which is usually faster than calling R functions. — Joshua Ulrich, Jan 03 '16 at 21:16

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

library(xts)
library(plyr)

z1df <- as.data.frame(z1)
z2df <- as.data.frame(z2)
z3df <- as.data.frame(z3)

res <- rbind.fill(z1df, z2df, z3df)
res[is.na(res)] <- 0
res

#  9902 9903 9904 9905 9906 9908 9901 9907 9909
#1    0    0    0    2    2    0    0    0    0
#2    3    4    0    6    5    8    0    0    0
#3    0    3    0    5    6    0    1    7    9

This is similar to the following stackoverflow post

combining two data frames of different lengths

Include the date column

res$Date <- c("2015-01-01", "2015-01-02", "2015-01-03") # the appropriate values
res$Date <- as.Date(res$Date)

And transform to xts object

xts(res[,-10], order.by=res[,10])

edited May 23 '17 at 12:32

Community

1
1

answered Jan 03 '16 at 18:05

steveb

5,382
2
27
36

2

`rbind.fill` isn't in xts. Your result also isn't an xts object. – Joshua Ulrich Jan 03 '16 at 18:06
@JoshuaUlrich Thanks. – steveb Jan 03 '16 at 18:18
The updated solution assumes the times in each object are sequential and non-overlapping, which is likely not the case. Applied to actual data, it would likely assign the wrong dates to each row. – Joshua Ulrich Jan 03 '16 at 18:22
Thanks; an interesting approach. (BTW, in my case, the date column would be non-overlapping, and could be made sequential.) – Darren Cook Jan 04 '16 at 17:27

How to merge xts objects with slightly different columns?

2 Answers2