Fill in missing dates in a dataframe

Question

I have two dataframes, interest rates and monthly standard deviation prices returns, that I have managed to merge together. However the interest rate data has gaps in its dates where the markets were not open, i.e. weekends and holidays. The monthly returns all start on the first of the month so where this lines up with a market closure the data doesn't merge correctly. An example of the dataframes is

Date        Rollingstd
01/11/2014  0.00925
01/10/2014  0.01341

Date        InterestRate
03/11/2014  2
31/10/2014  1.5

As you can see there is no 01/11/2014 in the interest rate data so merging together gives me

Date        InterestRate  Rollingstd
03/11/2014  2             0.01341
31/10/2014  1.5           0.01341

I guess a fix for this would be to expand the interest rate dataframe so that it includes all dates and just fill the interest rate data up so it looks like this

Date        InterestRate
03/11/2014  2
02/11/2014  1.5
01/11/2014  1.5
31/10/2014  1.5

This would ensure there are no missing dates in the dataframe. Any ideas on how I could do this?

Answer 1

Do you want this?

df2 <- read.table(text = 'Date        InterestRate
03/11/2014  2
31/10/2014  1.5', header = T)

df1 <- read.table(text = 'Date        Rollingstd
01/11/2014  0.00925
01/10/2014  0.01341', header = T)

library(tidyverse)

df1 %>% full_join(df2, by = 'Date') %>%
  mutate(Date = as.Date(Date, '%d/%m/%Y')) %>%
  arrange(Date) %>%
  complete(Date = seq.Date(min(Date), max(Date), 'days')) %>%
  fill(InterestRate, .direction = 'up') %>%
  as.data.frame()

#>          Date Rollingstd InterestRate
#> 1  2014-10-01    0.01341          1.5
#> 2  2014-10-02         NA          1.5
#> 3  2014-10-03         NA          1.5
#> 4  2014-10-04         NA          1.5
#> 5  2014-10-05         NA          1.5
#> 6  2014-10-06         NA          1.5
#> 7  2014-10-07         NA          1.5
#> 8  2014-10-08         NA          1.5
#> 9  2014-10-09         NA          1.5
#> 10 2014-10-10         NA          1.5
#> 11 2014-10-11         NA          1.5
#> 12 2014-10-12         NA          1.5
#> 13 2014-10-13         NA          1.5
#> 14 2014-10-14         NA          1.5
#> 15 2014-10-15         NA          1.5
#> 16 2014-10-16         NA          1.5
#> 17 2014-10-17         NA          1.5
#> 18 2014-10-18         NA          1.5
#> 19 2014-10-19         NA          1.5
#> 20 2014-10-20         NA          1.5
#> 21 2014-10-21         NA          1.5
#> 22 2014-10-22         NA          1.5
#> 23 2014-10-23         NA          1.5
#> 24 2014-10-24         NA          1.5
#> 25 2014-10-25         NA          1.5
#> 26 2014-10-26         NA          1.5
#> 27 2014-10-27         NA          1.5
#> 28 2014-10-28         NA          1.5
#> 29 2014-10-29         NA          1.5
#> 30 2014-10-30         NA          1.5
#> 31 2014-10-31         NA          1.5
#> 32 2014-11-01    0.00925          2.0
#> 33 2014-11-02         NA          2.0
#> 34 2014-11-03         NA          2.0

^{Created on 2021-05-23 by the reprex package (v2.0.0)}