merging irregular time ser

Question

I want to merge multiple (>2) time series into a single object. For example, here are multiple time series (w missing data, incomplete, and irregular):

library(tidyverse)
library(zoo)

A <- tibble(
  date = seq(ymd("1927-10-01"), ymd("1927-10-07"), by = "days"),
  flow = sample.int(100, 7))
B <- tibble(
  date = seq(ymd("1927-10-01"), ymd("1927-10-07"), by = "days"),
  flow = c(48, 71, NA, 8, 92, 22, 49))
C <- tibble(
  date = seq(ymd("1927-10-03"), ymd("1927-10-05"), by = "days"),
  flow = sample.int(100, 3))
D <- tibble(
  date = ymd("1927-10-02", "1927-10-03", "1927-10-05", "1927-10-06", "1927-10-07"),
  flow = sample.int(100, 5))

I tried the following...

merge.zoo(A, B, C, D)

A and B are fine, but C is misaligned and D fails to address the missing data for 1927-10-04.

I'd like to get a data frame or tibble with a single column for 'date' (sequential, no missing dates) and four columns with the flow data labeled 'A', 'B', etc.

Thinking that the issue may be with the format of the time series I'm trying to merge, I tried creating my example time series using tsibble(), but got the comparable results, I also tried using zoo(), but get an error:

A <- zoo(
  date = seq(ymd("1927-10-01"), ymd("1927-10-07"), by = "days"),
  flow = sample.int(100, 7))

I've tried many different approaches (cbind, merge, full_join, bind_cols, ts.union, ts.intersect)--I expect many of these, possibly all, might work but didn't for me. What am I missing?

Thanks!

Answer 1

1) merge.zoo merge.zoo is used with zoo objects so convert the input to zoo, perform the merge and convert back. Omit the last line if you want a zoo object rather than a data frame result.

library(tibble)
library(lubridate)
library(zoo)

L <- lst(A, B, C, D)

L %>% 
  lapply(read.zoo) %>% # create list of zoo objects
  do.call(what = "merge") %>% 
  fortify.zoo(name = "date") # convert zoo to data frame

##         date  A  B  C  D
## 1 1927-10-01 34 48 NA NA
## 2 1927-10-02 18 71 NA 35
## 3 1927-10-03 63 NA 38  8
## 4 1927-10-04 70  8 72 NA
## 5 1927-10-05 16 92 29 46
## 6 1927-10-06 68 22 NA 40
## 7 1927-10-07 88 49 NA 45

2) Base R Create the same list L and then rename the second column to A, B, C and D of the components respectively and finally use Reduce to repeatedly invoke the merges.

L <- mget(c("A", "B", "C", "D"))

lapply(names(L), \(nm) setNames(L[[nm]], c("date", nm))) |>
  Reduce(f = \(...) merge(..., all = TRUE))

Answer 2

Create an artificial data.frame a.

p = ls(pattern = '^[A-Z]$')
r = range(do.call('rbind', mget(p))$date)
a = data.frame(date = seq.Date(r[1], r[2], by='days'))

Long

merge and rbind.

1) Base R (preferred)

l = lapply(mget(p), merge, y=a, by='date', all=TRUE)
l = Map(`[<-`, l, 'source', value=names(l)) |> 
  do.call(what='rbind') |> `rownames<-`(NULL)

2) {collapse}

l = lapply(mget(p), merge, y=a, by='date', all=TRUE) |>
  collapse::rowbind(idcol='source')

3) {dplyr}

l = lapply(mget(p), merge, y=a, by='date', all=TRUE) |>
  dplyr::bind_rows(.id='source')

> head(l)
        date flow source
1 1927-10-01   98      A
2 1927-10-02   30      A
3 1927-10-03   67      A
4 1927-10-04   37      A
5 1927-10-05   27      A

Wide

merge and cbind.

l = cbind(a, lapply(mget(p), \(i) merge(i, a, all=TRUE)$flow) |>
            do.call(what='cbind')) |> setNames(c('date', p))

> l
        date  A  B  C  D
1 1927-10-01 80 48 NA NA
2 1927-10-02 61 71 NA 52
3 1927-10-03 70 NA 11 72
4 1927-10-04  8  8 70 NA
5 1927-10-05 57 92 33 70
6 1927-10-06  1 22 NA 46
7 1927-10-07 38 49 NA 55

Note

• You haven't set a seed. Results aren't comparable.
• I currently do not see any reason for external packages.