Sum every 3 rows

Question

I want to sum the numbers of first 3 rows, then sum the number of second 3 rows, and so on. Basically, sum every 3 rows.

Below is a reproducible code:

id <- 1:15
numbers <- 6:20
df <- data.frame(id, numbers)

Please help, thanks! In my real data, I need to sum every 550 rows.

score 6 · Accepted Answer · answered Dec 27 '19 at 20:29

A solution in base R:

grp = (1:nrow(df) - 1)%/%3
data.frame(
    range = aggregate(df$id, list(grp),
                      function(x) paste(range(x), collapse=" -- "))$x,
    sum = aggregate(df$numbers, list(grp), sum)$x,
    stringsAsFactors = FALSE)

##      range sum
## 1   1 -- 3  21
## 2   4 -- 6  30
## 3   7 -- 9  39
## 4 10 -- 12  48
## 5 13 -- 15  57

score 5 · Answer 2 · answered Dec 27 '19 at 19:54

We can create a grouping variable with gl and then get the sum

library(dplyr)
library(stringr)
n1 <- 3 # change to 550
df %>%
   group_by(grp = as.integer(gl(n(), n1, n()))) %>%
   summarise(range = str_c(first(id), last(id), sep=" -- "), sum = sum(numbers)) %>%
   select(-grp)
# A tibble: 5 x 2
#  range      sum
#  <chr>    <int>
#1 1 -- 3      21
#2 4 -- 6      30
#3 7 -- 9      39
#4 10 -- 12    48
#5 13 -- 15    57

score 4 · Answer 3 · answered Dec 27 '19 at 20:39

A base R solution where matrix() was used to reshape the data frame

dfout <- data.frame(ids = apply(matrix(df$id,nrow = 3),2,function(x) paste0(x[c(1,3)],collapse = "--")),
                    summation = colSums(matrix(df$numbers,nrow = 3)))

such that

> dfout
     ids summation
1   1--3        21
2   4--6        30
3   7--9        39
4 10--12        48
5 13--15        57

Sum every 3 rows

3 Answers3