Skip to content
Advertisement

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

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,86400
3,2019-01-16 10:00:05,86405
2,2019-01-16 10:00:10,86410
4,2019-01-16 10:00:15,86415
0,2019-01-16 10:00:20,86420

Advertisement

Answer

You need to change the dtype to 'timedelta64[s]' since you want the difference in seconds.

Replace

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

with

df['elapse_second']=(df['time'] - df.loc[0,'time']).astype('timedelta64[s]')

and your code will produce the expected outcome.

Output:

   data                time  elapse_second
0     4 2019-01-15 07:00:00            0.0
1     0 2019-01-15 07:00:05            5.0
2     3 2019-01-15 07:00:10           10.0
3     3 2019-01-15 07:00:15           15.0
4     3 2019-01-15 07:00:20           20.0
5     1 2019-01-16 07:00:00        86400.0
6     3 2019-01-16 07:00:05        86405.0
7     2 2019-01-16 07:00:10        86410.0
8     4 2019-01-16 07:00:15        86415.0
9     0 2019-01-16 07:00:20        86420.0
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement