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.

Please add to the question, some way to easily construct the data. One easy way for you (but not entirely human-readable) is to paste the outputs of dput(OIH.tmp), etc. Unless you already have human-readable commands, of course. I don't have a solution, but I am interested in the question. — Matthew Lundberg, Apr 22 '12 at 00:53
@MatthewLundberg - thanks for the quick reply. I just updated the body of my post with more information if that helps. Appreciate it. — codingknob, Apr 22 '12 at 01:39
This seems to be a bug in `merge.xts` because `merge(as.zoo(OIH.tmp), as.zoo(SU.tmp), as.zoo(SMH.tmp))` works. I'll investigate. — Joshua Ulrich, Apr 22 '12 at 09:53

score 3 · Answer 1 · answered Apr 22 '12 at 01:00

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>

answered Apr 22 '12 at 01:00

Dirk Eddelbuettel

360,940
56
644
725

Am I correct in assuming that the order of the merges does not matter? – Matthew Lundberg Apr 22 '12 at 01:06
Correct, as I just verified by playing with the same data. Easy here as we only have two different length. – Dirk Eddelbuettel Apr 22 '12 at 01:19
@Dirk - thank you for the help and example. I just updated the body of my question with more data. Is there a simpler way of combining the objects without having to manually insert the 'NA's to the objects that are shorter in length? Appreciate the help. – codingknob Apr 22 '12 at 01:35
His example does use objects of differing lengths. – Matthew Lundberg Apr 22 '12 at 01:45
@MatthewLundberg - right. I'm looking for a more automated way to achieve this. I use the quantmod package a lot and many of the functions automatically insert/pad NAs when the data is not the same length so I was wondering if there is a way I can do the same here. – codingknob Apr 22 '12 at 01:49
Seems that padding is not necessary for merge(). This might be a disconnect between your example and the "real" data at the end of the edited question. I take it that, as written, oneMonthReturn is not as you desire (even with the merge). And of course, the final example isn't reproducible without knowing myEnv. – Matthew Lundberg Apr 22 '12 at 01:56
`merge.xts` and `merge.zoo` do _not_ only take two arguments. `merge(OIH, SMH, SU)` works just fine. – Joshua Ulrich Apr 22 '12 at 09:43
Ah, thanks. But they did at one point, didn't they? Or `merge.zoo()` does methinks. – Dirk Eddelbuettel Apr 22 '12 at 10:47
They may have, but a quick look at the repository shows they both have accepted >2 arguments since 2008-07-28 (`merge.xts` initially just wrapped `merge.zoo`). – Joshua Ulrich Apr 22 '12 at 12:47

score 2 · Accepted Answer · answered Apr 22 '12 at 13:04

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) ))

Tyler Rinker · Answer 3 · 2012-04-22T04:13:38.920

0

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

edited Apr 22 '12 at 04:13

answered Apr 22 '12 at 02:20

Tyler Rinker

108,132
65
322
519

This is useful for `merge.default` and `merge.data.frame`, since they can only merge two objects at a time. `merge.xts` and `merge.zoo` can merge more than two objects at a time, so the repeated calls are unnecessary. I haven't tested, but `do.call(merge.xts, list(OIH, SU, SMH))` is probably more efficient than the `Reduce` call. – Joshua Ulrich Apr 22 '12 at 09:41

R cbind xts objects results in added/duplicate row

3 Answers3