1

Could someone kindly advise how best to approach making a new column in a dataframe, where each observation is an average/mean of the previous 12 observations (excluding the current observation). I have failed so far to find a similar answer on here so this would be greatly appreciated!

My data.frame:

LateCounts <- 

    Date    Count
1   Jan-19  7
2   Feb-19  4
3   Mar-19  9
4   Apr-19  8
5   May-19  7
6   Jun-19  4
7   Jul-19  4
8   Aug-19  5
9   Sep-19  2
10  Oct-19  5
11  Nov-19  7
12  Dec-19  4
13  Jan-20  3
14  Feb-20  4
15  Mar-20  5
16  Apr-20  2
17  May-20  3
18  Jun-20  2
19  Jul-20  3
20  Aug-20  4
21  Sep-20  3
22  Oct-20  2

I am currently using the following code:

LateCounts <- LateCounts %>% mutate(RollAvge=rollapplyr(Count, 12, mean, partial = TRUE))

This yields the following but the 12 month rolling average:

    Date    Count   RollAvge
1   Jan-19   7      7
2   Feb-19   4      5.5
3   Mar-19   9      6.666667
4   Apr-19   8      7
5   May-19   7      7
6   Jun-19   4      6.5
7   Jul-19   4      6.142857
8   Aug-19   5      6
9   Sep-19   2      5.555556
10  Oct-19   5      5.5
11  Nov-19   7      5.636364
12  Dec-19   4      5.5
13  Jan-20   3      5.166667
14  Feb-20   4      5.166667
15  Mar-20   5      4.833333
16  Apr-20   2      4.333333
17  May-20   3      4
18  Jun-20   2      3.833333
19  Jul-20   3      3.75
20  Aug-20   4      3.666667
21  Sep-20   3      3.75
22  Oct-20   2      3.5

What i actually need to achieve is the below. This is 12 month trailing or rolling average (where the values in the 'RollAvge' column are averages/means of the previous values in 'Count' column - not including the current month.

    Date    Count   RollAvge
1   Jan-19  7   
2   Feb-19  4       7
3   Mar-19  9       5.5
4   Apr-19  8       6.666667
5   May-19  7       7
6   Jun-19  4       7
7   Jul-19  4       6.5
8   Aug-19  5       6.142857
9   Sep-19  2       6
10  Oct-19  5       5.555556
11  Nov-19  7       5.5
12  Dec-19  4       5.636364
13  Jan-20  3       5.5
14  Feb-20  4       5.166667
15  Mar-20  5       5.166667
16  Apr-20  2       4.833333
17  May-20  3       4.333333
18  Jun-20  2       4
19  Jul-20  3       3.833333
20  Aug-20  4       3.75
21  Sep-20  3       3.666667
22  Oct-20  2       3.755556

Thanks,

NW320d
  • 15
  • 4

2 Answers2

1

We need to take the lag of the output derived from rollapply.

library(dplyr)
library(zoo)
LateCounts %>%
      mutate(RollAvge= lag(rollapplyr(Count, 12, mean, partial = TRUE)))

-output

#      Date Count RollAvge
#1  Jan-19     7       NA
#2  Feb-19     4 7.000000
#3  Mar-19     9 5.500000
#4  Apr-19     8 6.666667
#5  May-19     7 7.000000
#6  Jun-19     4 7.000000
#7  Jul-19     4 6.500000
#8  Aug-19     5 6.142857
#9  Sep-19     2 6.000000
#10 Oct-19     5 5.555556
#11 Nov-19     7 5.500000
#12 Dec-19     4 5.636364
#13 Jan-20     3 5.500000
#14 Feb-20     4 5.166667
#15 Mar-20     5 5.166667
#16 Apr-20     2 4.833333
#17 May-20     3 4.333333
#18 Jun-20     2 4.000000
#19 Jul-20     3 3.833333
#20 Aug-20     4 3.750000
#21 Sep-20     3 3.666667
#22 Oct-20     2 3.750000

data

LateCounts <- structure(list(Date = c("Jan-19", "Feb-19", "Mar-19", "Apr-19", 
"May-19", "Jun-19", "Jul-19", "Aug-19", "Sep-19", "Oct-19", "Nov-19", 
"Dec-19", "Jan-20", "Feb-20", "Mar-20", "Apr-20", "May-20", "Jun-20", 
"Jul-20", "Aug-20", "Sep-20", "Oct-20"), Count = c(7L, 4L, 9L, 
8L, 7L, 4L, 4L, 5L, 2L, 5L, 7L, 4L, 3L, 4L, 5L, 2L, 3L, 2L, 3L, 
4L, 3L, 2L)), class = "data.frame", row.names = c("1", "2", "3", 
"4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", 
"16", "17", "18", "19", "20", "21", "22"))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 1
    One can specify the prior 12 values explicity in `rollapply` avoiding `lag` by using `LateCounts %>% mutate(RollAvge= rollapplyr(Count, list(-(1:12)), mean, partial = TRUE, fill = NA))` Here `-(1:12)` says use offsets -1, -2, ..., -12. One can specify a separate vector of offsets for each row or if just one is specified, as it is here, then it will be recycled. – G. Grothendieck Oct 26 '20 at 02:39
1

Using dplyr and zoo there is a way to do it using data.frame function @NW320d using the same rolling average function but without mutate and pipes

library(dplyr)

library(zoo)

Using the LateCounts code by @akrun (thank you for that code snippet!)

> LateCounts <- structure(list(Date = c("Jan-19", "Feb-19", "Mar-19", "Apr-19", 
+ "May-19", "Jun-19", "Jul-19", "Aug-19", "Sep-19", "Oct-19", "Nov-19", 
+ "Dec-19", "Jan-20", "Feb-20", "Mar-20", "Apr-20", "May-20", "Jun-20", 
+ "Jul-20", "Aug-20", "Sep-20", "Oct-20"), Count = c(7L, 4L, 9L, 
+ 8L, 7L, 4L, 4L, 5L, 2L, 5L, 7L, 4L, 3L, 4L, 5L, 2L, 3L, 2L, 3L, 
+ 4L, 3L, 2L)), class = "data.frame", row.names = c("1", "2", "3", 
+ "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", 
+ "16", "17", "18", "19", "20", "21", "22"))

> data.frame(LateCounts$Count, rollavg=dplyr::lag(rollapplyr(LateCounts$Count, 12, mean, partial = TRUE)))

Output:
   LateCounts.Count  rollavg
1                 7       NA
2                 4 7.000000
3                 9 5.500000
4                 8 6.666667
5                 7 7.000000
6                 4 7.000000
7                 4 6.500000
8                 5 6.142857
9                 2 6.000000
10                5 5.555556
11                7 5.500000
12                4 5.636364
13                3 5.500000
14                4 5.166667
15                5 5.166667
16                2 4.833333
17                3 4.333333
18                2 4.000000
19                3 3.833333
20                4 3.750000
21                3 3.666667
22                2 3.750000
sreedta
  • 123
  • 1
  • 9