Combining multiple months into segments

December 30, 2021

I have a dataset where there is a monthyr column. The second column is the value column for those months.

test <- data.frame(
  monthyr = c("2019-01-01","2019-02-01","2019-04-01","2019-07-01","2019-08-01",
                            "2019-12-01","2020-03-01","2020-05-01","2020-09-01","2020-10-01",
                            "2021-02-01","2021-12-01"),
  value = c(10,20,30,40,50,60,70,80,90,10,20,30)
)

> test
      monthyr value
1  2019-01-01    10
2  2019-02-01    20
3  2019-04-01    30
4  2019-07-01    40
5  2019-08-01    50
6  2019-12-01    60
7  2020-03-01    70
8  2020-05-01    80
9  2020-09-01    90
10 2020-10-01    10
11 2021-02-01    20
12 2021-12-01    30

I want to aggregate this data into groups of 3 months. The desired data is as below

> test
      monthyr value segment_total
1  2019-01-01    10            60
2  2019-02-01    20            60
3  2019-04-01    30            60
4  2019-07-01    40           150
5  2019-08-01    50           150
6  2019-12-01    60           150
7  2020-03-01    70           240
8  2020-05-01    80           240
9  2020-09-01    90           240
10 2020-10-01    10            60
11 2021-02-01    20            60
12 2021-12-01    30            60

>Solution :

We could first create segments of 3 and group, then we could sum()

library(dplyr)
test %>% 
  group_by(group = rep(row_number(), each=3, length.out = n())) %>% 
  mutate(segment_total = sum(value, na.rm = TRUE)) %>% 
  ungroup() %>% 
  select(-group)

  monthyr    value segment_total
   <chr>      <dbl>         <dbl>
 1 2019-01-01    10            60
 2 2019-02-01    20            60
 3 2019-04-01    30            60
 4 2019-07-01    40           150
 5 2019-08-01    50           150
 6 2019-12-01    60           150
 7 2020-03-01    70           240
 8 2020-05-01    80           240
 9 2020-09-01    90           240
10 2020-10-01    10            60
11 2021-02-01    20            60
12 2021-12-01    30            60