Hi, First post here. Grateful for any help you can give. I have data which looks like this: id time x 1 12:01 5 1 12:02 14 1 12:03 6 1 12:04 3 2 12:01 98 2 12:02 23 2 12:03 1 2 12:04 4 3 12:01 5 3 12:02 65 3 12:03 23 3 12:04 23 But I want to add a column which is the cumulative sum of X, but only by id. I've used cumsum before, but not in this way. So the result should be something like: id time x cumsum 1 12:01 5 5 1 12:02 14 19 1 12:03 6 25 1 12:04 3 28 2 12:01 98 98 2 12:02 23 121 2 12:03 1 122 2 12:04 4 126 3 12:01 5 5 3 12:02 65 70 3 12:03 23 93 3 12:04 23 116 Any ideas please? -- View this message in context: http://r.789695.n4.nabble.com/Using-cumsum-with-group-by-tp4650457.html Sent from the R help mailing list archive at Nabble.com.
Thank you very much, I will try these tomorrow morning. On 22 November 2012 17:25, arun kirshna [via R] <ml-node+s789695n4650459h55@n4.nabble.com> wrote:> HI, > You can do this in many ways: > dat1<-read.table(text=" > id time x > 1 12:01 5 > 1 12:02 14 > 1 12:03 6 > 1 12:04 3 > 2 12:01 98 > 2 12:02 23 > 2 12:03 1 > 2 12:04 4 > 3 12:01 5 > 3 12:02 65 > 3 12:03 23 > 3 12:04 23 > ",sep="",header=TRUE,stringsAsFactors=FALSE) > dat1$Cumsum<-ave(dat1$x,dat1$id,FUN=cumsum) > #or > unlist(tapply(dat1$x,dat1$id,FUN=cumsum),use.names=FALSE) > # [1] 5 19 25 28 98 121 122 126 5 70 93 116 > #or > library(plyr) > ddply(dat1,.(id),function(x) cumsum(x[3]))[,2] > # [1] 5 19 25 28 98 121 122 126 5 70 93 116 > head(dat1) > # id time x Cumsum > #1 1 12:01 5 5 > #2 1 12:02 14 19 > #3 1 12:03 6 25 > #4 1 12:04 3 28 > #5 2 12:01 98 98 > #6 2 12:02 23 121 > A.K. > > > > > ________________________________ > If you reply to this email, your message will be added to the discussion > below: > http://r.789695.n4.nabble.com/Using-cumsum-with-group-by-tp4650457p4650459.html > To unsubscribe from Using cumsum with 'group by' ?, click here. > NAML-- View this message in context: http://r.789695.n4.nabble.com/Using-cumsum-with-group-by-tp4650457p4650461.html Sent from the R help mailing list archive at Nabble.com. [[alternative HTML version deleted]]
> transform(d, cumsumXById = ave(x, id, FUN=cumsum))id time x cumsumXById 1 1 12:01 5 5 2 1 12:02 14 19 3 1 12:03 6 25 4 1 12:04 3 28 5 2 12:01 98 98 6 2 12:02 23 121 7 2 12:03 1 122 8 2 12:04 4 126 9 3 12:01 5 5 10 3 12:02 65 70 11 3 12:03 23 93 12 3 12:04 23 116 Bill Dunlap Spotfire, TIBCO Software wdunlap tibco.com> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf > Of TheRealJimShady > Sent: Thursday, November 22, 2012 9:09 AM > To: r-help at r-project.org > Subject: [R] Using cumsum with 'group by' ? > > Hi, > > First post here. Grateful for any help you can give. I have data which looks > like this: > > id time x > 1 12:01 5 > 1 12:02 14 > 1 12:03 6 > 1 12:04 3 > 2 12:01 98 > 2 12:02 23 > 2 12:03 1 > 2 12:04 4 > 3 12:01 5 > 3 12:02 65 > 3 12:03 23 > 3 12:04 23 > > But I want to add a column which is the cumulative sum of X, but only by id. > I've used cumsum before, but not in this way. So the result should be > something like: > > id time x cumsum > 1 12:01 5 5 > 1 12:02 14 19 > 1 12:03 6 25 > 1 12:04 3 28 > 2 12:01 98 98 > 2 12:02 23 121 > 2 12:03 1 122 > 2 12:04 4 126 > 3 12:01 5 5 > 3 12:02 65 70 > 3 12:03 23 93 > 3 12:04 23 116 > > Any ideas please? > > > > > -- > View this message in context: http://r.789695.n4.nabble.com/Using-cumsum-with- > group-by-tp4650457.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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.
On 22-11-2012, at 18:08, TheRealJimShady wrote:> Hi, > > First post here. Grateful for any help you can give. I have data which looks > like this: > > id time x > 1 12:01 5 > 1 12:02 14 > 1 12:03 6 > 1 12:04 3 > 2 12:01 98 > 2 12:02 23 > 2 12:03 1 > 2 12:04 4 > 3 12:01 5 > 3 12:02 65 > 3 12:03 23 > 3 12:04 23 > > But I want to add a column which is the cumulative sum of X, but only by id. > I've used cumsum before, but not in this way. So the result should be > something like: > > id time x cumsum > 1 12:01 5 5 > 1 12:02 14 19 > 1 12:03 6 25 > 1 12:04 3 28 > 2 12:01 98 98 > 2 12:02 23 121 > 2 12:03 1 122 > 2 12:04 4 126 > 3 12:01 5 5 > 3 12:02 65 70 > 3 12:03 23 93 > 3 12:04 23 116 > > Any ideas please?Assuming your data are in a dataframe named df this should do what you want df[,"cumsum"] <- ave(df$x,by=df$id, FUN=cumsum) Berend
Hi, No problem. One more method if you wanted to try: library(data.table) dat2<-data.table(dat1) dat2[,list(x,time,Cumsum=cumsum(x)),list(id)] ?# ? id? x? time Cumsum ?#1:? 1? 5 12:01????? 5 ?#2:? 1 14 12:02???? 19 ?#3:? 1? 6 12:03???? 25 ?#4:? 1? 3 12:04???? 28 ?#5:? 2 98 12:01???? 98 ?#6:? 2 23 12:02??? 121 ?#7:? 2? 1 12:03??? 122 ?#8:? 2? 4 12:04??? 126 ?#9:? 3? 5 12:01????? 5 #10:? 3 65 12:02???? 70 #11:? 3 23 12:03???? 93 #12:? 3 23 12:04??? 116 A.K. ----- Original Message ----- From: TheRealJimShady <james.david.smith at gmail.com> To: r-help at r-project.org Cc: Sent: Thursday, November 22, 2012 12:27 PM Subject: Re: [R] Using cumsum with 'group by' ? Thank you very much, I will try these tomorrow morning. On 22 November 2012 17:25, arun kirshna [via R] <ml-node+s789695n4650459h55 at n4.nabble.com> wrote:> HI, > You can do this in many ways: > dat1<-read.table(text=" > id? ? time? ? x > 1? 12:01? ? 5 > 1? 12:02? 14 > 1? 12:03? 6 > 1? 12:04? 3 > 2? 12:01? 98 > 2? 12:02? 23 > 2? 12:03? 1 > 2? 12:04? 4 > 3? 12:01? 5 > 3? 12:02? 65 > 3? 12:03? 23 > 3? 12:04? 23 > ",sep="",header=TRUE,stringsAsFactors=FALSE) >? dat1$Cumsum<-ave(dat1$x,dat1$id,FUN=cumsum) > #or >? unlist(tapply(dat1$x,dat1$id,FUN=cumsum),use.names=FALSE) > # [1]? 5? 19? 25? 28? 98 121 122 126? 5? 70? 93 116 > #or > library(plyr) >? ddply(dat1,.(id),function(x) cumsum(x[3]))[,2] > # [1]? 5? 19? 25? 28? 98 121 122 126? 5? 70? 93 116 > head(dat1) > #? id? time? x Cumsum > #1? 1 12:01? 5? ? ? 5 > #2? 1 12:02 14? ? 19 > #3? 1 12:03? 6? ? 25 > #4? 1 12:04? 3? ? 28 > #5? 2 12:01 98? ? 98 > #6? 2 12:02 23? ? 121 > A.K. > > > > > ________________________________ > If you reply to this email, your message will be added to the discussion > below: > http://r.789695.n4.nabble.com/Using-cumsum-with-group-by-tp4650457p4650459.html > To unsubscribe from Using cumsum with 'group by' ?, click here. > NAML-- View this message in context: http://r.789695.n4.nabble.com/Using-cumsum-with-group-by-tp4650457p4650461.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.