Merge arbitrary number of xts objects of different lengths by index

Question

I'd like to merge 3 xts objects of different lengths into one xts with 3 columns and indexed by the first xts object's dates.

a_xts <- xts(4:10, Sys.Date()+4:10)
b_xts <- xts(c(7:12), Sys.Date()+4:9)
c_xts <- xts(15:18, Sys.Date()+4:7)

I'd like to preserve a_xts index dates. The merged xts should look like:

           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

My solution is to merge each one by one making a new xts object for each addition:

ab_xts <- merge(a_xts,b_xts, all = c(TRUE,FALSE))
abc_xts <- merge(ab_xts, c_xts, all = c(TRUE,FALSE))

Is there a better way to do this without converting to .zoo and back to .xts objects?

Edit

when merging all xts objects, I'd like to keep only the rows that match the index dates for a_xts without adding any additional index dates. For example:

d_xts <- xts(c(7:18), Sys.Date()+4:15

d_xts has greater length than a_xts, while b_xts and c_xts are less. Continuing my solution by adding xts objects one by one gets me there but seems inefficient.

> abcd_xts <- merge(abc_xts, d_xts, all = c(TRUE,FALSE))
> abcd_xts
           a_xts b_xts c_xts d_xts
2021-03-05     4     7    15     7
2021-03-06     5     8    16     8
2021-03-07     6     9    17     9
2021-03-08     7    10    18    10
2021-03-09     8    11    NA    11
2021-03-10     9    12    NA    12
2021-03-11    10    NA    NA    13

score 0 · Answer 1 · answered Mar 01 '21 at 16:54

0

We can place it in a list and use merge with Reduce

out <- Reduce(merge, mget(ls(pattern = '_xts$')))
colnames(out) <- paste0(letters[1:3], "_xts")

answered Mar 01 '21 at 16:54

akrun

874,273
37
540
662

G. Grothendieck · Accepted Answer · 2021-03-01T21:55:17.723

0

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

edited Mar 01 '21 at 21:55

answered Mar 01 '21 at 16:56

G. Grothendieck

254,981
17
203
341

As my edit explains, I need only the rows containing `a_xts` index dates. This solution will add index dates when adding an xts object with a greater length than `a_xts`. – Rod Purkey Mar 01 '21 at 21:13
Use `merge(a_xts, b_xts, c_xts)[time(a_xts), ]` (although in the example in the question it makes no difference). – G. Grothendieck Mar 01 '21 at 21:20
`merge(a_xts, b_xts, c_xts)[time(a_xts), ]` works. Can you post answer and I'll accept? – Rod Purkey Mar 01 '21 at 21:34

score 0 · Answer 3 · answered Mar 03 '21 at 18:07

I've discovered that merge() can left outer join on multiple xts objects with argument all = c(TRUE,FALSE). This matches only the rows from the first xts passed to merge().

> merge(a_xts,b_xts,c_xts,d_xts, all = c(TRUE,FALSE))

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

Merge arbitrary number of xts objects of different lengths by index

3 Answers3