How can I find the difference between two dates in pandas?
I have the following data type:
id=["Train A","Train A","Train A","Train B","Train B","Train B"]
arrival_time = ["0"," 2016-05-19 13:50:00","2016-05-19 21:25:00","0","2016-05-24 18:30:00","2016-05-26 12:15:00"]
departure_time = ["2016-05-19 08:25:00","2016-05-19 16:00:00","2016-05-20 07:45:00","2016-05-24 12:50:00","2016-05-25 23:00:00","2016-05-26 19:45:00"]
To get the following data:
id arrival_time departure_time
Train A 0 2016-05-19 08:25:00
Train A 2016-05-19 13:50:00 2016-05-19 16:00:00
Train A 2016-05-19 21:25:00 2016-05-20 07:45:00
Train B 0 2016-05-24 12:50:00
Train B 2016-05-24 18:30:00 2016-05-25 23:00:00
Train B 2016-05-26 12:15:00 2016-05-26 19:45:00
The data type of the departure time and arrival time is datetime64 [ns].
How do I find the time difference between the 1st departure and the 2nd row arrival time? I got tired of the following code and it didn't work. For example, to find the time difference between [2016-05-19 08:25:00] and [2016-05-19 13:50:00].
df['Duration'] = df.departure_time.iloc[i+1] - df.arrival_time.iloc[i]
I think you need to convert the string first dates
to_datetime
, also 0
need to convert to NaN
:
df = pd.DataFrame({'id': id, 'arrival_time':arrival_time, 'departure_time':departure_time})
df['arrival_time'] = pd.to_datetime(df['arrival_time'].replace('0', np.nan))
#another solution for replace not dates to NaT
#df['arrival_time'] = pd.to_datetime(df['arrival_time'], errors='coerce')
df['departure_time'] = pd.to_datetime(df['departure_time'])
print (df)
arrival_time departure_time id
0 NaT 2016-05-19 08:25:00 Train A
1 2016-05-19 13:50:00 2016-05-19 16:00:00 Train A
2 2016-05-19 21:25:00 2016-05-20 07:45:00 Train A
3 NaT 2016-05-24 12:50:00 Train B
4 2016-05-24 18:30:00 2016-05-25 23:00:00 Train B
5 2016-05-26 12:15:00 2016-05-26 19:45:00 Train B
Then shift
column departure_time
by group id
c groupby
and subtract column arrival_time
.
df['Duration'] = df.groupby('id')['departure_time'].shift() - df['arrival_time']
print (df)
arrival_time departure_time id Duration
0 NaT 2016-05-19 08:25:00 Train A NaT
1 2016-05-19 13:50:00 2016-05-19 16:00:00 Train A -1 days +18:35:00
2 2016-05-19 21:25:00 2016-05-20 07:45:00 Train A -1 days +18:35:00
3 NaT 2016-05-24 12:50:00 Train B NaT
4 2016-05-24 18:30:00 2016-05-25 23:00:00 Train B -1 days +18:20:00
5 2016-05-26 12:15:00 2016-05-26 19:45:00 Train B -1 days +10:45:00
Or maybe you need paging columns for a positive timedelta:
df['Duration'] = df['arrival_time'] - df.groupby('id')['departure_time'].shift()
print (df)
arrival_time departure_time id Duration
0 NaT 2016-05-19 08:25:00 Train A NaT
1 2016-05-19 13:50:00 2016-05-19 16:00:00 Train A 05:25:00
2 2016-05-19 21:25:00 2016-05-20 07:45:00 Train A 05:25:00
3 NaT 2016-05-24 12:50:00 Train B NaT
4 2016-05-24 18:30:00 2016-05-25 23:00:00 Train B 05:40:00
5 2016-05-26 12:15:00 2016-05-26 19:45:00 Train B 13:15:00
The latter can be converted timedelta
to seconds
with total_seconds
:
df['Duration'] = (df['arrival_time'] - df.groupby('id')['departure_time'].shift()).dt.total_seconds()
print (df)
arrival_time departure_time id Duration
0 NaT 2016-05-19 08:25:00 Train A NaN
1 2016-05-19 13:50:00 2016-05-19 16:00:00 Train A 19500.0
2 2016-05-19 21:25:00 2016-05-20 07:45:00 Train A 19500.0
3 NaT 2016-05-24 12:50:00 Train B NaN
4 2016-05-24 18:30:00 2016-05-25 23:00:00 Train B 20400.0
5 2016-05-26 12:15:00 2016-05-26 19:45:00 Train B 47700.0
source to share