Clever Geek Handbook

Reset TIME column when AMT> 0

I have a dataframe that looks like this:

ID  TIME  AMT
1    0    50
1    1    0
1    2    0
1    3    0
1    4    0
1    4    50
1    5    0
1    7    0
1    9    0
1    10   0
1    10   50

The column TIME

in the above data frame is continuous. I want to add another time column that resets the time from zero when AMT>0

. So my output dataframe should look like this:

    ID  TIME  AMT  TIME2
    1    0    50    0
    1    1    0     1 
    1    2    0     2 
    1    3    0     3 
    1    4    0     4
    1    4    50    0
    1    5    0     1
    1    7    0     3
    1    9    0     5
    1    10   0     6
    1    10   50    0

This is mainly achieved by subtracting TIME

from the "fixed" support TIME

with AMT>0

(for example, the reference time for the second AMT>0

is 4. Thus, TIME2

is calculated by subtracting 5-4=1

; 7-4=3

; 9-4=5

etc. How to do this automatically R.

+3


source to share


2 answers


A data.table

solution:



library(data.table)
setDT(DT)[,TIME2 := TIME-TIME[1],cumsum(AMT>0)]

#      ID TIME AMT TIME2
# 1:   1    0  50     0
# 2:   1    1   0     1
# 3:   1    2   0     2
# 4:   1    3   0     3
# 5:   1    4   0     4
# 6:   1    4  50     0
# 7:   1    5   0     1
# 8:   1    7   0     3
# 9:   1    9   0     5
# 10:  1   10   0     6
# 11:  1   10  50     0
+5


source


Originally posted the same answer as @agstudy, so here's an alternative possible R base solution

with(df, ave(TIME, cumsum(AMT > 0L), ID, FUN = function(x) x - x[1L]))
## [1] 0 1 2 3 4 0 1 3 5 6 0


or

library(dplyr)
df %>%
  group_by(cumsum(AMT > 0), ID) %>%
  mutate(TIME2 = TIME - first(TIME))
+4


source






More articles:


All Articles