'R - How to average every n row within ID with long data?
I have a large dataframe in long format that contains about 13k rows. It contains mostly numeric but some string variables. Here is example data of what it looks like.
id <- c(101,101,101,101,101,101,101,101,101,
102,102,102,102,102,102,
103,103,103,103,103,103,103,103,103,103,103)
color <- c("red","red","red","red","red","red","red","red","red",
"blue","blue","blue","blue","blue","blue",
"green","green","green","green","green","green","green","green","green","green","green")
time <- c(1:9, 1:6, 1:11)
var1 <- sample(1:3, 26, replace=TRUE)
var2 <- sample(1:3, 26, replace=TRUE)
var3 <- sample(1:3, 26, replace=TRUE)
df <- data.frame(id,color,time,var1,var2,var3)
id color time var1 var2 var3
1 101 red 1 1 1 3
2 101 red 2 1 3 1
3 101 red 3 1 3 1
4 101 red 4 1 3 3
5 101 red 5 2 2 3
6 101 red 6 1 1 2
7 101 red 7 1 3 1
8 101 red 8 1 2 1
9 101 red 9 1 2 2
10 102 blue 1 1 1 1
11 102 blue 2 1 3 2
12 102 blue 3 1 1 1
13 102 blue 4 3 2 1
14 102 blue 5 1 3 2
15 102 blue 6 2 1 1
16 103 green 1 3 1 3
17 103 green 2 2 3 2
18 103 green 3 2 3 1
19 103 green 4 1 1 3
20 103 green 5 2 3 2
21 103 green 6 3 3 2
22 103 green 7 3 2 3
23 103 green 8 3 1 2
24 103 green 9 3 1 2
25 103 green 10 3 2 1
26 103 green 11 1 2 1
I want to make this time-series dataframe smoother and average every n rows (e.g. every 3 rows) - averaging time, var1, var2, and var3 within id, leaving id and color unaffected. The end result should have now have 1 row for every 3 original rows. However, the only solution I have thought of so far is to split the dataframe in a list of dataframes by id, and then use aggregate for each new dataframe, but this is impractical with my real data and causes the color variable to become NA. Other attempts cause errors because my chosen n doesn't always cleanly average the rows (e.g. trying to average every 3 rows when there are 11 total rows for id 103). Is there a more practical solution?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
