How to add missing month in a dataframe?

Surprisingly challenging. what you want is to include missing months into your vector. For this you have to compute all months between the first and last dates, then check which ones your data already contains.

library(lubridate)
mydates <- as_date(c("2001-05-04", "2001-05-30", "2001-07-15", "2001-10-20"))
n <- length(mydates)

mymonths <- month(mydates)
myyears <- year(mydates)
df <- data.frame(mydates, myyears, mymonths)

firstdate <- floor_date(min(mydates), unit="month")
lastdate <- floor_date(max(mydates), unit = "month")
nbmonths <- as.numeric(round((lastdate - firstdate)/(365.25/12)))

fulldates <- firstdate%m+% months(0:nbmonths)
fullmonths <- month(fulldates)
fullyears <- year(fulldates)
fullyearmonths <- paste(fullyears, fullmonths, sep='-')

toadd <- as_date(ym(fullyearmonths[!fullyearmonths %in% myyearmonths]))
result <- c(mydates, toadd)
result <- result[order(result)]
[1] "2001-05-04" "2001-05-30" "2001-06-01" "2001-07-15" "2001-08-01" "2001-09-01" "2001-10-20"

You may merge with an auxiliary data frame containing the missing dates. "Date" format is required. In a function f we use the range of the dates, use the substrings several times (i.e. cut away the days) concatenate them with the original dates and sort the thing.

f <- \(x) {
  sq <- do.call(seq.Date, c(as.list(as.Date(paste0(substr(range(as.Date(x)), 1, 7), '-01'))), 'month'))
  sort(c(as.Date(x), sq[substr(sq, 1, 7) %in% substr(x, 1, 7)]))
}

merge(transform(df, starttime=as.Date(starttime)), data.frame(starttime=f(df$starttime)), all=TRUE)
#     starttime  X
# 1  2016-02-01 NA
# 2  2016-02-26  0
# 3  2016-04-01 NA
# 4  2016-04-12  0
# 5  2016-04-22  0
# 6  2016-08-01 NA
# 7  2016-08-04  0
# 8  2016-09-01 NA
# 9  2016-09-15  0
# 10 2016-09-16  0
# 11 2016-09-20  0
# 12 2016-09-22  0
# 13 2017-06-01 NA
# 14 2017-06-02  0

Data:

x <- c('2016-02-26', '2016-04-12', '2016-04-22', '2016-08-04', '2016-09-15', '2016-09-16', '2016-09-20', '2016-09-22', '2017-06-02')
df <- data.frame(starttime=x, X=0)

We could use seperate the dates and use complete:

library(tidyverse)

df <- tibble(starttime = as_date(c("2016-02-26",
                                   "2016-04-12",
                                   "2016-04-22",
                                   "2016-08-04",
                                   "2016-09-15",
                                   "2016-09-16",
                                   "2016-09-20",
                                   "2016-09-22",
                                   "2017-06-02")))
                                 
df |>                                 
  mutate(temp_day = day(starttime),
         temp_month = month(starttime),
         temp_year = year(starttime)) |>
  complete(temp_month = 1:12,
           temp_year = min(temp_year):max(temp_year)) |>
  mutate(temp_day = ifelse(is.na(temp_day), 1, temp_day),
         starttime = as_date(paste(temp_year, temp_month, temp_day, sep = '-'))) |>
  select(-starts_with("temp")) |>
  arrange(starttime)

Output:

# A tibble: 28 × 1
   starttime 
   <date>    
 1 2016-01-01
 2 2016-02-26
 3 2016-03-01
 4 2016-04-12
 5 2016-04-22
 6 2016-05-01
 7 2016-06-01
 8 2016-07-01
 9 2016-08-04
10 2016-09-15
11 2016-09-16
12 2016-09-20
13 2016-09-22
14 2016-10-01
15 2016-11-01
16 2016-12-01
17 2017-01-01
18 2017-02-01
19 2017-03-01
20 2017-04-01
21 2017-05-01
22 2017-06-02
23 2017-07-01
24 2017-08-01
25 2017-09-01
26 2017-10-01
27 2017-11-01
28 2017-12-01

My approach towards this problem is fairly simple. Against our dataset merge the sequence of dates (1st day of the month) by year & month as full join, so that will get the missing month with 1st day of the month. Once done that next step is fairly simple, just use if-else approach to choose the between the column. Detail code is given below.

To generate the test data I wrote the below code;

library(data.table)

a <- c('2016','2017','2018')
b <- c('1','2','4','5','6','7','10','11')
c <- c(seq(1,4),seq(14,17),seq(22,25))
d <- sample(100:300, 6, replace = FALSE)

d <- expand.grid(a,b,c,d)

e <- sample(nrow(d), size = 15, replace = FALSE)

f <- d[e,]

setDT(f)

f[ ,Var1 := as.character(Var1)]
f[ ,Var2 := as.character(Var2)]

f[ ,date := paste0(Var1, "-",
              ifelse(nchar(Var2)<2,paste0("0",Var2),Var2), "-",
                        ifelse(as.numeric(Var3)<10,paste0("0",Var3),Var3))]
f[ ,date := as.Date(date)]
colnames(f)[match("Var4",colnames(f))] <- "frequencies"
my_data <- f[,.(date,frequencies)]

my_data[, year_month := substr(my_data$date,1,7)]
head(my_data)

Finally we come up with 15 random dates between year 2016 to 2018, with frequency between 100 to 300 taken at random.

Next create the sequence of starting day (1st day) of the month between the dates needed.

date_df <- as.data.table(seq(as.Date("2016-01-01"), as.Date("2018-12-01"), by = 
"months"))
colnames(date_df) <- "date"

date_df[, year_month := substr(date_df$date,1,7)]
head(date_df)

This will generate the sequence between all the 1st days of the months falling between the date range

Next merge the two data frames as a full join by using year & month only. Then choose between the two date columns & finally keep only required column of date & frequency.

my_data2 <- merge(my_data, date_df, by = "year_month", all = TRUE)

my_data2[is.na(date.x), date := date.y]
my_data2[!is.na(date.x), date := date.x]
final_my_data <- my_data2[,.(date,frequencies)]

By choosing the date where it's missing (as NA) for original column of date.x & replacing it with date from new column date.y coming from our sequence will give us result needed.

Like & up vote if the solution is use full. Enjoy coding !