mysql存0100058_RFM模型了解和数据实践以及dataframe用sql语句查询

RFM模型了解和数据实践以及dataframe用sql语句查询RFM模型基础知识数据处理实践读取文件先求R的值同理求F计算M并转透视表合并并且分类利用sqlite3来查询(也就是sql语句查询)使用sqlalchemy来查询添加数据到数据库查询

RFM模型基础知识

R(Recency)：客户最近一次交易时间的间隔。R值越大，表示客户交易发生的日期越久，反之则表示客户交易发生的日期越近。

F(Frequency)：客户在最近一段时间内交易的次数。F值越大，表示客户交易越频繁，反之则表示客户交易不够活跃。

M(Monetary)：客户在最近一段时间内交易的金额。M值越大，表示客户价值越高，反之则表示客户价值越低。

数据处理实践

博客会上传一个csv文件里面是用户的交易信息，包含了订单id，用户id，时间，价格，商品类别等，编码是gbk。

文件比较大所以输出只截取前几个

本文是处理这个文件并得出rfm模型(并且都使用jupyter notebook)，如果是pycharm输出时加上print即可

读取文件

import pandas as pd

import numpy as np

data = pd.read_csv('RFM_TRAD_FLOW.csv', encoding='gbk')

data

输出(只截了前五个，也可以使用head()方法截取)

transIDcumidtimeamounttype_labeltype

094071000114JUN09:17:58:34199.0正常Normal

196251000116JUN09:15:09:13369.0正常Normal

2118371000101JUL09:14:50:36369.0正常Normal

3266291000114DEC09:18:05:32359.0正常Normal

4308501000112APR10:13:02:20399.0正常Normal

...

先求R的值

R也就是最近购买的时间，把时间转成时间戳即可

import time

# 转成时间戳

data['time'] = data['time'].map(lambda x:time.mktime(time.strptime(x, '%d%b%y:%H:%M:%S')))

# 分组求出最近时间，也就是时间戳最大的

group_obj = data.groupby(['cumid', 'type_label'])

R = group_obj[['time']].max()

R

输出(截取前几个)：

time

cumidtype_label

10001正常1.284699e+09

特价1.255316e+09

赠送1.284197e+09

退货1.278766e+09

10002正常1.276953e+09

赠送1.278129e+09

退货1.252047e+09

...

转透视表

转透视表以用户id为行索引，商品类别为列索引

会发现有很多NaN数据，这时候要进行数据清理

# 转透视表

r_trans = pd.pivot_table(R,index='cumid',columns='type_label',values='time')

# 处理NaN值，只有这两列有空值

r_trans[['特价','退货']] = r_trans[['特价','退货']].apply(lambda x:x.replace(np.nan,min(x)),axis=0)

# 添加一列为三个值最大的也就是最近的时间，这里不查找退货的

r_trans['r_max'] = r_trans[['正常', '特价','赠送']].apply(lambda x:max(x),axis=1)

r_trans

输出，这样就得到了最近的购买时间也就是R值

type_label正常特价赠送退货r_max

cumid

100011.284699e+091.255316e+091.284197e+091.278766e+091.284699e+09

100021.276953e+091.250297e+091.278129e+091.252047e+091.278129e+09

100031.282983e+091.262435e+091.280805e+091.275806e+091.282983e+09

100041.279534e+091.254833e+091.283057e+091.275571e+091.283057e+09

100051.277448e+091.250297e+091.282127e+091.270728e+091.282127e+09

100061.278652e+091.254053e+091.277637e+091.252659e+091.278652e+09

100071.285138e+091.250408e+091.279959e+091.272427e+091.285138e+09

...

同理求F

# 直接取某客户某商品有多少订单号就行了

F = group_obj[['transID']].count()

# 转透视表

f_trans = pd.pivot_table(F,index='cumid',columns='type_label',values='transID')

# NaN值转成0

f_trans.fillna(0, inplace=True)

# 退货的值是正的，计算时要为负数

f_trans['退货'] = f_trans['退货'].map(lambda x:-x)

# 计算的结果添加一列

f_trans['f_total'] = f_trans.apply(lambda x:sum(x), axis=1)

f_trans

输出结果：

type_label正常特价赠送退货f_total

cumid

1000115.02.08.0-2.023.0

1000212.00.05.0-1.016.0

1000315.01.08.0-1.023.0

1000415.02.012.0-1.028.0

100058.00.05.0-1.012.0

...

计算M并转透视表

# M就是总价，这里不限时间

M = group_obj[['amount']].sum()

# 转透视表

m_trans = pd.pivot_table(M,index='cumid',columns='type_label',values='amount')

# 还是填充为0

m_trans[['特价','退货']] = f_trans[['特价','退货']].fillna(0)

# 增加一列总价

m_trans['m_total'] =m_trans.apply(lambda x:sum(x),axis=1)

m_trans

输出：

type_label正常特价赠送退货m_total

cumid

100013608.002.00.0-2.03608.00

100021894.000.00.0-1.01893.00

100033503.001.00.0-1.03503.00

100042979.002.00.0-1.02980.00

100052368.000.00.0-1.02367.00

...

合并并且分类

# 合并三个表，取计算完的值即可

RFM = pd.concat([r_trans['r_max'],f_trans['f_total'],m_trans['m_total']],axis=1)

# cut分成3个等级，labels是展示的分数或者等级

RFM['r_score' ]= pd.cut(RFM.r_max,3,labels=[0,1,2])

RFM['f_score' ]= pd.cut(RFM.f_total,3,labels=[0,1,2])

RFM['m_score'] = pd.cut(RFM.m_total,3,labels=[0,1,2])

RFM

输出结果为：

r_maxf_totalm_totalr_scoref_scorem_score

cumid

100011.284699e+0923.03608.00211

100021.278129e+0916.01893.00000

100031.282983e+0923.03503.00211

100041.283057e+0928.02980.00221

100051.282127e+0912.02367.00100

100061.278652e+0911.02532.00000

100071.285138e+0924.04021.00211

100081.285149e+0929.04209.00221

...

这时候需要再进行处理，按照文章刚开始的图划分客户等级

def rfm_label(r,f,m):

# 对三列筛选,按图片所示

if r==2 and f==2 and m==2:

return '重要价值客户'

elif r==2 and f<2 and m==2:

return '重要发展客户'

elif r<2 and f==2 and m==2:

return '重要保持客户'

elif r<2 and f<2 and m==2:

return '重要挽留客户'

elif r==2 and f==2 and m<2:

return '一般价值客户'

elif r<2 and f==2 and m<2:

return '一般保持客户'

elif r<2 and f<2 and m<2:

return '一般挽留客户'

else:

return '一般发展客户'

# 添加一列客户标签

RFM['客户标签'] = RFM[['r_score','f_score','m_score']].apply(lambda x:rfm_label(x[0],x[1],x[2]), axis=1)

RFM

输出：

r_maxf_totalm_totalr_scoref_scorem_score客户标签

cumid

100011.284699e+0923.03608.00211一般发展客户

100021.278129e+0916.01893.00000一般挽留客户

100031.282983e+0923.03503.00211一般发展客户

100041.283057e+0928.02980.00221一般价值客户

100051.282127e+0912.02367.00100一般挽留客户

100061.278652e+0911.02532.00000一般挽留客户

100071.285138e+0924.04021.00211一般发展客户

...

处理完数据之后我们可以把这个数据写入文件RFM.to_csv(‘rfm.csv’),下面开始查询

利用sqlite3来查询(也就是sql语句查询)

先在内存中创建一个数据库，名字固定不可改

然后将dataframe对象注册成sql表，然后用sql语句查询即可

import pandas as pd

import sqlite3

# 先读取之前保持的文件

rfm = pd.read_csv('rfm.csv')

# 使用特定名称在内存中创建一个数据库

con = sqlite3.connect(':memory:')

print(con) # 这是个对象

# 将dataframe对象注册成可sql查询的表

rfm.to_sql('rfm', con) # 第一个参数是表名

# 按价值排序

data = pd.read_sql_query("SELECT * FROM rfm ORDER BY m_score DESC ", con)

# 查找重要客户

data_imp = pd.read_sql_query("SELECT * FROM rfm WHERE 客户标签='重要价值客户' ", con)

print(data_imp)

使用sqlalchemy来查询

添加数据到数据库

import pandas as pd

- List item

from sqlalchemy import create_engine

# 将数据写入mysql的数据库，但需要先通过sqlalchemy.create_engine建立连接,且字符编码设置为utf8，否则有些latin字符不能处理

yconnect = create_engine('mysql+pymysql://root:password@localhost:3306/databasename?charset=utf8')

pd.io.sql.to_sql(thedataframe,'tablename', yconnect, schema='databasename', if_exists='append')

to_sql中

第一个参数thedataframe是需要导入的pd dataframe,

第二个参数tablename是将导入的数据库中的表名

第三个参数yconnect是启动数据库的接口，pd 1.9以后的版本，除了sqllite，均需要通过sqlalchemy来设置

第四个参数databasename是将导入的数据库名字

第五个参数if_exists='append’的意思是，如果表tablename存在，则将数据添加到这个表的后面

sqlalchemy.create_engine是数据库引擎

参数

说明

mysql

是要用的数据库

pymysql

是需要用的接口程序

root

是数据库账户

password

是数据库密码

localhost

是数据库所在服务器的地址，这里是本机

3306

是mysql占用的端口

elonuse

是数据库的名字

charset=utf8

是设置数据库的编码方式，这样可以防止latin字符不识别而报错

也可以直接使用dataframe对象的方法

# 读取文件并生成dataframe对象

rfm = pd.read_csv('rfm.csv')

# 创建连接

conn = create_engine('mysql+pymysql://root:mysql@localhost:3306/lianxi?charset=utf8')

# 添加进数据库

rfm.to_sql('rfm1', conn, schema='lianxi', if_exists='append')

查询

查询方式和上面一样

print(pd.read_sql_query("SELECT * FROM rfm WHERE 客户标签='重要价值客户' ", conn))