Tim Pekan - 1 year ago 70
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!

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download