Calculate difference of 2 dates in minutes in pandas

Question

I have 2 columns in a dataframe. I want to calculate the difference of 2 columns in minutes and write the result into a new column

Input

Planned Pickup date/time    Actual Pickup date/time      
07/05/2018 09:28:00         07/05/2018 09:33:15               
14/05/2018 17:00:00         15/05/2018 08:44:08               
15/05/2018 17:00:00         15/05/2018 10:52:50              
15/05/2018 17:00:00         15/05/2018 15:03:34             
15/05/2018 17:00:00         15/05/2018 15:03:34              
16/05/2018 17:00:00         16/05/2018 16:00:38

I want to calculate the difference the actual and planned pickup in minutes and write the result into a new column in dataframe called data['time difference']

Expected Output

Planned Pickup date/time    Actual Pickup date/time      Time Difference
07/05/2018 09:28:00         07/05/2018 09:33:15               5
14/05/2018 17:00:00         15/05/2018 08:44:08               944
15/05/2018 17:00:00         15/05/2018 10:52:50              -368
15/05/2018 17:00:00         15/05/2018 15:03:34              -117
15/05/2018 17:00:00         15/05/2018 15:03:34              -117
16/05/2018 17:00:00         16/05/2018 16:00:38              -60

How can this done in pandas

jezrael · Answer 1 · 2018-07-24T06:28:36.873

Use:

data['time difference'] = ((pd.to_datetime(data['Actual Pickup date/time']) - 
                            pd.to_datetime(data['Planned Pickup date/time']))
                                .dt.total_seconds() / 60)
print (data)

  Planned Pickup date/time Actual Pickup date/time  time difference
0      07/05/2018 09:28:00     07/05/2018 09:33:15         5.250000
1      14/05/2018 17:00:00     15/05/2018 08:44:08       944.133333
2      15/05/2018 17:00:00     15/05/2018 10:52:50      -367.166667
3      15/05/2018 17:00:00     15/05/2018 15:03:34      -116.433333
4      15/05/2018 17:00:00     15/05/2018 15:03:34      -116.433333
5      16/05/2018 17:00:00     16/05/2018 16:00:38       -59.366667

Or if need floor values:

data['time difference'] = ((pd.to_datetime(data['Actual Pickup date/time']) - 
                            pd.to_datetime(data['Planned Pickup date/time']))
                                .astype('<m8[m]').astype(int))
print (data)


  Planned Pickup date/time Actual Pickup date/time  time difference
0      07/05/2018 09:28:00     07/05/2018 09:33:15                5
1      14/05/2018 17:00:00     15/05/2018 08:44:08              944
2      15/05/2018 17:00:00     15/05/2018 10:52:50             -368
3      15/05/2018 17:00:00     15/05/2018 15:03:34             -117
4      15/05/2018 17:00:00     15/05/2018 15:03:34             -117
5      16/05/2018 17:00:00     16/05/2018 16:00:38              -60

Calculate difference of 2 dates in minutes in pandas

1 Answers1