Calculate TimeDiff in Pandas based on a column values

Question

Having a dataframe like that:

Desirable result is to get aggregated IDs with time diffs between Start and End looking like that:

Tried simple groupings and diffs but it does not work:

df[df['Name'] == 'Start'].groupby('ID')['Time']-\
df[df['Name'] == 'End'].groupby('ID')['Time']

How this task can be done in pandas? Thanks!

can u share ur dataframe in a better way : https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — sammywemmy, Feb 05 '20 at 22:12
sure, but what's the problem with this view? it's confusing for some reason? — Keithx, Feb 05 '20 at 22:35
You are supposed to generate the data, not paste a pic. That's why i attached the link. — sammywemmy, Feb 05 '20 at 23:41

score 1 · Accepted Answer · answered Feb 06 '20 at 00:01

A possible solution is to join the table on itself like this:

df_start = df[df['Name'] == 'Start']
df_end = df[df['Name'] == 'End']
df_merge = df_start.merge(df_end, on='id', suffixes=('_start', '_end'))
df_merge['diff'] = df_merge['Time_end'] - df_merge['Time_start']
print(df_merge.to_string())

Output:

   id Name_start          Time_start Name_end            Time_end            diff
0   1      Start 2017-11-02 12:00:14      End 2017-11-07 22:45:13 5 days 10:44:59
1   2      Start 2018-01-28 06:53:09      End 2018-02-05 13:31:14 8 days 06:38:05

Johnny M · Answer 2 · 2020-02-06T00:21:26.807

Here you go.

Generate data:

df = pd.DataFrame({'ID':[1, 1,2, 2],
    'Name': ['Start', 'End', 'Start', 'End'],
    'Time': [pd.datetime(2020, 1,1,0,1,0), pd.datetime(2020, 1,2,0,0,0),
             pd.datetime(2020, 1,1,0,0,0), pd.datetime(2020, 1,2,0,0,0)]})

Get TimeDelta:

df_agg = df[df['Name'] == 'Start'].reset_index()[['ID', 'Time']]
df_agg = df_agg.rename(columns={"Time": "Start"})
df_agg['End'] = df[df['Name'] == 'End'].reset_index()['Time']
df_agg['TimeDelta'] = df_agg['End'] - df_agg['Start']

Get timediff as decimal value in days, like your example:

df_agg['TimeDiff_days'] = df_agg['TimeDelta'] / np.timedelta64(1,'D')
df_agg

Result:

    ID  Start               End         TimeDelta       TimeDiff_days
0   1   2020-01-01 00:01:00 2020-01-02  0 days 23:59:00 0.999306
1   2   2020-01-01 00:00:00 2020-01-02  1 days 00:00:00 1.000000

Calculate TimeDiff in Pandas based on a column values

2 Answers2