CODEWITHSUNDEEP
r
Anshul S
Anshul S

Reputation: 281

Populate missing dates using groupby another column

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?

Upvotes: 2

Views: 35

Answers (1)

Bas
Bas

Reputation: 4658

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

Upvotes: 2

Related Questions