2.7 案例：用户价值分类结果可视化展示

随笔2个月前发布小雪的日常防护

3 0 0

2.7 案例：用户价值分类结果可视化展示

【实验目的】

将用户价值分类结果导入MySQL数据库

1.掌握Python连接数据库方式

2.掌握python导入数据到数据库的方式

【实验原理】

常见的Python读取数据源

文件格式和文件系统。对于存储在本地文件系统或分布式文件系统（比如HDFS）中的数据，Spark可以访问很多种不同的文件格式。包括文本文件、JSON、SequenceFile、以及protocol buffer。

Spark SQL中的结构化数据源。

数据库和键值存储。Spark自带的库以及一些第三方库，可以用来连接HBase、JDBC源。

2.7 案例：用户价值分类结果可视化展示

Python连接MySql工具：PyMySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb

【实验环境】

AnolisOS 8.8

hadoop-3.0.0

spark-2.4.3

pyspark

python3.5

mysql-5.7.26

Pycharm-2018.3.2

pymysql==0.10.1

【实验内容】

将用户价值分类结果导入MySQL数据库

【实验步骤】

切换用户vmuser（密码：vm123456）

1.打开Linux终端模拟器，创建目录，如果目录已经存在，则无需重复创建

mkdir -p /data/uservalue

2.进入/data/uservalue，下载数据

cd /data/uservalue

wget http://uservalue/User_Value_Class.parquet.zip

3.解压文件

unzip User_Value_Class.parquet.zip

2.7 案例：用户价值分类结果可视化展示

4.新打开一个终端，无需转换操作用户，直接执行如下命令

使用pip命令，安装pymysql到python3.5上。

pip3 install pymysql -i https://pypi.doubanio.com/simple

2.7 案例：用户价值分类结果可视化展示

python3 -m pip install –upgrade pip

pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

2.7 案例：用户价值分类结果可视化展示

安装实验中所需要的库

pip3 install pyarrow

2.7 案例：用户价值分类结果可视化展示

5.回到最开始的终端执行如下命令，在Linux终端，输入以下命令启动MySQL服务，密码：'strongs'

sudo service mysqld status

2.7 案例：用户价值分类结果可视化展示

mysql -h localhost -P 3306 -u root -p

进入MySQL库，密码为：strongs

mysql -u root -p

6.创建uservalue 数据库

create database uservalue default charset utf8 collate utf8_general_ci;

2.7 案例：用户价值分类结果可视化展示

7.切换到uservalue 库

use uservalue;

8.创建表User_Value_Class

create table User_Value_Class(user_id varchar(20),User_Value_Class varchar(20));

9.查看User_Value_Class表数据结构

2.7 案例：用户价值分类结果可视化展示

10.vmuser 账户进入图形界面

打开Pycharm，点击【新建项目】，位置修改为：/home/vmuser/PycharmProjects/UserValue，取消掉创建main.py的对号

2.7 案例：用户价值分类结果可视化展示

11.在UserValue项目下，新建python文件，取名为User_Value_Class

2.7 案例：用户价值分类结果可视化展示

12.打开User_Value_Class.py编写代码

import pymysql

import pyarrow.parquet as pq

data = pq.read_table(“/data/uservalue/User_Value_Class.parquet”).to_pandas()

conn = pymysql.connect(host='localhost',

user='root',

password='strongs',

database='uservalue',

port = 3306,

charset = 'utf8')

cur=conn.cursor()

id = data['user_id']

value = data['User_Value_Class']

newdata = dict(zip(id,value))

for key in newdata.keys():

sql='''insert into uservalue.User_Value_Class(user_id,User_Value_Class) values({},{}) '''.format(“'”+key+”'”,”'”+newdata.get(key)+”'”)

print(sql)

cur.execute(sql)

conn.commit()

conn.close()

2.7 案例：用户价值分类结果可视化展示

13.运行文件，控制台显示插入sql命令，同时数据会插入数据库中

2.7 案例：用户价值分类结果可视化展示

说明：由于数据量大，插入数据时间较长，请耐心等待。

14.在数据库中查看结果

select count(*) from User_Value_Class;

2.7 案例：用户价值分类结果可视化展示

15.查看前10条数据

select * from User_Value_Class limit 10;

2.7 案例：用户价值分类结果可视化展示

如上所示：数据已经插入MySQL数据库中。

【一、统计不同类型价值用户的数量】

11.统计不同类型价值用户的数量

SELECT user_value_class,sum(user_id) as num FROM User_Value_Class GROUP BY user_value_class;

2.7 案例：用户价值分类结果可视化展示

【二、将不同类型价值用户可视化展示】

12.在项目下新建python文件，取名为：User_Value_Class_pie

2.7 案例：用户价值分类结果可视化展示

13.连接数据库

import pymysql

# 打开数据库连接

db = pymysql.connect(host='localhost',

user='root',

password='strongs',

database='user_value')

14.查询数据

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象

fetchall(): 接收全部的返回结果行.

rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

# 2.使用 cursor() 方法创建一个游标对象 cursor

cursor = db.cursor()

# 使用 execute() 方法执行 SQL 查询

cursor.execute(“SELECT user_value_class,sum(user_id) as num FROM User_Value_Class GROUP BY user_value_class;”)

# 使用 fetchall() 方法获取多条数据

data = cursor.fetchall()

#将不同类型用户的名称和数量分别存储在列表中

user_value_class=[]

num=[]

for row in data :

user_value_class.append(row[0])

num.append(row[1])

15.Matplotlib绘制饼图

# 3.matplotlib绘制饼图

import matplotlib.pyplot as plt

import matplotlib.font_manager as fm #字体管理器

my_font = fm.FontProperties(fname=”/data/uservalue/simhei.ttf”)

colors = ['DarkGoldenrod','DarkSalmon','BurlyWood','DarkKhaki']

plt.figure(figsize=(10, 10))

plt.pie(num, labels=user_value_class, autopct=”%1.2f%%”, colors=colors,

textprops={'fontsize': 14}, labeldistance=1.05)

plt.legend(fontsize=10,loc=4)

plt.title(“用户价值分类数量占比”, fontsize=24,fontproperties=my_font)

plt.show()

16.关闭数据库连接

#4. 关闭数据库连接

db.close()

17.完整代码为：

import pymysql

# 1.打开数据库连接

db = pymysql.connect(host='localhost',

user='root',

password='strongs',

database='uservalue')

# 2.使用 cursor() 方法创建一个游标对象 cursor

cursor = db.cursor()

# 使用 execute() 方法执行 SQL 查询

cursor.execute(“SELECT user_value_class,sum(user_id) as num FROM User_Value_Class GROUP BY user_value_class;”)

# 使用 fetchall() 方法获取多条数据

data = cursor.fetchall()

#将不同类型用户的名称和数量分别存储在列表中

user_value_class=[]

num=[]

for row in data :

user_value_class.append(row[0])

num.append(row[1])

# 3.matplotlib绘制饼图

import matplotlib.pyplot as plt

colors = ['DarkGoldenrod','DarkSalmon','BurlyWood','DarkKhaki']

plt.figure(figsize=(10, 6))

plt.pie(num, labels=user_value_class, autopct=”%1.2f%%”, colors=colors,

textprops={'fontsize': 14}, labeldistance=1.05)

plt.legend(fontsize=10,loc=4)

plt.title(“User Value Classification”, fontsize=24)

#plt.show()

plt.savefig(“/data/uservalue/test.png”)

#4. 关闭数据库连接

db.close()

到/data/uservalue/下载test.png文件

18.选中User_Value_Class_pie.py，【鼠标右键】-【运行】

2.7 案例：用户价值分类结果可视化展示

19.结果如下所示：

2.7 案例：用户价值分类结果可视化展示

分析：

可以看出，重要价值用户Imp_Value_Cus，他们是最优质的用户，需要重点关注并保持，应该提高满意度，增加留存；

对于重要保持用户Imp_Keep_Cus，他们最近有购买，但购买频率不高，可以通过活动等提高其购买频率；

对于重要发展用户Imp_Develop_Cus，他们虽然最近没有购买，但以往购买频率高，可以做优惠券或活动触达，以防止流失；

对于一般价值用户Imp_Stay_Cus，他们最近没有购买，以往购买频率也不高，特别容易流失，所以应该赠送优惠券或推送活动信息，唤醒购买意愿。

总结：

本次统计周期内，重要挽留客户占比最大，达到69.50%，其次是重要发展客户，比例为15.49%。重要价值客户和重要保持客户占比较低。大部分的用户消费频次较低，平台需加大推送宣传力度，采取多样营销措施，加强促销活动，提高重要挽留客户和重要发展客户的消费频次，促使其向其他用户价值类型转变。针对于重要价值客户和重要保持客户，提供差异化的VIP服务，提高VIP客户的购物体验，同时，将VIP服务作为一个重要的宣传渠道，吸引普通用户向VIP用户转变。

至此，实验结束！

# 随笔