Create beginning of date ranges with a varying number of days

user_19886047 0
Viewed 43

I have a data frame with a list of enddates of fiscal quarters between 2014 and 2021. I want to create a separate column with the beginning of each of these quarters. Note that the format is YYYY-MM-DD. It looks like this:

df <- data.frame(end = c("2014-12-31", "2015-03-31","2015-06-30", "2015-09-30", "2015-12-31", "2016-03-31"))

The dates quarters are always from

  • Jan 1st to Mar 31st
  • Apr 1st to Jun 30th
  • Jul 1st to Sep 30th
  • Oct 1st to Dec 31st

My current code works by creating a separate column for each date with an ifelse statement and then adding them all together like this:

hmd$dtime_2 <- ifelse(hmd$dtime == "2015-03-31","2014-01-01", "")
hmd$dtime_3 <- ifelse(hmd$dtime == "2015-06-30", "2015-04-01", "")
hmd$dtime_4 <- ifelse(hmd$dtime == "2015-09-30","2015-07-01", "")
hmd$dtime_5 <- ifelse(hmd$dtime == "2015-12-31", "2015-10-01", "")

I know that there has to be an easier way to do this and I'd appreciate the help!

dplyr r date
3 Answers

This is made easily in lubridate. Get the first day of the month with floor_date and retracts two months to it with %m-% months(2). You then get the first date of the semester.

library(lubridate)
library(dplyr)
df %>% 
  mutate(start = floor_date(ymd(end), unit = "month") %m-% months(2))

         end      start
1 2014-12-31 2014-10-01
2 2015-03-31 2015-01-01
3 2015-06-30 2015-04-01
4 2015-09-30 2015-07-01
5 2015-12-31 2015-10-01
6 2016-03-31 2016-01-01
user_134606020

You could use seq.Date.

sapply(df$end, \(x) as.character(seq.Date(as.Date(x) + 1, by='-1 month', length.out=2)[2]))
# [1] "2014-12-01" "2015-03-01" "2015-06-01" "2015-09-01" "2015-12-01" "2016-03-01"

Data:

df <- structure(list(end = structure(c(16435, 16525, 16616, 16708, 
16800, 16891), class = "Date")), row.names = c(NA, -6L), class = "data.frame")
user_65740380

1) Base R This can be done in base R. There is no advantage to dplyr here but you can replace transform with mutate leaving everything else the same if you are using dplyr already. This gives start as a Date class object but if you want character then surround the right hand side with as.character(...) .

transform(df, start = as.Date(cut(as.Date(end), "quarter")))
##          end      start
## 1 2014-12-31 2014-10-01
## 2 2015-03-31 2015-01-01
## 3 2015-06-30 2015-04-01
## 4 2015-09-30 2015-07-01
## 5 2015-12-31 2015-10-01
## 6 2016-03-31 2016-01-01

2) Base R - 2 Here is a different way to use base R. We convert end to Date class and then subtract 80 days. That is enough to get us into the first month of the quarter so we can just grab the year and month and add a suffix of -01. This gives start as a character vector. If you want it as a Date class object surround the right hand side with as.Date(...) .

transform(df, start = format(as.Date(df$end) - 80, "%Y-%m-01"))
##          end      start
## 1 2014-12-31 2014-10-01
## 2 2015-03-31 2015-01-01
## 3 2015-06-30 2015-04-01
## 4 2015-09-30 2015-07-01
## 5 2015-12-31 2015-10-01
## 6 2016-03-31 2016-01-01

3) yearqtr Another approach is to use yearqtr class. Convert to a Date object and then yearqtr and back to Date. yearqtr objects specifically represent years and quarters without months or days and when converted back to Date give the beginning of quarter Date (or end of quarter if the frac = 1 argument to the yearqtr method of as.Date is used). The comments made about mutate and as.character in (1) apply here too.

library(zoo)

transform(df, start = as.Date(as.yearqtr(as.Date(end))))
##          end      start
## 1 2014-12-31 2014-10-01
## 2 2015-03-31 2015-01-01
## 3 2015-06-30 2015-04-01
## 4 2015-09-30 2015-07-01
## 5 2015-12-31 2015-10-01
## 6 2016-03-31 2016-01-01
user_5165480
Related