sanga collins - 2 months ago 3x
R Question

# R, calculating last 3 average

I have the following data frame in R. It contains the statistics of each player in the Olympics basketball tournament
Sample Table

Each game is denoted by a number in the game column. I would like to create a new column with the average of the last 3 games. When following examples in similar posts, my biggest down fall is having games numbers instead of actual dates that seem to be required for other methods.

Any assistance would be greatly appreciated.
Thanks

EDIT:
To clarify a little more based on some of the solutions and suggestions. For each row I would like to have the new column show the Average minutes or points from the last 3 games. So far the suggestion make each row show the average of games 3, 4, & 5.

So for example.
Player A, game = 3
Avg Pts = mean(pts game1, pts game2, pts game3)

Player B, game = 4
Avg pts = mean(pts game2 ,pts game3, pts game4)

I hope that clears it up.
Thanks

Data:

I am very new at this. I hope this is the appropriate method for sharing data.

``````structure(list(Player = structure(c(1L, 2L, 6L, 8L, 17L, 21L,
23L, 24L, 24L, 24L, 24L, 25L, 26L, 15L, 20L, 20L, 12L, 15L, 11L,
5L, 15L, 16L, 14L, 9L, 20L, 11L, 18L, 4L, 12L, 9L, 4L, 9L, 20L,
12L, 5L, 13L, 22L, 7L, 11L, 20L, 4L, 5L, 10L, 11L, 14L, 19L,
3L, 7L, 14L, 5L), .Label = c("Adas Juskevicius", "Alex Abrines",
"Andrew Bogut", "Bojan Bogdanovic", "Boris Diaw", "Brock Motum",
"Dario Saric", "Dwight Lewis", "Facundo Campazzo", "Ike Diogu",
"Jianlian Yi", "Jonas Maciulis", "Kevin Durant", "Luis Scola",
"Mantas Kalnietis", "Matt Dellavedova", "Miguel Marriaga", "Milos Teodosic",
"Nikola Mirotic", "Pau Gasol", "Rafa Luz", "Ricky Rubio", "Roberto Acuna",
"Vaidas Kariniauskas", "Windi Graterol", "Zeljko Sakic"), class = "factor"),
Team = structure(c(8L, 6L, 2L, 12L, 12L, 3L, 1L, 8L, 8L,
8L, 8L, 12L, 5L, 8L, 6L, 6L, 8L, 8L, 4L, 7L, 8L, 2L, 1L,
1L, 6L, 4L, 10L, 5L, 8L, 1L, 5L, 1L, 6L, 8L, 7L, 11L, 6L,
5L, 4L, 6L, 5L, 7L, 9L, 4L, 1L, 6L, 2L, 5L, 1L, 7L), .Label = c("ARG",
"AUS", "BRZ", "CHN", "CRO", "ESP", "FRA", "LTU", "NGR", "SRB",
"USA", "VEN"), class = "factor"), Pos = structure(c(3L, 4L,
2L, 5L, 2L, 5L, 1L, 2L, 2L, 2L, 2L, 1L, 4L, 3L, 1L, 1L, 4L,
5L, 2L, 2L, 5L, 3L, 2L, 3L, 1L, 4L, 5L, 2L, 2L, 3L, 2L, 3L,
1L, 2L, 2L, 4L, 3L, 4L, 4L, 1L, 2L, 2L, 2L, 4L, 1L, 2L, 1L,
4L, 1L, 2L), .Label = c("C", "PF", "PG", "SF", "SG"), class = "factor"),
game = c(4L, 5L, 4L, 5L, 3L, 4L, 3L, 1L, 2L, 3L, 4L, 5L,
5L, 3L, 2L, 3L, 3L, 4L, 3L, 3L, 2L, 4L, 3L, 3L, 5L, 5L, 5L,
4L, 2L, 2L, 2L, 5L, 4L, 4L, 2L, 2L, 1L, 4L, 4L, 1L, 5L, 4L,
3L, 2L, 4L, 2L, 2L, 3L, 2L, 1L), Status = c(0L, 0L, 0L, 0L,
0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 1L, 1L, 1L, 1L, 0L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L), Drafted = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 85,
82, 80, 78, 77, 74, 68, 68, 68, 65, 64, 63, 62, 62, 61, 61,
60, 59, 59, 59, 58, 57, 57, 57, 56, 56, 56, 55, 55, 55, 55,
54, 54, 53, 53, 52, 51), Min = c(11.04, 1.44, 16.56, 2.88,
4.8, 1.92, 13.68, 3.84, 9.36, 2.64, 21.12, 17.04, 0.24, 36.48,
32.16, 23.28, 26.88, 17.28, 33.6, 28.56, 30.48, 19.92, 30.24,
25.92, 27.84, 34.8, 15.12, 36, 28.8, 29.04, 29.28, 21.36,
23.04, 18.72, 21.12, 25.2, 12.24, 27.12, 32.88, 31.92, 34.08,
18.24, 27.6, 32.64, 33.6, 32.88, 24.72, 34.8, 35.76, 31.44
), FIC = c(3.8, 1.5, 10.2, 1, 0, -1, 0.2, 0.5, -3.2, -1,
0.6, 4.5, -0.5, 15.6, 9.5, 11.1, 0.5, 7.8, 17, 16.8, 25.2,
10.5, 10, 6, 14.4, 6, 7.5, 15.5, 14.8, 6.2, 7.9, 3, 26.9,
0.8, 11.4, 16, -1, 4.9, 14.1, 18.5, 5.9, 6.5, 10, 10, 10,
8, 19, 9, 12.1, 7.5), FP = c(8, 4, 21.75, 2, 2.75, -0.5,
4.75, 1.5, 2.5, 1.25, 8.5, 13, 0, 35.25, 37, 32.25, 17, 18.5,
39.5, 34.25, 49, 19.25, 28.75, 20.25, 41.25, 27.5, 16.5,
39.25, 33.5, 29, 30.75, 13.25, 47.25, 9, 24.5, 28.5, 6.25,
19.5, 38.25, 40.25, 27.5, 17, 21.75, 37.5, 29, 21, 38.5,
30.75, 37.75, 25.75), FPM = c(0.72463768115942, 2.77777777777778,
1.31340579710145, 0.694444444444444, 0.572916666666667, -0.260416666666667,
0.347222222222222, 0.390625, 0.267094017094017, 0.473484848484848,
0.402462121212121, 0.762910798122066, 0, 0.966282894736842,
1.15049751243781, 1.38530927835052, 0.632440476190476, 1.07060185185185,
1.17559523809524, 1.19922969187675, 1.60761154855643, 0.96636546184739,
0.950727513227513, 0.78125, 1.48168103448276, 0.790229885057471,
1.09126984126984, 1.09027777777778, 1.16319444444444, 0.99862258953168,
1.05020491803279, 0.620318352059925, 2.05078125, 0.480769230769231,
1.16003787878788, 1.13095238095238, 0.51062091503268, 0.719026548672566,
1.16332116788321, 1.2609649122807, 0.806924882629108, 0.932017543859649,
0.78804347826087, 1.14889705882353, 0.863095238095238, 0.638686131386861,
1.55744336569579, 0.883620689655172, 1.05564876957494, 0.819020356234097
), PTS = c(5L, 2L, 15L, 0L, 0L, 0L, 2L, 0L, 0L, 0L, 6L, 9L,
0L, 17L, 13L, 16L, 10L, 16L, 18L, 11L, 21L, 6L, 12L, 10L,
19L, 20L, 7L, 28L, 21L, 10L, 18L, 10L, 23L, 4L, 7L, 16L,
0L, 7L, 20L, 26L, 22L, 10L, 7L, 19L, 14L, 6L, 9L, 15L, 23L,
9L), TPM = c(1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
1L, 0L, 2L, 0L, 0L, 2L, 2L, 0L, 0L, 1L, 0L, 1L, 2L, 1L, 0L,
1L, 3L, 3L, 1L, 1L, 1L, 5L, 0L, 1L, 2L, 0L, 1L, 2L, 3L, 4L,
0L, 0L, 3L, 2L, 0L, 1L, 3L, 3L, 1L), Ast = c(2L, 0L, 2L,
0L, 1L, 0L, 0L, 1L, 2L, 0L, 1L, 1L, 0L, 7L, 1L, 3L, 1L, 2L,
1L, 9L, 12L, 8L, 4L, 1L, 1L, 2L, 5L, 2L, 2L, 8L, 2L, 1L,
5L, 2L, 5L, 5L, 1L, 0L, 2L, 1L, 1L, 0L, 3L, 0L, 1L, 2L, 6L,
3L, 0L, 2L), Reb = c(0L, 0L, 3L, 0L, 1L, 0L, 1L, 0L, 0L,
1L, 2L, 2L, 0L, 5L, 10L, 7L, 6L, 0L, 10L, 9L, 4L, 1L, 7L,
3L, 13L, 2L, 0L, 3L, 4L, 4L, 7L, 1L, 5L, 0L, 4L, 2L, 1L,
6L, 9L, 9L, 2L, 4L, 7L, 6L, 10L, 8L, 12L, 7L, 9L, 5L), BLK = c(0L,
1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 4L, 2L,
0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 2L, 2L, 0L, 0L, 1L, 0L, 1L,
0L, 2L, 0L, 3L, 1L, 0L, 1L, 2L, 0L, 0L, 0L, 1L, 1L, 0L, 1L,
3L, 1L, 1L, 2L), STL = c(0L, 0L, 0L, 1L, 0L, 0L, 1L, 0L,
0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 3L, 0L, 1L, 0L, 1L,
2L, 0L, 0L, 1L, 2L, 1L, 2L, 0L, 0L, 2L, 1L, 0L, 0L, 2L, 2L,
0L, 0L, 1L, 1L, 0L, 4L, 0L, 0L, 0L, 1L, 1L, 2L), TO = c(1L,
0L, 0L, 0L, 0L, 1L, 1L, 0L, 1L, 0L, 3L, 1L, 0L, 3L, 3L, 2L,
6L, 3L, 0L, 3L, 2L, 0L, 1L, 0L, 3L, 4L, 1L, 2L, 2L, 5L, 3L,
0L, 0L, 0L, 3L, 1L, 1L, 3L, 2L, 0L, 5L, 0L, 1L, 1L, 3L, 0L,
2L, 6L, 4L, 2L)), .Names = c("Player", "Team", "Pos", "game",
"Status", "Drafted", "Min", "FIC", "FP", "FPM", "PTS", "TPM",
"Ast", "Reb", "BLK", "STL", "TO"), row.names = c(NA, 50L), class = "data.frame")
``````

You can use `rollmeanr` from the `zoo` package with `dplyr`. This has the feature that not only the last three games of a player is averaged, but the last three game moving average is computed for each player. The code is as follows:

``````library(dplyr)
library(zoo)

avg.last.3 <- function (x) if (length(x) < 3) NA*x else rollmeanr(x, 3, fill = NA)  ## 1.

res <- df %>% group_by(Player) %>% arrange(game) %>%     ## 2.
mutate(Avg.Pts=avg.last.3(PTS)) %>%        ## 3.
ungroup() %>% arrange(Player,game)         ## 4.
``````

Notes:

1. Define a function `avg.last.3` that applies the function `rollmeanr` with window length of `3`. `rollmeanr` specifies `align="right"` to average the last three games, and we pad any result that does not have three days to average by `NA`. Note that the `if` condition in this function is needed so that:
• length of `x` is at least the window length for `rollmeanr` as required by `rollmeanr`
• `avg.last.3` returns a vector that is the same length as its input as required by `mutate`.
2. First `group_by` the `Player`. Since I noted that the `game` column is not necessarily sorted for each `Player`, we sort by `game` in ascending order.
3. Use `mutate` to create a new column `Avg.Pts` resulting from applying the `avg.last.3` function on a column, for example `PTS`.
4. Finally, `ungroup` and present the result sorted by `Player` followed by `game`

Of course, you can get the average of any number of columns by:

``````mutate(Avg.Pts=avg.last.3(PTS), Avg.Min=avg.last.3(Min), Avg.Ast=avg.last.3(Ast), ...)
``````

The results averaging only the `PTS` column is given by (printing only the first six columns plus `PTS` and `Avg.Pts`):

``````print(res[,c(colnames(res)[1:6],"PTS","Avg.Pts")],n=50)
### A tibble: 50 x 8
##                Player   Team    Pos  game Status Drafted   PTS   Avg.Pts
##                <fctr> <fctr> <fctr> <int>  <int>   <dbl> <int>     <dbl>
##1     Adas Juskevicius    LTU     PG     4      0       0     5        NA
##2         Alex Abrines    ESP     SF     5      0       0     2        NA
##3         Andrew Bogut    AUS      C     2      1      53     9        NA
##4     Bojan Bogdanovic    CRO     PF     2      1      59    18        NA
##5     Bojan Bogdanovic    CRO     PF     4      1      61    28        NA
##6     Bojan Bogdanovic    CRO     PF     5      1      55    22 22.666667
##7           Boris Diaw    FRA     PF     1      1      51     9        NA
##8           Boris Diaw    FRA     PF     2      1      57     7        NA
##9           Boris Diaw    FRA     PF     3      1      68    11  9.000000
##10          Boris Diaw    FRA     PF     4      1      55    10  9.333333
##11         Brock Motum    AUS     PF     4      0       0    15        NA
##12         Dario Saric    CRO     SF     3      1      53    15        NA
##13         Dario Saric    CRO     SF     4      1      56     7        NA
##14        Dwight Lewis    VEN     SG     5      0       0     0        NA
##15    Facundo Campazzo    ARG     PG     2      1      60    10        NA
##16    Facundo Campazzo    ARG     PG     3      1      64    10        NA
##17    Facundo Campazzo    ARG     PG     5      0      59    10 10.000000
##18           Ike Diogu    NGR     PF     3      1      55     7        NA
##19         Jianlian Yi    CHN     SF     2      1      55    19        NA
##20         Jianlian Yi    CHN     PF     3      1      74    18        NA
##21         Jianlian Yi    CHN     SF     4      1      56    20 19.000000
##22         Jianlian Yi    CHN     SF     5      1      62    20 19.333333
##23      Jonas Maciulis    LTU     PF     2      1      61    21        NA
##24      Jonas Maciulis    LTU     SF     3      1      78    10        NA
##25      Jonas Maciulis    LTU     PF     4      1      58     4 11.666667
##26        Kevin Durant    USA     SF     2      1      57    16        NA
##27          Luis Scola    ARG      C     2      1      52    23        NA
##28          Luis Scola    ARG     PF     3      1      65    12        NA
##29          Luis Scola    ARG      C     4      1      54    14 16.333333
##30    Mantas Kalnietis    LTU     SG     2      1      68    21        NA
##31    Mantas Kalnietis    LTU     PG     3      1      85    17        NA
##32    Mantas Kalnietis    LTU     SG     4      1      77    16 18.000000
##33    Matt Dellavedova    AUS     PG     4      1      68     6        NA
##34     Miguel Marriaga    VEN     PF     3      0       0     0        NA
##35      Milos Teodosic    SRB     SG     5      0      62     7        NA
##36      Nikola Mirotic    ESP     PF     2      1      54     6        NA
##37           Pau Gasol    ESP      C     1      1      56    26        NA
##38           Pau Gasol    ESP      C     2      1      82    13        NA
##39           Pau Gasol    ESP      C     3      1      80    16 18.333333
##40           Pau Gasol    ESP      C     4      1      59    23 17.333333
##41           Pau Gasol    ESP      C     5      1      63    19 19.333333
##42            Rafa Luz    BRZ     SG     4      0       0     0        NA
##43         Ricky Rubio    ESP     PG     1      1      57     0        NA
##44       Roberto Acuna    ARG      C     3      1       0     2        NA
##45 Vaidas Kariniauskas    LTU     PF     1      0       0     0        NA
##46 Vaidas Kariniauskas    LTU     PF     2      0       0     0        NA
##47 Vaidas Kariniauskas    LTU     PF     3      0       0     0  0.000000
##48 Vaidas Kariniauskas    LTU     PF     4      0       0     6  2.000000
##49      Windi Graterol    VEN      C     5      0       0     9        NA
##50        Zeljko Sakic    CRO     SF     5      0       0     0        NA
``````
Source (Stackoverflow)