Add missing date rows

[vkatti]

Suppose I have monthly data df with missing rows as below. Notice that the last monthly row is missing.

library(dplyr, warn.conflicts = FALSE)
library(lubridate, warn.conflicts = FALSE)
set.seed(123)
df <- tibble(col1 = seq.Date(lubridate::as_date("2019-01-01"), length.out = 12L, by = "month"), 
             col2 = sample(5:500, 12),
             col3 = sample(1:1000, 12),
             col4 = rnorm(12, mean = 5)))
(df <- df[-c(2,4,6, 12),]) # removed rows 2, 4, 6 and last row 12

How do i ensure that all the missing rows are added back with Zero values? I tried using tsibble::fill_gaps() but that doesn't help to add the last missing row? Is there any way apart from doing a left-join with another tibble with just the full monthly dates?

[gabriel.de.wit]

Hi, try tidyr::complete:

library(dplyr, warn.conflicts = FALSE)
library(tidyr)
library(lubridate, warn.conflicts = FALSE)
set.seed(123)

date_sequence <- seq.Date(lubridate::as_date("2019-01-01"), length.out = 12L, by = "month")

df <- tibble(col1 = date_sequence, 
             col2 = sample(5:500, 12),
             col3 = sample(1:1000, 12),
             col4 = rnorm(12, mean = 5))

df2 <- df[-c(2,4,6, 12),] # removed rows 2, 4, 6 and last row 12


df3 <- tidyr::complete(df2, col1 = date_sequence, fill = list(col2 = 0L, col3 = 0L, col4 = 0))

[vkatti]

Thank you so much. Somehow I missed tidyr::complete ()