[R] Counting occurrences in a moving window

[Gabor Grothendieck]

On Thu, Jun 2, 2011 at 11:41 AM, mdvaan <mathijsdevaan at gmail.com> wrote:
> Hi list, based on the following data.frame I would like to create a variable
> that indicates the number of occurrences of A in the 3 years prior to the
> current year:
>
> 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))
>
> becomes:
>
> A            B      C
> 8025  1995  0
> 8026  1995  0
> 8029  1995  0
> 8026  1996  1
> 8025  1997  1
> 8026  1997  2
> 8025  1997  1
> 8027  1997  0
> 8026  1999  2
> 8027  1999  2
> 8028  1995  0
> 8029  1998  1
> 8025  1997  1
> 8027  1997  0
> 8026  1999  2
> 8027  1999  2
> 8028  1995  0
> 8029  2000  1
>
> So 8026 in 1997 = 2 because 8026 can be found in 1995 and 1996 which are
> both within the appropriate window (1996 - 1994).
>
> Any ideas? I looked at the rollapply vignette, but couldn't figure out how
> to apply it to my data.
>

Try this:

> DF$C <- sapply(1:nrow(DF), function(i)
+ sum(DF$B < DF$B[i] & DF$B >= DF$B[i]-3 & DF$A[i] == DF$A))
> DF
      A    B C
1  8025 1995 0
2  8026 1995 0
3  8029 1995 0
4  8026 1996 1
5  8025 1997 1
6  8026 1997 2
7  8025 1997 1
8  8027 1997 0
9  8026 1999 2
10 8027 1999 2
11 8028 1995 0
12 8029 1998 1
13 8025 1997 1
14 8027 1997 0
15 8026 1999 2
16 8027 1999 2
17 8028 1995 0
18 8029 1998 1

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