本课程共七个章节，课程地址：7周成为数据分析师（完结）_哔哩哔哩_bilibili
- 数据分析思维
- 业务知识
- Excel
- 数据可视化
- SQL
- 统计学
- Python
第七周：Python（P86-P143）
- Python的数据科学环境（P86）
- Python基础（P87-P97）
- 数据分析常用包：Numpy和Pandas（P98-P112）
- Python连接数据库（P113-P114）
- 数据分析案例（P115-P124）
- 数据可视化：Matplotlib和Seaborn（P125-P138）
- 数据分析平台（P139-P143）
目录
7. 可以用 orderinfo表 进行类似练习的分析（复用性）
四、Python连接数据库（P113-P114）
- Python原生的连接
- Pandas连接
安装pymysql：
pip install pymysql --trusted-host pypi.tuna.tsinghua.edu.cn
# 若电脑里同时有Python3和Python2，可以改为pip3 install pymysql1. Python原生的连接
- 建立连接
- 建立游标
- 游标执行具体的SQL，得到结果
- 将结果赋值，循环
连接MySQL：
import pymysql
conn = pymysql.connect(
host = 'localhost', # 主机，数据库所在位置
user = 'root',
password = 'root',
db = 'data', # 建的schema
port = 3306,
charset = 'utf8'
)
# 创建一个游标
cur = conn.cursor()
cur.execute('select * from company') # 2296（行）
data = cur.fetchall()
data # 元组的形式
for d in data:
print(d)
# print(d[0],d[1],d[2]) # 切片
加上查询条件：
cur.execute(" select * from company where companySize = '150-500人' ") # 577
data = cur.fetchall()
for d in data:
print(d[0],d[1],d[2])
conn.commit() # 增删改查后要记得提交
cur.close()
conn.close() # 读完后记得close2. pandas连接
import pymysql
import pandas as pd
from sqlalchemy import create_engine
sql = 'select * from company'
engine = create_engine('mysql+pymysql://root:root@localhost:3306/data?charset=utf8') # 使用pymysql连接mysql
pd.read_sql(sql,engine)
3. 写入数据库
import pymysql
import pandas as pd
from sqlalchemy import create_engine
def reader(query,db='data'):
sql = query
engine = create_engine('mysql+pymysql://root:root@localhost:3306/{0}?charset=utf8'.format(db))
df = pd.read_sql(sql,engine)
return df
df_company = reader('select * from company')
df_dataanalyst = reader('select * from dataanalyst')
统计每个城市下不同公司的数量
merged = pd.merge(df_dataanalyst, df_company, on = 'companyId')
merged.head()
result = merged.groupby(['city','companyFullName']).count()['positionId'].reset_index()
result.head()
写入数据库：result.to_sql(name, con, if_exists='fail', index=True, )
- if_exists：默认为fail。改为append，若这张表已经存在，则在表里插入数据；若不存在则自动新建一张表
- index：默认为True，写入时将数据框里的索引也作为一个字段写入数据库
# 写入数据库
result.to_sql(name = 'newtable', con = 'mysql+pymysql://root:root@localhost:3306/data?charset=utf8', if_exists='append', index=False)
右键 - Alter Table
发现字段的类型不太符合我们日常的习惯，故建议：先在数据库中建好表，定义好所有的字段
再写入数据库：
# 写入数据库
result.to_sql(name = 'newtable2', con = 'mysql+pymysql://root:root@localhost:3306/data?charset=utf8', if_exists='append', index=False)建表的时候字段有3个，但是写入的时候只有2个字段，是可以的，该值为空（若建表时不允许为空会报错）
# 砍掉一个字段
del result['city']
result
# 再写入newtable2，不会报错五、数据分析案例（P115-P124）
1. Markdown
- 加粗：在字的两边都加上**
- 分点阐述：在字的前面加上-，再空格
- 快捷键：esc + m 变为 Markdown，esc + c 变为 Code
- 换行：两个空格
2. 数据集 & 预处理
txt 文件的读取方式是：pd.read_table()
import pandas as pd
import numpy as np
columns = ['user_id', 'order_dt', 'order_products', 'order_amount'] # 列名，分别是用户ID、购买日期、购买产品数、购买金额
df = pd.read_table('CDNOW_master.txt', names=columns, sep='\s+') # sep='\s+'：分隔符里有多个字符串，+表示正则匹配一些基本信息：
- df.head() 查看前五行
- df.info() 查看基本信息，发现order_dt（购买日期）是int64，而不是datetime64
将order_dt（购买日期）修改为datetime64类型：
df['order_dt'] = pd.to_datetime(df.order_dt, format='%Y%m%d') # Y表示四位数的年份，y表示两位数的年份（如90年）也可以在导入数据的时候更改字段类型：
df = pd.read_table('CDNOW_master.txt', names=columns, sep='\s+', parse_dates= , date_parser= )
- parse_dates：把哪些字段转化成时间格式
- date_parser：具体时间类型，如上式中的format='%Y%m%d'
- df.describe() 描述性统计
得出结论：
- 大部分订单只消费了少量商品（平均2.4），有一定极值干扰
- 用户的消费金额比较稳定，平均消费35元，中位数在35元，有一定极值干扰
新增字段：
df['month'] = df.order_dt.values.astype('datetime64[M]')
3. 进行用户消费趋势的分析（按月）
- 每月的消费总金额
- 每月的消费次数
- 每月的产品购买量
- 每月的消费人数
（1）每月的消费总金额
grouped_month = df.groupby('month')
order_month_amount = grouped_month.order_amount.sum()
order_month_amount.head()
# 加载数据可视化包
import matplotlib.pyplot as plt
# 可视化显示在页面
%matplotlib inline
# 更改设计风格
plt.style.use('ggplot')
# ------------------------------以上代码可照搬---------------------------------
order_month_amount.plot()
由上图可知，消费金额在前三个月达到最高峰，后续消费额较为稳定，有轻微下降趋势
（2）每月的消费次数
# 因为一个user_id可能消费多次
grouped_month.user_id.count().plot()
由上图可知，前三个月消费订单数在10000笔左右，后续月份的平均消费订单数则在2500笔左右
（3）每月的产品购买量
# 一笔订单可能会卖出多个产品，故使用sum()而不是count()
grouped_month.order_products.sum().plot()
（4）每月的消费人数
# 一个人在一个月可能有多笔消费，故需要去重，即.drop_duplicates()
df.groupby('month').user_id.apply(lambda x:len(x.drop_duplicates())).plot()
由上图可知：
- 每月消费人数低于每月消费次数，但差异不大
- 前三个月每月的消费人数在8000-10000之间，后续月份平均消费人数在2000人不到
另法 —— 数据透视表
df.pivot_table(index='month',
values=['order_products','order_amount','user_id'],
aggfunc={'order_products':'sum', # 每月的产品购买量
'order_amount':'sum', # 每月的消费总金额
'user_id':'count'}).head() # 每月的消费次数
# 数据透视不容易去重，故每月的消费人数无
4. 用户个体消费分析
- 用户消费金额、消费次数的描述统计
- 用户消费金额和消费次数的散点图
- 用户消费金额的分布图
- 用户消费次数的分布图
- 用户累计消费金额占比（百分之多少的用户占了百分之多少的消费额）
（1）用户消费金额、消费次数的描述统计
grouped_user = df.groupby('user_id')
grouped_user.sum().describe()
得出以下结论：
- 用户平均购买了7张CD，但是中位数只有3，说明小部分用户购买了大量的CD
- 用户平均消费106元，中位值有43，判断同上，有极值干扰
（2）用户消费金额和消费次数的散点图
grouped_user.sum().plot.scatter(x='order_amount',y='order_products')
散点图对极端值较为敏感，个别极端值会将整张图拉大，使集中的数据都堆积在左下角
可以通过过滤数据（query函数，类似于SQL中的where）的方法改变
grouped_user.sum().query('order_amount<4000').plot.scatter(x='order_amount',y='order_products')
从图中可以看出：
用户消费金额和消费次数呈线性关系（每个用户购买CD次数与金额有正比关系），说明CD的客单价较为稳定
（3）用户消费金额的分布图
# 直方图 hist
grouped_user.sum().order_amount.plot.hist(bins=20) # bins=20，即分为20组
从直方图可知，用户消费金额绝大部分呈现集中趋势，小部分异常值干扰了判断，可以通过过滤操作排除异常
（4）用户消费次数的分布图
grouped_user.sum().query('order_products<100').order_amount.plot.hist(bins=20)100是怎么确定出来的？
切比雪夫定理：95%的数据都集中在距离平均数（7.122656）五个标准差（5*16.983531）之内，约为100
使用切比雪夫定理过滤掉异常值，计算95%的数据的分布情况
（5）用户累计消费金额占比（百分之多少的用户占了百分之多少的消费额）
- 首先把数据进行升序排列
user_cumsum = grouped_user.sum().sort_values('order_amount') # 升序操作
user_cumsum
- 累加求和
user_cumsum = grouped_user.sum().sort_values('order_amount').cumsum()
user_cumsum
- 累计百分比
user_cumsum = (grouped_user.sum().sort_values('order_amount').cumsum())/ 2500315.63
user_cumsum
apply函数实现同样效果：
user_cumsum = grouped_user.sum().sort_values('order_amount').apply(lambda x:x.cumsum()/x.sum()) # x.cumsum()/x.sum()百分比
user_cumsum
user_cumsum.reset_index().order_amount.plot()
按用户消费金额进行升序排列，由图可知50%的用户仅贡献了15%的消费额度，而排名前5000的用户就贡献了60%的消费额
5. 用户消费行为
- 用户第一次消费（首购）
- 用户最后一次消费
- 新老客消费比
- 多少用户仅消费了一次？
- 每月新客占比？
- 用户分层
- RFM
- 新客（第一次消费）、活跃（一直在持续消费）、回流（一段时间未消费，又一次消费）、流失/不活跃（一段时间未消费）
- 用户购买周期（按订单）
- 用户消费周期描述
- 用户消费周期分布
- 用户生命周期（按第一次&最后一次消费）
- 用户生命周期描述
- 用户生命周期分布
（1）用户第一次消费（首购）
业务意义：和渠道相关
grouped_user.min().order_dt # 最小的时间，即距离现在最遥远
# 趋势图
grouped_user.min().order_dt.value_counts().plot()
由图可知：
- 用户第一次消费（首购）分布，集中在前三个月
- 其中，在2月11日至2月25日有一次剧烈的波动，可能是奖励机制/渠道发生变化
（2）用户最后一次消费
grouped_user.max().order_dt.value_counts().plot()
由图可知：
- 用户最后一次消费的分布比第一次分布广
- 大部分最后一次购买，集中在前三个月，说明有很多用户购买了一次之后就不再进行购买
- 随着时间的递增，最后一次购买数也在递增，消费呈现流失上升的状况
（3）新老客消费比：多少用户仅消费了一次？
user_life = grouped_user.order_dt.agg(['min','max'])
user_life.head()
若min和max为同一天，说明该用户仅消费了一次
(user_life['min'] == user_life['max']).value_counts()
True即仅消费一次，说明有一半用户就消费了一次
（4）新老客消费比：每月新客占比？
- 把user_id和month进行group by分组
- 再和用户第一次消费join
（5）用户分层：RFM
商业模型，三个维度：用户消费额，用户消费次数，用户距今一次消费
使用象限法切分成具体粒度，8块，立方体
rfm = df.pivot_table(index = 'user_id',
values = ['order_amount','order_products','order_dt'],
aggfunc = {
'order_amount':'sum', # 用户消费额
'order_products':'sum', # 用户消费次数（这里用用户消费的产品数替代，以增加区分度）
'order_dt':'max' # 离现在最近的那天
})
rfm.head()
# 由于数据年份整体距今比较久，所以拿用户消费日期距其中max的天数做维度
-(rfm.order_dt - rfm.order_dt.max())
# 去掉天数后的days
rfm['R'] = -(rfm.order_dt - rfm.order_dt.max()) / np.timedelta64(1,'D')
# 对列名重命名
rfm.rename(columns = {'order_products':'F', 'order_amount':'M'}, inplace = True)
rfm
rfm[['R','F','M']].apply(lambda x:x-x.mean())
- R > 0 说明距今消费时间比较久远
- F > 0 说明消费频次较高
- M > 0 说明消费金额较高
def rfm_func(x):
level = x.apply(lambda x:'1' if x>0 else '0')
label = level.R + level.F + level.M
d = {
'111':'重要价值客户',
'011':'重要保持客户',
'101':'重要挽留客户',
'001':'重要发展客户',
'110':'一般价值客户',
'010':'一般保持客户',
'100':'一般挽留客户',
'000':'一般发展客户'
}
result = d[label]
return result
rfm['label'] = rfm[['R','F','M']].apply(lambda x:x-x.mean()).apply(rfm_func, axis=1) # 逐行应用
rfm
rfm.groupby('label').sum() # 结果为左图
rfm.groupby('label').count() # 结果为右图
# 散点图
rfm.plot.scatter('F','R')
# 加上不同颜色以区分
rfm.loc[rfm.label == '重要价值客户', 'color'] = 'g'
rfm.loc[~(rfm.label == '重要价值客户'), 'color'] = 'r'
rfm.plot.scatter('F','R', c=rfm.color) # 新增一个颜色字段
散点图 F维度 被一些 >200 的值拉伸开来了，这些极值会极大地影响平均值，导致 x-x.mean() 不准确。解决方法：
- 把 mean 改为中位数 median
- 人工排除极值，根据切比雪夫定理过滤
从RFM分层可知，大部分用户为重要保持客户，但是这是由于极值的影响，所以RFM的划分标准应该以业务为准
- 尽量用小部分的用户覆盖大部分的额度
- 不要为了数据好看划分等级
（6）用户分层：新客（第一次消费）、活跃（一直在持续消费）、回流（一段时间未消费，又一次消费）、流失/不活跃（一段时间未消费）
# user_id为行，月份为列，画数据透视表
pivoted_counts = df.pivot_table(index = 'user_id',
columns = 'month',
values = 'order_dt',
aggfunc = 'count').fillna(0)
pivoted_counts.head()
# 消费过为1（无论消费过多少次），没有消费为0
df_purchase = pivoted_counts.applymap(lambda x:1 if x>0 else 0)
df_purchase.head()
陷阱：部分用户注册较晚（user_id靠后），如3月份才注册，但是1、2月份会被记作未消费
# 查看user_id较靠后的五行数据
df_purchase.tail()
def active_status(data):
status = []
for i in range(18): # 一共有18个月
# 若本月没有消费
if data[i] == 0:
if len(status)>0:
if status[i-1] == 'unreg': # 若之前是未注册，则依旧为未注册
status.append('unreg')
else:
status.append('unactive') # 若之前有消费，则为流失/不活跃
else:
status.append('unreg') # 其他情况，为未注册
# 若本月有消费
else:
if len(status) == 0:
status.append('new') # 若是第一次消费，则为新用户
else:
if status[i-1] == 'unactive':
status.append('return') # 若之前有过消费，且上个月为不活跃，则为回流
elif status[i-1] == 'unreg':
status.append('new') # 如果上个月为未注册，则为新用户
else:
status.append('active') # 除此之外，为活跃
return pd.Series(status)
purchase_status = df_purchase.apply(active_status, axis=1) # 逐行应用
purchase_status.columns = df_purchase.columns # 18月（列）
purchase_status.head()
purchase_status.tail()
# 统计每个月不同活跃程度的计数
purchase_status_ct = purchase_status.replace('unreg', np.NaN).apply(lambda x:pd.value_counts(x))
purchase_status_ct
purchase_status_ct.fillna(0).T.head() # 把NaN填充为0
purchase_status_ct.fillna(0).T.apply(lambda x:x/x.sum(),axis=1) # 占比
由上表可知，每月的用户消费状态变化
- 活跃用户：持续消费的用户，对应的是消费运营的质量
- 回流用户：之前不消费，本月才消费，对应的是使唤回运营
- 不活跃用户：对应的是流失
# 面积图
purchase_status_ct.fillna(0).T.plot.area()
（7）用户购买周期（按订单）：用户消费周期描述
# 用户可能有多个订单，计算同一个用户之间订单的相隔时间
order_diff = grouped_user.apply(lambda x:x.order_dt - x.order_dt.shift())
order_diff.head(10)
错位函数 shift()：
order_diff.describe() # describe()函数也会自动排除掉空值
（8）用户购买周期（按订单）：用户消费周期分布
# 直方图 hist
(order_diff / np.timedelta64(1,'D')).hist(bins = 20)
/ np.timedelta64(1,'D') 的作用：
（9）用户生命周期（按第一次&最后一次消费）：用户生命周期描述
user_life = grouped_user.order_dt.agg(['min','max'])
user_life.head()
(user_life['max'] - user_life['min']).describe()
用户平均消费134天，中位数仅0天
（10）用户生命周期（按第一次&最后一次消费）：用户生命周期分布
((user_life['max'] - user_life['min'])/ np.timedelta64(1,'D')).hist(bins = 40)
由图可知：用户的生命周期受只购买一次的用户影响比较厉害（可以排除）
# 排除掉只消费一次的用户
u_l = ((user_life['max']-user_life['min']).reset_index()[0] / np.timedelta64(1,'D'))
u_l[u_l > 0].hist(bins = 40)
6. 复购率和回购率分析
- 复购率
- 自然月内，购买多次的用户占比（买了两次及以上的用户在整体消费用户中的占比）
- 回购率
- 曾经购买过的用户在某一时期内再次购买的占比（当月消费过的用户在次月仍旧消费的占比）
（1）复购率
# user_id为行，月份为列，画数据透视表
pivoted_counts = df.pivot_table(index = 'user_id',
columns = 'month',
values = 'order_dt',
aggfunc = 'count').fillna(0)
# 用户在每个月的消费次数
pivoted_counts.head()
purchase_r = pivoted_counts.applymap(lambda x: 1 if x>1 else np.NaN if x==0 else 0) # 如果x>1则赋值为1（多次以上的用户消费），x≤1中，若x=0，赋值为NaN，除此之外即x=1，赋值为0（消费1次）
purchase_r.head()
# sum()为复购人数，count()只会排除NaN，而0和1都会计算，故为总消费人数
purchase_r.sum() / purchase_r.count() # 复购率
# 折线图
(purchase_r.sum() / purchase_r.count()).plot(figsize = (10,4)) # 宽10高4的矩形
复购率稳定在20%左右，前三个月因为有大量新用户涌入，而这批用户只购买了一次，所以导致复购率降低
（2）回购率
# 1代表消费过，0代表未消费过
df_purchase = pivoted_counts.applymap(lambda x:1 if x>0 else 0)
df_purchase.head()
def purchase_back(data):
status = []
for i in range(17):
if data[i] == 1: # 当月消费过
if data[i+1] == 1: # 次月仍旧消费
status.append(1)
if data[i+1] == 0: # 次月未消费
status.append(0)
else:
status.append(np.NaN) # 当月没有消费则要赋予空值，不予计算
status.append(np.NaN) # 最后一个月没有下一个月的数据进行计算了，所以要人工赋予一个空值NaN
return pd.Series(status)
purchase_b = df_purchase.apply(purchase_back, axis=1) # 逐行计算
purchase_b.head()
- 若为1.0，则是当月消费过，次月仍旧消费
- 若为0.0，则是当月消费过，次月没有消费
- 若为NaN，则是当月没有消费
# sum()为次月消费过的，count()为当月消费过的
(purchase_b.sum() / purchase_b.count()).plot(figsize = (10,4))
7. 可以用 orderinfo表 进行类似练习的分析（复用性）
paidTime 精确到具体时分秒
select
date(paidTime) as order_dt,
useId as user_id,
sum(price) as order_amount,
count(orderId) as order_products
from data.orderinfo
where isPaid = '已支付' and paidTime > '0000-00-00 00:00:00'
group by date(paidTime),useId
加载到DataFrame里：
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
def reader(query, db='data'):
sql = query
engine = create_engine('mysql+pymysql://root:root@localhost:3306/{0}?charset=gbk'.format(db))
df = pd.read_sql(sql,engine)
return df
df = reader(
"""
select
date(paidTime) as order_dt,
useId as user_id,
sum(price) as order_amount,
count(orderId) as order_products
from data.orderinfo
where isPaid = '已支付' and paidTime > '0000-00-00 00:00:00'
group by date(paidTime),useId
"""
)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
