How do I merge two XTS by date and value in R?

Question

So I want to merge two XTS based on date and value. Suppose one of them is like this.

a <- c(1,2,3,4)
b <- c(2,4,6,8)
c <- c(3,6,9,12)
x <- xts(cbind(a,b,c),order.by=as.Date(c("2015-01-02","2015-01-05","2015-01-06","2015-01-07")))
x

So we get this...

           a b  c
2015-01-02 1 2  3
2015-01-05 2 4  6
2015-01-06 3 6  9
2015-01-07 4 8 12

The second XTS is like this...

d <- c("b","a","c","b")
e <- c(10,20,10,30)
y <- xts(cbind(d,e),order.by=as.Date(c("2015-01-02","2015-01-05","2015-01-05","2015-01-06")))
y

So we get this (would be nice to get rid of the quotation marks too)...

           d   e   
2015-01-02 "b" "10"
2015-01-05 "a" "20"
2015-01-05 "c" "10"
2015-01-06 "b" "30"

I would like to get this result...

           d   e     val
2015-01-02 "b" "10"  2
2015-01-05 "a" "20"  2
2015-01-05 "c" "10"  6
2015-01-06 "b" "30"  6

Ronak Shah · Accepted Answer · 2019-11-17T04:03:50.780

2

We can use match to create a matrix with row/column index to subset from x.

library(zoo)
y$val <- as.character(coredata(x)[cbind(match(index(y), index(x)), 
                                        match(y$d, names(x)))])
y
#            d   e    val
#2015-01-02 "b" "10" "2"
#2015-01-05 "a" "20" "2"
#2015-01-05 "c" "10" "6"
#2015-01-06 "b" "30" "6"

edited Nov 17 '19 at 04:03

answered Nov 17 '19 at 03:54

Ronak Shah

377,200
20
156
213

Hold on, this isn't right. The output should be y not x. – Pasha S Nov 17 '19 at 03:57
@PashaS yes, updated the answer. The quotes are there around because you have characters as well as numbers in them so everything gets coerced into characters. – Ronak Shah Nov 17 '19 at 04:06
Great. Thanks! Anyway I can get the quotes off? I need to average the "val" column by the "e" values. – Pasha S Nov 17 '19 at 04:08
If you remove the `d` column then you can convert `y` to numeric. – Ronak Shah Nov 17 '19 at 04:15

How do I merge two XTS by date and value in R?

1 Answers1