Skip to content
Advertisement

Calculate time between two different values in the same pandas column

I have data that look like the following

Device     Time        Condition
D1  01/11/2019 00:00    issue
D1  01/11/2019 00:15    issue
D1  01/11/2019 00:30    issue
D1  01/11/2019 00:45    issue
D1  01/11/2019 01:00    issue
D1  01/11/2019 01:15    Resolved
D1  01/11/2019 01:30    Resolved
D2  01/11/2019 01:45    issue
D2  01/11/2019 02:00    Resolved
D1  01/11/2019 01:45    issue
D1  01/11/2019 02:00    Resolved

I need to create a new column that will find the time between the first issue and the first resolved. I need a groupby statement that will keep the first issue and the first resolved for all the issues. Then find the time – When I use group by Device and condition it just kept one issue per device.

The desired output is like the following

Device  Time    Condition   durationTofix
D1  01/11/2019 00:00    issue   
D1  01/11/2019 00:15    issue   
D1  01/11/2019 00:30    issue   
D1  01/11/2019 00:45    issue   
D1  01/11/2019 01:00    issue   
D1  01/11/2019 01:15    Resolved    01:15
D1  01/11/2019 01:30    Resolved    
D2  01/11/2019 01:45    issue   
D2  01/11/2019 02:00    Resolved    00:15
D1  01/11/2019 01:45    issue   
D1  01/11/2019 02:00    Resolved    00:15

As groupby Device and Condition is not enough I thought to create an index column

data["index"] = data.groupby(['Device','condition']).??? #Something like cumcount() but it is not cumcount in this case

Then use pivot table for the time calculations

H = data.pivot_table(index=['index','Device'], columns=['condition'], values='Timestamp',aggfunc=lambda x: x)
H['durationTofix'] = H['Resolved']- H['issue']

Advertisement

Answer

The biggest problem is how to group your issues/resolved properly, which can be done by a reversed cumsum:

df["Time"] = pd.to_datetime(df["Time"])

df["group"] = (df["Condition"].eq("Resolved")&df["Condition"].shift(-1).eq("issue"))[::-1].cumsum()[::-1]

df["diff"] = (df[~df.duplicated(["Condition","group"])].groupby("group")["Time"].transform(lambda d: d.diff()))

print (df)

   Device                Time Condition  group     diff
0      D1 2019-01-11 00:00:00     issue      2      NaT
1      D1 2019-01-11 00:15:00     issue      2      NaT
2      D1 2019-01-11 00:30:00     issue      2      NaT
3      D1 2019-01-11 00:45:00     issue      2      NaT
4      D1 2019-01-11 01:00:00     issue      2      NaT
5      D1 2019-01-11 01:15:00  Resolved      2 01:15:00
6      D1 2019-01-11 01:30:00  Resolved      2      NaT
7      D2 2019-01-11 01:45:00     issue      1      NaT
8      D2 2019-01-11 02:00:00  Resolved      1 00:15:00
9      D1 2019-01-11 01:45:00     issue      0      NaT
10     D1 2019-01-11 02:00:00  Resolved      0 00:15:00
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement