Menu
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
456
There are several ways to calculate the time difference between two dates in Python using Pandas. The first is to subtract one date from the other. This returns a timedelta such as 0 days 05:00:00 that tells us the number of days, hours, minutes, and seconds between the two dates.
To get the difference between two-time, subtract time1 from time2. A result is a timedelta object. The timedelta represents a duration which is the difference between two-time to the microsecond resolution. To get a time difference in seconds, use the timedelta.
Comparison between pandas timestamp objects is carried out using simple comparison operators: >, <,==,< = , >=. The difference can be calculated using a simple '–' operator. Given time can be converted to pandas timestamp using pandas. Timestamp() method.
Subtract one datetime object from another to return a timedelta object. Call timedelta. total_seconds() with timedelta as the object from the previous step, to return the total seconds between the two datetime objects. Divide the result by 60 to return the time difference in minutes.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
