R cbind xts objects results in added/duplicate row

Question

I want to merge three xts objects together with cbind:

> OIH.tmp <-
structure(c(NA, 7.7, 5.1, -6.9, -2.6), index = structure(c(1325221200, 
1327986000, 1330491600, 1333080000, 1334894400), tzone = "", tclass = "yearmon"),
tclass = "Date", tzone = "", src = "yahoo", updated = structure(1335041586.83363,
class = c("POSIXct", "POSIXt")), .indexTZ = "", .indexCLASS = "yearmon",
.Dim = c(5L, 1L), .Dimnames = list(NULL, "OIH"), class = c("xts", "zoo"))

> SMH.tmp <-
structure(c(NA, 9.3, 2.9, 3.7, -5), index = structure(c(1325134800, 
1327986000, 1330491600, 1333080000, 1334894400), tzone = "", tclass = "yearmon"),
tclass = "Date", tzone = "", src = "yahoo", updated = structure(1335041596.41175,
class = c("POSIXct", "POSIXt")), .indexTZ = "", .indexCLASS = "yearmon",
.Dim = c(5L, 1L), .Dimnames = list(NULL, "SMH"), class = c("xts", "zoo"))

> SU.tmp <-
structure(c(NA, -9.4, -6.9, -2.3, -18.1, -22.6, 22.7, -6.1, -4, 
18, 4.1, -9.4, -4.7), index = structure(c(1304049600, 1306814400, 
1309406400, 1311912000, 1314763200, 1317355200, 1320033600, 1322629200, 
1325221200, 1327986000, 1330491600, 1333080000, 1334894400), tzone = "",
tclass = "yearmon"), tclass = "Date", tzone = "", src = "yahoo", 
updated = structure(1335041613.0055, class = c("POSIXct", "POSIXt")),
.indexTZ = "", .indexCLASS = "yearmon", .Dim = c(13L, 1L),
.Dimnames = list(NULL, "SU"), class = c("xts", "zoo"))

> cbind(OIH.tmp, SU.tmp, SMH.tmp)
          OIH    SU  SMH
Apr 2011   NA    NA   NA
May 2011   NA  -9.4   NA
Jun 2011   NA  -6.9   NA
Jul 2011   NA  -2.3   NA
Aug 2011   NA -18.1   NA
Sep 2011   NA -22.6   NA
Oct 2011   NA  22.7   NA
Nov 2011   NA  -6.1   NA
Dec 2011   NA    NA   NA
Dec 2011   NA  -4.0   NA
Jan 2012  7.7  18.0  9.3
Feb 2012  5.1   4.1  2.9
Mar 2012 -6.9  -9.4  3.7
Apr 2012 -2.6  -4.7 -5.0

Notice that there is an additional/duplicate row for Dec 2011, which I don't want. I can think of messy ways to achieve my end goal (here) but I'm sure there must be something more simple/elegant - merge by the index of one object perhaps. This seems simple enough but I've read through documentation for cbind and merge and have not found a simple solution.

I actually have a series of objects that I want to combine/merge. I just used the 3 you see here to illustrate the problem. I actually use the following command to construct the return series:

oneMonthReturn <- do.call(merge, lapply(tickers.tmp, function(x) 
  round(ROC(Cl(to.monthly(get(x, myEnv))),1) * 100, 1) ))

> dput(tickers.tmp)
c("DJI", "GSPC", "IXIC", "GSPTSE", "XLE", "OIH", "XOP", "XLI", 
"XLB", "XLF", "XRT", "XLK", "SMH", "XLY", "XLP", "XLU", "XLV", 
"PPH", "MOO", "GLD", "SLV", "GDX", "TLT", "X", "SU", "TCK", "ACHN", 
"IDIX", "AGU")


> dput(oneMonthReturn)
structure(c(NA, -1.9, -1.2, -2.2, -4.5, -6.2, 9.1, 0.8, NA, 1.4, 
3.3, 2.5, 2, -1.4, NA, -1.4, -1.8, -2.2, -5.8, -7.4, 10.2, -0.5, 
NA, 0.8, 4.3, 4, 3.1, -2.1, NA, -1.3, -2.2, -0.6, -6.6, -6.6, 
10.6, -2.4, NA, -0.6, 7.7, 5.3, 4.1, -3, NA, -1, -3.7, -2.7, 
-1.4, -9.4, 5.3, -0.4, NA, -2.1, 4.1, 1.5, -2, -2, NA, -4.3, 
-2.3, 1.4, -10.8, -16, 17.5, 1.7, NA, -2.5, 2.2, 5.8, -4.3, -4, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 7.7, 5.1, -6.9, -2.6, 
NA, -4.5, -3.5, 5.7, -14.8, -22.5, 22.8, 2.4, NA, -4.6, 3.5, 
8.3, -4.1, -7.7, NA, -2.8, -1, -7.1, -6.8, -10.3, 13.4, 1.4, 
NA, -0.4, 7.1, 2.8, 0.5, -1.8, NA, -2.8, -1, -3.5, -7.3, -18.5, 
16, 0.2, NA, -3, 10.4, -0.6, 0, -1.3, NA, -3.4, -3.1, -3.6, -10.1, 
-12.5, 13.4, -5.2, NA, 1.5, 7.8, 4.9, 6.8, -3.9, NA, 1.5, -1.4, 
-0.2, -7.1, -7.1, 12.9, -1.3, NA, 1.3, 4.8, 6.5, 3.9, -0.5, NA, 
-1.1, -2.9, 0.4, -5.5, -3.5, 9.7, -1.5, NA, -0.7, 6, 6.9, 4.1, 
-3, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 9.3, 2.9, 3.7, -5, 
NA, -0.3, -0.6, -1.4, -5.4, -7.5, 11.3, -0.7, NA, 0.7, 5.7, 4.4, 
4.3, -1, NA, 2.5, -3.4, -1.3, 0.2, -4.1, 4.5, 2.7, NA, 1.8, -1.4, 
3.7, 2.5, 0.7, NA, 2.1, -1.2, -0.9, 2.1, -0.8, 3.6, 1, NA, 2.2, 
-3.7, 0.6, 0.4, -0.1, NA, 2.4, -1.6, -4, -2.1, -5.1, 5.6, 0.9, 
NA, 2.4, 3.1, 1.1, 3.9, -0.9, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 0, 2.5, 3, 0.2, NA, -2.4, -3.1, 0.6, -3.6, -18.9, 14.6, 
-1.6, NA, -4.4, 8.5, 2.5, 0.4, -1.5, NA, -1.8, -2.5, 8.1, 11.6, 
-11.7, 5.7, 1.7, NA, -11.3, 10.8, -3, -1.3, -1.6, NA, -22.1, 
-10.5, 13.8, 4, -33.6, 14.6, -4.4, NA, -17.2, 18.1, 3.9, -6.7, 
-2, NA, -6.7, -6.3, 4.1, 9.9, -12.9, 6.4, 2.7, NA, -16.1, 9.3, 
-1.9, -11.2, -7.2, NA, 2.9, -2.7, 4, 8.9, 12.1, -4.2, 1.7, NA, 
2.8, -0.3, -2.9, -4.6, 4.2, NA, -3.4, -0.2, -14.1, -28.4, -31.3, 
14.2, 7.4, NA, -3.1, 13.2, -10.4, 7.6, -1.3, NA, -9.4, -6.9, 
-2.3, -18.1, -22.6, 22.7, -6.1, NA, -4, 18, 4.1, -9.4, -4.7, 
NA, -3.1, -3.5, -2.6, -10.9, -41.8, 31.8, -9.4, NA, -3.6, 18.4, 
-5.7, -11.4, 3.5, NA, 29.4, -0.7, -0.4, -18.8, -26.3, 29.2, 5.1, 
NA, 13.6, 37.5, -5.5, -9.2, -14.6, NA, -6.7, 5.1, 29, -14, -15.2, 
18.4, 23.6, NA, -2, 58.6, -12.8, -18.5, -16.9, NA, -2.7, -0.3, 
-0.4, -1.6, -25.4, 21.1, -16.2, NA, -4.2, 17.9, 5.9, 1.4, 0.2
), .Dim = c(14L, 29L), .Dimnames = list(NULL, c("DJI", "GSPC", 
"IXIC", "GSPTSE", "XLE", "OIH", "XOP", "XLI", "XLB", "XLF", "XRT", 
"XLK", "SMH", "XLY", "XLP", "XLU", "XLV", "PPH", "MOO", "GLD", 
"SLV", "GDX", "TLT", "X", "SU", "TCK", "ACHN", "IDIX", "AGU")), index = structure(c(1304049600, 
1306814400, 1309406400, 1311912000, 1314763200, 1317355200, 1320033600, 
1322629200, 1325134800, 1325221200, 1327986000, 1330491600, 1333080000, 
1334894400), tzone = "", tclass = "yearmon"), .indexTZ = "", .indexCLASS = "yearmon", tclass = c("POSIXct", 
"POSIXt"), tzone = "", src = "yahoo", updated = structure(1335041583.80238, class = c("POSIXct", 
"POSIXt")), class = c("xts", "zoo"))

Appreciate the help.

Answer 1

The issue with your call to to.monthly, not with merge.xts (as I originally thought). This solution will not work with the version of xts currently on CRAN, but it will work with revision 613 or greater from R-forge.

The problem arises because to.monthly aligns the series on the last time of the period in the actual index, not the theoretical last time of the period; and it does not drop the time component of the index by default. In your case, the last time of 2011-12 is 2011-12-29 for SMH and 2011-12-30 for the other two objects.

If you set drop.time=TRUE (again this only current works with the R-forge version of xts), the results are as you expect:

oneMonthReturn <- do.call(merge, lapply(tickers.tmp, function(x) 
  round(ROC(Cl(to.monthly(get(x, myEnv),drop.time=TRUE)),1) * 100, 1) ))

Answer 2

For a multiple merge (using Dirk's data) with many columns you can use the Reduce function. I wrapped up the usage in a function that you could tweak to meet your needs. Credit for answering this question correctly should go to Dirk as he answered correctly already, this is just an alternative method as I was bored on a Saturday night :) Shoot, I don't even know what xts is for.

multi.xts.merge <- function(listOguys) {
    dat <- Reduce(function(x, y) {merge.xts(x, y)}, listOguys)
    names(dat) <- as.character(substitute(listOguys))[-1]
    return(dat)
}

multi.xts.merge(list(OIH, SU, SMH))

Note that you have to supply a list to this function

Answer 3

This should do it (on simpler daya as you didn't post a dput() of your data):

 newvar <- merge(merge(OIH.tmp, SU.tmp), SMH.tmp)

merge.xts() only takes two arguments so you have to call it repeatedly. The default aggregations "do the right thing" here:

R> OIH <- xts(c(NA, 1:4), 
+             order.by=seq(as.Date("2011-12-01"), by="1 month", length=5))
R> SMH <- xts(c(NA, 1:4), 
+             order.by=seq(as.Date("2011-12-01"), by="1 month", length=5))
R> SU <- xts(c(NA, 1:12), 
+            order.by=seq(as.Date("2011-04-01"), by="1 month", length=13))
R> merge(OIH, merge(SU, SMH))
           OIH SU SMH
2011-04-01  NA NA  NA
2011-05-01  NA  1  NA
2011-06-01  NA  2  NA
2011-07-01  NA  3  NA
2011-08-01  NA  4  NA
2011-09-01  NA  5  NA
2011-10-01  NA  6  NA
2011-11-01  NA  7  NA
2011-12-01  NA  8  NA
2012-01-01   1  9   1
2012-02-01   2 10   2
2012-03-01   3 11   3
2012-04-01   4 12   4
R>