2

I want to include the missing dates in the data frame DF for each bodlane

DF:

Bodlane                      Day           Holiday
185_AB1                     2020-05-01      True
185_AB1                     2020-05-05      True
201_AB1                     2020-05-02      True
201_AB1                     2020-05-05      True

Output:

Bodlane                      Day           Holiday
185_AB1                     2020-05-01      True
185_AB1                     2020-05-02      NA
185_AB1                     2020-05-03      NA
185_AB1                     2020-05-04      NA
185_AB1                     2020-05-05      True
201_AB1                     2020-05-02      True
201_AB1                     2020-05-03      NA
201_AB1                     2020-05-04      NA
201_AB1                     2020-05-05      True

Can someone help me in getting the missing dates for each bodlane?

Anshul S
  • 281
  • 1
  • 5

1 Answers1

2

The complete() function in the tidyverse does what you want:

library(tidyverse)

df <- read_table("Bodlane                      Day           Holiday
185_AB1                     2020-05-01      True
185_AB1                     2020-05-05      True
201_AB1                     2020-05-02      True
201_AB1                     2020-05-05      True")

df %>% 
  group_by(Bodlane) %>% 
  complete(Day = seq.Date(min(Day), max(Day), by = "d")) %>%
  ungroup()

which gives

  Bodlane Day        Holiday
  <chr>   <date>     <lgl>  
1 185_AB1 2020-05-01 TRUE   
2 185_AB1 2020-05-02 NA     
3 185_AB1 2020-05-03 NA     
4 185_AB1 2020-05-04 NA     
5 185_AB1 2020-05-05 TRUE   
6 201_AB1 2020-05-02 TRUE   
7 201_AB1 2020-05-03 NA     
8 201_AB1 2020-05-04 NA     
9 201_AB1 2020-05-05 TRUE
Bas
  • 4,628
  • 1
  • 14
  • 16
  • It's taking a lot of time as I have records in millions. Can it be done in a faster way? – Anshul S Apr 29 '20 at 11:56
  • I'm not a `data.table` expert, but maybe that would speed things up: https://stackoverflow.com/questions/43483497/data-table-equivalent-of-tidyrcomplete – Bas Apr 29 '20 at 13:30