R: How to fill out the date that does not have any case?

Question

I have a data frame like,

db1 <- data.frame(date=as.Date(c('2020/01/01','2020/01/03','2020/01/07')),case=c(1,3,4))

I want to fill out the date like,

db2 <- data.frame(date=as.Date(c('2020/01/01','2020/01/02','2020/01/03','2020/01/04','2020/01/05','2020/01/06','2020/01/07')),case=c(1,0,3,0,0,0,4))

Please tell me how to do.

Ronak Shah · Accepted Answer · 2020-03-14T07:22:02.080

We can use tidyr::complete to complete the missing dates.

tidyr::complete(db1, date = seq(min(date), max(date), by = "1 day"), 
                 fill = list(case = 0))

#   date        case
#  <date>     <dbl>
#1 2020-01-01     1
#2 2020-01-02     0
#3 2020-01-03     3
#4 2020-01-04     0
#5 2020-01-05     0
#6 2020-01-06     0
#7 2020-01-07     4

Another way with dplyr could be :

library(dplyr)

db1 %>%
  slice(-n()) %>%
  tidyr::uncount(case + 1) %>%
  group_by(date) %>%
  mutate(newdate = date + row_number() - 1, 
         case = replace(case, row_number() != 1, 0)) %>%
  bind_rows(db1 %>% slice(n()) %>% rename(newdate = date)) %>%
  ungroup() %>%
  select(-date)

R: How to fill out the date that does not have any case?

1 Answers1