CODEWITHSUNDEEP
pythonpandasdatetime
gene
gene

Reputation: 11

How to calculate total running hours/ minutes/seconds of timestamps in a python pandas series?

I have a Dataframe with start and stop times of a motor. My end goal is to calculate how many total minutes, hours or seconds my motor has been running. I've been able to calculate the elapsed time by subtracting stop from start time and put them in a new column ELAPSED_TIME. Now I'm having difficulties getting the sum of my elapsed time. I would like to create a TOTAL column with the value 00:00:48 as per my sample data.

START                    STOP                      ELAPSED_TIME 
2020-08-23 11:52:04.587  2020-08-23 11:52:13.407   00:00:09
2020-08-23 12:52:02.703  2020-08-23 12:52:11.420   00:00:09
2020-08-23 13:52:01.840  2020-08-23 13:52:15.060   00:00:14 
2020-08-23 14:51:59.200  2020-08-23 14:52:14.577   00:00:16

My code currently looks like this with this error.

df_values["TOTAL"] = df_values["ELAPSED_TIME"].sum()

TypeError: unsupported operand type(s) for +: 'int' and 'datetime.time'

My dataType currently looks like this <class 'pandas.core.series.Series'> and I tried pd.to_datetime to change it but it didn't work.

Upvotes: 1

Views: 994

Answers (1)

Henry Ecker
Henry Ecker

Reputation: 35626

If you're going to do further computation on datetimes/timedeltas it's best to leave them in the correct type until the end as str type data will not behave as desired:

# Convert START and STOP to_datetime if not already
df['START'] = pd.to_datetime(df['START'])
df['STOP'] = pd.to_datetime(df['STOP'])

# Calculate Duration and round up to second (to match OP)
df['ELAPSED TIME'] = (df['STOP'] - df['START']).dt.ceil('S')

# Calculate Sum
df['TOTAL'] = df['ELAPSED TIME'].sum()

References:

df:

                    START                    STOP    ELAPSED TIME           TOTAL
0 2020-08-23 11:52:04.587 2020-08-23 11:52:13.407 0 days 00:00:09 0 days 00:00:48
1 2020-08-23 12:52:02.703 2020-08-23 12:52:11.420 0 days 00:00:09 0 days 00:00:48
2 2020-08-23 13:52:01.840 2020-08-23 13:52:15.060 0 days 00:00:14 0 days 00:00:48
3 2020-08-23 14:51:59.200 2020-08-23 14:52:14.577 0 days 00:00:16 0 days 00:00:48

Upvotes: 1

Related Questions