CODEWITHSUNDEEP
rmean
Flammulation
Flammulation

Reputation: 346

R: Calculating mean value for preceding rows and within groups

x<-c("A","B")
y<-c(1:10)
dat<-expand.grid(visit=y,site=x)

I would like to get a column that has the mean value for visit of the preceding rows within each site. The first visits will have no values.

So example of returned data

 visit site  mean
   1    A      
   2    A     1
   3    A   1.5
   4    A     2
   5    A   2.5
   6    A     3
   1    B  etc..

Upvotes: 0

Views: 231

Answers (1)

Matthew Lundberg
Matthew Lundberg

Reputation: 42629

Using y = 1:6 for this, to match the example in the question.

You can get the running averages with by and cumsum:

with(dat, by(visit, site, FUN=function(x) cumsum(x)/1:length(x)))
## site: A
## [1] 1.0 1.5 2.0 2.5 3.0 3.5
## ----------------------------------------------------------------------------------------------------- 
## site: B
## [1] 1.0 1.5 2.0 2.5 3.0 3.5

These are almost what you want. You want them shifted by one, and don't want the last entry. That's easy enough to do (if a bit odd of a requirement).

with(dat, by(visit, site, FUN=function(x) c(NA, head(cumsum(x)/1:length(x), -1))))
## site: A
## [1]  NA 1.0 1.5 2.0 2.5 3.0
## ----------------------------------------------------------------------------------------------------- 
## site: B
## [1]  NA 1.0 1.5 2.0 2.5 3.0

And you can easily present these in a single column with unlist:

dat$mean <- unlist(with(dat, by(visit, site, FUN=function(x) c(NA, head(cumsum(x)/1:length(x), -1)))))
dat
##    visit site mean
## 1      1    A   NA
## 2      2    A  1.0
## 3      3    A  1.5
## 4      4    A  2.0
## 5      5    A  2.5
## 6      6    A  3.0
## 7      1    B   NA
## 8      2    B  1.0
## 9      3    B  1.5
## 10     4    B  2.0
## 11     5    B  2.5
## 12     6    B  3.0

Upvotes: 1

Related Questions