CODEWITHSUNDEEP
pythonpandas
echo55
echo55

Reputation: 329

Pandas, average diff date per id

I have a dataframe that look like this :

745416  ,2019-03-13 ,0.89   ,_3428611
1015533 ,2019-03-13 ,1.99   ,_3428674
486224  ,2019-03-13 ,16.99  ,_3427319
486224  ,2019-03-13 ,9.99   ,_3427320
745416  ,2019-03-13 ,0.89   ,_3428629
176106  ,2019-03-13 ,1.99   ,_3428983
486224  ,2019-03-13 ,13.49  ,_3427321
1015533 ,2019-03-13 ,1.99   ,_3428675
288537  ,2019-03-13 ,15.99  ,_3426780
745416  ,2019-03-13 ,1.19   ,_3428626
176106  ,2019-03-13 ,1.79   ,_3428987
67092   ,2019-03-13 ,16.99  ,_3426557
67092   ,2019-03-13 ,15.49  ,_3426562
67092   ,2019-03-13 ,11.49  ,_3426558

It's a list of orders, one ID can have many rows , the date can change of course.

I want to compute the average interval between orders for each IDs.

What i tried to do:

grouped by id

For each ID do a diff().mean() on the Date columns.

grouped = df.groupby('ID')
for a in grouped:
   r['id'] = a['id']
   r['avg_interval'] = a['date'].diff().mean()

The output i want:

 123123, 2 days
 123453, 1 day 5 hours
 122656, 4 days
 143143, 2 days
 123123, 2 days
 17 2767, 2 days

I'm sure there is a better way to do it .

Upvotes: 0

Views: 324

Answers (1)

Raghul Raj
Raghul Raj

Reputation: 1458

You can do something like this:

df.groupby('ID').apply(lambda x: ((x['date']-x['date'].shift()).fillna(0)).mean())

But before you do this, make sure that the date column is converted to python date format:

df['date']=pd.to_datetime(df['date'],format='%Y-%m-%d')

Upvotes: 3

Related Questions