Merging a Large List of Xts Objects

How do I merge a large list of xts objects via loop / function in R?

This can be accomplished by getting a character vector of all the objects with names ending in .oc, putting them in a list, then calling merge via do.call.

# Make up some data
set.seed(21)
x.oc <- xts(rnorm(10), Sys.Date()-10:1)
y.oc <- xts(rnorm(10), Sys.Date()-10:1)
z.oc <- xts(rnorm(10), Sys.Date()-10:1)
x <- y <- z <- 1:10

objNames <- ls(pattern="*oc$")    # object names
objList <- lapply(objNames, get)  # list of objects
names(objList) <- objNames        # assign names to list
do.call(merge, objList)           # merge all objects in list

Using this method would be easier if you loaded the xts objects into a list (objList) as you received them from the API. Then you would only need do.call(merge, objList).

Merging many lists of different XTS objects in R

We could do this with Map - as the list of xts elements have the same tickers in the same order, just use Map instead of creating a list of lists

library(xts)
out <- Map(merge.xts, L, Marubozu, SMA)

merging a large list of xts objects

I use the strategy provided by Dominik in his answer to this question

I have turned it into a function in my qmao package. This code is also at the core of getSymbols.FI in the FinancialInstrument package.

do.call.rbind <- function(lst) {
  while(length(lst) > 1) {
    idxlst <- seq(from=1, to=length(lst), by=2)
    lst <- lapply(idxlst, function(i) {
      if(i==length(lst)) { return(lst[[i]]) }
      return(rbind(lst[[i]], lst[[i+1]]))
    })
  }
  lst[[1]]
}

If you want to rbind data.frames, @JoshuaUlrich has provided an elegant solution here

---

As far as I can tell (without looking very closely) memory is not an issue with any of the three solutions offered (@JoshuaUlrich's, @Alex's, and qmao::do.call.rbind). So, it comes down to speed...

library(xts)
l <- lapply(Sys.Date()-6000:1, function(x) {
    N=60*8;xts(rnorm(N),as.POSIXct(x)-seq(N*60,1,-60))})
GS <- do.call.rbind
JU <- function(x) Reduce(rbind, x)
Alex <- function(x) do.call(rbind, lapply(x, as.data.frame)) #returns data.frame, not xts

identical(GS(l), JU(l)) #TRUE

library(rbenchmark)
benchmark(GS(l), JU(l), Alex(l), replications=1)
     test replications elapsed relative user.self sys.self user.child sys.child
3 Alex(l)            1  89.575 109.9080    56.584   33.044          0         0
1   GS(l)            1   0.815   1.0000     0.599    0.216          0         0
2   JU(l)            1 209.783 257.4025   143.353   66.555          0         0

do.call.rbind clearly wins on speed.

Merging multiple XTS objects by index

Joshua answer the question in the comments. Posting solution for the sake of having the Q answered. It is as simple as this:

XTSM.T <- merge(XTS1,XTS2,XTS3, all=TRUE)

or

XTSM.F <- merge(XTS1,XTS2,XTS3, all=FALSE)

How to merge all xts objects in an environment

Since you want the entire object, you can simply convert the environment to a list. Then merge using do.call(), like you've already done. For example:

library(quantmod)
e <- new.env()
getSymbols("IBM;MSFT", env = e)
pframe <- do.call(merge, as.list(e))
head(pframe)
#            IBM.Open IBM.High IBM.Low IBM.Close IBM.Volume IBM.Adjusted
# 2007-01-03    97.18    98.40   96.26     97.27    9196800     76.18299
# 2007-01-04    97.25    98.79   96.88     98.31   10524500     76.99751
# 2007-01-05    97.60    97.95   96.91     97.42    7221300     76.30047
# 2007-01-08    98.50    99.50   98.35     98.90   10340000     77.45959
# 2007-01-09    99.08   100.33   99.07    100.07   11108200     78.37598
# 2007-01-10    98.50    99.05   97.93     98.89    8744800     77.45180
#            MSFT.Open MSFT.High MSFT.Low MSFT.Close MSFT.Volume MSFT.Adjusted
# 2007-01-03     29.91     30.25    29.40      29.86    76935100      23.08097
# 2007-01-04     29.70     29.97    29.44      29.81    45774500      23.04231
# 2007-01-05     29.63     29.75    29.45      29.64    44607200      22.91091
# 2007-01-08     29.65     30.10    29.53      29.93    50220200      23.13508
# 2007-01-09     30.00     30.18    29.73      29.96    44636600      23.15826
# 2007-01-10     29.80     29.89    29.43      29.66    55017400      22.92637

Merging Multiple XTS Objects into a single XTS object

This can be easily done by using the quantmod add-on qmao. Take for instance several ticker symbols, download data (or import data from file ) as xts-objects and then create one object with prices or returns in one go. Let’s assume you want to create a correlation matrix from the daily returns:

library(qmao)
tickers <- c('MSFT','AAPL','AMZN')
getsSymbols(tickers, from = '2010-01-01')
# xts-object of daily returns (RF stands for `Return Frame`,PF returns a
# Price Frame)
returns <- RF(tickers, silent = TRUE, type = 'discrete')

> tail(returns)
                    MSFT         AAPL         AMZN
2017-01-10 -0.0003192848  0.001008513 -0.001279876
2017-01-11  0.0091025233  0.005373176  0.003920085
2017-01-12 -0.0091786360 -0.004175365  0.018297408
2017-01-13  0.0014374700 -0.001760998  0.004301657
2017-01-17 -0.0027113556  0.008064508 -0.009080505
2017-01-18 -0.0004797537 -0.000083350 -0.002766377

To get the correlation matrix:

> cor(returns,use = 'pairwise.complete.obs')
          MSFT      AAPL      AMZN
MSFT 1.0000000 0.4655617 0.4701170
AAPL 0.4655617 1.0000000 0.4390303
AMZN 0.4701170 0.4390303 1.0000000

Have a look at the functions PF and RF they are compact and incredibly useful.

Merging xts objects with some common columns

The order.by i.e. index of xts should be Date/Datetime class. According to `?xts

order.by - a corresponding vector of unique times/dates - must be of a known time-based class.

If the initial objects are xts and the dates are Date class, an option is to rbind after converting to data.frame with fortify.zoo

library(xts)
library(dplyr)
bind_rows(fortify.zoo(xt1), fortify.zoo(xt2))
       Index AAPL AMZ AA AXP BA
1 2020-11-01  100  85 90  70 NA
2 2020-12-01  105  70 80  90 NA
3 2021-01-01  108  75 NA  80 50
4 2021-02-01  110  60 NA  70 60

---

If we want to reconvert to xts

out <- bind_rows(fortify.zoo(xt1), fortify.zoo(xt2))
xts(out[-1], order.by = out$Index)
           AAPL AMZ AA AXP BA
2020-11-01  100  85 90  70 NA
2020-12-01  105  70 80  90 NA
2021-01-01  108  75 NA  80 50
2021-02-01  110  60 NA  70 60

data

xt1 <- structure(c(100L, 105L, 85L, 70L, 90L, 80L, 70L, 90L), .Dim = c(2L, 
4L), .Dimnames = list(NULL, c("AAPL", "AMZ", "AA", "AXP")), index = structure(c(1604188800, 
1606780800), tzone = "UTC", tclass = "Date"), class = c("xts", 
"zoo"))

xt2 <- structure(c(108L, 110L, 75L, 60L, 80L, 70L, 50L, 60L), .Dim = c(2L, 
4L), .Dimnames = list(NULL, c("AAPL", "AMZ", "AXP", "BA")), index = structure(c(1609459200, 
1612137600), tzone = "UTC", tclass = "Date"), class = c("xts", 
"zoo"))

Merge arbitrary number of xts objects of different lengths by index

We can use the following (although for the specific example in the question the [...] is not needed).

merge(a_xts, b_xts, c_xts)[time(a_xts), ]

giving:

           a_xts b_xts c_xts
2021-03-05     4     7    15
2021-03-06     5     8    16
2021-03-07     6     9    17
2021-03-08     7    10    18
2021-03-09     8    11    NA
2021-03-10     9    12    NA
2021-03-11    10    NA    NA

---

Related Topics