RFM
1. 导入数据
import pandas as pd
import numpy as np
trad_flow = pd.read_csv('RFM_TRAD_FLOW.csv', encoding='gbk')
trad_flow.head()
| transID | cumid | time | amount | type_label | type |
---|
0 | 23351 | 10006 | 27SEP09:20:10:22 | 58.0 | 特价 | Special_offer |
---|
1 | 23372 | 30031 | 27SEP09:21:33:35 | 69.0 | 特价 | Special_offer |
---|
2 | 23447 | 40102 | 28SEP09:21:12:34 | 69.0 | 特价 | Special_offer |
---|
3 | 23448 | 20173 | 28SEP09:21:12:34 | 69.0 | 特价 | Special_offer |
---|
4 | 23449 | 40017 | 28SEP09:21:13:48 | 69.0 | 特价 | Special_offer |
---|
2.计算 RFM
M = pd.pivot_table(trad_flow, index='cumid', columns='type', values='amount', aggfunc='sum')
M.head()
type | Normal | Presented | Special_offer | returned_goods |
---|
cumid | | | | |
---|
10001 | 3608.0 | 0.0 | 420.0 | -694.0 |
---|
10002 | 1894.0 | 0.0 | NaN | -242.0 |
---|
10003 | 3503.0 | 0.0 | 156.0 | -224.0 |
---|
10004 | 2979.0 | 0.0 | 373.0 | -40.0 |
---|
10005 | 2368.0 | 0.0 | NaN | -249.0 |
---|
F = pd.pivot_table(trad_flow, index='cumid', columns='type', values='transID', aggfunc='count')
F.head()
type | Normal | Presented | Special_offer | returned_goods |
---|
cumid | | | | |
---|
10001 | 15.0 | 8.0 | 2.0 | 2.0 |
---|
10002 | 12.0 | 5.0 | NaN | 1.0 |
---|
10003 | 15.0 | 8.0 | 1.0 | 1.0 |
---|
10004 | 15.0 | 12.0 | 2.0 | 1.0 |
---|
10005 | 8.0 | 5.0 | NaN | 1.0 |
---|
R = pd.pivot_table(trad_flow, index='cumid', columns='type', values='time', aggfunc='max')
R.head()
type | Normal | Presented | Special_offer | returned_goods |
---|
cumid | | | | |
---|
10001 | 21JUL09:09:31:26 | 31MAR10:20:29:48 | 12OCT09:10:59:13 | 10JUL10:20:41:54 |
---|
10002 | 29JUL09:19:21:41 | 31MAY09:14:19:33 | None | 04SEP09:14:51:57 |
---|
10003 | 31AUG09:16:11:01 | 27JUN10:18:25:55 | 02JAN10:20:19:39 | 06JUN10:14:29:04 |
---|
10004 | 26NOV09:20:38:32 | 29AUG10:12:44:52 | 20AUG09:20:00:52 | 03JUN10:21:16:01 |
---|
10005 | 29MAY10:15:49:22 | 30JUN10:22:08:49 | None | 08APR10:19:53:48 |
---|
3.衡量客户对打折商品的偏好
M['Special_offer'] = M['Special_offer'].fillna(0)
M['spe_ratio'] = M['Special_offer'] / (M['Special_offer'] + M['Normal'])
M_rank = M.sort_values('spe_ratio', ascending=False, na_position='last').head()
M_rank
type | Normal | Presented | Special_offer | returned_goods | spe_ratio |
---|
cumid | | | | | |
---|
10151 | 765.0 | 0.0 | 870.0 | NaN | 0.532110 |
---|
40033 | 1206.0 | 0.0 | 761.0 | -848.0 | 0.386884 |
---|
40236 | 1155.0 | 0.0 | 691.0 | -793.0 | 0.374323 |
---|
30225 | 1475.0 | 0.0 | 738.0 | -301.0 | 0.333484 |
---|
20068 | 1631.0 | 0.0 | 731.0 | -239.0 | 0.309483 |
---|
M_rank['spe_ratio_group'] = pd.qcut(M_rank['spe_ratio'], 4)
M_rank.head()
type | Normal | Presented | Special_offer | returned_goods | spe_ratio | spe_ratio_group |
---|
cumid | | | | | | |
---|
10151 | 765.0 | 0.0 | 870.0 | NaN | 0.532110 | (0.387, 0.532] |
---|
40033 | 1206.0 | 0.0 | 761.0 | -848.0 | 0.386884 | (0.374, 0.387] |
---|
40236 | 1155.0 | 0.0 | 691.0 | -793.0 | 0.374323 | (0.333, 0.374] |
---|
30225 | 1475.0 | 0.0 | 738.0 | -301.0 | 0.333484 | (0.308, 0.333] |
---|
20068 | 1631.0 | 0.0 | 731.0 | -239.0 | 0.309483 | (0.308, 0.333] |
---|