1

I need to combine two xts objects of intraday OHLC data, a and b. One starts earlier, the other ends later and they overlap. What I need is a new xts object c that starts at the first period of a and ends with the last period of b. If for one period both a and b provide OHCLV a has priority, else take either of a and b.

For example: 

 > a
##                           a.Open       a.High       a.Low       a.Close       a.Volume
## 2014-02-18 09:55:00       184.14       184.46      184.07        184.11        5712100
## 2014-02-18 10:55:00       184.12       184.28      183.65        184.17       13912700
## 2014-02-18 11:55:00       184.17       184.49      184.12        184.38        7251000
## 2014-02-18 12:55:00       184.38       184.45      184.18        184.26        5521100
## 2014-02-18 13:55:00       184.26       184.40      184.11        184.15        4700300
## 2014-02-18 15:55:00       184.40       184.47      184.26        184.37       10324400
## 2014-02-18 16:05:00       184.38       184.38      184.20        184.24       20452900
## 2014-02-19 09:55:00       183.69       184.29      183.69        184.24        5445800
## 2014-02-19 10:55:00       184.25       184.95      184.16        184.74       18007800

> b
##                             b.Open         b.High         b.Low         b.Close        b.Volume
## 2014-02-14 15:55:00         183.96         184.06        183.15          183.99         18607278
## 2014-02-14 16:05:00         183.99         184.00        183.98          183.99            52504
## 2014-02-18 09:55:00         184.16         184.28        184.11          184.15          3932026
## 2014-02-18 10:55:00         184.14         184.49        184.12          184.40          7570591
## 2014-02-18 11:55:00         184.40         184.45        184.18          184.35          5201513
## 2014-02-18 12:55:00         184.35         184.40        184.11          184.16          4870456
## 2014-02-18 13:55:00         184.15         184.48        184.13          184.41          5824933
## 2014-02-18 14:55:00         184.41         184.47        184.26          184.42          7617463
## 2014-02-18 15:55:00         184.43         184.43        183.98          184.29          9575112
## 2014-02-18 16:00:00         184.27         184.27        184.26          184.27             1990

should result in: 

> c
##                        c.Open       c.High       c.Low       c.Close       c.Volume
## 2014-02-14 15:55:00       183.96       184.06      183.15        183.99       18607278
## 2014-02-14 16:05:00       183.99       184.00      183.98        183.99          52504                   
## 2014-02-18 09:55:00       184.14       184.46      184.07        184.11        5712100
## 2014-02-18 10:55:00       184.12       184.28      183.65        184.17       13912700
## 2014-02-18 11:55:00       184.17       184.49      184.12        184.38        7251000
## 2014-02-18 12:55:00       184.38       184.45      184.18        184.26        5521100
## 2014-02-18 13:55:00       184.26       184.40      184.11        184.15        4700300
## 2014-02-18 14:55:00         184.41         184.47        184.26          184.42          7617463
## 2014-02-18 15:55:00       184.40       184.47      184.26        184.37       10324400
## 2014-02-18 16:05:00       184.38       184.38      184.20        184.24       20452900
## 2014-02-19 09:55:00       183.69       184.29      183.69        184.24        5445800
## 2014-02-19 10:55:00       184.25       184.95      184.16        184.74       18007800

Notice that the missing values for the 14:55:00 period in a are filled with the values from b.

marco
  • 73
  • 1
  • 8

1 Answers1

0

This isn't really a merge question/issue. You really want to rbind all the values from a, with those from b when a does not have an observation for a given time.

a <- structure(c(184.14, 184.12, 184.17, 184.38, 184.26, 184.4, 184.38, 
183.69, 184.25, 184.46, 184.28, 184.49, 184.45, 184.4, 184.47, 184.38,
184.29, 184.95, 184.07, 183.65, 184.12, 184.18, 184.11, 184.26, 184.2,
183.69, 184.16, 184.11, 184.17, 184.38, 184.26, 184.15, 184.37, 184.24,
184.24, 184.74, 5712100, 13912700, 7251000, 5521100, 4700300, 10324400,
20452900, 5445800, 18007800), .Dim = c(9L, 5L), .Dimnames = list(NULL,
c("a.Open", "a.High", "a.Low", "a.Close", "a.Volume")),
index = structure(c(1392738900, 1392742500, 1392746100, 1392749700,
1392753300, 1392760500, 1392761100, 1392825300, 1392828900), tzone = "",
tclass = c("POSIXct", "POSIXt")), class = c("xts", "zoo"),
.indexCLASS = c("POSIXct", "POSIXt"), tclass = c("POSIXct", "POSIXt"),
.indexTZ = "", tzone = "")

b <- structure(c(183.96, 183.99, 184.16, 184.14, 184.4, 184.35, 184.15, 
184.41, 184.43, 184.27, 184.06, 184, 184.28, 184.49, 184.45, 184.4, 184.48,
184.47, 184.43, 184.27, 183.15, 183.98, 184.11, 184.12, 184.18, 184.11,
184.13, 184.26, 183.98, 184.26, 183.99, 183.99, 184.15, 184.4, 184.35,
184.16, 184.41, 184.42, 184.29, 184.27, 18607278, 52504, 3932026, 7570591,
5201513, 4870456, 5824933, 7617463, 9575112, 1990), .Dim = c(10L, 5L),
.Dimnames = list(NULL, c("b.Open", "b.High", "b.Low", "b.Close", "b.Volume")),
index = structure(c(1392414900, 1392415500, 1392738900, 1392742500,
1392746100, 1392749700, 1392753300, 1392756900, 1392760500, 1392760800),
tzone = "", tclass = c("POSIXct", "POSIXt")), class = c("xts", "zoo"),
.indexCLASS = c("POSIXct", "POSIXt"), tclass = c("POSIXct", "POSIXt"),
.indexTZ = "", tzone = "")

We can simply subset b to only have observations that aren't in a, then call rbind:

> rbind(a, b[!(index(b) %in% index(a))])
                    a.Open a.High  a.Low a.Close a.Volume
2014-02-14 15:55:00 183.96 184.06 183.15  183.99 18607278
2014-02-14 16:05:00 183.99 184.00 183.98  183.99    52504
2014-02-18 09:55:00 184.14 184.46 184.07  184.11  5712100
2014-02-18 10:55:00 184.12 184.28 183.65  184.17 13912700
2014-02-18 11:55:00 184.17 184.49 184.12  184.38  7251000
2014-02-18 12:55:00 184.38 184.45 184.18  184.26  5521100
2014-02-18 13:55:00 184.26 184.40 184.11  184.15  4700300
2014-02-18 14:55:00 184.41 184.47 184.26  184.42  7617463
2014-02-18 15:55:00 184.40 184.47 184.26  184.37 10324400
2014-02-18 16:00:00 184.27 184.27 184.26  184.27     1990
2014-02-18 16:05:00 184.38 184.38 184.20  184.24 20452900
2014-02-19 09:55:00 183.69 184.29 183.69  184.24  5445800
2014-02-19 10:55:00 184.25 184.95 184.16  184.74 18007800
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418