Sum column over specific rownumbers in grouped dataframe in R

user_12380466 0
Viewed 134

I have a dataframe like this:

df = data.frame(
  x = 1:100,
  y = rep(1:10, times = 10, each = 10)
) %>% 
  group_by(y)

And I would like to compute the sum of x from the 3rd to the 6th row of each group of y. I think this should be easy, but I just can not figure it out at the moment.

In pseudocode I imagine something like this:

df %>% 
  mutate(
    sum(x, ifelse(between(row_number(), 3,6)))
  )

But this of course does not work. I would like to solve it with some dplyr-function, but also in base R I cannot think of a fast solution. For the first group the sum would be 3+4+5+6....

dplyr dataframe r
4 Answers

One option could be:

df %>% 
 group_by(y) %>%
 mutate(z = sum(x[row_number() %in% 3:6]))

       x     y     z
   <int> <int> <int>
 1     1     1    18
 2     2     1    18
 3     3     1    18
 4     4     1    18
 5     5     1    18
 6     6     1    18
 7     7     1    18
 8     8     1    18
 9     9     1    18
10    10     1    18
user_59645570

You could also do this with filter() and summarise() and obtain a group-wise summary:

df %>% 
  group_by(y) %>% 
  mutate(rn = 1:n()) %>%
  filter(rn %in% 3:6) %>%
  summarise(x_sum = sum(x))

# A tibble: 10 x 2
       y x_sum
   <int> <int>
 1     1    18
 2     2    58
 3     3    98
 4     4   138
 5     5   178
 6     6   218
 7     7   258
 8     8   298
 9     9   338
10    10   378
user_38341390

Update: If you want to sum multiple sequences from x then you can sum by index:

df %>%  
  group_by(y) %>% 
  mutate(sum_row3to6 = sum(x[3:6]),
         sum_row1to4 = sum(x[1:4])
         )

Output:

       x     y sum_row3to6 sum_row1to4
   <int> <int>       <int>       <int>
 1     1     1          18          10
 2     2     1          18          10
 3     3     1          18          10
 4     4     1          18          10
 5     5     1          18          10
 6     6     1          18          10
 7     7     1          18          10
 8     8     1          18          10
 9     9     1          18          10
10    10     1          18          10

First answer: We could use slice summarise

library(dplyr)
df %>% 
  group_by(y) %>% 
  slice(3:6) %>% 
  summarise(sum = sum(x))

Output:

       y   sum
   <int> <int>
 1     1    18
 2     2    58
 3     3    98
 4     4   138
 5     5   178
 6     6   218
 7     7   258
 8     8   298
 9     9   338
10    10   378
user_133216470

data.table

library(data.table)
df = data.frame(
  x = 1:100,
  y = rep(1:10, times = 10, each = 10)
)
setDT(df)[rowid(y) %in% 3:6, list(sum_x = sum(x)), by = y][]
#>      y sum_x
#>  1:  1    18
#>  2:  2    58
#>  3:  3    98
#>  4:  4   138
#>  5:  5   178
#>  6:  6   218
#>  7:  7   258
#>  8:  8   298
#>  9:  9   338
#> 10: 10   378

Created on 2021-05-21 by the reprex package (v2.0.0)

user_120254830
Related