Home > Back-end >  Generate date sequence by months
Generate date sequence by months

Time:08-15

This is the result I want:

library(lubridate)

res <- structure(
  c(
    16160, 16251, 16343, 16435, 16525, 16616, 16708, 
    16800, 16891, 16982, 17074, 17166, 17256, 17347, 17439, 17531, 
    17621, 17712, 17804, 17896, 17986, 18077, 18169, 18261, 18352, 
    18443, 18535, 18627, 18717, 18808, 18900
  ), 
  class = "Date"
)

res
#>  [1] "2014-03-31" "2014-06-30" "2014-09-30" "2014-12-31" "2015-03-31"
#>  [6] "2015-06-30" "2015-09-30" "2015-12-31" "2016-03-31" "2016-06-30"
#> [11] "2016-09-30" "2016-12-31" "2017-03-31" "2017-06-30" "2017-09-30"
#> [16] "2017-12-31" "2018-03-31" "2018-06-30" "2018-09-30" "2018-12-31"
#> [21] "2019-03-31" "2019-06-30" "2019-09-30" "2019-12-31" "2020-03-31"
#> [26] "2020-06-30" "2020-09-30" "2020-12-31" "2021-03-31" "2021-06-30"
#> [31] "2021-09-30"

I have to generate it as a sequence though.

I have managed to get the result using a while loop:

dateA <- min(res)
dateB <- max(res)
by <- months(3)

# initialize a vector to contain all dates:
myres <- dateA

while (TRUE) {
  n <- length(myres)
  next_date <- ceiling_date(myres[n] %m % by, unit = by) - 1
  
  # if next_date is less than dateB, add it to myres & continue looping:
  if (next_date < dateB) {
    myres[n   1] <- next_date
    next
  }
  
  # if next_date is equal to or greater than dateB, add it and terminate loop:
  if (next_date >= dateB) {
    myres[n   1] <- next_date
    break
  }
}

identical(res, myres)
#> [1] TRUE

I was hoping for something like:

x <- seq.Date(from = dateA, to = dateB, by = "3 months")
x
#>  [1] "2014-03-31" "2014-07-01" "2014-10-01" "2014-12-31" "2015-03-31"
#>  [6] "2015-07-01" "2015-10-01" "2015-12-31" "2016-03-31" "2016-07-01"
#> [11] "2016-10-01" "2016-12-31" "2017-03-31" "2017-07-01" "2017-10-01"
#> [16] "2017-12-31" "2018-03-31" "2018-07-01" "2018-10-01" "2018-12-31"
#> [21] "2019-03-31" "2019-07-01" "2019-10-01" "2019-12-31" "2020-03-31"
#> [26] "2020-07-01" "2020-10-01" "2020-12-31" "2021-03-31" "2021-07-01"

But that doesn't seem to work.

Is there a simpler way than the while loop? I'd appreciate any pointers in the right direction.

CodePudding user response:

I think this is most elegantly solved by taking the day of the month component out of the equation while you are generating the sequence. With the clock package, you can generate a month precision sequence, then easily set the day to the last day of the month.

library(clock)

start <- as.Date("2014-03-31")
end <- as.Date("2021-09-30")

start <- calendar_narrow(as_year_month_day(start), "month")
end <- calendar_narrow(as_year_month_day(end), "month")

# Month precision dates
start
#> <year_month_day<month>[1]>
#> [1] "2014-03"
end
#> <year_month_day<month>[1]>
#> [1] "2021-09"

seq(start, end, by = 3) |>
  set_day("last") |>
  as.Date()
#>  [1] "2014-03-31" "2014-06-30" "2014-09-30" "2014-12-31" "2015-03-31"
#>  [6] "2015-06-30" "2015-09-30" "2015-12-31" "2016-03-31" "2016-06-30"
#> [11] "2016-09-30" "2016-12-31" "2017-03-31" "2017-06-30" "2017-09-30"
#> [16] "2017-12-31" "2018-03-31" "2018-06-30" "2018-09-30" "2018-12-31"
#> [21] "2019-03-31" "2019-06-30" "2019-09-30" "2019-12-31" "2020-03-31"
#> [26] "2020-06-30" "2020-09-30" "2020-12-31" "2021-03-31" "2021-06-30"
#> [31] "2021-09-30"

Created on 2022-08-15 by the reprex package (v2.0.1)

CodePudding user response:

1) Base R Add one to the start and end dates to get the start and end of the next month, generate the sequence and subtract one to get the end of the months. No packages are used.

st <- as.Date("2014-03-31")
en <- as.Date("2021-09-30")
seq(st   1, en   1, by = "3 months") - 1
##  [1] "2014-03-31" "2014-06-30" "2014-09-30" "2014-12-31" "2015-03-31"
##  [6] "2015-06-30" "2015-09-30" "2015-12-31" "2016-03-31" "2016-06-30"
## [11] "2016-09-30" "2016-12-31" "2017-03-31" "2017-06-30" "2017-09-30"
## [16] "2017-12-31" "2018-03-31" "2018-06-30" "2018-09-30" "2018-12-31"
## [21] "2019-03-31" "2019-06-30" "2019-09-30" "2019-12-31" "2020-03-31"
## [26] "2020-06-30" "2020-09-30" "2020-12-31" "2021-03-31" "2021-06-30"
## [31] "2021-09-30"

2) yearmon It would also be possible to create a yearmon sequence consisting only of year/month with no day and then use as.Date.yearmon with the frac=1 argument which instructs it to use end of month in the conversion to Date class.

library(zoo)

st <- as.yearmon("2014-03-31")
en <- as.yearmon("2021-09-30")
as.Date(seq(st, en, 3/12), frac = 1)

3) mondate The mondate package understands end of months. Calculate the difference in months between the start and end and then add the appropriate sequence as shown.

library(mondate)

st <- mondate("2014-03-31")
en <- mondate("2021-09-30")
as.Date(mondate(st   seq(0, as.numeric(en - st), 3)))

4) lubridate Find the number of months between the two dates as d and then add a sequence of months. In that case it will understand tht we want month ends.

library(lubridate)

st <- ymd("2014-03-31")
en <- ymd("2021-09-30")
st %m % months(seq(0, round(interval(st, en) / months(1)), 3))

CodePudding user response:

Assuming you're starting with just the extremes,

res <- range(res)
res
# [1] "2014-03-31" "2021-09-30"
do.call(seq.Date, c(as.list(lubridate::ceiling_date(res, unit = "month")), by = "3 months")) - 1L
#  [1] "2014-03-31" "2014-06-30" "2014-09-30" "2014-12-31" "2015-03-31" "2015-06-30" "2015-09-30" "2015-12-31" "2016-03-31"
# [10] "2016-06-30" "2016-09-30" "2016-12-31" "2017-03-31" "2017-06-30" "2017-09-30" "2017-12-31" "2018-03-31" "2018-06-30"
# [19] "2018-09-30" "2018-12-31" "2019-03-31" "2019-06-30" "2019-09-30" "2019-12-31" "2020-03-31" "2020-06-30" "2020-09-30"
# [28] "2020-12-31" "2021-03-31" "2021-06-30" "2021-09-30"

If you're not as comfortable with the do.call part, then

res <- lubridate::ceiling_date(range(res), unit = "month")
res
# [1] "2014-04-01" "2021-10-01"
seq.Date(res[1], res[2], by = "3 months")
#  [1] "2014-04-01" "2014-07-01" "2014-10-01" "2015-01-01" "2015-04-01" "2015-07-01" "2015-10-01" "2016-01-01" "2016-04-01"
# [10] "2016-07-01" "2016-10-01" "2017-01-01" "2017-04-01" "2017-07-01" "2017-10-01" "2018-01-01" "2018-04-01" "2018-07-01"
# [19] "2018-10-01" "2019-01-01" "2019-04-01" "2019-07-01" "2019-10-01" "2020-01-01" "2020-04-01" "2020-07-01" "2020-10-01"
# [28] "2021-01-01" "2021-04-01" "2021-07-01" "2021-10-01"
  • Related