[Solved] Complete missing values in time series using previous day data – using R

by


If I understand correctly, the trick is that you want to fill downward except for the bottommost NAs. And the problem with tidyr‘s fill is that it goes all the way down.

This isn’t a fully-tidyverse solution, but for this data:

library(dplyr)
library(tidyr)
data <- tribble(
  ~Date, ~time_series_1, ~time_series_2,
  as.Date("2019-01-01"), NA, 10,
  as.Date("2019-02-01"), 5, NA,
  as.Date("2019-03-01"), 10, NA,
  as.Date("2019-04-01"), 20, 6,
  as.Date("2019-05-01"), 30, NA,
  as.Date("2019-06-01"), NA, 8,
  as.Date("2019-07-01"), 7, NA,
  as.Date("2019-08-01"), 5, NA,
  as.Date("2019-09-01"), NA, NA,
  as.Date("2019-10-01"), NA, NA
)

You can determine the ending date for each time series separately:

LastTS1Date <- with( data, max(Date[!is.na(time_series_1)])) 
LastTS2Date <- with( data, max(Date[!is.na(time_series_2)]))

And then use baseR filter syntax to only change the part of the data frame that goes up to those dates:

data[data$Date <= LastTS1Date,] <-
  data[data$Date <= LastTS1Date,] %>% fill(time_series_1)

data[data$Date <= LastTS2Date,] <-
  data[data$Date <= LastTS2Date,] %>% fill(time_series_2)

solved Complete missing values in time series using previous day data – using R