电商用户行为分析案例--天池数据集User Behavior Data from Taobao(python环境MySQL操作+Pyecharts可视化)

项目的背景及数据预处理过程，本文不再进行介绍。

可以参考上一篇博文：
电商用户行为分析案例–天池数据集User Behavior Data from Taobao

本文基于上文已经预处理并导入MySQL的数据，在Python环境下，连接数据库进行取数。

Python环境下，MySQL数据库的操作，可以参考博文：
Python环境下MySQL数据库的操作

分析的思路及角度，也可以参见上篇博文，本文不再赘述。

本文重点介绍两个方面：

1.Python环境下，连接MySQL并进行取数操作；

2.运用python强大的可视化库Pyecharts进行可视化分析，并制作简单的可视化仪表板。

在安装pyecharts库的时候，可能会遇到安装后报错的现象:
注意：直接pip安装，是安装的最新版本的pyecharts。而新版本需要在python 3.6+的环境下运行，建议各位安装新版本，老版本已经不维护，而且新旧版本并不兼容。
(本人的电脑安装的是python 3.5的环境，pyecharts安装好后，总是报错。所以又在虚拟机里装了个python 3.6的环境，使用就正常了。)

学习pyecharts，强烈推荐直接在官方文档学习，内容很丰富，各种接口参数也很容易找到。
pyecharts文档中文网址

一、连接数据库并提取数据

1、导入所需要的库

import numpy as np
import pandas as pd
import pymysql  #python环境下操作MySQL的库
from sqlalchemy import create_engine  #python环境下基于ORM框架操作MySQL的库
import pyecharts.options as opts  #pyecharts是一个比较强大的可视化库
from pyecharts.charts import Bar,Line,Funnel,Gauge,Page  #本文会用到的几个表
from pyecharts.components import Table  #表格展示会用到
from pyecharts.options import ComponentTitleOpts  #表格里会用到

2、连接数据库

本文基于两种连接MySQL的方式都做尝试，其中sqlalchemy库取出的数据，可以直接读到Pandas里，易于处理。而比较简单一些的取数，比如就需要取一个数字，本文就用pymysql来处理。

#pymysql创建连接
conn=pymysql.connect(host='localhost',port=3306,user='root',passwd='***',db='test',charset='utf8')
#sqlalchemy创建连接
engine=create_engine('mysql+pymysql://root:passwd@localhost:3306/test?charset=utf8')

3、编写python环境下,从MySQL取数需要的SQL语句

这些SQL语句上篇博文都有介绍，本文不再过多解释。

其中有个坑就是直接从上篇博文复制过来SQL语句的话，有些执行会报错。
原因是SQL语句中存在’%‘字符，这个跟Python默认字符是有冲突的。
解决方案就是：直接改为’%%'就不会报错了。

#总访问量
sql_pv='''
select count(behavetype) as 总访问量
from user 
where behavetype = 'pv';
'''

#日均访问量
sql_day_pv='''
select dates,count(behavetype) as 日均访问量
from user 
where behavetype = 'pv'
group by dates
order by dates;'''

#用户总数
sql_user_all='''
select count(distinct user_id) as 用户总数
from user;'''

#有购买行为的用户数量
sql_user_buy='''
select count(distinct user_id) as 购买用户数
from user 
where behavetype = 'buy';'''

#用户的购物情况
#创建视图
user_view='''create view user_behave AS
select user_id,count(behavetype),
sum(case when behavetype = 'pv' THEN 1 ELSE 0 end) as 点击次数,
sum(case when behavetype = 'fav' THEN 1 ELSE 0 end) as 收藏次数,
sum(case when behavetype = 'cart' THEN 1 ELSE 0 end) as 加购数,
sum(case when behavetype = 'buy' THEN 1 ELSE 0 end) as 购买数
from user 
group by user_id 
order by count(behavetype) DESC;'''

#用户购物情况
sql_user_type='''select * from user_behave;'''

#复购率：产生两次或两次以上购买的用户占购买用户的比例
sql_user_buy2='''
select
sum(case WHEN 购买数 > 1 THEN 1 ELSE 0 end) as 购买数大于1次,
sum(case when 购买数 > 0 Then 1 ELSE 0 end) as 总购买数,
concat(round(sum(case WHEN 购买数 > 1 THEN 1 ELSE 0 end)/sum(case when 购买数 > 0 Then 1 ELSE 0 end)*100,2),'%%') as 复购率
from user_behave;'''

#用户行为转化漏斗
#用户购物行为统计
sql_user_buytype='''select 
sum(点击次数) as 点击次数,
sum(收藏次数) as 收藏次数,
sum(加购数) as 加购数,
sum(购买数) as 购买数
from user_behave;'''

#用户购买行为转化率
sql_user_buytyperate='''SELECT
concat(round(sum(点击次数)/sum(点击次数)*100,2),'%%') as pv,
concat(round((sum(收藏次数)+sum(加购数))/sum(点击次数)*100,2),'%%') as pv_to_favCart,
concat(round(sum(购买数)/sum(点击次数)*100,2),'%%') as pv_to_buy
from user_behave;'''

#购买率高和购买率低的人群特点
sql_user_buy_rate_high1='''select user_id,点击次数,收藏次数,加购数,购买数,
round(购买数/点击次数*100,2) as 购买率
from user_behave
group by user_id
order by 购买率 DESC;'''

sql_user_buy_rate_high2='''select user_id,点击次数,收藏次数,加购数,购买数,
concat(round(购买数/点击次数*100,2),'%%') as 购买率
from user_behave
group by user_id
order by 购买数 DESC;'''

sql_user_buy_rate_low='''select user_id,点击次数,收藏次数,加购数,购买数,
concat(round(购买数/点击次数*100,2),'%%') as 购买率
from user_behave
group by user_id
order by 购买数;'''

#基于时间维度了解用户的行为习惯
#一天中用户的活跃时段分布
sql_user_hours='''select hours,count(behavetype) as 用户行为总量,
sum(case when behavetype = 'pv' THEN 1 ELSE 0 end) as 点击次数,
sum(case when behavetype = 'fav' THEN 1 ELSE 0 end) as 收藏次数,
sum(case when behavetype = 'cart' THEN 1 ELSE 0 end) as 加购数,
sum(case when behavetype = 'buy' THEN 1 ELSE 0 end) as 购买数
from user 
group by hours
order by hours;'''

#一周中用户活跃时段分布
sql_user_weeks='''select date_format(dates,'%%W') as weeks,count(behavetype) as 用户行为总量,
sum(case when behavetype = 'pv' THEN 1 ELSE 0 end) as 点击次数,
sum(case when behavetype = 'fav' THEN 1 ELSE 0 end) as 收藏次数,
sum(case when behavetype = 'cart' THEN 1 ELSE 0 end) as 加购数,
sum(case when behavetype = 'buy' THEN 1 ELSE 0 end) as 购买数
from user 
where dates between '2017-11-27' and '2017-12-03'
group by weeks
order by weeks;'''

#基于RFM模型找出有价值的用户
sql_rfm_set='''set @userBuyNum = (
select count(distinct user_id) as 购买用户数
from user 
where behavetype = 'buy');'''

sql_rfm='''
select r.*,f.frequency,f.freq_rank,
(
(case when 
r.recent_rank <= @userBuyNum*1/4 then 4
when (r.recent_rank > @userBuyNum*1/4) and (r.recent_rank <= @userBuyNum*2/4) then 3
when (r.recent_rank > @userBuyNum*2/4) and (r.recent_rank <= @userBuyNum*3/4) then 2
else 1 end)+
(case when 
f.freq_rank <= @userBuyNum*1/4 then 4
when (f.freq_rank > @userBuyNum*1/4) and (f.freq_rank <= @userBuyNum*2/4) then 3
when (f.freq_rank > @userBuyNum*2/4) and (f.freq_rank <= @userBuyNum*3/4) then 2
else 1 end)
) as user_value
from

(select a.*,(@rank:=@rank+1) as recent_rank
FROM
(SELECT user_id,datediff('2017-12-04',max(dates)) as recent
from user 
where behavetype = 'buy'
group by user_id 
order by recent) as a,
(select @rank:=0) as b) as r

left JOIN

(select a.*,(@rank2:=@rank2+1) as freq_rank
FROM
(select user_id,count(behavetype) as frequency
from user 
where behavetype = 'buy'
group by user_id 
order by frequency DESC) as a,
(select @rank2:=0) as b) as f

on r.user_id = f.user_id;'''

4、pymql创建游标

使用完之后，必须关闭游标，并关闭连接。

#自动提交
conn.autocommit(True)
#创建游标
cursor=conn.cursor()

5、从MySQL里取数

pandas有个接口read_sql_query或者read_sql，可以直接把取出的数读为pandas的数据格式。

#1.总访问量
cursor.execute(sql_pv)
for row in cursor:
    pv=row
pv=pv[0]

#2.日均访问量
day_pv=pd.read_sql_query(sql_day_pv,engine)

#3.用户总数
cursor.execute(sql_user_all)
for row in cursor:
    user_all=row
user_all=user_all[0]

#4.有购买行为的用户数量
cursor.execute(sql_user_buy)
for row in cursor:
    user_buy=row[0]

#5.用户的购物情况
user_type=pd.read_sql_query(sql_user_type,engine)

#6.复购率
user_buy2=pd.read_sql_query(sql_user_buy2,engine)

#7.用户购物行为统计
user_buytype=pd.read_sql(sql_user_buytype,engine)

#8.用户购买行为转化率
user_buytyperate=pd.read_sql(sql_user_buytyperate,engine)

#9.1购买率高人群特征
user_buy_rate_high1=pd.read_sql(sql_user_buy_rate_high1,engine)

#9.2购买数高人群特征
user_buy_rate_high2=pd.read_sql(sql_user_buy_rate_high2,engine)

#9.3购买数低人群特征
user_buy_rate_low=pd