[R] lag, count

[jim holtman]

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 <ruipbarradas at 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 at 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 at 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.