Fill gaps between dates in xts

Question

So I have a very basic question. Let's say we have a few date gaps in a time series object, and I want to fill those gaps with an arbitrary value. For example let's say we have:

i <- c(seq.Date(from = as.Date("2015-01-01", format = "%Y-%m-%d"), to = as.Date("2016-01-01", format = "%Y-%m-%d"), by = "month"),
       seq.Date(from = as.Date("2017-01-01", format = "%Y-%m-%d"), to = as.Date("2018-01-01", format = "%Y-%m-%d"),by = "month"))

ts <- xts(rep(0,length(i)), order.by = i)

            [,1]
2015-01-01    0
2015-02-01    0
2015-03-01    0
2015-04-01    0
2015-05-01    0
2015-06-01    0
2015-07-01    0
2015-08-01    0
2015-09-01    0
2015-10-01    0
2015-11-01    0
2015-12-01    0
2016-01-01    0
2017-01-01    0
2017-02-01    0
2017-03-01    0
2017-04-01    0
2017-05-01    0
2017-06-01    0
2017-07-01    0
2017-08-01    0
2017-09-01    0
2017-10-01    0
2017-11-01    0
2017-12-01    0
2018-01-01    0

What I wish to achieve is "fill" the time series ts for all months between two arbitrary dates i.e. start.date and end.date with a 1. Any suggestions?

My attempt:

  if(index(ts)[1] > start.date){
    len.aux <- length(seq(from = start.date, to = index(ts)[1] %m-% months(1), by = "month"))
    ts <- c(xts(rep(1, len.aux), order.by = seq.Date(from = start.date, to = index(ts)[1] %m-% months(1), by = "month")), ts)
  } 
  if(index(ts)[length(ts)] < end.date){
    len.aux <- length(seq(from = index(ts)[length(ts)] %m+% months(1), to = end.date, by = "month"))
    ts <- c(ts, xts(rep(1, len.aux), order.by = seq.Date(from = index(ts)[length(ts)] %m+% months(1), to = end.date, by = "month")))
  }

However, this only fills the gaps for the "tails" of the series and doesn't fill gaps in between.

Thanks for the help!

Please note that this is only a minimal working example of my issue

you mean it should fill all the dates from feb 2016 to dec 2016 and fill 1 as the value? — Stupid_Intern, Mar 23 '19 at 03:13
All the dates between an arbitrary start and end date which are not accounted for in the original series. So we could say from Jan 15 until October 18 (by month) — migueldva, Mar 23 '19 at 03:16
Possible duplicate of [Insert rows for missing dates/times](https://stackoverflow.com/questions/16787038/insert-rows-for-missing-dates-times) — phiver, Mar 23 '19 at 08:53

score 0 · Answer 1 · answered Mar 26 '19 at 09:14

You may use the tsibble package:

library(tsibble)
i <- c(seq.Date(from = as.Date("2015-01-01", format = "%Y-%m-%d"), to = as.Date("2016-01-01", format = "%Y-%m-%d"), by = "month"),
       seq.Date(from = as.Date("2017-01-01", format = "%Y-%m-%d"), to = as.Date("2018-01-01", format = "%Y-%m-%d"),by = "month"))

tsibble(datetime = yearmonth(i), 
        value    = 0, index = datetime) %>% 
  fill_gaps(value = 1) %>% 
  View()

The yearmonth function will ensure that the index is monthly (daily is default). The function fill_gaps will then include missing months and will set missing values in column value to 1 (default is NA).

Fill gaps between dates in xts

1 Answers1