quarter to annual dplyr financial years

Question

I'd like to aggregate some data from quarterly to annual, based on financial years. So the year would cover September to June. I can manage to aggregate over the year, but it is calendar year aggregation. An example below:

Date <- seq(as.Date("1999-03-01"), as.Date("2002-12-01"), by = "quarter")

df <- data.frame(Date, value = 1:length(Date))

df %>% 
  mutate(Year = year(Date),
        Quarter = quarter(Date)) %>% 
  group_by(Year) %>% 
  summarise(value = sum(value))

Which looks like the below:

 Year value
  <dbl> <int>
1  1999    10
2  2000    26
3  2001    42
4  2002    58

But I am expecting something like this:

 Date value
1 1999     3
2 2000    18
3 2001    34
4 2002    50
5 2003    31

Seems like something dplyr can handle, but I can't figure it out.

score 3 · Accepted Answer · answered Aug 03 '17 at 10:17

You need to add one more column to enable the grouping you want. I like to use lubridate for date operations:

library(tidyverse)
library(lubridate)

Date <- seq(ymd("1999-03-01"), ymd("2002-12-01"), by = "quarter")

df <- data.frame(Date, value = 1:length(Date))

df %>% 
        mutate(Year = year(Date),
               Quarter = quarter(Date),
               Finyear = ifelse(Quarter <= 2, Year, Year+1)) %>% 
        group_by(Finyear) %>%
        summarise(value = sum(value))

quarter to annual dplyr financial years

1 Answers1