使用Pandas对Excel数据进行交叉分析:添加字段,百分比堆栈可视化等

问题背景

现在有一个基本的数据样本, 包含如下字段

1
['营销名', '内部机型名', '系统版本', '模块', 'AI分类', '二级分类', '反馈描述', '反馈时间', 'sim卡','电话', 'IMEI', '位置', '异常反馈']

基于这些数据可以分析的维度,例如:

  • 哪些模块的问题较为突出,(相对占比较高)
  • 这些模块中的哪些机型的问题较大
  • 用户的这些问题随着事件反馈的变化
  • 用户投诉的内容
  • 单个用户投诉的数量,(投诉专业户的需要降权)

基于这些内容可以构建一个模块软件质量的评分体系,但是这也是后话, 这里主要看下如何更好的得到上述各个指标。

营销名 内部机型名 系统版本 模块 AI分类 反馈描述 反馈时间 sim卡 位置
5G体验终端 TD1903 1.3.4 待定 不准备 2019-04-28 14:58:44.0 unknow-unknow 山东省-德州市-NULL
5G体验终端 TD1903 1.3.4 其他 其他 兔兔咯了 2019-04-28 14:58:29.0 unknow-unknow 山东省-德州市-NULL
Z5x PD1911 1.2.0 其他 其他 是的 2019-04-28 06:16:43.0 NULL 广东省-东莞市-NULL
Z5x PD1911 1.1.0 其他 其他 一!! 2019-04-25 13:18:06.0 NULL 广东省-东莞市-NULL
Z5x PD1911 1.0.1 其他 其他 一。 2019-04-21 05:01:09.0 NULL 广东省-东莞市-NULL

模块占比

在这里首先碰到的问题是, 在模块字段中有部分记录是同时包含多个模块的。在mongodb中可以通过unwind操作符来展开这个列表字段,要想得到准确的模块占比, 就必须能够获取拆分并展开这部分数据。不过在pandas中要完成这个操作就比较复杂了。

拆分模块字段

拆分模块字段主要包含两个步骤:

  • 将模块字段转换成列表
  • 将列表内容展开
1
2
3
4
5
6
7
8
9
10
11
12
## 将模块字段转换成列表形式 
def split_module(row) :
if pd.isna(row["模块"]) :
return []
else:
return row["模块"].split(" ")
data["modules"] = data.apply(lambda row: split_module(row) ,axis = 1) # axis表示按照行处理数据

## 按照modules字段来展开列 / unwind, unpack
t = data.apply(lambda row: pd.Series(row["module"]),axis=1).stack().reset_index(level=1, drop=True) # 通过stack将发生了行列转换, 然后再通过reset_index删除多余的index
t.name = "module"
data_unpacked = data.drop("module",axis = 1).join(t) # 使用index进行匹配。

在上面这段代码中涉及到几个api:apply、stack、reset_index以及join,具体的使用方法可以参见官方文档,地址可以搜索或者在参考文档中查看。

经过上面的计算就得到了展开’模块’字段后的数据集了。

统计各模块的反馈占比

在sql中进行分类统计的方法是groupby + aggfunction,在DataFrame中也是相似的。

1
2
module_dist = data_unpacked.groupby("module").agg({"module":"count"}) 
module_dist.sort_values(by="module", ascending= False ,inplace=True)

这样就能得到各个module所包含的参数,并且按照次数从高到低排序。

可视化占比数据

在jupyter上默认无法使用中文,需要单独的指定中文字体。

1
2
3
from pylab import mpl  # 或者 import matplotlib  as mpl 
mpl.rcParams['font.sans-serif'] = ['FangSong'] # 指定默认字体
mpl.rcParams['axes.unicode_minus'] = False # 解决保存图像是负号'-'显示为方块的问题

对于这个占比数据可以通过柱状图来展示,当然饼图是最能直观的显示数量的方法。

1
module_dist[:30].plot( kind = "pie",y = "module" ,figsize=(20,20) ,fontsize=15)

上面是提取前30个样本,按照module字段的数量来绘制饼图,得到如下图像:

为了保证数据安全,具体的模块名称没有添加。 不过从这个图形可以整个python下面的可视化系统的有如下问题:

  • 美观度 :
  • 细节完成度:

所以在使用dataframe的关注点应该在数据的纵横捭阖上,也就是各种维度和方式的处理上,而不是数据处理的最终阶段——数据可视化。 数据可视化的方式则是将pandas处理得到的中间结果导出到 Excel 中再进行可视化。

模块交叉分析

如上可以得到模块这个单一维度上的差异变化,不过前面可以看到数据维度上还包含反馈时间、机型信息等,可以针对这些维度和各个模块的反馈数量进行交叉分析,查看各个模块问题的进一步的归属问题。

随着时间上的变化各模块上的反馈数量

进行如上分析的首先需要格式化日期字段数据。

1
2
data_unpacked["dt"] = data_unpacked.apply(lambda row:pd.to_datetime(row["反馈时间"]) ,axis = 1 )  
data_unpacked["date"] = data_unpacked.apply(lambda row: pd.to_datetime(row["反馈时间"]).date() ,axis = 1 )

可以使用datatime模块来处理,不过需要在使用strptime方法时指定解析时间格式的语法,而pandas中的to_datetime方法则更加的智能,可以自动的将日期字符串转化为Timestamp对象。

创建二维交叉数据
1
2
moduleByDate = data_unpacked.groupby(["module","date"]).agg({"module":"count"})
moduleByDate.to_excel("moduleByDate.xlsx", merge_cells = False)

和之前采用的groupby对象相似,不过传入的参数是一个数列。 和上一次相同,把数据导出到excel, 将merge_cells设置为False时,导出的文档就不会合并index了。不过在excel绘制图形时却报了每个图表最多只能容纳255个数据系列错误。

看来只能尝试使用pandas来进行可视化了。

创建更友好的dataframe数据结构
1
data_unpacked.groupby(["module","date"])["date"].count().unstack("module").plot(kind = "bar" ,stacked = True, legend = False, figsize=(20,10),fontsize=13)

上图就显示了按照日期的各个模块的用户反馈变化。整理数据的代码则非常的有启发性,通过这种方式得到的原始数据结构能够更直观的理解。先将一个DataFrameGroupBy 对象通过slice操作被转换为SeriesGroupBy对象,这个过程是选择date字段;然后通过count方法,计算计算date字段上所包含数据数量,得到一个包含多层index(module和date)的series对象;通过unstack操作,将module这个子index转移到colum上,从而形成了一个DataFrame。

不过由于module数量太多,看不出来各个module随着日期变化的占比差异。对此有两个优化思路:

  • 将反馈数量较少的模块整理起来
  • 直接计算出来各个模块的占比,变相的绘制百分比堆积柱状图 。
模块占比

在这个过程还是需要使用到apply方法,对原始的DataFrame数据进行整理。

1
2
3
4
5
6
7
8
9
10
11
12
13
def filter_top_module(name, reserved_names ): 
if name in reserved_names:
return name
else:
return "others"

reserved_modules = list(data_unpacked.groupby("module")["module"].count().sort_values(ascending = False)[1:15].index ) # 这样就省的后面再删除其他这个大头了。
# del reserved_modules[0] # 删除最多的选项, 其他项目。

data_unpacked["module2"] = data_unpacked.apply(lambda row : filter_top_module(row["module"], reserved_names= reserved_modules) ,axis=1 )
reservedModulesDate = data_unpacked.groupby(["module2" ,"date"])["date"].count().unstack("module2")
reservedModulesDatePct = reservedModules_date.apply( lambda row : row[1:]/ row[1:].sum() ,axis = 1) # 过滤掉各项的第一个元素,others
reservedModulesDatePct.plot(kind = "bar", stacked = True , legend = False, figsize=(20,10),fontsize=13) # 直接使用二维dataframe进行求和就好。

如上得到各个前15个模块的百分比堆积柱状图了。

从上图可以较为直观的看出来各个模块问题占比的变化。 黄色代表升级后出现问题,可见在四月月中的时候,该模块反馈问题的比例增加较多,可能月中软件版本更新较多,某些变化带来了用户使用上的不满。

不过太多的模块杂糅到一起就很难分析出来各个模块横向纵向变化,解决的方案就是每个模块绘制一张图。

每个模块绘制变化图形 / ggplot 中的facet
1
reservedModulesDatePct.plot(kind = "bar" , legend = False, figsize=(20,15),fontsize=13 ,subplots= True ,layout=(5,3))

通过设定对应的参数layout来完成子图的布局。上图可以直观的看出来各个模块随着日期的变化。

针对column的排序

在数据绘制的时候,最好是按照一定的逻辑,如上图的14个图形中是默认是按照目标的columns中数据顺序进行的排序,但是这一点非常不符合汇报的认知。 在这里排序可以有两个逻辑,按照模块的反馈数量或者模块的波动率排序。 在这里采用第一种方法。

1
reservedModulesDatePctSorted = reservedModulesDatePct.reindex( reservedModulesDatePct.sum().sort_values( ascending = False ).index,axis = 1)

注意有一点,更改index的时候没有inplace的参数,所以必须返回一个对应的数值。

如上是根据时间维度对各个模块进行了交叉分析,可以根据需求针对其他的维度来完成交叉分析,例如机型、地理未知等。不过这些基本上是重复的如上的操作步骤,就不在这里赘述。

小结

很早之前就有看过pandas使用教程,不过均没有熟练的掌握使用方法,现在回想原因有二:拦路虎太多;没有结合实际威问题出发。导致学习的过程没有坚持下去,也没有什么结果。

这次通过真实的意见反馈数据的整理,对数据进行了汇总和二次的交叉分析,在这个过程中了解pandas中关键数据结构DataFrame的处理和整合方法。其中最有用也最具灵活性的就是apply方法,可以利用lambda表达式,来定义函数来处理行或者列的数据。在整理交叉分析的过程中也碰到各种各样的问题,所幸基本上stackoverflow上都有解决方案。

参考文档

pandas-percentage-of-total-with-groupby

Efficiently split Pandas Dataframe cells containing lists into multiple rows 给出unwind的pandas实现方法。

pandas-when-cell-contents-are-lists-create-a-row-for-each-element-in-the-list

pandas-create-new-column-based-on-values-from-other-columns 揭示了apply的应用方法,apply输入的参数是row或者column,具体根据axis参数值决定。

pandas.DataFrame.stack Stack the prescribed level(s) from columns to index

pandas.Series.reset_index level参数: only remove the specified levels from the index ,所以设置的参数是抛弃的层

pandas.DataFrame.join Join columns with other DataFrame either on index or on a key column

Group By: split-apply-combine 这里的文档UserGuide比起api更系统些,会介绍使用场景和具体的使用方法。而api文档则是知其所以然。

DataFrameGroupBy.agg 给出了几种进行计算的方法,包含:函数名,函数列表,函数dict(字典key为具体字段)等方式

pandas.DataFrame.sort_values 注意理解这里的axis等行为,不过有点理解无能, 所以先固化吧

pandas.DataFrame.plot 使用kind参数来指明可视化类型

pandas.DataFrame.plot.pie y 参数: Label or position of the column to plot

time-data-does-not-match-format datetime模块中的解析时间字符串需要指定格式,而pd.to_datetime 可以直接转换。

pandas.DataFrame.unstack Pivot a level of the (necessarily hierarchical) index labels

pandas-plotting-a-stacked-bar-chart 给出了进行groupby进行数据整理的方法

pandas.Series.drop Remove elements of a Series based on specifying the index labels

how-do-i-plot-facet-plots-in-pandas 设置个subplot=True就可以解决问题了。

Building structured multi-plot grids 在sns中可以使用FacetGrid来构建多子图的数据,不过处理起来依然不如ggplot灵活

Provides rolling window calculations Provides rolling window calculations

pandas-series-change-order-of-index

本文原创,请随意转载,但请添加文章链接,并将链接放置在文章开头,谢谢。

随手请吃块糖呗