python - How can i get the average delivery time (stored as timedelta64[ns]) per day on Pandas?

Question

So, i have the following data frame on pandas:

I need to create another data frame with the average delivery time by day, by i'm having a hardtime with this task.

So, as you can see, I have all the 365 days of 2018 and I need to calculate what was the average time the user had to wait before getting his order delivered. I only need the hours, minutes and seconds, because the number of days will never be grater than 0. And the delivery time is timedelta64 format.

Thank you folks!

question from:https://stackoverflow.com/questions/65872018/how-can-i-get-the-average-delivery-time-stored-as-timedelta64ns-per-day-on-p

Answer 1

User groupby and mean

import pandas as pd
from io import StringIO

#Data preprocessing(ignore)
data = StringIO('''
2018-01-01 ,0 days 00:58:26 
2018-01-01 ,0 days 01:27:04 
2018-01-01 ,0 days 00:17:27 
2018-01-01 ,0 days 00:14:26 
2018-01-01 ,0 days 01:08:33 
''')

#Converting to datetime and timedelta object
df = pd.read_csv(data,names=['date','delivery_time'],parse_dates=['date'])
df['delivery_time'] = pd.to_timedelta(df['delivery_time'])

#Grouping by date and then finding mean of delivery time
df.groupby(['date']).mean(numeric_only=False)

Output:

            delivery_time
date    
2018-01-01  0 days 00:49:11.200000

using dt.components you can easily extract hours minutes and seconds from your timedelta column. Something like following should generate your result.

df.groupby(['date']).mean(numeric_only=False)['delivery_time'].dt.components.iloc[:, 1:4]