2

I would like to calculate a rolling sum (or a custom function) of 3 previous values, treating each group separately. I have tried this:

require(dplyr)

# Build dataframe
df <- data.frame(person = c(rep("Peter", 5), rep("James", 5)),
                 score1 = c(1,3,2,5,4,6,8,4,5,3),
                 score2 = c(1,1,1,5,1,3,4,8,9,0))

# Attempt rolling sum by group
df %>% 
  group_by(person) %>% 
  mutate(s1_rolling = rollsumr(score1, k = 3, fill = NA),
         s2_rolling = rollsumr(score2, k = 3, fill = NA))

But the new columns do not treat each group separately, instead continuing down the whole dataset:

   person score1 score2 s1_rolling s2_rolling
   <chr>   <dbl>  <dbl>      <dbl>      <dbl>
 1 Peter       1      1         NA         NA
 2 Peter       3      1         NA         NA
 3 Peter       2      1          6          3
 4 Peter       5      5         10          7
 5 Peter       4      1         11          7
 6 James       6      3         15          9
 7 James       8      4         18          8
 8 James       4      8         18         15
 9 James       5      9         17         21
10 James       3      0         12         17

I would like row 6 and 7 to show NA in the two new columns, because until row 8 there is insufficient James data to sum 3 rows.

How can I do this?

Cenlle
  • 37
  • 4

2 Answers2

3

It could be that plyr was also loaded and the mutate from plyr masked the mutate from dplyr. We could use dplyr::mutate

library(dplyr)
library(zoo)
df %>% 
 group_by(person) %>% 
 dplyr::mutate(s1_rolling = rollsumr(score1, k = 3, fill = NA),
     s2_rolling = rollsumr(score2, k = 3, fill = NA))
# A tibble: 10 x 5
# Groups:   person [2]
#   person score1 score2 s1_rolling s2_rolling
#   <chr>   <dbl>  <dbl>      <dbl>      <dbl>
# 1 Peter       1      1         NA         NA
# 2 Peter       3      1         NA         NA
# 3 Peter       2      1          6          3
# 4 Peter       5      5         10          7
# 5 Peter       4      1         11          7
# 6 James       6      3         NA         NA
# 7 James       8      4         NA         NA
# 8 James       4      8         18         15
# 9 James       5      9         17         21
#10 James       3      0         12         17

If there are more than one column, we can also use across

df %>%
   group_by(person) %>%
   dplyr::mutate(across(starts_with('score'), 
       ~ rollsumr(., k = 3, fill = NA), .names = '{col}_rolling'))

For a faster version, use RcppRoll::roll_sumr

df %>% 
    group_by(person) %>% 
    dplyr::mutate(across(starts_with('score'), 
       ~ RcppRoll::roll_sumr(., 3, fill = NA), .names = '{col}_rolling'))

The behavior can be reproduced with plyr::mutate

df %>% 
   group_by(person) %>% 
   plyr::mutate(s1_rolling = rollsumr(score1, k = 3, fill = NA),
          s2_rolling = rollsumr(score2, k = 3, fill = NA))
# A tibble: 10 x 5
# Groups:   person [2]
#   person score1 score2 s1_rolling s2_rolling
#   <chr>   <dbl>  <dbl>      <dbl>      <dbl>
# 1 Peter       1      1         NA         NA
# 2 Peter       3      1         NA         NA
# 3 Peter       2      1          6          3
# 4 Peter       5      5         10          7
# 5 Peter       4      1         11          7
# 6 James       6      3         15          9
# 7 James       8      4         18          8
# 8 James       4      8         18         15
# 9 James       5      9         17         21
#10 James       3      0         12         17
akrun
  • 874,273
  • 37
  • 540
  • 662
3

I would suggest a slider approach with slide_dbl() function with works similar to zoo and it is compatible with dplyr:

library(slider)
library(dplyr)

#Code
# Build dataframe
df <- data.frame(person = c(rep("Peter", 5), rep("James", 5)),
                 score1 = c(1,3,2,5,4,6,8,4,5,3),
                 score2 = c(1,1,1,5,1,3,4,8,9,0))

# Attempt rolling sum by group
df %>% 
  group_by(person) %>% 
  mutate(s1_rolling = slide_dbl(score1, sum, .before = 2, .complete = TRUE),
         s2_rolling = slide_dbl(score2, sum, .before = 2, .complete = TRUE))

Output:

# A tibble: 10 x 5
# Groups:   person [2]
   person score1 score2 s1_rolling s2_rolling
   <fct>   <dbl>  <dbl>      <dbl>      <dbl>
 1 Peter       1      1         NA         NA
 2 Peter       3      1         NA         NA
 3 Peter       2      1          6          3
 4 Peter       5      5         10          7
 5 Peter       4      1         11          7
 6 James       6      3         NA         NA
 7 James       8      4         NA         NA
 8 James       4      8         18         15
 9 James       5      9         17         21
10 James       3      0         12         17
Duck
  • 39,058
  • 13
  • 42
  • 84