[R] Counting occurrences in a moving window

[Gabor Grothendieck]

On Fri, Jun 3, 2011 at 8:11 AM, mdvaan <mathijsdevaan at gmail.com> wrote:
> Would it be possible to use the sqldf package and the ave function to simply
> run ave over a limited set? So something like:
>
> DF = data.frame(read.table(textConnection("  A  B
> 8025  1995
> 8026  1995
> 8029  1995
> 8026  1996
> 8025  1997
> 8026  1997
> 8025  1997
> 8027  1997
> 8026  1999
> 8027  1999
> 8028  1995
> 8029  1998
> 8025  1997
> 8027  1997
> 8026  1999
> 8027  1999
> 8028  1995
> 8029  1998"),head=TRUE,stringsAsFactors=FALSE))
>
> library(sqldf)
> years<-c(1995:1999)
> for (t in 1:length(years))
>        {
>        year = as.numeric(years[t])
>        m<-sqldf('select * from DF where B between $year-1 AND $year-4')
>        n<-ave(m$A,m$A,FUN = length)
>        }
>
> How do I get the correct values in DF$C? Thanks!!

In sqldf it would be like this:

sqldf("select x.*, sum(x.A = y.A and y.B < x.B and y.B >= x.B-3) C
from DF x, DF y group by x.rowid")


-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com