**我们可以使用df.pivot_table()
,来实现同样的功能,参数如下
index:行索引,传入原始数据的列名columns:列索引,传入原始数据的列名values: 要做聚合操作的列名aggfunc:聚合函数**
数据透视表
学习目标
知道什么是透视表掌握Pandas透视表(pivot_table)的使用方法1 Pandas 透视表概述
透视表是一种可以对数据动态排布并且分类汇总的表格格式。或许大多数人都在Excel使用过数据透视表,也体会到它的强大功能,而在pandas中它被称作pivot_table。
数据透视表(Pivot Table)是一种交互式的表,可以进行某些计算,如求和与计数等。所进行的计算与数据,和数据透视表中的排列有关。之所以称为数据透视表,是因为可以动态地改变它们的版面布置,以便按照不同方式分析数据,也可以重新安排行号、列标和页字段。每一次改变版面布置时,数据透视表会立即按照新的布置重新计算数据。另外,如果原始数据发生更改,则可以更新数据透视表。在使用Excel做数据分析时,透视表是很常用的功能,Pandas也提供了透视表功能,对应的API为pivot_tablePandas pivot_table函数介绍:pandas有两个pivot_table函数 pandas.pivot_tablepandas.DataFrame.pivot_tablepandas.pivot_table 比 pandas.DataFrame.pivot_table 多了一个参数data,data就是一个dataframe,实际上这两个函数相同 pivot_table参数中最重要的四个参数 values,index,columns,aggfunc,下面通过案例介绍pivot_tabe的使用
2 零售会员数据分析案例
2.1 案例业务介绍
业务背景介绍
某女鞋连锁零售企业,当前业务以线下门店为主,线上销售为辅
通过对会员的注册数据以及的分析,监控会员运营情况,为后续会员运营提供决策依据
会员等级说明
① 白银: 注册(0)
② 黄金: 下单(1~3888)
③ 铂金: 3888~6888
④ 钻石: 6888以上
数据分析要达成的目标
描述性数据分析使用业务数据,分析出会员运营的基本情况
案例中用到的数据
① 会员信息查询.xlsx
② 会员消费报表.xlsx
③ 门店信息表.xlsx
④ 全国销售订单数量表.xlsx
分析会员运营的基本情况
从量的角度分析会员运营情况:
① 整体会员运营情况(存量,增量)
② 不同渠道(线上,线下)的会员运营情况
③ 线下业务,拆解到不同的地区、门店会员运营情况
从质的角度分析会员运营情况:
① 会销比
② 连带率
2.2 会员存量、增量分析
加载数据:每月会员数量的存量和增量是最基本的指标,通过会员数量考察会员运营情况
会员信息查询.xlsx
,加载时间较久,耐心等待import pandas as pd custom_info = pd.read_excel('data/会员信息查询.xlsx')custom_info.info()
会员信息数据集查看显示结果:
<class 'pandas.core.frame.DataFrame'>RangeIndex: 952714 entries, 0 to 952713Data columns (total 12 columns):会员卡号952714 non-null object会员等级952714 non-null object会员来源952714 non-null object注册时间952714 non-null datetime64[ns]所属店铺编码 952714 non-null object门店店员编码 253828 non-null object省份 264801 non-null object城市 264758 non-null object性别 952714 non-null object生日 785590 non-null object年齡 952705 non-null float64生命级别952714 non-null objectdtypes: datetime64[ns](1), float64(1), object(10)memory usage: 87.2+ MB
# 会员信息查询custom_info.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sPg0QTJC-1630499468923)(./img/数据透视表-01.png)]
pandas读取行数较多的xlsx文件速度较慢,可以将xlsx转为csv之后,pandas再次读取csv,速度就会快很多
custom_info.to_csv('data/会员信息查询.csv', encoding='utf-8')custom_info = pd.read_csv('data/会员信息查询.csv')custom_info.info()
对比发现,csv数据比原xlsx多了一列显示结果:
C:\ProgramData\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py:3146: DtypeWarning: Columns (7,8) have mixed types.Specify dtype option on import or set low_memory=False.has_raised = await self.run_ast_nodes(code_ast.body, cell_name,<class 'pandas.core.frame.DataFrame'>RangeIndex: 952714 entries, 0 to 952713Data columns (total 13 columns):# ColumnNon-Null Count Dtype --- -------------------- ----- 0 Unnamed: 0 952714 non-null int64 1 会员卡号 952714 non-null object 2 会员等级 952714 non-null object 3 会员来源 952714 non-null object 4 注册时间 952714 non-null object 5 所属店铺编码952714 non-null object 6 门店店员编码253828 non-null object 7 省份264801 non-null object 8 城市264758 non-null object 9 性别952714 non-null object 10 生日785590 non-null object 11 年齡952705 non-null float6412 生命级别 952714 non-null object dtypes: float64(1), int64(1), object(11)memory usage: 94.5+ MB
Unnamed: 0
,将其删除del custom_info['Unnamed: 0'] custom_info.info()
会员信息查询显示结果:
<class 'pandas.core.frame.DataFrame'>RangeIndex: 952714 entries, 0 to 952713Data columns (total 12 columns):# Column Non-Null Count Dtype --- ------ -------------- ----- 0 会员卡号 952714 non-null object 1 会员等级 952714 non-null object 2 会员来源 952714 non-null object 3 注册时间 952714 non-null object 4 所属店铺编码 952714 non-null object 5 门店店员编码 253828 non-null object 6 省份264801 non-null object 7 城市264758 non-null object 8 性别952714 non-null object 9 生日785590 non-null object 10 年齡952705 non-null float6411 生命级别 952714 non-null object dtypes: float64(1), object(11)memory usage: 87.2+ MB
custom_info.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RqfXDYA3-1630499468925)(./img/数据透视表-01.png)]
需要按月统计注册的会员数量,注册时间原始数据需要处理成年-月
的形式
# 给 会员信息表 添加年月列custom_info['注册时间'][0]custom_info['注册时间'][0][:7]custom_info['注册年月'] = custom_info['注册时间'].apply(lambda x: x[:7])custom_info.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bO9zDfEo-1630499468932)(./img/数据透视表-02.png)]
按月计算会员数量的增量
# 对注册年月列进行分组,分组后依据会员卡号计算每组数据的行数,因为会员卡号都是不同的month_count = custom_info.groupby('注册年月')['会员卡号'].count()month_count = month_count.reset_index()month_count = month_count.rename(columns={'会员卡号': '月增量'})month_count.head()# 至此,我们完成了数据透视表,效果如下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PkfbLPP9-1630499468934)(./img/数据透视表-03.png)]
我们可以使用df.pivot_table()
,来实现同样的功能,参数如下 index:行索引,传入原始数据的列名columns:列索引,传入原始数据的列名values: 要做聚合操作的列名aggfunc:聚合函数
# 以注册年月作为索引,对会员卡号列使用pandas内置的count函数进行计算,返回新的df对象month_count = custom_info.pivot_table(index='注册年月', values='会员卡号', aggfunc='count')month_count# 返回结果一致
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wD3lQIFX-1630499468935)(./img/数据透视表-04.png)]
重新设定行索引
month_count = month_count.reset_index()month_count = month_count.rename(columns={'会员卡号': '月增量'})month_count.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o2XhB3t9-1630499468936)(./img/数据透视表-03.png)]
计算会员数量的存量
#通过pandas.series对象的内置函数cumsum()对月增量做累积求和month_count['存量'] = month_count['月增量'].cumsum()month_count
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V8hI9UKb-1630499468937)(./img/数据透视表-05.png)]
2.3 增量等级分布
通过groupby实现,注册年月,会员等级,按这两个字段分组,对任意字段计数会员增量存量不能真实反映会员运营的质量,需要对会员的增量存量数据做进一步拆解
首先思考:从哪些维度来拆解? 从指标构成来拆解: 会员 = 白银会员+黄金会员+铂金会员+钻石会员 从业务流程来拆解: 当前案例,还可以按业务分线上、线下,也可以进一步拆解:按大区,按门店 接下来我们就按照会员等级进行拆解:会员等级分布分析的目的,以及要分析的指标会员按照等级拆解分为:
① 白银: 注册(0)
② 黄金: 下单(1~3888)
③ 铂金: 3888~6888
④ 钻石: 6888以上
由于会员等级跟消费金额挂钩,所以会员等级分布分析可以说明会员的质量
month_degree_count = custom_info.groupby(['注册年月','会员等级'])[['会员卡号']].count()month_degree_count
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nQAO8UT5-1630499468938)(./img/数据透视表-08.png)]
分组之后得到的是multiIndex类型的多级索引,我们使用df.unstack()
使长数据变形为宽数据
# 长数据变形为宽数据month_degree_count = month_degree_count.unstack()month_degree_count
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MlnMkSIy-1630499468938)(./img/数据透视表-09.png)]
可以通过df.__dict__
,更清晰的查看df对象的多级索引关系
month_degree_count.__dict__# 输出结果如下{'_is_copy': None,'_mgr': BlockManagerItems: MultiIndex([('会员卡号', '白银会员'),('会员卡号', '钻石会员'),('会员卡号', '铂金会员'),('会员卡号', '黄金会员')],names=[None, '会员等级'])Axis 1: Index(['-08', '-09', '-10', '-11', '-12', '-01','-02', '-03', '-04', '-05', '-06', '-07','-08', '-09', '-10', '-11', '-12', '-01','-02', '-03'],dtype='object', name='注册年月')IntBlock: slice(0, 4, 1), 4 x 20, dtype: int64,'_item_cache': {},'_attrs': {}}
根据df的多级索引关系,尝试获取白银会员的增量数据
month_degree_count['会员卡号']['白银会员']# 输出结果如下注册年月-08 376648-09 530-10 603-111311-123512-0111244-029937-0341546-0462613-0519317-0611292-073932-0895584-09 133090-109093-116313-122808-013661-024001-0310580Name: 白银会员, dtype: int64
获取索引值构成的列表
index_list = month_degree_count.index.values.tolist()index_list# 输出结果如下['-08','-09','-10','-11','-12','-01','-02','-03','-04','-05','-06','-07','-08','-09','-10','-11','-12','-01','-02','-03']
根据已获取的索引值列表,以及白银会员每月的增量数据,构造新的df对象
new_degree_count = pd.DataFrame({'白银会员': month_degree_count['会员卡号']['白银会员']}, index=index_list)new_degree_count
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BCZ6JXry-1630499468939)(./img/数据透视表-10.png)]
为新的df对象分别添加 黄金会员、铂金会员、钻石会员的增量数据
# new_degree_count['白银会员'] = month_degree_count['会员卡号']['白银会员']new_degree_count['黄金会员'] = month_degree_count['会员卡号']['黄金会员']new_degree_count['铂金会员'] = month_degree_count['会员卡号']['铂金会员']new_degree_count['钻石会员'] = month_degree_count['会员卡号']['钻石会员']new_degree_count
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ozMEG60T-1630499468940)(./img/数据透视表-11.png)]
上述需求我们可以直接使用df.pivot_table()
实现
# 以注册年月为索引,对会员等级进行长变宽(对会员等级列的值进行分组),针对会员卡号列的值进行计算,计算使用内置函数count求总数member_rating = custom_info.pivot_table(index='注册年月', columns='会员等级', values='会员卡号', aggfunc='count')member_rating
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RUARJRaA-1630499468941)(./img/数据透视表-12.png)]
2.4 增量等级占比分析
添加增量总计列增量等级占比分析,查看增量会员的消费情况
# 添加列,值为按行求和 axis='columns' 轴=列==>按行计算member_rating['总计'] = member_rating.sum(axis='columns')member_rating.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YZ00Xa8P-1630499468942)(./img/数据透视表-15.png)]
计算白银和黄金会员等级占比,铂金钻石会员数量太少暂不计算
member_rating['白银会员占比'] = member_rating['白银会员'] / member_rating['总计']member_rating['黄金会员占比'] = member_rating['黄金会员'] / member_rating['总计']# member_rating['铂金会员占比'] = member_rating['铂金会员'] / member_rating['总计']# member_rating['钻石会员占比'] = member_rating['钻石会员'] / member_rating['总计']member_rating
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-inCZqF9H-1630499468942)(./img/数据透视表-16.png)]
2.5 整体等级分布
需求:计算各个等级会员总数占整体的百分比思路:按照会员等级分组,计算每组的会员数量,用每组会员数量/全部会员数量按照会员等级分组,计算每组的会员数量;通过之前的学习,有三种实现方式,代码如下
# 会员按等级分组groupby实现# ratio = custom_info.groupby('会员等级')[['会员卡号']].count().reset_index()# ratio# 另一种写法# ratio2 = custom_info.groupby('会员等级').agg({'会员卡号':'count'}).reset_index()# ratio2# df.pivot_table的实现方式ratio3 = custom_info.pivot_table(index='会员等级', values='会员卡号', aggfunc='count').reset_index()ratio3
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ExmFSzAn-1630499468943)(./img/数据透视表-18.png)]
计算占比:用每组会员数量/全部会员数量
# 修改列名ratio3.columns = ['会员等级', '会员数']ratio3['占比'] = ratio3['会员数'] / (ratio3['会员数'].sum())ratio3
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YDxpaLqj-1630499468944)(./img/数据透视表-19.png)]
2.6 线上线下增量分析
将“会员来源”字段进行拆解,统计线上线下会员增量从业务角度,将会员数据拆分成线上和线下,比较每月线上线下会员的运营情况
# 原始数据custom_info# 透视表实现:以注册年月列作为索引,对会员来源列的值进行分组,分组后对会员卡号列的值进行数量统计from_data = custom_info.pivot_table(index=['注册年月'], columns='会员来源', values ='会员卡号', aggfunc='count')from_data
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wkMdP1b4-1630499468944)(./img/数据透视表-21.png)]
可视化
# 删除第一行数据from_data = from_data[1:]# 出图from_data.plot(figsize=(20,8), fontsize=16, grid=True)plt.title("电商与线下会员增量分析", fontsize=18)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-noDRU0HX-1630499468945)(./img/数据透视表-22.png)]
2.7 地区店均会员数量
会员信息查询表中,只有店铺信息,没有地区信息,需要从门店信息表中关联地区信息加载数据#查看门店信息表store_info = pd.read_excel('data/门店信息表.XLSX')store_info
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jgXPzqFf-1630499468946)(./img/数据透视表-23.png)]
只需要用到门店信息表中的店铺代码
,地区编码
两列
store_info[['店铺代码','地区编码']].head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L98mNRSK-1630499468947)(./img/数据透视表-24.png)]
合并df,使custom_info与store_info基于店铺编码进行关联,将地区编码添加到custom_info中
# pd.merge合并df,左表custom_info,右表为store_info[['店铺代码','地区编码']],分别指定左表和右表的关联字段(列)custom_info1 = pd.merge(custom_info, store_info[['店铺代码','地区编码']], left_on='所属店铺编码', right_on='店铺代码')# 新表中就有了地区编码编码信息custom_info1
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1LSnzz5X-1630499468947)(./img/数据透视表-25.png)]
按照不同地区,分别统计会员总数
#统计不同地区的会员数量,注意电商入口的地区编码为GBL6D01district = custom_info1.groupby('地区编码')[['会员卡号']].count()#修改列名district.columns = ['会员数量']district
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-exeh49IM-1630499468948)(./img/数据透视表-26.png)]
获取地区编码和店铺编码,并去重
# 只取2列返回df,对df按行去重store_info2 = custom_info1[['地区编码','所属店铺编码']].drop_duplicates()store_info2
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ODKXsRFK-1630499468949)(./img/数据透视表-27.png)]
计算每个地区的店铺数量
# 去重之后再统计每个地区店铺的总数store_count_s = store_info2.groupby('地区编码')['所属店铺编码'].count()store_count_s# 返回结果如下地区编码GBL601040GBL602041GBL603078GBL604047GBL605038GBL606031GBL6070 114GBL608070GBL609066GBL610038GBL611046GBL61GBL613071GBL614050GBL6D014Name: 所属店铺编码, dtype: int64
对不同地区会员总数
df添加店铺数
列
# 添加列:店铺数district['店铺数'] = store_count_sdistrict
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nBBCgVcz-1630499468949)(./img/数据透视表-28.png)]
计算地区每店平均会员数,以及全部店铺的平均会员数
# 计算每个地区 每店平均会员数量,并添加列district['每店平均会员数'] = round(district['会员数量'] / district['店铺数'])# 计算总体 每店平均会员数量,并添加列district['总平均会员数'] = district['会员数量'].sum() / district['店铺数'].sum()# 指定列排序,由大到小district = district.sort_values(by='每店平均会员数', ascending=False)district.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cOJlBIRZ-1630499468950)(./img/数据透视表-29.png)]
2.8 各地区会销比
什么是会销比:会销比统计的是会员消费占所有销售金额的比例会销比的作用:通过会销比可以衡量会员的整体质量会销比的计算:会销比 = 会员消费的金额 / 全部客户消费的金额由于数据脱敏的原因,没有全部客户消费金额的数据,所以用如下方式替换会销比 = 会员消费的订单数 / 全部销售订单数加载
data/会员消费报表.xlsx
数据,时间较长custom_consume=pd.read_excel('data/会员消费报表.xlsx')custom_consume.head()
加载data/全国销售订单数量表.xlsx
数据,时间较长
all_orders=pd.read_excel('data/全国销售订单数量表.xlsx')all_orders.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1vJZ0MOQ-1630499468951)(./img/数据透视表-31.png)]
为会员消费报表添加年月列
# 添加年月列 这里年月要转换成整数,因为等会后面要链接的字段是整数# 取其中一条数据举例int(custom_consume['订单日期'][0][:7].replace('-', ''))# lambda x: int(...)) 的...就是 int(custom_consume['订单日期'][0]custom_consume['年月'] = custom_consume['订单日期'].apply(lambda x: int(x[:7].replace('-', '')))custom_consume.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RHRh7Rlp-1630499468952)(./img/数据透视表-32.png)]
为会员消费报表添加地区编码,合并custom_consume和store_info部分字段,两个表的关联字段是店铺代码
store_info.head()custom_consume=pd.merge(custom_consume, store_info[['店铺代码','地区编码']], on='店铺代码')custom_consume.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ykINC4qW-1630499468952)(./img/数据透视表-33.png)]
按地区编码,分年月统计会员购买订单数量
# 以地区编码作为索引,对年月列进行分组,对消费数量(订单数)按照aggfunc指定的函数进行计算# 参数margins=True表示,每行每列都执行aggfunc参数指定的函数,会新增汇总统计列All和汇总统计行Allmember_orders = custom_consume.pivot_table(index='地区编码', columns='年月', values='消费数量', aggfunc=sum, margins=True)member_orders
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q6ECUu1D-1630499468953)(./img/数据透视表-34.png)]
按地区编码,分年月统计全部(会员及非会员)购买订单数量
# 以地区作为索引,对年月进行分组,对全部订单数做sum求和计算,最后添加汇总统计行和汇总统计列country_sales=all_orders.pivot_table(index='地区代码', columns='年月', values='全部订单数', aggfunc=sum, margins=True)country_sales
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BZrkfZI3-1630499468954)(./img/数据透视表-35.png)]
计算各地区订单数量上的会销比
# 会销比 = 会员消费的订单数 / 全部销售订单数result = member_orders / country_salesresult# df.applymap()对每一个值都保留2位小数;计算速度会慢result = result.applymap(lambda x: format(x, ".2%"))result
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yHLTwstS-1630499468955)(./img/数据透视表-36.png)]
统计线下地区的会销比
# 删除线上销售的GBL6D01行,仅统计线下的会销比member_orders = member_orders.drop(['GBL6D01'])# 会销比 = 会员消费的订单数 / 全部销售订单数result = member_orders / country_salesresult# df.applymap()对每一个值都保留2位小数result = result.applymap(lambda x: format(x, ".2%"))result
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-x5CQa6Ic-1630499468957)(./img/数据透视表-37.png)]
2.9 会员连带率分析
连带率的概念:连带率 = 消费数量(售出货物的总数) / 订单数量,是指销售的件数和交易的次数相除后的数值,反映的是顾客平均单次消费的产品件数连带率的作用: 连带率直接影响到客单价:连带率越高、客单价越高大多数情况下,连带率反应运营质量:连带率变高,说明客单价变高、顾客一次买的东西变多了,从而说明运营动作有效果 为什么要计算分析连带率? 通过连带率分析可以反映出人、货、场几个角度的业务问题获取分母df:按地区、年月统计订单的数量
custom_consume # 查看消费和地区店铺的连接表# 去掉退单数据,去掉网店数据order_data = custom_consume.query(" 订单类型=='下单' & 地区编码!='GBL6D01'")# 提取有用的字段,并去重order_count = order_data[['年月','地区编码','订单号']].drop_duplicates()# 按地区、年月统计订单的数量order_count = order_count.pivot_table(index='地区编码', columns='年月', values='订单号', aggfunc='count')order_count
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4gXE0E3d-1630499468957)(./img/数据透视表-38.png)]
获取分子df:按地区、年月统计消费商品数量的总和
order_data # 订单数据表# 按地区、年月统计消费数量的总和consume_count = order_data.pivot_table(index='地区编码', columns='年月', values='消费数量', aggfunc=sum)consume_count.head()
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0VP3RNGp-1630499468958)(./img/数据透视表-39.png)]
计算连带率:连带率 = 消费数量(售出货物的总数) / 订单数量
result = consume_count / order_countround(result[01][0], 2) # 保留2位小数# 显示二位小数result = result.applymap(lambda x: round(x, 2))result
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NLVSjN74-1630499468958)(./img/数据透视表-40.png)]