Sum column values after every 10 rows in dataframe R

Question

I have a dataframe with 300000 rows in R as follows:

11 2990000 3000000 0.00000000
11 3000000 3010000 2.30247191
11 3010000 3020000 0.32213483
11 3020000 3030000 0.91696629
11 3030000 3040000 1.81595506
11 3040000 3050000 0.27269663
11 3050000 3060000 2.21988764
11 3060000 3070000 3.44640449
11 3070000 3080000 2.02134831
11 3080000 3090000 1.22123596 #10th row
11 3090000 3100000 3.47089888
11 3100000 3110000 3.08921348
11 3110000 3120000 3.11786517
11 3120000 3130000 1.44325843
11 3130000 3140000 0.00000000
11 3140000 3150000 0.00000000
11 3150000 3160000 2.55146067
11 3160000 3170000 0.63460674
11 3170000 3180000 1.08415730
11 3180000 3190000 2.73101124 #20th row

I want to make a new dataframe which sums up column 4 of every 10 rows, and outputs column 2 and column 3 of the first and the 10th row in consideration, respectively. The output of this should be:

11 2990000 3090000 14.5391
11 3090000 3190000 18.12247

First construct a grouping variable. You can use `gl()` or `rep()`. For calculating the sums you can use, e.g. `aggregate()` — jogo, Oct 24 '18 at 11:28

Roman · Answer 1 · 2018-10-24T12:43:33.187

You can try a tidyverse

library(tidyverse)
d %>% 
  group_by(gr=gl(n()/10,10)) %>% 
  summarise(Sum=sum(V4)) 
# A tibble: 2 x 2
  gr      Sum
  <fct> <dbl>
1 1      14.5
2 2      18.1

or try mutate

d %>% 
  group_by(gr=gl(n()/10,10)) %>% 
  mutate(Sum=sum(V4)) %>%
  slice(10)
# A tibble: 2 x 6
# Groups:   gr [2]
     V1      V2      V3    V4 gr      Sum
  <int>   <int>   <int> <dbl> <fct> <dbl>
1    11 3080000 3090000  1.22 1      14.5
2    11 3180000 3190000  2.73 2      18.1

In base R you can use ave

ave(d$V4, factor(rep(1:(nrow(d)/10), each=10)), FUN=sum)[seq(10, nrow(d), 10)]

jogo · Accepted Answer · 2018-10-24T11:49:49.470

Here is a solution with data.table:

library("data.table")
D <- fread(
"11 2990000 3000000 0.00000000
11 3000000 3010000 2.30247191
11 3010000 3020000 0.32213483
11 3020000 3030000 0.91696629
11 3030000 3040000 1.81595506
11 3040000 3050000 0.27269663
11 3050000 3060000 2.21988764
11 3060000 3070000 3.44640449
11 3070000 3080000 2.02134831
11 3080000 3090000 1.22123596
11 3090000 3100000 3.47089888
11 3100000 3110000 3.08921348
11 3110000 3120000 3.11786517
11 3120000 3130000 1.44325843
11 3130000 3140000 0.00000000
11 3140000 3150000 0.00000000
11 3150000 3160000 2.55146067
11 3160000 3170000 0.63460674
11 3170000 3180000 1.08415730
11 3180000 3190000 2.73101124")

D[, .(V2=V2[1], V3=V3[.N], V4=sum(V4)), by=gl(D[, .N]/10, 10)]
# > D[, .(V2=V2[1], V3=V3[.N], V4=sum(V4)), by=gl(D[, .N]/10, 10)]
#    gl      V2      V3       V4
# 1:  1 2990000 3090000 14.53910
# 2:  2 3090000 3190000 18.12247

score 0 · Answer 3 · answered Oct 24 '18 at 12:19

Putting together existing answers from:

Select every nth row from dataframe
Sum every nth points

v = D$V4
n = 10

#using @jogo's example data
cbind(
  D[ seq(1, nrow(D), n), 1:3 ],
  tapply(v, (seq_along(v)-1) %/% n, sum)
  )

#    V1      V2      V3       V2
# 1: 11 2990000 3000000 14.53910
# 2: 11 3090000 3100000 18.12247

Sum column values after every 10 rows in dataframe R

3 Answers3