How to recommend recurring action

Question

I have a dataset that contains the following columns: id_customer (customer identifier), id_receiver (identifier of the person who receives the money), money (money sent), and the date the money was sent. I need to know which customers recurrently send money to the same person and recommend them to send money (recommend amount of money and date). How can I do it? Dataset example:

df= pd.DataFrame({'id_customer ': ['1', '1', '1'],
                  'id_receiver ': ['A', 'A', 'B'],
                  'date': [20230101, 20230201, 20230506],
                  'money': [10,10,50] 
})

In this example, client 1 sends person A on the 1st of each month 10 euros. I want to recommend that on the 1st of the following month you send 10 euros to person A

Answer 1

I would use a custom groupby.agg to get the number of transfers, the average frequency and average amount:

out = (
df.assign(date=pd.to_datetime(df['date'], format='%Y%m%d'))
 .sort_values(by='date')
 .groupby(['id_customer', 'id_receiver'], as_index=False)
 .agg(**{'number_of_transfers': ('date', 'count'),
         'average_frequency': ('date', lambda x: x.diff().mean()),
         'average_amount': ('money', 'mean')
        })
)

Output:

  id_customer id_receiver  number_of_transfers average_frequency  average_amount
0           1           A                    2           31 days            10.0
1           1           B                    1               NaT            50.0