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