Pandas: How to I find the average number of days between visits, grouped by customer?

user_14814034 0
Viewed 78

In Python/Pandas, I want to create a column in my dataframe that shows the average number of days between customer visits at a venue. That is, for each customer, what are the average number of days between that customer's visits?

Data looks like

Image of My Data

Sorry I'm really inexperienced and don't know how to type the data up other than this. I am following the solution in this StackOverflow answer, except that that person wanted the average number of days between visits in general, and I want days between visits for each customer. Thank you.

pandas-groupby pandas dataframe python
2 Answers

For each customer, you would need to have date_of_first_visit and date_of_most_recent_visit as well as the number_of_visits. Then the equation would be something like

days_since_first = date_of_most_recent_visit - date_of_first_visit

average_days_between = days_since_first / number_of_visits

user_163944420

I think this might work:

avg_days_btw_visit_p_customer = df.groupby('CustomerID', as_index=False)['Days_Btw_Visit'].agg('mean')

This will return the folowing:

     CustomerID     Days_Btw_Visit
0             1                9.5

1             2               29.0

2             3                NaN

3             4                3.0

Also, if you want to get rid of the NaN, you can use:

avg_days_btw_visit_p_customer = avg_days_btw_visit_p_customer.dropna()
user_105318350
Related