Calculating Average Time Difference Among Items Grouped By A Specific Column

I have the following dataframe: userid | time 1 22.01.2001 13:00 1 22.01.2001 13:05 1 22.01.2001 13:07 2 22.01.2001 14:00 2 22.01.2001

Solution 1:

Consider the following approach:

In [84]: df.sort_values('time').groupby('userid')['time'] \
           .apply(lambda x: x.diff().dt.seconds.mean()/60)
Out[84]:
userid
13.500000219.66666732.0000004          NaN
Name: time, dtype: float64

Some explanations:

First we sort the DF by time column, otherwise we might have negative difference.

Then we group by userid and for each group we calculate a time difference for all consecutive rows (sorted) - this will produce a Series of timedelta64[ns] dtype, which has an .dt.seconds accessor.

Using .dt.seconds.mean() we can calculate the average for each group

UPDATE:

take the mean over only the differences that are smaller than 60 minutes

In [122]: threshold = 60
     ...:
     ...: (df.sort_values('time').groupby('userid')['time']
     ...:    .apply(lambda x: (x.diff().dt.seconds/60)
     ...:                     .to_frame('diff')
     ...:                     .query("diff < @threshold")['diff'].mean()))
     ...:
Out[122]:
userid
13.500000219.66666732.0000004          NaN
Name: time, dtype: float64