Message-ID: <BANLkTimUzCD1MbX4gpBp0J1yJ06UXTPndg@mail.gmail.com>
Date: 2011-06-03T16:16:23Z
From: Gabor Grothendieck
Subject: Counting occurrences in a moving window
In-Reply-To: <1307103078103-3570652.post@n4.nabble.com>
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