How to use data from two XTS to create a new XTS?

Question

Suppose I have the following two XTS...

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

and

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

These give me...

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

and

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

I'd like to get the following XTS...

            a  b
2015-01-02 10 20
2015-01-05 NA 10
2015-01-06 30 NA
2015-01-07 20 40

I tried a couple of different methods such as using the functions match and coredata but wasn't able to get the exact answer. I think maybe starting with the first XTS with blank values and then filling it in with the values from the second XTS will work best. Not sure how to do that though.

Because it is inherently a matrix and matrix can hold only single class — akrun, Nov 18 '19 at 22:17

akrun · Answer 1 · 2019-11-18T22:21:38.163

2

The issue is that xts object is also a matrix

is.matrix(x)
#[1] TRUE

According to ?matrix

as.matrix is a generic function. The method for data frames will return a character matrix if there is only atomic columns and any non-(numeric/logical/complex) column, applying as.vector to factors and format to other non-character columns. Otherwise, the usual coercion hierarchy (logical < integer < double < complex) will be used, e.g., all-logical data frames will be coerced to a logical matrix, mixed logical-integer will give a integer matrix, etc.

and matrix can hold only a single class. If there is single character element, it will change the whole dataset to character class

edited Nov 18 '19 at 22:21

answered Nov 18 '19 at 22:18

akrun

874,273
37
540
662

OK so there's no way to do it? – Pasha S Nov 18 '19 at 22:21
@PashaS. You can have a `data.frame` or a `list` which can store different types – akrun Nov 18 '19 at 22:23
1

OK i'll give it a try. Thanks. – Pasha S Nov 18 '19 at 22:25
@PashaS You may check `?tsibble` for storing time series objects with other column types – akrun Nov 18 '19 at 22:30

score 1 · Answer 2 · answered Nov 19 '19 at 02:14

1

This looks like reshaping of y object. It would be simple to do that if you convert it into dataframe, do reshaping/pre-processing with the dataframe and then convert it back to xts object.

library(tidyverse)
library(xts)

data.frame(y) %>%
  rownames_to_column('date') %>%
  mutate(date = as.Date(date, "X%Y.%m.%d")) %>%
  pivot_wider(names_from = c, values_from = d) %>%
  #For old tidyr use spread instead of pivot_wider
  #spread(c, d) %>%
  mutate_at(vars(a, b), ~as.integer(as.character(.)))  %>%
  column_to_rownames('date') %>%
  as.xts 

#            a  b
#2015-01-02 10 20
#2015-01-05 NA 10
#2015-01-06 30 NA
#2015-01-07 20 40

answered Nov 19 '19 at 02:14

Ronak Shah

377,200
20
156
213

Thanks. For some reason it can't find the functions pivot_wider or spread. – Pasha S Nov 19 '19 at 18:59
@PashaS Do you have `tidyverse` installed? Do `install.packages("tidyverse")` and then `library(tidyverse)` and check the above code. – Ronak Shah Nov 19 '19 at 23:45

How to use data from two XTS to create a new XTS?

2 Answers2