I’m trying to get the total hours worked through with this line of code:
df['Hours'] = (df['T1 Finish'] - df['T1 Start']) * 24
The data entered for Start and Finish is in the 24hour clock format (example start 13:00, finish 21:00)
I’ve tried reformatting it and converting with this:
df['T1 Finish'] = pd.to_datetime(df['T1 Finish'].astype(str))
df['T1 Start'] = pd.to_datetime(df['T1 Start'].astype(str))
I’ve also tried:
df['T1 Finish'] / np.timedelta64(1, 'h')
df['T1 Start'] / np.timedelta64(1, 'h')
But even after doing this, I’m still getting an error but this time the TypeError. Is there any other way I can obtain the number of hours worked from the subtraction of the finish and start times?
I’ve read up on the documentation around .to_timedelta, but I don’t really understand how to apply it to my code.
Thanks.
>Solution :
You can substract timedeltas or datetimes and output are timedeltas, which are converted to seconds by Series.dt.total_seconds, for hours divide by 3600:
df['T1 Finish'] = pd.to_timedelta(df['T1 Finish'] + ':00')
df['T1 Start'] = pd.to_timedelta(df['T1 Start'] + ':00')
Or:
df['T1 Finish'] = pd.to_datetime(df['T1 Finish'].astype(str))
df['T1 Start'] = pd.to_datetime(df['T1 Start'].astype(str))
For dates use:
df['T1 Finish'] = pd.to_datetime(df['T1 Finish'].astype(str)).dt.normalize()
df['T1 Start'] = pd.to_datetime(df['T1 Start'].astype(str)).dt.normalize()
For times:
df['T1 Finish'] = pd.to_timedelta(df['T1 Finish'].astype(str))
df['T1 Start'] = pd.to_timedelta(df['T1 Start'].astype(str))
df['Hours'] = (df['T1 Finish'] - df['T1 Start']).dt.total_seconds() / 3600