How to calculate time difference between two pandas column

Question

My df looks like,

    start               stop
0   2015-11-04 10:12:00 2015-11-06 06:38:00
1   2015-11-04 10:23:00 2015-11-05 08:30:00
2   2015-11-04 14:01:00 2015-11-17 10:34:00
4   2015-11-19 01:43:00 2015-12-21 09:04:00

print(time_df.dtypes)

start       datetime64[ns]
stop        datetime64[ns]

dtype: object

I am trying to find the time difference between, stop and start.

I tried, pd.Timedelta(df_time['stop']-df_time['start']) but it gives TypeError: data type "datetime" not understood

df_time['stop']-df_time['start'] also gives same error.

My expected output,

 2D,?H
 1D,?H
 ...
 ...

jezrael · Accepted Answer · 2018-07-17T12:36:36.517

4

You need omit pd.Timedelta, because difference of times return timedeltas:

df_time['td'] = df_time['stop']-df_time['start']
print (df_time)
                start                stop               td
0 2015-11-04 10:12:00 2015-11-06 06:38:00  1 days 20:26:00
1 2015-11-04 10:23:00 2015-11-05 08:30:00  0 days 22:07:00
2 2015-11-04 14:01:00 2015-11-17 10:34:00 12 days 20:33:00

EDIT: Another solution is subtract numpy arrays:

df_time['td'] = df_time['stop'].values - df_time['start'].values
print (df_time)
                start                stop               td
0 2015-11-04 10:12:00 2015-11-06 06:38:00  1 days 20:26:00
1 2015-11-04 10:23:00 2015-11-05 08:30:00  0 days 22:07:00
2 2015-11-04 14:01:00 2015-11-17 10:34:00 12 days 20:33:00

edited Jul 17 '18 at 12:36

answered Jul 17 '18 at 12:24

jezrael

822,522
95
1,334
1,252

tried that too, `TypeError: data type "datetime" not understood` – Pyd Jul 17 '18 at 12:27
@pyd - What is your pandas version? – jezrael Jul 17 '18 at 12:27
pandas version '0.19.2' – Pyd Jul 17 '18 at 12:28
@pyd - Is possible upgrade? – jezrael Jul 17 '18 at 12:28
@pyd - It is problem with numpy version, upgrade should help, check [this](https://github.com/facebook/prophet/issues/256#issuecomment-354906674) – jezrael Jul 17 '18 at 12:32
actually I cannot do, my organization firewall blocks pip requests. isin't possible in my version – Pyd Jul 17 '18 at 12:32
ok, I will try upgrade pandas and check, and update you – Pyd Jul 17 '18 at 12:32
2

Only ine idea - `df_time['td'] = df_time['stop'].values - df_time['start'].values` – jezrael Jul 17 '18 at 12:33
that worked, thank you :) – Pyd Jul 17 '18 at 12:35
how to drop `-` in `-2 days +03:34:00` returned output – Pyd Jul 17 '18 at 12:39
can you tell me, how to open my jupyter notebook on my virtual enviroinment, where I have pandas upgraded version – Pyd Jul 17 '18 at 12:40
1

@pyd - Then use `df_time['td'] = np.abs(df_time['stop'].values - df_time['start'].values)` – jezrael Jul 17 '18 at 12:40
1

@pyd - You can check [this](https://stackoverflow.com/q/37085665) - never do it, so no experience – jezrael Jul 17 '18 at 12:43

score 1 · Answer 2 · answered Jul 17 '18 at 12:30

1

First make sure that you have dates in your column

data.loc[:, 'start'] = pd.to_datetime(data.loc[:, 'start'])
data.loc[:, 'stop'] = pd.to_datetime(data.loc[:, 'stop'])

Then substract

data['delta'] = data['stop'] - data['start']

answered Jul 17 '18 at 12:30

Treizh

322
5
12

I guess it's just a typo, but just to be sure: sometimes you write time_df and other times df_time – Treizh Jul 17 '18 at 12:36

How to calculate time difference between two pandas column

2 Answers2

Linked

Related