Here is a solution using 'dplyr' > require(dplyr) > lag<-read.table(text=" ID, y1, y2 + 1,0,12/25/2014 + 1,125,9/15/2015 + 1,350,1/30/2016 + 2,0,12/25/2012 + 2,450,9/15/2014 + 2,750,1/30/2016 + 2, 656, 11/30/2016 + ",sep=",",header=TRUE) > > new_lag <- lag %>% + mutate(y2 = as.Date(y2, format = "%m/%d/%Y")) %>% # convert date + arrange(ID, y2) %>% # sort if necessary + group_by(ID) %>% + mutate(flag = seq(n()), + y1diff = c(0, diff(y1)), + y2diff = c(0, diff(y2)) + ) > > > new_lag Source: local data frame [7 x 6] Groups: ID [2]
ID y1 y2 flag y1diff y2diff <int> <int> <date> <int> <dbl> <dbl> 1 1 0 2014-12-25 1 0 0 2 1 125 2015-09-15 2 125 264 3 1 350 2016-01-30 3 225 137 4 2 0 2012-12-25 1 0 0 5 2 450 2014-09-15 2 450 629 6 2 750 2016-01-30 3 300 502 7 2 656 2016-11-30 4 -94 305 Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it. On Sat, Oct 15, 2016 at 2:54 PM, Rui Barradas <ruipbarra...@sapo.pt> wrote: > I forgot about the sorting part and assumed the data.frame was already > sorted. If not, after converting y2 to class Date, you can do > > lag <- lag[order(lag$ID, lag$y2), ] > > Rui Barradas > > > Em 15-10-2016 19:45, Rui Barradas escreveu: >> >> Hello, >> >> Try the following. >> >> >> lag<-read.table(text=" ID, y1, y2 >> 1,0,12/25/2014 >> 1,125,9/15/2015 >> 1,350,1/30/2016 >> 2,0,12/25/2012 >> 2,450,9/15/2014 >> 2,750,1/30/2016 >> 2, 656, 11/30/2016 >> ",sep=",",header=TRUE) >> >> str(lag) >> lag$y2 <- as.Date(lag$y2, format = "%m/%d/%Y") >> str(lag) >> >> # 1) >> flag <- ave(lag$ID, lag$ID, FUN = seq_along) >> lag2 <- cbind(lag[1], flag, lag[-1]) >> >> # 2) >> y1dif <- ave(lag2$y1, lag2$ID, FUN = function(y) c(0, y[-1] - >> y[-length(y)])) >> y2dif <- unlist(tapply(lag2$y2, lag2$ID, FUN = function(y) c(0, y[-1] - >> y[-length(y)]))) >> >> lag2 <- cbind(lag2, y1dif, y2dif) >> lag2 >> >> Hope this helps, >> >> Rui Barradas >> >> Em 15-10-2016 17:57, Val escreveu: >>> >>> Hi all, >>> >>> I want sort the data by ID and Y2 then count the number of rows within >>> IDs. Assign a "flag" variable to reach row starting from first to >>> the last row. >>> For instance, in the following data ID "1" has three rows and each >>> row is assigned flag sequentially 1, 2,3. >>> >>> 2. In the second step, within each ID, I want get the difference >>> between the subsequent row values of y1 and y2(date) values. >>> Within each ID the first value of y1diff and y2diff are always 0. The >>> second values for each will be the current row minus the previous >>> row. >>> >>> >>> >>> lag<-read.table(text=" ID, y1, y2 >>> ID,Y1,y2 >>> 1,0,12/25/2014 >>> 1,125,9/15/2015 >>> 1,350,1/30/2016 >>> 2,0,12/25/2012 >>> 2,450,9/15/2014 >>> 2,750,1/30/2016 >>> 2, 656, 11/30/2016 >>> ",sep=",",header=TRUE) >>> >>> output looks like as follows >>> >>> ID,flag,y1,y2,y1dif,y2dif >>> 1,1,0,12/25/2014,0,0 >>> 1,2,125,9/15/2015,125,264 >>> 1,3,350,1/30/2016,225,137 >>> 2,1,0,12/25/2012,0,0 >>> 2,2,450,9/15/2014,450,629 >>> 2,3,750,1/30/2016,300,502 >>> 2, 4, 656 11/30/2016, -94, 305 >>> >>> Thank you >>> >> >> ______________________________________________ >> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >> 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. > > > ______________________________________________ > R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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. ______________________________________________ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see 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.