text_html_output_1.png
from datetime import timedelta

import pandas as pd

df = pd.read_csv('../input/flo-data2/flo_data_20k.csv')

date_columns = df.columns[df.columns.str.contains('date')]
df[date_columns] = df[date_columns].apply(pd.to_datetime)

today_date = df['last_order_date'].max() + timedelta(days=2)

rfm_df = df.groupby('master_id').agg({'last_order_date': lambda date: (today_date - date.max()).days, 'total_order_num': lambda num: num.sum(), 'total_value': lambda value: value.sum()})
rfm_df.columns = ['recency', 'frequency', 'monetary']
rfm_df['RECENCY_SCORE'] = pd.qcut(rfm_df['recency'], 5, labels=[5, 4, 3, 2, 1])
rfm_df['FREQUENCY_SCORE'] = pd.qcut(rfm_df['frequency'].rank(method='first'), 5, labels=[1, 2, 3, 4, 5])
rfm_df['MONETARY_SCORE'] = pd.qcut(rfm_df['monetary'], 5, labels=[1, 2, 3, 4, 5])
rfm_df['RF_SCORE'] = rfm_df[['RECENCY_SCORE', 'FREQUENCY_SCORE']].astype(str).apply(lambda x: ''.join(x), axis=1)
seg_map = {'[1-2][1-2]': 'hibernating', '[1-2][3-4]': 'at_risk', '[1-2]5': 'cant_loose', '3[1-2]': 'about_to_sleep', '33': 'need_attention', '[3-4][4-5]': 'loyal_customers', '41': 'promising', '51': 'new_customers', '[4-5][2-3]': 'potential_loyalists', '5[4-5]': 'champions'}
rfm_df['SEGMENT'] = rfm_df['RF_SCORE'].replace(seg_map, regex=True)

rfm_df.groupby('SEGMENT').agg({'recency': ['count', 'mean', 'min', 'max'], 'frequency': ['mean', 'min', 'max'], 'monetary': ['mean', 'min', 'max']})