Tim Pekan Tim Pekan - 3 months ago 8
R Question

How to get every quarter of a date interval in R?

what I have is a data frame with many products, prices and startdate/enddate the object has been online.

product startdate enddate price
1 2012-03-17 2016-09-08 10
2 2014-05-16 2015-06-29 8
3 2015-07-01 2016-04-02 9


What I want to have is to get every quarter and year of the time the product has been online. For example for product 3: Q3 15, Q4 15, Q1 16, Q2 16.

I already transformed it into interval class via:

library(lubridate)
interval <- interval(startdate,enddate)
interval


I searched for a way to get the quarters out of that interval but couldn't find a solution.

My overall goal is to calculate the mean of the prices of every product online for every quarter.

Any help would be appreciated. Thank you!

Answer

If df is your data frame, what the following does is generate the sequence of all months from startdate to enddate, retain unique combinations of product and quarters and calculate the average.

library(lubridate)
library(dplyr)

df <- df %>%
  mutate(startdate = ymd(startdate),
         enddate = ymd(enddate)) 

df$output <- mapply(function(x,y) seq(x, y, by =  "month"),
                    df$startdate,
                    df$enddate)

df %>% 
  tidyr::unnest(output) %>%
  mutate(quarter = paste0("Q",quarter(output), " ", year(output))) %>%
  select(-output) %>%
  group_by(product, startdate, enddate, quarter) %>%
  filter(row_number(quarter) == 1) %>%
  summarise(mean(price))

Result for the first row of your data frame would be:

   product  startdate    enddate quarter `mean(price)`
     <int>     <date>     <date>   <chr>         <dbl>
1        1 2012-03-17 2016-09-08 Q1 2012            10
2        1 2012-03-17 2016-09-08 Q1 2013            10
3        1 2012-03-17 2016-09-08 Q1 2014            10
4        1 2012-03-17 2016-09-08 Q1 2015            10
5        1 2012-03-17 2016-09-08 Q1 2016            10
6        1 2012-03-17 2016-09-08 Q2 2012            10
7        1 2012-03-17 2016-09-08 Q2 2013            10
8        1 2012-03-17 2016-09-08 Q2 2014            10
9        1 2012-03-17 2016-09-08 Q2 2015            10
10       1 2012-03-17 2016-09-08 Q2 2016            10
11       1 2012-03-17 2016-09-08 Q3 2012            10
12       1 2012-03-17 2016-09-08 Q3 2013            10
13       1 2012-03-17 2016-09-08 Q3 2014            10
14       1 2012-03-17 2016-09-08 Q3 2015            10
15       1 2012-03-17 2016-09-08 Q3 2016            10
16       1 2012-03-17 2016-09-08 Q4 2012            10
17       1 2012-03-17 2016-09-08 Q4 2013            10
18       1 2012-03-17 2016-09-08 Q4 2014            10
19       1 2012-03-17 2016-09-08 Q4 2015            10