【第七周：Python（三）】7周成为数据分析师

本课程共七个章节，课程地址：7周成为数据分析师（完结）_哔哩哔哩_bilibili

1. 数据分析思维
2. 业务知识
3. Excel
4. 数据可视化
5. SQL
6. 统计学
7. Python

第七周：Python（P86-P143） 

1. Python的数据科学环境（P86）
2. Python基础（P87-P97）
3. 数据分析常用包：Numpy和Pandas（P98-P112）
4. Python连接数据库（P113-P114）
5. 数据分析案例（P115-P124）
6. 数据可视化：Matplotlib和Seaborn（P125-P138）
7. 数据分析平台（P139-P143）

目录

第七周：Python（P86-P143） 

四、Python连接数据库（P113-P114）

1. Python原生的连接

2. pandas连接 

3. 写入数据库 

五、数据分析案例（P115-P124）

1. Markdown

2. 数据集 & 预处理

3. 进行用户消费趋势的分析（按月）

4. 用户个体消费分析 

5. 用户消费行为

6. 复购率和回购率分析

7. 可以用 orderinfo表 进行类似练习的分析（复用性）

四、Python连接数据库（P113-P114）

• Python原生的连接
• Pandas连接 

安装pymysql： 

pip install pymysql --trusted-host pypi.tuna.tsinghua.edu.cn

# 若电脑里同时有Python3和Python2，可以改为pip3 install pymysql

1. 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()   # 读完后记得close

2. 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 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
"""
)