find mean for every n rows in table by few columns?

Question

I have a case - table like

and I need to find mean for v2 for every 2 rows with same pair of date-v1

I have no idea, because I know

aggregate(df$v2, by = list(df$date), mean)

but my task is different and as output I need something like

    date v1 v2
    1991 1  2.5
    1991 2  2
    1992 1  1
etc

Reopen rationale: I didn't see that cited question as being a duplicate. The questioner clearly wanted the last `v1==1` to NOT be in the same grouping as the other two `v1==1` rows. The cited question would have wanted them together. If the question is only based on paired data-v1 groups then there probably are duplicated questions out there, but if it's only based on the adjacent v1 values that are the same then it might be harder to find a dupe. @DmitryGolubev: You should clarify the rule to be used and make a bigger example but only in the case that it is the latter rule. — IRTFM, Mar 24 '18 at 20:19

score 1 · Answer 1 · answered Mar 24 '18 at 20:05

1

aggregate(df$v2, by = list(df$date), mean)

You could modify this and extend it to multiple variables in order to solve your problem:

aggregate(df, by = list(df$date,df$v1), mean)

answered Mar 24 '18 at 20:05

Daniel

1,291
6
15

MKR · Answer 2 · 2018-03-26T12:20:33.177

0

Use of rollapply function from zoo package is very handy in such scenario.

library(zoo)
z <- zoo(df)
#width = 2, by = 2 for adjacent rows. 
rollapply(z, 2, mean, na.rm=TRUE, by = 2, partial = TRUE)

#   date v1  v2
# 1 1991  1 2.5
# 3 1991  2 2.0
# 5 1992  1 1.0

Update

Seems OP wants to calculate mean for every n row of his choice. In that way use of v1 column should be avoided.

One option using dplyr can be as:

library(dplyr)

n <- 2  #It can be set as 3, 4 etc. Number of row for which mean is needed
df %>%
  group_by(date, everyN = ceiling(row_number()/n)) %>%
  summarise(v2 = mean(v2, na.rm = TRUE))

Data

df <- read.table(text = 
"date v1 v2
1991 1  2
1991 1  3
1991 2  1
1991 2  3
1992 1  1", header = T)

edited Mar 26 '18 at 12:20

answered Mar 24 '18 at 21:27

MKR

19,739
4
23
33

This doesn’t works with empty warnings on big dataset( – tehdima Mar 25 '18 at 19:21
@DmitryGolubev Has your dataset got `NA` in it? – MKR Mar 25 '18 at 19:27
@DmitryGolubev Probably you should try my 2nd option then. – MKR Mar 26 '18 at 12:21
with v1 is the main problem i have, it's like user's ID, and full case is seatching mean of t1 for every few dates per v1. But anyway thanks – tehdima Mar 27 '18 at 10:40

find mean for every n rows in table by few columns?

2 Answers2