merge xts objects with suffixes dynamically

user_4436572 0
Viewed 66

The task is to dynamically merge multiple xts objects into one big blob with suffixes, where suffixes are essentially the column names for the xts objects.

Sample Data:

library(xts)
a <- data.frame(alpha=1:10, beta=2:11)
xts1 <- xts(x=a, order.by=Sys.Date() - 1:10)
b <- data.frame(alpha=3:12, beta=4:13)
xts2 <- xts(x=b, order.by=Sys.Date() - 1:10)
c <- data.frame(alpha=5:14, beta=6:15)
xts3 <- xts(x=c, order.by=Sys.Date() - 1:10)

Static way of merging:

$> merge.zoo(xts1, xts2, xts3, suffixes=c("A", "B", "C"))
# output
           alpha.A beta.A alpha.B beta.B alpha.C beta.C
2022-03-11      10     11      12     13      14     15
2022-03-12       9     10      11     12      13     14
2022-03-13       8      9      10     11      12     13
2022-03-14       7      8       9     10      11     12
2022-03-15       6      7       8      9      10     11
2022-03-16       5      6       7      8       9     10
2022-03-17       4      5       6      7       8      9
2022-03-18       3      4       5      6       7      8
2022-03-19       2      3       4      5       6      7
2022-03-20       1      2       3      4       5      6

I might have more than 3 xts objets and 3 is just an arbitrary demonstration.

I've tried do.call but my do.call attempt failed with/out suffixes because I can't wrap up the 3 xts objects into a data structure that's accepted by do.call as a list (in R's language it should be a vector of 3 items).

do.call Demo:

# do.call with xts objects as separate args and suffixes, works
do.call(merge.zoo, list(xts1, xts2, xts3, suffixes=c("A", "B", "C")))

# do.call with xts objects wrapped up as list and suffixes, failed

# because R takes each element of list as vector and essentially a list of xts objects is a list of 3 lists, each of which has a xts object.

xts.list <- list(xts1, xts2, xts3)

# check data type
class(xts.list[[1]]) # output: xts,zoo
class(xts.list[1]) # output: list

# do.call failed attempt
do.call(merge.zoo, list(xts.list, suffixes=c("A", "B", "C")))
# Error Message
Error in zoo(structure(x, dim = dim(x)), index(x), ...) : 
“x” : attempt to define invalid zoo object

In other words, if I can unpack the list into dynamic number of arguments, I'd be able to get this idea to work; however I can't seem to find a way to either unpack arguments in R or some other solutions.

Disclaimer: The ultimate problem I am trying to solve is to be able to plot the time series data in the multi-panel view eventually; ggplot does not work with most of the packages I am using on a daily basis.

Disclaimer 2: merge.xts ignores suffixes (a bug), merge.zoo is the working alternative. For more information take a look here

xts zoo r
2 Answers

We can pass everything in a list i.e.

library(zoo)
c(xts.list, list(suffixes=c("A", "B", "C")))

Now, use the merge in do.call

do.call(merge.zoo, c(xts.list, list(suffixes=c("A", "B", "C"))))

-output

            alpha.A beta.A alpha.B beta.B alpha.C beta.C
2022-03-11      10     11      12     13      14     15
2022-03-12       9     10      11     12      13     14
2022-03-13       8      9      10     11      12     13
2022-03-14       7      8       9     10      11     12
2022-03-15       6      7       8      9      10     11
2022-03-16       5      6       7      8       9     10
2022-03-17       4      5       6      7       8      9
2022-03-18       3      4       5      6       7      8
2022-03-19       2      3       4      5       6      7
2022-03-20       1      2       3      4       5      6

Note that the first argument to merge is variadic component (... - which can take one or more xts data, where as all the other components are named and that is the reason we are creating the list with names only for those components i.e. suffixes. According to ?merge

merge(..., all = TRUE, fill = NA, suffixes = NULL, join = "outer", retside = TRUE, retclass = "xts", tzone = NULL, drop=NULL, check.names=NULL)

Thus, when we want to append a list i.e. xts.list with another list element, wrap the second named vector in a list and then just concatenate. It is similar to

> c(list(1), list(a = 1, b = 2))
[[1]]
[1] 1

$a
[1] 1

$b
[1] 2

and not the following as this create a nested list

> list(list(1), list(a = 1, b = 2))
[[1]]
[[1]][[1]]
[1] 1


[[2]]
[[2]]$a
[1] 1

[[2]]$b
[1] 2
user_37322710

This is now (finally) fixed in this commit. Thanks for the nudge to get this fixed!

library(xts)
idx <- Sys.Date() - 1:10
x1 <- xts(cbind(alpha = 1:10, beta = 2:11), idx)
x2 <- xts(cbind(alpha = 3:12, beta = 4:13), idx)
x3 <- xts(cbind(alpha = 5:14, beta = 6:15), idx)

suffixes <- LETTERS[1:3]

merge(x1, x2, x3, suffixes = suffixes)
##            alpha.A beta.A alpha.B beta.B alpha.C beta.C
## 2022-05-13      10     11      12     13      14     15
## 2022-05-14       9     10      11     12      13     14
## 2022-05-15       8      9      10     11      12     13
## 2022-05-16       7      8       9     10      11     12
## 2022-05-17       6      7       8      9      10     11
## 2022-05-18       5      6       7      8       9     10
## 2022-05-19       4      5       6      7       8      9
## 2022-05-20       3      4       5      6       7      8
## 2022-05-21       2      3       4      5       6      7
## 2022-05-22       1      2       3      4       5      6
user_2716160
Related