[FIXED] How to calculate average of a timedelta column?

Issue

I want to remove the '0 days' from my results, while being able to perform calculations with the formatted results

0 0 days 00:27:00 1 0 days 01:22:00 2 0 days 00:52:00 3 0 days 01:04:00 4 0 days 00:07:00 Name: Tempo Formatado, dtype: timedelta64[ns]

This is the result that I expected:

00:27:00 01:22:00 00:52:00 01:04:00 00:07:00

and being able to calculate the average, which in this case is 00:46

Solution

To calculate the average of a timedelta column while formatting the results to exclude the '0 days' part, you can follow these steps:

Convert the timedelta values to seconds. This simplifies the computation of the average. Calculate the average of these seconds. Convert the average back to a timedelta, and then format it to display only the time part. Here's a Python code snippet using Pandas to achieve this:

import pandas as pd

# Assuming you have a DataFrame 'df' with a timedelta column named 'Tempo Formatado'
# Example DataFrame creation:
data = {'Tempo Formatado': pd.to_timedelta(['0 days 00:27:00', '0 days 01:22:00', 
                                           '0 days 00:52:00', '0 days 01:04:00', 
                                           '0 days 00:07:00'])}
df = pd.DataFrame(data)

# Convert the timedelta to seconds
df['seconds'] = df['Tempo Formatado'].dt.total_seconds()

# Calculate the average in seconds
average_seconds = df['seconds'].mean()

# Convert the average back to timedelta
average_timedelta = pd.to_timedelta(average_seconds, unit='s')

# Format the result to exclude 'days'
average_formatted = str(average_timedelta).split('days ')[-1].strip()

print("Average time:", average_formatted)

Answered By - Vinicius Lima