Aggregate column - how to handle uneven dataframe

I want to aggregate per Chr every third row (sum of three rows). However, since my df is not divisible by 3, I am not sure how to handle the last remaining rows, which could be just 1 or 2 rows. If two rows remain, I would like to just sum those 2 remaining rows.

• Input

data.frame(Chr = c("chr1","chr1","chr1","chr1","chr1","chr2","chr2","chr2","chr2","chr2","chr3"),
           value = c(1,3,1,3,5,6,3,1,3,5,0),
           seq = c(1,2,3,4,5,1,2,3,4,5,6))

• Output (using dplyr mutate, keeping all columns)

data.frame(Chr = c("chr1","chr1","chr1","chr1","chr1","chr2","chr2","chr2","chr2","chr2","chr3"),
           value = c(1,3,1,3,5,6,3,1,3,5,0),
           seq = c(1,2,3,4,5,1,2,3,4,5,6),
           agg = c(5,5,5,8,8,10,10,10,8,8,8))

You can use (row_number()-1) %/% 3 to group per 3 observations.

library(dplyr)

df %>%
  mutate(Grp = (row_number()-1) %/% 3 + 1, .by = Chr) %>%
  mutate(agg = sum(value), .by = c(Chr, Grp))

#     Chr value seq Grp agg
# 1  chr1     1   1   1   5
# 2  chr1     3   2   1   5
# 3  chr1     1   3   1   5
# 4  chr1     3   4   2   8
# 5  chr1     5   5   2   8
# 6  chr2     6   1   1  10
# 7  chr2     3   2   1  10
# 8  chr2     1   3   1  10
# 9  chr2     3   4   2   8
# 10 chr2     5   5   2   8
# 11 chr3     0   6   1   0

If the Grp column is not needed, you can drop it with select(-Grp).

Here is a similar yet other approach: Trying out the new .by argument.
I tried to use it also for the last mutate but it was not possible to combine cumsum(.. with .by:

library(dplyr)

df %>% 
  mutate(group = as.integer(gl(n(),3,n())), .by=Chr) %>% 
  mutate(id = row_number(), .by = c(Chr, group)) %>% 
  group_by(Chr, sumgroup = cumsum(id == 1)) %>% 
  mutate(agg = sum(value)) %>% 
  ungroup() %>% 
  select(Chr, value, seq, agg)

  Chr   value   seq   agg
   <chr> <dbl> <dbl> <dbl>
 1 chr1      1     1     5
 2 chr1      3     2     5
 3 chr1      1     3     5
 4 chr1      3     4     8
 5 chr1      5     5     8
 6 chr2      6     1    10
 7 chr2      3     2    10
 8 chr2      1     3    10
 9 chr2      3     4     8
10 chr2      5     5     8
11 chr3      0     6     0

An option with dplyr & ave

library(dplyr) # >= 1.1.0
df1 %>% 
  mutate(agg = ave(value, as.integer(gl(n(), 3, n())), FUN = sum), .by = Chr)

-output

    Chr value seq agg
1  chr1     1   1   5
2  chr1     3   2   5
3  chr1     1   3   5
4  chr1     3   4   8
5  chr1     5   5   8
6  chr2     6   1  10
7  chr2     3   2  10
8  chr2     1   3  10
9  chr2     3   4   8
10 chr2     5   5   8
11 chr3     0   6   0

Or with data.table

library(data.table)
 setDT(df1)[,  agg := .SD[, rep(sum(value), .N),
    as.integer(gl(.N, 3, .N))]$V1, Chr]

-output

> df1
     Chr value seq agg
 1: chr1     1   1   5
 2: chr1     3   2   5
 3: chr1     1   3   5
 4: chr1     3   4   8
 5: chr1     5   5   8
 6: chr2     6   1  10
 7: chr2     3   2  10
 8: chr2     1   3  10
 9: chr2     3   4   8
10: chr2     5   5   8
11: chr3     0   6   0

Diving the row number (within Chr groups) by 3 and rounding up this ratio gives 1 for the first 3 values, 2 for the next 3 values, etc. You can then group by Chr and this variable to compute your sums:

d %>% 
  group_by(Chr) %>%
  mutate(Chr_group = ceiling(row_number()/3)) %>% 
  group_by(Chr, Chr_group) %>%
  mutate(agg = sum(value)) %>%
  ungroup()