user3022875 user3022875 - 3 months ago 7
R Question

Get last day in quarter

I have a sequence of dates and each date has a value

dat = data.frame(date = seq(as.Date("2015/1/1"), as.Date("2016/8/1"), "days"), value =seq(1, length(seq(as.Date("2015/1/1"), as.Date("2016/8/1"), "days")),1 ))


I'd like to subset the dataframe and return the last day in the quarter with a value

So for that dates above the results should be

2015-03-31 90
2015-06-30 181
2015-09-30 273
2015-12-31 365
2016-03-31 456
2016-06-30 547
2016-08-01 579

Answer

Here's just one way

library(dplyr)
library(lubridate)
dat %>% 
  group_by(quarter=quarter(date, T)) %>% 
  filter(date==max(date)) %>% 
  ungroup %>% 
  select(-quarter)
# # A tibble: 7 x 2
#         date value
#       <date> <dbl>
# 1 2015-03-31    90
# 2 2015-06-30   181
# 3 2015-09-30   273
# 4 2015-12-31   365
# 5 2016-03-31   456
# 6 2016-06-30   547
# 7 2016-08-01   579