I have a dataset in R similar to this
df
day team data
1 A 5
1 B 2
1 C 1
2 A 2
2 B 3
2 C 1
3 A 0
3 B 2
3 C 1
... ... ...
I now want to calculate the mean of data conditionally based on day and team which shall be added to every line in the dataset. The mean shall be calculated based on team and all previous lines where day is within a certain range compared to the day in the line. The range shall be defined by a variable.
For example if range <- 2 the mean shall be calculated for every team as followed:
day team data mean
1 A 5 5
1 B 2 2
1 C 1 1
2 A 2 3.5
2 B 3 2.5
2 C 1 1
3 A 0 1
3 B 2 2.5
3 C 1 1
... ... ... ...
How can I achieve this for example with aggregate()?
Based on the comments above the solution was simpler then expected by calculating the moving average with the package zoo as followed
First order and group data by team and day
df <- df[order(df$team, df$day), ]
df <- group_by(df, team)
Then appending the moving average
df <- mutate(df, moving_avg2 =rollmean(team, k=2, fill=NA, align='right'))