How to restart a sequence based on values ​​in another column OR refer to the previous column value in R

Question

I am trying to number in order the locations collected over a period of time (with time from the previous location> 60 seconds). I excluded columns that are not relevant to this question, so the example data looks like this:

TimeSincePrev
1
1
1
1
511
1
2
286
1

My desired result is as follows: (sorry for the underscores, but I could not otherwise understand how to make it include my spaces to make the columns obvious ...)

TimeSincePrev ___ NoInSeries
eleven
12
13
fourteen
511 ______________ 1
12
2 ________________ 3
286 ______________ 1
12
... etc. for another 3,500 lines

I tried several ways to approach this unsuccessfully:

Firstly, I tried to make ifelse, where I would make NoInSequence 1 if TimeSincePrev was more than a minute, otherwise the value of the previous row is +1 .. (In this case, I first insert the column of the row number to help me refer to the previous row, but I I suspect there is an easier way to do this?)

df $ NoInSeries <- ifelse ((dfTimeSincePrev> 60), 1, ((df [((df $ LineNo) -1), "NoInSeries"]) + 1)).

I am not getting any errors, but this only gives me 1s where I want to restart the sequences, but does not fill out any of the other values:

TimeSincePrev ___ NoInSeries
1 ________________ NA
1 ________________ NA
1 ________________ NA
1 ________________ NA
511 ______________ 1
1 ________________ NA
2 ________________ NA
286 ______________ 1
1 ________________ NA
I guess this has something to do with trying to refer to yourself?

My other approach was to try to get it to execute sequences of numbers (max. 15), reloading each time the TimeSincePrev value changed:

df $ NoInSeries <- ave (df $ TimeSincePrev, df $ TimeSincePrev, FUN = function (y) 1:15)

I still do not get errors, but in the same way as before, with the SB in place and without any other numbers.

Thanks for any help!

Answer 1

Using ave after creating a group that defines a serial change using ( diff + cumsum )

 dt$NoInSeries <- ave(dt$TimeSincePrev, cumsum(dt$TimeSincePrev >60), FUN=seq) 

Result:

 dt # TimeSincePrev NoInSeries # 1 1 1 # 2 1 2 # 3 1 3 # 4 1 4 # 5 511 1 # 6 1 2 # 7 2 3 # 8 286 1 # 9 1 2 

add description of steps:

 ## detect time change > 60 seconds ## group value by the time change (gg <- cumsum(dt$TimeSincePrev >60)) [1] 0 0 0 0 1 1 1 2 2 ## get the sequence by group ave(dt$TimeSincePrev, gg, FUN=seq) [1] 1 2 3 4 1 2 3 1 2 

Answer 2

Using data.table

 library(data.table) setDT(dt)[,NoInSeries:=seq_len(.N), by=cumsum(TimeSincePrev >60)] dt # TimeSincePrev NoInSeries #1: 1 1 #2: 1 2 #3: 1 3 #4: 1 4 #5: 511 1 #6: 1 2 #7: 2 3 #8: 286 1 #9: 1 2 

or

  indx <- c(which(dt$TimeSincePrev >60)-1, nrow(dt)) sequence(c(indx[1], diff(indx))) #[1] 1 2 3 4 1 2 3 1 2 

data

  dt <- data.frame(TimeSincePrev=c(1,1,1,1,511, 1,2, 286,1))