CODEWITHSUNDEEP
pythonpandas
Ratchainant Thammasudjarit
Ratchainant Thammasudjarit

Reputation: 3177

Find average day gap from group by pandas

I'm using python 3 and have a dataframe as follows:

df = pd.DataFrame({'product':['P01','P01','P01','P02'],'date':['2017-01-01','2017-01-15','2017-01-20','2017-01-01']})

So my data look like this

product      date
P01          2017-01-01
P01          2017-01-15
P01          2017-01-20
P02          2017-01-01

I would like to find the average day gap group by product and the expected results is the following

product       daygap (avg)
P01        (14 + 5)/2 = 9.5  -> 14 is the difference between 2017-01-15 and 2017-01-01
                             -> 5 is the difference between 2017-01-20 and 2017-01-15
P02             0 -> There is no day gap

May I have your suggestions? Thank you in advance.

Upvotes: 0

Views: 260

Answers (1)

akuiper
akuiper

Reputation: 215047

You can use diff with mean on date column after grouping data frame by product:

df.date = pd.to_datetime(df.date)
df.groupby('product').date.agg(lambda x: x.diff().mean()).dt.total_seconds()/(24*3600)

#   product
#P01    9.5
#P02    NaN
#Name: date, dtype: float64

Upvotes: 1

Related Questions