Groupby customer and shop - get average frequency of transactions. Problem with dates

user_11222963 0
Viewed 56

I have the following DF of transactions. Date format is year/month/day

print(df)

   customer_id       shop date_of_transaction
0         John  McDonalds          2020-02-03
1         John  McDonalds          2020-02-04
2         John  McDonalds          2020-02-05
3         John        KFC          2020-02-06
4         John        KFC          2020-02-07
5         John        KFC          2020-02-08
6         Mary  McDonalds          2020-02-09
7         Mary  McDonalds          2020-02-10
8         Mary  McDonalds          2020-02-11
9         Mary        KFC          2020-02-12
10        Mary        KFC          2020-02-13
11         Joe        KFC          2020-02-14
12         Joe  McDonalds          2020-02-15
13         Joe  McDonalds          2020-02-16
14         Joe  McDonalds          2020-02-17
15         Joe        KFC          2020-02-18
16         Joe        KFC          2020-02-19
17         Joe        KFC          2020-02-20
18         Joe  MCDonalds          2020-02-21

I want to get the average frequency of their transactions, for each shop.

For example, Joe went to McDonalds 4 times between 15th Feb and 21st Feb. That's 6 days between his first and last transactions. So he would go to McDonalds every 1.5 days.

I want to create a new dataframe with this info. So I try this:

df.groupby(['customer_id','shop'])['date_of_transaction'].apply(lambda  x: (max(x) - min (x))/len(x))

customer_id  shop     
Joe          KFC         1 days 12:00:00
             McDonalds   1 days 12:00:00
John         KFC         0 days 16:00:00
             McDonalds   0 days 16:00:00
Mary         KFC         0 days 12:00:00
             McDonalds   0 days 16:00:00

Joe's average frequency for McDonalds here says 1 days. It should be 1.5 days.

If I remove the division, we get:

df.groupby(['customer_id','shop'])['date_of_transaction'].apply(lambda  x:(max(x) - min (x)))

customer_id  shop     
Joe          KFC         6 days
             McDonalds   6 days
John         KFC         2 days
             McDonalds   2 days
Mary         KFC         1 days
             McDonalds   2 days

It's just when I try to divide it by the number of visits for each person in each shop, it doesn't work.

I have tried adding astype(int) to (max(x) - min (x)) but it doesn't work. I know it is a problem with the timedelta object, but I can't convert it to int. I have also added .dt.days to the timedelta object, with no luck.

Ideally, I would like to finish with a dataframe like this (note - frequency figures are made up):

  customer_id  McDonalds Frequency  KFC Frequency
0        John                    1              2
1        Mary                    3              4
2         Joe                    5              6

My practice df. If you load the df, you can convert the date with dayfirst:

df['date_of_transaction'] = pd.to_datetime(df['date_of_transaction'],dayfirst=True)


df.to_dict()

{'customer_id': {0: 'John', 1: 'John', 2: 'John', 3: 'John', 4: 'John', 5: 'John', 6: 'Mary', 7: 'Mary', 8: 'Mary', 9: 'Mary', 10: 'Mary', 11: 'Joe', 12: 'Joe', 13: 'Joe', 14: 'Joe', 15: 'Joe', 16: 'Joe', 17: 'Joe', 18: 'Joe'}, 'shop': {0: 'McDonalds', 1: 'McDonalds', 2: 'McDonalds', 3: 'KFC', 4: 'KFC', 5: 'KFC', 6: 'McDonalds', 7: 'McDonalds', 8: 'McDonalds', 9: 'KFC', 10: 'KFC', 11: 'KFC', 12: 'McDonalds', 13: 'McDonalds', 14: 'McDonalds', 15: 'KFC', 16: 'KFC', 17: 'KFC', 18: 'McDonalds'}, 'date_of_transaction': {0: Timestamp('2020-02-03 00:00:00'), 1: Timestamp('2020-02-04 00:00:00'), 2: Timestamp('2020-02-05 00:00:00'), 3: Timestamp('2020-02-06 00:00:00'), 4: Timestamp('2020-02-07 00:00:00'), 5: Timestamp('2020-02-08 00:00:00'), 6: Timestamp('2020-02-09 00:00:00'), 7: Timestamp('2020-02-10 00:00:00'), 8: Timestamp('2020-02-11 00:00:00'), 9: Timestamp('2020-02-12 00:00:00'), 10: Timestamp('2020-02-13 00:00:00'), 11: Timestamp('2020-02-14 00:00:00'), 12: Timestamp('2020-02-15 00:00:00'), 13: Timestamp('2020-02-16 00:00:00'), 14: Timestamp('2020-02-17 00:00:00'), 15: Timestamp('2020-02-18 00:00:00'), 16: Timestamp('2020-02-19 00:00:00'), 17: Timestamp('2020-02-20 00:00:00'), 18: Timestamp('2020-02-21 00:00:00')}}
pandas python
2 Answers

Your issue here is that you're treating the return value as a date when it's actually a time delta, 1 days 12:00:00 is 1.5 days as in one day and 12 hours have elapsed.

Let's reshape your data a little using an aggregated groupby, then we can edit your timedelta using np.timedelta64:

df1 = df.groupby(['customer_id','shop']).agg(mind=('date_of_transaction','min'),
                                      maxd=('date_of_transaction','max'),
                                      no_visits=('customer_id','count')).reset_index(0)


print(df1)

                            mind       maxd  no_visits
customer_id shop                                      
Joe         KFC       2020-02-20 2020-02-14          4
            McDonalds 2020-02-21 2020-02-15          4
John        KFC       2020-02-08 2020-02-06          3
            McDonalds 2020-02-05 2020-02-03          3
Mary        KFC       2020-02-13 2020-02-12          2
            McDonalds 2020-02-11 2020-02-09          3

df1['timedelta'] = ((df1['maxd'] - df1['mind']) / df1['no_visits']) / np.timedelta64(1,'D')

                           mind       maxd  no_visits  timedelta
customer_id shop                                                 
Joe         KFC       2020-02-20 2020-02-14          4   1.500000
            McDonalds 2020-02-21 2020-02-15          4   1.500000
John        KFC       2020-02-08 2020-02-06          3   0.666667
            McDonalds 2020-02-05 2020-02-03          3   0.666667
Mary        KFC       2020-02-13 2020-02-12          2   0.500000
            McDonalds 2020-02-11 2020-02-09          3   0.666667

Then we use crosstab:

df2 = (
    pd.crosstab(df1["customer_id"], df1.index, df1["timedelta"], aggfunc="first")
    .add_suffix("_visits")
    .reset_index(0)
)

print(df2)

col_0 customer_id  KFC_visits  McDonalds_visits
0             Joe    1.500000          1.500000
1            John    0.666667          0.666667
2            Mary    0.500000          0.666667

or a wonderful one line by our resident guru Scott Boston

df.groupby(["customer_id", "shop"])["date_of_transaction"].agg(
    lambda x: (np.ptp(x) / np.timedelta64(1, "D")) / x.count()
).unstack(1).add_suffix('_visits')


shop         KFC_visits  McDonalds_visits
customer_id                              
Joe            1.500000          1.500000
John           0.666667          0.666667
Mary           0.500000          0.666667
user_93751020

I tried to use dt.days to convert the Timedelta object. Using .days works.

So...


grouped = df.groupby(['customer_id','shop'])['date_of_transaction'].apply(lambda  x: ((max(x) - min (x)).days)/len(x))


customer_id  shop     
Joe          KFC          1.500000
             McDonalds    1.500000
John         KFC          0.666667
             McDonalds    0.666667
Mary         KFC          0.500000
             McDonalds    0.666667

And to get my desired dataframe shape, I used pd.pivot_table:


pd.pivot_table(grouped, values='date_of_transaction', index="customer_id", columns='shop')

shop              KFC  McDonalds
customer_id                     
Joe          1.500000   1.500000
John         0.666667   0.666667
Mary         0.500000   0.666667

Another solution is doing the aggregate function direction in the pivot table, no need for groupby first:

agg_func = lambda  x: ((max(x) - min (x)).days)/len(x)
df.pivot_table('date_of_transaction', 'customer_id', 'shop', agg_func , 0)
user_112229630
Related