7
votes

I have some data with groups for which I want to compute a summary (sum or mean) over a fixed number of periods. I'm trying to do this with a group_by followed by mutate and then operating with the variable and its dplyr::lag. Here is an example:

library(tidyverse)
df <- data.frame(group = rep(c("A", "B"), 5), 
                  x = c(1, 3, 4, 7, 9, 10, 17, 29, 30, 55))
df %>% 
    group_by(group) %>% 
    mutate(cs = x + lag(x, 1, 0) + lag(x, 2, 0) + lag(x, 3, 0)) %>% 
    ungroup()

Which yields the desired result:

# A tibble: 10 x 3
    group     x    cs
   <fctr> <dbl> <dbl>
 1      A     1     1
 2      B     3     3
 3      A     4     5
 4      B     7    10
 5      A     9    14
 6      B    10    20
 7      A    17    31
 8      B    29    49
 9      A    30    60
10      B    55   101

Is there a shorter way to accomplish this? (Here I calculated four values but I actually need twelve or more).

1
do you want it for the whole group? like cumsum? or just lagged by x number of periods behind?Matt W.

1 Answers

9
votes

Perhaps you could use the purrr functions reduce and map included with the tidyverse:

library(tidyverse)
df <- data.frame(group = rep(c("A", "B"), 5), 
                 x = c(1, 3, 4, 7, 9, 10, 17, 29, 30, 55))

df %>% 
  group_by(group) %>% 
  mutate(cs = reduce(map(0:3, ~ lag(x, ., 0)), `+`)) %>%
  ungroup()
#> # A tibble: 10 x 3
#>     group     x    cs
#>    <fctr> <dbl> <dbl>
#>  1      A     1     1
#>  2      B     3     3
#>  3      A     4     5
#>  4      B     7    10
#>  5      A     9    14
#>  6      B    10    20
#>  7      A    17    31
#>  8      B    29    49
#>  9      A    30    60
#> 10      B    55   101

To see what's happening here it's probably easier to see with a simpler example that doesn't require a group.

v <- 1:5
lagged_v <- map(0:3, ~ lag(v, ., 0))
lagged_v
#> [[1]]
#> [1] 1 2 3 4 5
#> 
#> [[2]]
#> [1] 0 1 2 3 4
#> 
#> [[3]]
#> [1] 0 0 1 2 3
#> 
#> [[4]]
#> [1] 0 0 0 1 2

reduce(lagged_v, `+`)
#> [1]  1  3  6 10 14