Pandas measure elapsed time since a condition

  • A+
Category:Languages

I have the following dataframe:

               Time   Work 2018-12-01 10:00:00     Off 2018-12-01 10:00:02     On 2018-12-01 10:00:05     On 2018-12-01 10:00:06     On 2018-12-01 10:00:07     On 2018-12-01 10:00:09    Off 2018-12-01 10:00:11    Off 2018-12-01 10:00:14     On 2018-12-01 10:00:16     On 2018-12-01 10:00:18     On 2018-12-01 10:00:20    Off 

I would like to creat a new column with the elapsed time since the device started working.

               Time   Work    Elapsed Time 2018-12-01 10:00:00    Off               0 2018-12-01 10:00:02     On               2 2018-12-01 10:00:05     On               5 2018-12-01 10:00:06     On               6 2018-12-01 10:00:07     On               7 2018-12-01 10:00:09    Off               0 2018-12-01 10:00:11    Off               0 2018-12-01 10:00:14     On               3 2018-12-01 10:00:16     On               5 2018-12-01 10:00:18     On               7 2018-12-01 10:00:20    Off               0 

How can I do it?

 


You can use groupby:

# df['Time'] = pd.to_datetime(df['Time'], errors='coerce') # Uncomment if needed. sec = df['Time'].dt.second df['Elapsed Time'] = (     sec - sec.groupby(df.Work.eq('Off').cumsum()).transform('first'))  df                   Time Work  Elapsed Time 0  2018-12-01 10:00:00  Off             0 1  2018-12-01 10:00:02   On             2 2  2018-12-01 10:00:05   On             5 3  2018-12-01 10:00:06   On             6 4  2018-12-01 10:00:07   On             7 5  2018-12-01 10:00:09  Off             0 6  2018-12-01 10:00:11  Off             0 7  2018-12-01 10:00:14   On             3 8  2018-12-01 10:00:16   On             5 9  2018-12-01 10:00:18   On             7 10 2018-12-01 10:00:20  Off             0 

If there's a possibility your device can span multiple minutes while in the "On", then, initialise sec as:

sec = df['Time'].astype(int) // 10e8  df['Elapsed Time'] = (     sec - sec.groupby(df.Work.eq('Off').cumsum()).transform('first'))  df                   Time Work  Elapsed Time 0  2018-12-01 10:00:00  Off           0.0 1  2018-12-01 10:00:02   On           2.0 2  2018-12-01 10:00:05   On           5.0 3  2018-12-01 10:00:06   On           6.0 4  2018-12-01 10:00:07   On           7.0 5  2018-12-01 10:00:09  Off           0.0 6  2018-12-01 10:00:11  Off           0.0 7  2018-12-01 10:00:14   On           3.0 8  2018-12-01 10:00:16   On           5.0 9  2018-12-01 10:00:18   On           7.0 10 2018-12-01 10:00:20  Off           0.0 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: