Moving window per group

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Moving window per group

mdvaan
Hi,

I have a data.frame of the following type:

F = data.frame(read.table(textConnection("    A  B
1 1  4  
2 1  3
3 1  1
4 1  4
5 1  2
6 1  2
7 1  2
8 2  1
9 2  1
10 2  1
11 2  1
12 3  2
13 3  4
14 3  1
15 3  1
16 3  1"),head=TRUE,stringsAsFactors=FALSE))

F
   A B
1  1 4
2  1 3
3  1 1
4  1 4
5  1 2
6  1 2
7  1 2
8  2 1
9  2 1
10 2 1
11 2 1
12 3 2
13 3 4
14 3 1
15 3 1
16 3 1

I want to generate a new column in which I calculate the (cum)sum of the last 3 B's for each group A, so that F$C becomes:

   A B C
1  1 4 0
2  1 3 4
3  1 1 7
4  1 4 8
5  1 2 8
6  1 2 7
7  1 2 8
8  2 1 0
9  2 1 1
10 2 1 2
11 2 1 3
12 3 2 0
13 3 4 2
14 3 1 6
15 3 1 7
16 3 1 6

I tried this:
library(zoo)
F$C = rollapply(as.zoo(F$B), 3, FUN = function(x) cumsum(x)-(x),na.pad=TRUE)

Thanks!
Reply | Threaded
Open this post in threaded view
|

Re: Moving window per group

Gabor Grothendieck
On Thu, Mar 10, 2011 at 11:27 AM, mathijsdevaan <[hidden email]> wrote:

> Hi,
>
> I have a data.frame of the following type:
>
> F = data.frame(read.table(textConnection("    A  B
> 1 1  4
> 2 1  3
> 3 1  1
> 4 1  4
> 5 1  2
> 6 1  2
> 7 1  2
> 8 2  1
> 9 2  1
> 10 2  1
> 11 2  1
> 12 3  2
> 13 3  4
> 14 3  1
> 15 3  1
> 16 3  1"),head=TRUE,stringsAsFactors=FALSE))
>
> F
>   A B
> 1  1 4
> 2  1 3
> 3  1 1
> 4  1 4
> 5  1 2
> 6  1 2
> 7  1 2
> 8  2 1
> 9  2 1
> 10 2 1
> 11 2 1
> 12 3 2
> 13 3 4
> 14 3 1
> 15 3 1
> 16 3 1
>
> I want to generate a new column in which I calculate the (cum)sum of the
> last 3 B's for each group A, so that F$C becomes:
>
>   A B C
> 1  1 4 0
> 2  1 3 4
> 3  1 1 7
> 4  1 4 8
> 5  1 2 8
> 6  1 2 7
> 7  1 2 8
> 8  2 1 0
> 9  2 1 1
> 10 2 1 2
> 11 2 1 3
> 12 3 2 0
> 13 3 4 2
> 14 3 1 6
> 15 3 1 7
> 16 3 1 6
>
> I tried this:
> library(zoo)
> F$C = rollapply(as.zoo(F$B), 3, FUN = function(x) cumsum(x)-(x),na.pad=TRUE)
>

Try this:

   library(zoo)

   roll <- function(x) {
      x <- zoo(c(0, 0, 0, x))
      rollapply(x, 4, sum, align = "right") - x
   }
   F$C <- unlist(tapply(F$B, F$A, roll))


In the unreleased version of zoo there is a partial= argument on
rollapply which will make it possible to reduce roll, above, to this:

   roll <- function(x) rollapply(x, 4, sum, align = "right", partial = TRUE) - x



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

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Moving window per group

mdvaan
Thanks Gabor, that worked great!

Gabor Grothendieck wrote
On Thu, Mar 10, 2011 at 11:27 AM, mathijsdevaan <mathijsdevaan@gmail.com> wrote:
> Hi,
>
> I have a data.frame of the following type:
>
> F = data.frame(read.table(textConnection("    A  B
> 1 1  4
> 2 1  3
> 3 1  1
> 4 1  4
> 5 1  2
> 6 1  2
> 7 1  2
> 8 2  1
> 9 2  1
> 10 2  1
> 11 2  1
> 12 3  2
> 13 3  4
> 14 3  1
> 15 3  1
> 16 3  1"),head=TRUE,stringsAsFactors=FALSE))
>
> F
>   A B
> 1  1 4
> 2  1 3
> 3  1 1
> 4  1 4
> 5  1 2
> 6  1 2
> 7  1 2
> 8  2 1
> 9  2 1
> 10 2 1
> 11 2 1
> 12 3 2
> 13 3 4
> 14 3 1
> 15 3 1
> 16 3 1
>
> I want to generate a new column in which I calculate the (cum)sum of the
> last 3 B's for each group A, so that F$C becomes:
>
>   A B C
> 1  1 4 0
> 2  1 3 4
> 3  1 1 7
> 4  1 4 8
> 5  1 2 8
> 6  1 2 7
> 7  1 2 8
> 8  2 1 0
> 9  2 1 1
> 10 2 1 2
> 11 2 1 3
> 12 3 2 0
> 13 3 4 2
> 14 3 1 6
> 15 3 1 7
> 16 3 1 6
>
> I tried this:
> library(zoo)
> F$C = rollapply(as.zoo(F$B), 3, FUN = function(x) cumsum(x)-(x),na.pad=TRUE)
>

Try this:

   library(zoo)

   roll <- function(x) {
      x <- zoo(c(0, 0, 0, x))
      rollapply(x, 4, sum, align = "right") - x
   }
   F$C <- unlist(tapply(F$B, F$A, roll))


In the unreleased version of zoo there is a partial= argument on
rollapply which will make it possible to reduce roll, above, to this:

   roll <- function(x) rollapply(x, 4, sum, align = "right", partial = TRUE) - x



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

______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.