python - pandas get difference of 2 times assuming if end_time is lower than start_time it is the next day

Question

Assuming this is my dataframe:

date        start_time end_time
1/1/2018     20:00       21:00
1/1/2018     23:00       1:00

I want to add another column, named duration which is obviously end_time - start_time

My problem is that if I write something like:

pd.to_datetime(train_2.end_time,format='%H:%M:%S')-pd.to_timedelta(train_2.start_time))

It thinks that the second line is negative (as 23:00>1:00), while it's really positive as 1:00 refers to the next day (1/2/2018), so I want the duration to be 2 hours.

How can I achieve such a result?

Any help will be appreciated!

标签： pythonpandasdatetimetimedelta

Answer 1

您可以尝试通过转换为日期戳来进行减法，并且对于所有负值的例外情况，添加额外的天数

df['duration'] = pd.to_datetime(df.end_time) -  pd.to_datetime(df.start_time)
df.loc[df.duration.dt.total_seconds() <0,'duration'] += pd.Timedelta(1,'D')

出去：

date    start_time  end_time    duration
0   1/1/2018    20:00   21:00   01:00:00
1   1/1/2018    23:00   1:00    02:00:00