CODEWITHSUNDEEP
rdatedplyr
DunkinDont
DunkinDont

Reputation: 93

Create beginning of date ranges with a varying number of days

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

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!

Upvotes: 1

Views: 172

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 270170

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

Upvotes: 1

jay.sf
jay.sf

Reputation: 73602

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")

Upvotes: 1

Ma&#235;l
Ma&#235;l

Reputation: 52329

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

Upvotes: 1

Related Questions