In R, I am getting multiple duplicated rows in merge.xts. How do I not?

Question

I have a list, with each member being a stock, and various fields of historical bloomberg data. For example:

> head(isData[[2]])
        date CUR_MKT_CAP OPERATING_INCOME_SEQ_GROWTH OPER_ROE PX_LAST SHORT_AND_LONG_TERM_DEBT
1 1999-12-31          NA                          NA       NA   62.33                       NA
2 2000-01-01          NA                          NA       NA   62.33                       NA
3 2000-01-02          NA                          NA       NA   62.33                       NA
4 2000-01-03          NA                          NA       NA   62.93                       NA
5 2000-01-04          NA                          NA       NA   61.88                       NA

I am creating from this list 5 xts objects, each containing the particular data point of every stock at every day in the past:

oigIS <- do.call(merge.xts, lapply(isData, function(x) {
  if(!(is.null(x$OPERATING_INCOME_SEQ_GROWTH)))
    xts(x$OPERATING_INCOME_SEQ_GROWTH/100, order.by = x[,1])
}))

This works well, creating a structure as I would expect:

> head(oigIS)[,1:5]
           ALPI.IS.Equity ACC.IS.Equity AHOT.IS.Equity AJP.IS.Equity X3M.IS.Equity
1999-12-31             NA            NA             NA            NA      1.712294
2000-01-01             NA            NA             NA            NA      1.712294
2000-01-02             NA            NA             NA            NA      1.712294
2000-01-03             NA            NA             NA            NA      1.712294
2000-01-04             NA            NA             NA            NA      1.712294
2000-01-05             NA            NA             NA            NA      1.712294

HOWEVER, this creates duplicate entries on some dates:

> head(oigIS['200301/200302'],10)[,1:5]
           ALPI.IS.Equity ACC.IS.Equity AHOT.IS.Equity AJP.IS.Equity X3M.IS.Equity
2003-01-01             NA            NA             NA            NA      0.133008
2003-01-01             NA            NA             NA            NA            NA
2003-01-02             NA            NA             NA            NA      0.133008
2003-01-02             NA            NA             NA            NA            NA
2003-01-03             NA            NA             NA            NA      0.133008
2003-01-03             NA            NA             NA            NA            NA
2003-01-04             NA            NA             NA            NA      0.133008
2003-01-04             NA            NA             NA            NA            NA
2003-01-05             NA            NA             NA            NA      0.133008
2003-01-05             NA            NA             NA            NA            NA

So how do I stop this??

I assumed the time must not match up or some such nonsense, so I tried to strip out the time:

oigIS <- do.call(merge.xts, lapply(isData, function(x) {
  if(!(is.null(x$OPERATING_INCOME_SEQ_GROWTH)))
    xts(x$OPERATING_INCOME_SEQ_GROWTH/100, order.by = as.Date(format(x[,1], '%Y-%m-%d')))
}))

But this did not work:

> head(oigIS['200301/200302'],10)[,8:13]
           X3M.IS.Equity ALST.IS.Equity AKOP.IS.Equity ADCIC.IS.Equity ABAN.IS.Equity AMRJ.IS.Equity
2003-01-01      0.133008             NA             NA        0.086917             NA       0.175158
2003-01-01            NA             NA             NA              NA             NA             NA
2003-01-02      0.133008             NA             NA        0.086917             NA       0.175158
2003-01-02            NA             NA             NA              NA             NA             NA
2003-01-03      0.133008             NA             NA        0.086917             NA       0.175158
2003-01-03            NA             NA             NA              NA             NA             NA
2003-01-04      0.133008             NA             NA        0.086917             NA       0.175158
2003-01-04            NA             NA             NA              NA             NA             NA
2003-01-05      0.133008             NA             NA        0.086917             NA       0.175158
2003-01-05            NA             NA             NA              NA             NA             NA

Any ideas???

You generally should not call methods directly. You should use `do.call(merge, ...)` instead of `do.call(merge.xts, ...)`. The issue appears to be related to differing timezones, but your second attempt should have avoided that. Without a [reproducible example](http://stackoverflow.com/q/5963269/271616), I can't provide more help. — Joshua Ulrich, May 29 '16 at 13:33
I dont understand this: You generally should not call methods directly. You should use do.call(merge, ...) instead of do.call(merge.xts, ...). What do you mean method? merge.xts is a method and merge is not? — lukehawk, May 31 '16 at 12:01
`merge` is a S3 generic function. `merge.xts` is a S3 method, just like `merge.zoo`, `merge.data.frame`, `merge.default`, etc. You should use the generic function and let the S3 dispatch mechanism determine which method to call. Assume you switch to using a data.frame instead of xts objects. Your current could would break, but would continue to work if you use the generic function. — Joshua Ulrich, May 31 '16 at 12:10
Oh. Thanks. So are you saying that by using do.call(merge,...) the 'S3 dispatch mechanism' will determine it is an xts, and use the merge.xts method? — lukehawk, May 31 '16 at 12:22

In R, I am getting multiple duplicated rows in merge.xts. How do I not?

0 Answers0