2

I am finding this to be quite tricky. I have an R time series data frame, consisting of a value for each day for about 50 years of data. I would like to compute the mean of only the last 5 values for each month. This would be simple if each month ended in the same 31st day, in which case I could just subset. However, as we all know some months end in 31, some in 30, and then we have leap years. So, is there a simple way to do this in R without having to write a complex indexing function to take account of all the possibilities including leap years? Perhaps a function that works on zoo type objects? The data frame is as follows:

        Date val
1 2014-01-06 1.49
2 2014-01-03 1.38
3 2014-01-02 1.34
4 2013-12-31 1.26
5 2013-12-30 2.11
6 2013-12-26 3.20
7 2013-12-25 3.00
8 2013-12-24 2.89
9 2013-12-23 2.90
10 2013-12-22 4.5
user2238328
  • 259
  • 2
  • 6
  • 16
  • 1
    See http://stackoverflow.com/questions/8333838/how-do-you-generate-a-sequence-of-the-last-day-of-the-month-over-two-years-in-r – James King Jan 07 '14 at 03:58

1 Answers1

3

tapply Try this where dd is your data frame and we have assumed that the Date column is of class "Date". (If dd is already sorted in descending order of Date as it appears it might be in the question then we can shorten it a bit by replacing the anonymous function with function(x) mean(head(x, 5)) .)

> tapply(dd$val, format(dd$Date, "%Y-%m"), function(x) mean(tail(sort(x), 5)))
 2013-12  2014-01 
2.492000 1.403333

aggregate.zoo In terms of zoo we can do this which returns another zoo object and its index is of class "yearmon". (In the case of zoo it does not matter whether dd is sorted or not since zoo will sort it automatically.)

> library(zoo)
> z <- read.zoo(dd)
> aggregate(z, as.yearmon, function(x) mean(tail(x, 5)))
Dec 2013 Jan 2014 
2.492000 1.403333

REVISIONS. Made some corrections.

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • Great thanks, this works quite well, looks like tapply does much of the grouping/aggregation I was attempting to do in the background. I was also not aware that the apply can take custom functions as input. – user2238328 Jan 07 '14 at 18:57