I'm looking for a simpler way to update an xts time series object using data in another xts object. Data for overlapping time periods & dimensions should be updated, additional time periods should be added, and missing series dimensions should be added as needed. Currently I'm using a combination of merge, subset, and assignment. Is there a way to do this in fewer steps?
Two xts times series objects with one dimension in common (y) and two time periods in common (2018 Q2 and 2018 Q3).
library(xts)
t <- as.yearqtr(paste(2018, 1:4, sep = ":Q"), format = "%Y:Q%q")
short <- xts(
matrix(1, ncol = 2, nrow = 2, dimnames = list(NULL, c("x", "y"))),
order.by = t[2:3]
)
long <- xts(
matrix(0, ncol = 2, nrow = 4, dimnames = list(NULL, c("y", "z"))),
order.by = t
)
short
x y
2018 Q2 1 1
2018 Q3 1 1
long
y z
2018 Q1 0 0
2018 Q2 0 0
2018 Q3 0 0
2018 Q4 0 0
Desired result for case 1: updating short with long
x y z
2018 Q1 NA 0 0
2018 Q2 1 0 0
2018 Q3 1 0 0
2018 Q4 NA 0 0
Desired result for case 2: updating long with short
x y z
2018 Q1 NA 0 0
2018 Q2 1 1 0
2018 Q3 1 1 0
2018 Q4 NA 0 0
Merge for non-overlapping dimensions, then subset and assign for overlapping dimensions (as in: Updating an XTS object)
short2 <- short
for (j in setdiff(colnames(long), colnames(short2))) {
short2 <- merge(short2, long[, j])
}
short3 <- short2
for (j in intersect(colnames(short3), colnames(long))) {
short3[index(long), j] <- long[, j]
}
short3
x y z
2018 Q1 NA 0 0
2018 Q2 1 0 0
2018 Q3 1 0 0
2018 Q4 NA 0 0
Same approach: merge for non-overlapping series dimensions, then subset and assign for overlapping dimensions
long2 <- long
for (j in setdiff(colnames(short), colnames(long2))) {
long2 <- merge(long2, short[, j])
}
long3 <- long2
for (j in intersect(colnames(short), colnames(long3))) {
long3[index(short), j] <- short[, j]
}
long3
y z x
2018 Q1 0 0 NA
2018 Q2 1 0 1
2018 Q3 1 0 1
2018 Q4 0 0 NA
Is there anything simpler than this two-step process? Perhaps a function or option from another package.
There's no way in R to merge while assigning priorities to columns that share the same name. I had a similar question a while ago. R must produce unique column names by default. You can directly assign columns a common name afterward using setNames, but R will always assign unique names (see ?make.names for some explanation). This is not recommended though as it will make actions after that much more complicated.
Manipulating ts and xts objects is also complicated. It can be done, but isn't really worth the time. It's best to convert to data.frame or tibble and do your business in those formats, then convert back.
The following is a tidyverse solution and also uses the timetk package.
library(xts)
library(timetk)
library(dplyr)
xts::merge.xts(long, short) %>% #merge xts objects using merge.xts
timetk::tk_tbl() %>% #convert xts object to tibble
dplyr::mutate(y = dplyr::coalesce(y.1, y)) %>% #replace y with coalesced y & y.1
dplyr::select(-y.1) %>% #deselect y.1
timetk::tk_xts(silent = T) #convert back to xts
y z x
2018 Q1 0 0 NA
2018 Q2 1 0 1
2018 Q3 1 0 1
2018 Q4 0 0 NA