Merging xts objects with some common columns-CodePudding

I am back-testing a trading strategy on the members of a stock index. I have historical stock price date for every years. I would like to merge these time-series objects. My problem is that the index constituents are not the same , therefore I could not simple rbind them. Merging is not an option as well, because it creates separate columns for the same stocks from different years. Could someone suggest me a solution?

Here is an example of my problem: xts1:

                                  AAPL    AMZ   AA    AXP     
              11/01/2020          100     85     90    70     
              12/01/2020          105     70     80    90

xts2:

                                  AAPL    AM    AXP     BA
              01/01/2021          108     75     80     50
              02/01/2021          110     60     70     60

final xts:

                                  AAPL    AMZ   AA    AXP     BA
              11/01/2020          100     85     90    70     NA
              12/01/2020          105     70     80    90     NA
              01/01/2021          108     75     NA    80     50
              02/01/2021          110     60     NA    70     60

CodePudding user response：

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

CodePudding user response：

Using xts1 and xts2 shown in reproducible form in the Note at the end we can combine them using c and cbind.

Merge <- function(xts1, xts2) {

  nms1 <- names(xts1)
  nms2 <- names(xts2)
  both <- intersect(nms1, nms2)

  cbind(c(xts1[, both], xts2[, both]), 
        xts1[, !nms1 %in% both], 
        xts2[, !nms2 %in% both])
}

x <- Merge(xts1, xts2); x

giving this xts object:

           AAPL AXP AMZ AA AM BA
2020-11-01  100  70  85 90 NA NA
2020-12-01  105  90  70 80 NA NA
2021-01-01  108  80  NA NA 75 50
2021-02-01  110  70  NA NA 60 60

Also note that xts supports yearmon class which represents a year and month internally as year fraction where the fraction is 0 for Jan, 1/12 for Feb, ..., 11/12 for Dec and it displays as shown below.

aggregate(x, as.yearmon)

giving:

         AAPL AXP AMZ AA AM BA
Nov 2020  100  70  85 90 NA NA
Dec 2020  105  90  70 80 NA NA
Jan 2021  108  80  NA NA 75 50
Feb 2021  110  70  NA NA 60 60

Note

library(xts)

Lines1 <- "                      AAPL    AMZ   AA    AXP     
              11/01/2020          100     85     90    70     
              12/01/2020          105     70     80    90     "
xts1 <- read.zoo(text = Lines1, format = "%m/%d/%Y", index = 0) |>
  as.xts()

Lines2 <- "                                  AAPL    AM    AXP     BA
              01/01/2021          108     75     80     50
              02/01/2021          110     60     70     60 " 
xts2 <- read.zoo(text = Lines2, format = "%m/%d/%Y", index = 0) |>
  as.xts()