Calculate state duration with pandas

user_4944825 0
Viewed 121

I have the following pandas dataframe representing some time series data when a certain signal was switched on (True state):

datetime state
2021-01-01 01:00:00 True
2021-01-01 04:00:00 True
2021-01-01 05:30:00 False
2021-02-01 23:00:00 True
2021-03-01 01:30:00 False
2021-05-10 06:00:00 True

I need to calculate for how long was the signal in True state for each day as shown in the table below.

datetime duration(1h)
01.01.2021 4.5
02.01.2021 1
03.01.2021 1.5
04.01.2021 0
05.01.2021 18

I tried to do it on the database level using InfluxQL, but with no result so I decided to do it in Python instead.

Here's the example dataframe:

import pandas as pd

d = {'state': [True, True, False, True, False, True], 'datetime': ['2021-01-01T01:00:00Z', '2021-01-01T04:00:00Z', '2021-01-01T05:30:00Z', '2021-01-02T23:00:00Z', '2021-01-03T01:30:00Z', '2021-01-05T06:00:00Z']}
df = pd.DataFrame(data=d)
df = df.set_index(pd.to_datetime(df['datetime'])) # set datetime as DatetimeIndex

I've tried options with converting the boolean values to integers and then using scipy.integrate to get the area beneath the plot corresponding with the duration. Also tried some approaches with getting the timedelta between the consecutive point and doing some cumulative sum there, but no luck again.

I think the main problem is the grouping by days (notice the True duration between 2021-02-01 23:00:00 and 2021-03-01 01:30:00 - it aggregates to 1h on 2021-02-01 and 1.5h on 2021-03-01).

If anyone has any advice on has to achieve this with pandas, I'd really appreciate it.

influxdb pandas scipy numpy python
1 Answers

IIUC, you can try:

  1. Convert to datetime.
  2. use pivot_table to restructure the dataframe.
  3. fill the missing values for a particular day with suitable values.
  4. calculate the difference.
  5. use asfreq('1D') to fill the missing days.
  6. fill NAN with 0.
df.datetime = pd.to_datetime(df.datetime, format='%Y-%d-%m %H:%M:%S')
df1 = df.pivot_table(index=[df.datetime.dt.date],
                     columns='state', values='datetime', aggfunc='first')
df1[True] = df1[True].fillna(pd.to_datetime(df1.index.to_series()))
df1[False] = df1[False].fillna(pd.to_datetime(
    df1.index.to_series()) + pd.DateOffset(+1))
result = (df1[False] - df1[True]).asfreq('1D').fillna(pd.Timedelta(seconds=0))

OUTPUT:

datetime
2021-01-01   0 days 04:30:00
2021-01-02   0 days 01:00:00
2021-01-03   0 days 01:30:00
2021-01-04   0 days 00:00:00
2021-01-05   0 days 18:00:00
Freq: D, dtype: timedelta64[ns]
user_154380330
Related