[R] cumulative sum by group and under some criteria

Mon Feb 4 22:44:55 CET 2013

Hi,
Thanks. This extract every row that satisfy the condition, but I need look at the last row (the maximum of cumulative sum) for each block (m1,n1). for example, if I set the criteria 

res2$cterm1_P1L<0.6 & res2$cterm1_P0H<0.95, this should extract m1= 3, n1 = 2. 

Hi,
I am not sure I understand your question.
res2$cterm1_P1L<0.6 & res2$cterm1_P0H<0.95
 #[1] TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE
#[16] TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE
#[31] TRUE TRUE TRUE

This will extract all the rows.

res2[,1:2][res2$cterm1_P1L<0.01 & res2$cterm1_P1L!=0,]  
#   m1 n1
#21  3  2
This extract only the row you wanted.  

For the different groups:

aggregate(cterm1_P1L~m1+n1,data=res2,max)
#  m1 n1 cterm1_P1L
#1  2  2    0.01440
#2  3  2    0.00032
#3  2  3    0.01952

 aggregate(cterm1_P1L~m1+n1,data=res2,function(x) max(x)<0.01)
 # m1 n1 cterm1_P1L
#1  2  2      FALSE
#2  3  2       TRUE
#3  2  3      FALSE

res4<-aggregate(cterm1_P1L~m1+n1,data=res2,function(x) max(x)<0.01)
res4[,1:2][res4[,3],]
#  m1 n1
#2  3  2

A.K.

----- Original Message -----
From: "Zjoanna2013 at gmail.com" <Zjoanna2013 at gmail.com>
To: smartpink111 at yahoo.com
Cc: 
Sent: Sunday, February 3, 2013 3:58 PM
Subject: Re: cumulative sum by group and under some criteria

Hi,
Let me restate my questions. I need to get the m1 and n1 that satisfy some criteria, for example in this case, within each group, the maximum cterm1_p1L ( the last row in this group) <0.01. I need to extract m1=3, n1=2, I only need m1, n1 in the row.

Also, how to create the structure from the data.frame, I am new to R, I need to change the maxN and run the loop to different data. 
Thanks very much for your help!

<quote author='arun kirshna'>
HI,

I think this should be more correct:
maxN<-9 
c11<-0.2 
c12<-0.2 
p0L<-0.05 
p0H<-0.05 
p1L<-0.20 
p1H<-0.20 

d <- structure(list(m1 = c(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3), 
    n1 = c(2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 
    3, 3, 3, 3, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2), x1 = c(0, 
    0, 0, 1, 1, 1, 2, 2, 2, 0, 0, 0, 0, 1, 1, 1, 1, 2, 2, 2, 
    2, 0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3), y1 = c(0, 1, 2, 0, 
    1, 2, 0, 1, 2, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 
    2, 0, 1, 2, 0, 1, 2, 0, 1, 2), Fmm = c(0, 0, 0, 0.7, 0.59, 
    0.64, 1, 1, 1, 0, 0, 0, 0, 0.63, 0.7, 0.74, 0.68, 1, 1, 1, 
    1, 0, 0, 0, 0.62, 0.63, 0.6, 0.63, 0.6, 0.68, 1, 1, 1), Fnn = c(0, 
    0.64, 1, 0, 0.51, 1, 0, 0.67, 1, 0, 0.62, 0.69, 1, 0, 0.54, 
    0.62, 1, 0, 0.63, 0.73, 1, 0, 0.63, 1, 0, 0.7, 1, 0, 0.7, 
    1, 0, 0.58, 1), Qm = c(1, 1, 1, 0.65, 0.45, 0.36, 0.5, 0.165, 
    0, 1, 1, 1, 1, 0.685, 0.38, 0.32, 0.32, 0.5, 0.185, 0.135, 
    0, 1, 1, 1, 0.69, 0.37, 0.4, 0.685, 0.4, 0.32, 0.5, 0.21, 
    0), Qn = c(1, 0.36, 0, 0.65, 0.45, 0, 0.5, 0.165, 0, 1, 0.38, 
    0.31, 0, 0.685, 0.38, 0.32, 0, 0.5, 0.185, 0.135, 0, 1, 0.37, 
    0, 0.69, 0.3, 0, 0.685, 0.3, 0, 0.5, 0.21, 0), term1_p0 = c(0.81450625, 
    0.0857375, 0.00225625, 0.0857375, 0.009025, 0.0002375, 0.00225625, 
    0.0002375, 6.25e-06, 0.7737809375, 0.1221759375, 0.00643031249999999, 
    0.0001128125, 0.081450625, 0.012860625, 0.000676875, 1.1875e-05, 
    0.0021434375, 0.0003384375, 1.78125e-05, 3.125e-07, 0.7737809375, 
    0.081450625, 0.0021434375, 0.1221759375, 0.012860625, 0.0003384375, 
    0.00643031249999999, 0.000676875, 1.78125e-05, 0.0001128125, 
    1.1875e-05, 3.125e-07), term1_p1 = c(0.4096, 0.2048, 0.0256, 
    0.2048, 0.1024, 0.0128, 0.0256, 0.0128, 0.0016, 0.32768, 
    0.24576, 0.06144, 0.00512, 0.16384, 0.12288, 0.03072, 0.00256, 
    0.02048, 0.01536, 0.00384, 0.00032, 0.32768, 0.16384, 0.02048, 
    0.24576, 0.12288, 0.01536, 0.06144, 0.03072, 0.00384, 0.00512, 
    0.00256, 0.00032)), .Names = c("m1", "n1", "x1", "y1", "Fmm", 
"Fnn", "Qm", "Qn", "term1_p0", "term1_p1"), row.names = c(NA, 
33L), class = "data.frame")

library(zoo)
lst1<- split(d,list(d$m1,d$n1))
res2<-do.call(rbind,lapply(lst1[lapply(lst1,nrow)!=0],function(x){
x[,11:14]<-NA;
x[,11:12][x$Qm<=c11,]<-cumsum(x[,9:10][x$Qm<=c11,]);
x[,13:14][x$Qn<=c12,]<-cumsum(x[,9:10][x$Qn<=c12,]);
colnames(x)[11:14]<- c("cterm1_P0L","cterm1_P1L","cterm1_P0H","cterm1_P1H");
x1<-na.locf(x);
x1[,11:14][is.na(x1[,11:14])]<-0;
x1}))
row.names(res2)<- 1:nrow(res2)

 res2
 #  m1 n1 x1 y1  Fmm  Fnn    Qm    Qn     term1_p0 term1_p1   cterm1_P0L
cterm1_P1L   cterm1_P0H cterm1_P1H

#1   2  2  0  0 0.00 0.00 1.000 1.000 0.8145062500  0.40960 0.0000000000  
 0.00000 0.0000000000    0.00000
#2   2  2  0  1 0.00 0.64 1.000 0.360 0.0857375000  0.20480 0.0000000000  
 0.00000 0.0000000000    0.00000
#3   2  2  0  2 0.00 1.00 1.000 0.000 0.0022562500  0.02560 0.0000000000  
 0.00000 0.0022562500    0.02560
#4   2  2  1  0 0.70 0.00 0.650 0.650 0.0857375000  0.20480 0.0000000000  
 0.00000 0.0022562500    0.02560
#5   2  2  1  1 0.59 0.51 0.450 0.450 0.0090250000  0.10240 0.0000000000  
 0.00000 0.0022562500    0.02560
#6   2  2  1  2 0.64 1.00 0.360 0.000 0.0002375000  0.01280 0.0000000000  
 0.00000 0.0024937500    0.03840
#7   2  2  2  0 1.00 0.00 0.500 0.500 0.0022562500  0.02560 0.0000000000  
 0.00000 0.0024937500    0.03840
#8   2  2  2  1 1.00 0.67 0.165 0.165 0.0002375000  0.01280 0.0002375000  
 0.01280 0.0027312500    0.05120
#9   2  2  2  2 1.00 1.00 0.000 0.000 0.0000062500  0.00160 0.0002437500  
 0.01440 0.0027375000    0.05280
#10  3  2  0  0 0.00 0.00 1.000 1.000 0.7737809375  0.32768 0.0000000000  
 0.00000 0.0000000000    0.00000
#11  3  2  0  1 0.00 0.63 1.000 0.370 0.0814506250  0.16384 0.0000000000  
 0.00000 0.0000000000    0.00000
#12  3  2  0  2 0.00 1.00 1.000 0.000 0.0021434375  0.02048 0.0000000000  
 0.00000 0.0021434375    0.02048
#13  3  2  1  0 0.62 0.00 0.690 0.690 0.1221759375  0.24576 0.0000000000  
 0.00000 0.0021434375    0.02048
#14  3  2  1  1 0.63 0.70 0.370 0.300 0.0128606250  0.12288 0.0000000000  
 0.00000 0.0021434375    0.02048
#15  3  2  1  2 0.60 1.00 0.400 0.000 0.0003384375  0.01536 0.0000000000  
 0.00000 0.0024818750    0.03584
#16  3  2  2  0 0.63 0.00 0.685 0.685 0.0064303125  0.06144 0.0000000000  
 0.00000 0.0024818750    0.03584
#17  3  2  2  1 0.60 0.70 0.400 0.300 0.0006768750  0.03072 0.0000000000  
 0.00000 0.0024818750    0.03584
#18  3  2  2  2 0.68 1.00 0.320 0.000 0.0000178125  0.00384 0.0000000000  
 0.00000 0.0024996875    0.03968
#19  3  2  3  0 1.00 0.00 0.500 0.500 0.0001128125  0.00512 0.0000000000  
 0.00000 0.0024996875    0.03968
#20  3  2  3  1 1.00 0.58 0.210 0.210 0.0000118750  0.00256 0.0000000000  
 0.00000 0.0024996875    0.03968
#21  3  2  3  2 1.00 1.00 0.000 0.000 0.0000003125  0.00032 0.0000003125  
 0.00032 0.0025000000    0.04000
#22  2  3  0  0 0.00 0.00 1.000 1.000 0.7737809375  0.32768 0.0000000000  
 0.00000 0.0000000000    0.00000
#23  2  3  0  1 0.00 0.62 1.000 0.380 0.1221759375  0.24576 0.0000000000  
 0.00000 0.0000000000    0.00000
#24  2  3  0  2 0.00 0.69 1.000 0.310 0.0064303125  0.06144 0.0000000000  
 0.00000 0.0000000000    0.00000
#25  2  3  0  3 0.00 1.00 1.000 0.000 0.0001128125  0.00512 0.0000000000  
 0.00000 0.0001128125    0.00512
#26  2  3  1  0 0.63 0.00 0.685 0.685 0.0814506250  0.16384 0.0000000000  
 0.00000 0.0001128125    0.00512
#27  2  3  1  1 0.70 0.54 0.380 0.380 0.0128606250  0.12288 0.0000000000  
 0.00000 0.0001128125    0.00512
#28  2  3  1  2 0.74 0.62 0.320 0.320 0.0006768750  0.03072 0.0000000000  
 0.00000 0.0001128125    0.00512
#29  2  3  1  3 0.68 1.00 0.320 0.000 0.0000118750  0.00256 0.0000000000  
 0.00000 0.0001246875    0.00768
#30  2  3  2  0 1.00 0.00 0.500 0.500 0.0021434375  0.02048 0.0000000000  
 0.00000 0.0001246875    0.00768
#31  2  3  2  1 1.00 0.63 0.185 0.185 0.0003384375  0.01536 0.0003384375  
 0.01536 0.0004631250    0.02304
#32  2  3  2  2 1.00 0.73 0.135 0.135 0.0000178125  0.00384 0.0003562500  
 0.01920 0.0004809375    0.02688  
#33  2  3  2  3 1.00 1.00 0.000 0.000 0.0000003125  0.00032 0.0003565625  
 0.01952 0.0004812500    0.02720

#Sorry, some values in my previous solution didn't look right. I didn't 
A.K.

----- Original Message -----
From: Zjoanna <Zjoanna2013 at gmail.com>
To: r-help at r-project.org
Cc: 
Sent: Friday, February 1, 2013 12:19 PM
Subject: Re: [R] cumulative sum by group and under some criteria

Thank you very much for your reply. Your code work well with this example.
I modified a little to fit my real data, I got an error massage.

Error in split.default(x = seq_len(nrow(x)), f = f, drop = drop, ...) :
  Group length is 0 but data length > 0

On Thu, Jan 31, 2013 at 12:21 PM, arun kirshna [via R] <
ml-node+s789695n4657196h87 at n4.nabble.com> wrote:

> Hi,
> Try this:
> colnames(d)<-c("m1","n1","x1","y1","p11","p12")
> library(zoo)
> res1<- do.call(rbind,lapply(lapply(split(d,list(d$m1,d$n1)),function(x)
> {x$cp11[x$x1>1]<- cumsum(x$p11[x$x1>1]);x$cp12[x$y1>1]<-
> cumsum(x$p12[x$y1>1]);x}),function(x)
> {x$cp11<-na.locf(x$cp11,na.rm=F);x$cp12<- na.locf(x$cp12,na.rm=F);x}))
> #there would be a warning here as one of the list element is NULL.  The,
> warning is okay
> row.names(res1)<- 1:nrow(res1)
> res1[,7:8][is.na(res1[,7:8])]<- 0
> res1
>  #  m1 n1 x1 y1  p11  p12 cp11 cp12
> #1   2  2  0  0 0.00 0.00 0.00 0.00
> #2   2  2  0  1 0.00 0.50 0.00 0.00
> #3   2  2  0  2 0.00 1.00 0.00 1.00
> #4   2  2  1  0 0.50 0.00 0.00 1.00
> #5   2  2  1  1 0.50 0.50 0.00 1.00
> #6   2  2  1  2 0.50 1.00 0.00 2.00
> #7   2  2  2  0 1.00 0.00 1.00 2.00
> #8   2  2  2  1 1.00 0.50 2.00 2.00
> #9   2  2  2  2 1.00 1.00 3.00 3.00
> #10  3  2  0  0 0.00 0.00 0.00 0.00
> #11  3  2  0  1 0.00 0.50 0.00 0.00
> #12  3  2  0  2 0.00 1.00 0.00 1.00
> #13  3  2  1  0 0.33 0.00 0.00 1.00
> #14  3  2  1  1 0.33 0.50 0.00 1.00
> #15  3  2  1  2 0.33 1.00 0.00 2.00
> #16  3  2  2  0 0.67 0.00 0.67 2.00
> #17  3  2  2  1 0.67 0.50 1.34 2.00
> #18  3  2  2  2 0.67 1.00 2.01 3.00
> #19  3  2  3  0 1.00 0.00 3.01 3.00
> #20  3  2  3  1 1.00 0.50 4.01 3.00
> #21  3  2  3  2 1.00 1.00 5.01 4.00
> #22  2  3  0  0 0.00 0.00 0.00 0.00
> #23  2  3  0  1 0.00 0.33 0.00 0.00
> #24  2  3  0  2 0.00 0.67 0.00 0.67
> #25  2  3  0  3 0.00 1.00 0.00 1.67
> #26  2  3  1  0 0.50 0.00 0.00 1.67
> #27  2  3  1  1 0.50 0.33 0.00 1.67
> #28  2  3  1  2 0.50 0.67 0.00 2.34
> #29  2  3  1  3 0.50 1.00 0.00 3.34
> #30  2  3  2  0 1.00 0.00 1.00 3.34
> #31  2  3  2  1 1.00 0.33 2.00 3.34
> #32  2  3  2  2 1.00 0.67 3.00 4.01
> #33  2  3  2  3 1.00 1.00 4.00 5.01
> A.K.
>
> ------------------------------
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://r.789695.n4.nabble.com/cumulative-sum-by-group-and-under-some-criteria-tp4657074p4657196.html
> To unsubscribe from cumulative sum by group and under some criteria, click
> here<http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=4657074&code=WmpvYW5uYTIwMTNAZ21haWwuY29tfDQ2NTcwNzR8LTE3NTE1MDA0MzY=>
> .
> NAML<http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>

--
View this message in context:
http://r.789695.n4.nabble.com/cumulative-sum-by-group-and-under-some-criteria-tp4657074p4657315.html
Sent from the R help mailing list archive at Nabble.com.
    [[alternative HTML version deleted]]

______________________________________________
R-help at 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.

</quote>
Quoted from: 
http://r.789695.n4.nabble.com/cumulative-sum-by-group-and-under-some-criteria-tp4657074p4657360.html