Home Getting Value Error when calculating time diffrences from a reference time in Pandas

Questions

Getting Value Error when calculating time diffrences from a reference time in Pandas

January 9, 2022

Given a df

4,2019-01-15 07:00:00
0,2019-01-15 07:00:05
3,2019-01-15 07:00:10
3,2019-01-15 07:00:15
3,2019-01-15 07:00:20
1,2019-01-16 10:00:00
3,2019-01-16 10:00:05
2,2019-01-16 10:00:10
4,2019-01-16 10:00:15
0,2019-01-16 10:00:20

I would like to calculate the time diffrence between the first row and the subsequent rows under the column time and express the result in the unit of seconds.

df['elapse_second']=pd.Timedelta(df['time'] - df.loc[0,'time']).seconds / 3600.0

However the compiler return an error

ValueError: Value must be Timedelta, string, integer, float, timedelta or convertible, not Series

The code to reproduce the above error is

import pandas as pd
import numpy as np

np.random.seed(0)

np.random.seed(0)
data_time=['2019-01-15 7:00:00','2019-01-16 7:00:00']
lapse=5 # unit in second
alist=[pd.DataFrame(np.random.randint(5,size=(5)),columns=['data']) for _ in range (2)]
all_df=[]
for disdf,ndata_time in zip(alist,data_time):
  disdf['time']=pd.date_range(start=ndata_time, periods=len(disdf), freq='5S')
  all_df.append(disdf)

df=pd.concat(all_df).reset_index(drop=True)

# t1 = pd.to_datetime('2019-01-15 7:00:00')
t1=df.loc[0,'time']
df['elapse_second']=pd.Timedelta(df['time'] - df.loc[0,'time']).seconds / 3600.0

Expected output

4,2019-01-15 07:00:00,0
0,2019-01-15 07:00:05,5
3,2019-01-15 07:00:10,10
3,2019-01-15 07:00:15,15
3,2019-01-15 07:00:20,20
1,2019-01-16 10:00:00,43200
3,2019-01-16 10:00:05,43205
2,2019-01-16 10:00:10,43210
4,2019-01-16 10:00:15,43215
0,2019-01-16 10:00:20,43220

>Solution :

Since your time column is already a datetime, you can simply subtract the 1st row using df.loc and extract the seconds from thr result using Series.dt.seconds:

In [475]: df['difference_in_seconds'] = (df['time'] - df.loc[0, 'time']).dt.seconds

In [476]: df
Out[476]: 
   data                time  difference_in_seconds
0     4 2019-01-15 07:00:00                      0
1     0 2019-01-15 07:00:05                      5
2     3 2019-01-15 07:00:10                     10
3     3 2019-01-15 07:00:15                     15
4     3 2019-01-15 07:00:20                     20
5     1 2019-01-16 10:00:00                  10800
6     3 2019-01-16 10:00:05                  10805
7     2 2019-01-16 10:00:10                  10810
8     4 2019-01-16 10:00:15                  10815
9     0 2019-01-16 10:00:20                  10820