Calculating which quarters occur in a timeframe

user20012036 0
Viewed 29

My dataset has monthly reporting which needs to be summed to return both the quarterly value and the 12 month rolling rate. I have successfully created a column specifying which quarter each row is from by using df$Quarter <- quarter(df$Month, fiscal_start = 4, with_year = T), this returns as 2022.1etc which I then use as part of my group_by to sum all values in that quarter. I however now need to create a row for each area which returns the 4 quarter sum based upon when I update the dataset, which will be done quarterly.

If this were my data I would want it to end up something like the second table |Area|Quarter|Measure_1| |----|-------|---------| |Area_a|2022.1|5| |Area_a|2021.4|1| |Area_a|2021.3|2| |Area_a|2021.2|6| |Area_b|2022.1|9| |Area_b|2021.4|7| |Area_b|2021.3|2| |Area_b|2021.2|1|

It doesn't need to be exactly like this but this is the rough idea of what I want to happen

Area Quarter Measure_1 Timeframe
Area_a 2022.1 5 Quarterly
Area_a 2021.4 1 Quarterly
Area_a 2021.3 2 Quarterly
Area_a 2021.2 6 Quarterly
Area_a 2022.1 14 12 month rolling
Area_b 2022.1 9 Quarterly
Area_b 2021.4 7 Quarterly
Area_b 2021.3 2 Quarterly
Area_b 2021.2 1 Quarterly
Area_b 2022.1 19 12 month rolling
r
1 Answers

The following code produces the required results from your sample data. If your real data covers multiple years for each Area, then you would have to calculate a year variable and then include it along with Area in the group_by().

want <- df %>% 
    group_by(Area) %>% 
    # use summarise to calculate totals
    # Quarter variable will be used to sort the output, new_Quarter will ensure
    #  that the total row has the maximum Quarter value for that area
    summarise(new_Quarter=max(Quarter), Quarter=min(Quarter)-0.05, Measure_1=sum(Measure_1)) %>% 
    bind_rows(df) %>% # combine the totals with the original data
    arrange(Area,-Quarter)%>% # sort so that total rows come after the quarters
    mutate(Quarter=if_else(is.na(new_Quarter),Quarter,new_Quarter), # assign maximum Quarter to total row
           Timeframe=if_else(is.na(new_Quarter),'Quarterly','12 month rolling')) %>% # add label
    select(-new_Quarter) # remove temporary variable
shaun_m0
Related