How can I get mean of every n rows and keep the date index?

Question

I have a dataframe with a year index and a val index.

I would like to create a mean of every n rows of val and keep the corresponding year index.

Basically, the output would be (for n=2)

year val
1990 Mean(row1,row2)
1992 Mean(row3,row4)
1994 Mean(row5,row6)
1996 Mean(row7,row8)

How can I do this?

structure(list(year = c(1990, 1991, 1992, 1993, 1994, 1995, 1996, 
1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 
2008, 2009, 2010, 2011, 2012, 2013), val = c(84L, 67L, 72L, 138L, 
111L, 100L, 221L, 108L, 204L, 125L, 82L, 157L, 175L, 252L, 261L, 
185L, 146L, 183L, 245L, 172L, 98L, 216L, 89L, 144L)), .Names = c("year", 
"val"), row.names = 13:36, class = "data.frame")

Answer 1

A short one-liner solution with data.table:

library(data.table)

setDT(df)[,.(val=mean(val)), year-0:1]
#    year   val
# 1: 1990  75.5
# 2: 1992 105.0
# 3: 1994 105.5
# 4: 1996 164.5
# 5: 1998 164.5
# 6: 2000 119.5
# 7: 2002 213.5
# 8: 2004 223.0
# 9: 2006 164.5
#10: 2008 208.5
#11: 2010 157.0
#12: 2012 116.5

Answer 2

You could create a grouping variable using rep:

n = 2
dd$group <- rep(1:(nrow(dd)/n), each = n)

Then you can use your library of choice to do group_wise operations. I've used data.table.

library(data.table)
setDT(dd)

#Getting the result is then trivial    
res <- dd[, .(year = min(year), mean_val = mean(val)), by = group]

Answer 3

Using rollapply from zoo package

> library(zoo)
> res <- rollapply(df, width=2, by=2, FUN=mean)
> res[,1] <- floor(res[,1])
> res
      year   val
 [1,] 1990  75.5
 [2,] 1992 105.0
 [3,] 1994 105.5
 [4,] 1996 164.5
 [5,] 1998 164.5
 [6,] 2000 119.5
 [7,] 2002 213.5
 [8,] 2004 223.0
 [9,] 2006 164.5
[10,] 2008 208.5
[11,] 2010 157.0
[12,] 2012 116.5

alternatively:

rollapply(df, width=2, by=2, FUN=function(x) c(min(x), mean(x)))[, c(1,4)]

Answer 4

dplyr solution - add a grouping variable (1,1,2,2,3,3 etc), then compute the mean of val within groups, and use the smallest year within groups, and then drop the grouping variable:

> require(dplyr)
> d %>% group_by(G=trunc(2:(n()+1)/2)) %>% 
   summarise(mean=mean(val),year=min(year)) %>% 
   select(-G)
Source: local data frame [12 x 2]

    mean year
1   75.5 1990
2  105.0 1992
3  105.5 1994
4  164.5 1996
5  164.5 1998
6  119.5 2000
7  213.5 2002
8  223.0 2004
9  164.5 2006
10 208.5 2008
11 157.0 2010
12 116.5 2012

Generalised into a function for n, and using a neater method to compute the grouping variable:

meanN =
function(df, n){
df %>% group_by(G=(0:(n()-1))%/%n) %>%  summarise(mean=mean(val),year=min(year)) %>% select(-G)
}

> meanN(d, 2)
Source: local data table [12 x 2]

    mean year
1   75.5 1990
2  105.0 1992
3  105.5 1994
4  164.5 1996
5  164.5 1998
6  119.5 2000
7  213.5 2002
8  223.0 2004
9  164.5 2006
10 208.5 2008
11 157.0 2010
12 116.5 2012
> meanN(d, 12)
Source: local data table [2 x 2]

      mean year
1 122.4167 1990
2 180.5000 2002

Answer 5

You can use seq along with colMeans function

data.frame(Year = df[seq(1, length(df$year), 2), ]$year, Mean = colMeans(matrix(df$val, nrow=2)))

#   Year  Mean
# 1  1990  75.5
# 2  1992 105.0
# 3  1994 105.5
# 4  1996 164.5
# 5  1998 164.5
# 6  2000 119.5
# 7  2002 213.5
# 8  2004 223.0
# 9  2006 164.5
# 10 2008 208.5
# 11 2010 157.0
# 12 2012 116.5