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.

CodePudding user response：

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