第3章〓Python表格处理分析


视频讲解



3.1背景介绍

Office办公软件在日常工作学习中的应用可以说是无处不在，其中Excel是可编程性最好的办公软件，使用Excel时经常会要读取、修改和创建大数据量的Excel表格，纯粹依靠手工完成这些工作十分耗时，而且操作的过程中十分容易出错。本章将介绍如何借助Python的openpyxl模块完成这些工作，提升工作效率。Python中的openpyxl模块能够对Excel文件进行创建、读取和修改，让计算机自动进行大量烦琐重复的Excel文件处理成为可能。本章将围绕以下3个重点内容展开。

（1） 修改已有的Excel表单。

（2） 从Excel表单中提取信息。

（3） 创建更为复杂的Excel表单，为表格添加样式、图表等。

在此之前，读者应该熟知Python的基本语法，能够熟练使用Python的基本数据结构，包括dict、list等，并且理解面向对象编程的基本概念。

在开始之前，读者可能会有疑问： 什么时候应该选择使用openpyxl这样的编程工具，而不是直接使用Excel的操作界面来完成工作呢？虽然这样的实际场景数不胜数，但以下这几个例子十分有代表性，提供给读者参考。

假设你在经营一个网店，当你每次需要将新商品上架到网页上时，需要将相应的商品信息填入店铺的系统，而所有的商品信息一开始都记录在若干Excel表格中。如果你需要将这些信息导入系统，就必须遍历Excel表格的每一行，并在店铺系统中重新输入。我们将这种场景抽象成从Excel表单中导出信息。

假设你是一个用户信息系统的管理员，公司在某次促销活动中需要导出所有用户的联系方式到可打印的文件中，并交给销售人员进行电话营销。显然Excel表单是可视化呈现这些信息的不二之选。这样的场景可以称为向Excel表单中导入信息。

假设你是一所中学的数学教师，一次期中测验后你需要整理汇总20个班级的成绩，并制作相应的统计图表。而令人绝望的是，你发现每个班级的成绩散落在不同的表单文件中，无法使用Excel内置的统计工具汇总。我们将这种场景称为Excel表单内部的信息聚合与提取。

类似的问题难以枚举，却无不例外地令人头痛。但是，如果学会使用openpyxl工具，这些就都不再是问题。


3.2前期准备与基本操作
3.2.1基本术语概念说明

在后面章节中将会用表31中的术语名词来指代表格操作中的具体概念。


表31基本术语



术语含义

工作簿指创建或者操作的主要文件对象，通常来讲，一个.xlsx文件对应一个工作簿
工作表工作表通常用来划分工作簿中的不同内容，一个工作簿中可以包含多个不同的工作表
列一列指工作表中垂直排列的一组数据，在Excel中，通常用大写字母指代一列，如第一列通常是A
行一行指工作表中水平排列的一组数据，在Excel中，通常用数字指代一行，如第一行通常是1
单元格一个单元格由一个行号和一个列号唯一确定，如A1指位于第A列第一行的单元格








3.2.2安装openpyxl并创建一个工作簿

如同大多数Python模块，我们可以通过pip工具安装openpyxl，只需要在命令行终端中执行以下命令： 

pip install openpyxl




安装完毕之后，就可以用几行代码创建一个十分简单的工作簿了，代码如下所示。

1from openpyxl import Workbook  

2   

3workbook = Workbook()  

4sheet = workbook.active  

5   

6sheet［"A1"］ = "hello"  

7sheet［"B1"］ = "world!"  

8   

9workbook.save(filename="hello_world.xlsx")





首先从openpyxl包中导入Workbook对象，并在第3行创建一个实例workbook。在第4行中，通过workbook的active属性获取默认的工作表。在第6行和第7行中，向工作表的A1和B1两个位置分别插入hello和world！两个字符串。最后，通过workbook的save方法，将新工作簿存储在名为hello_world.xlsx的文件中。打开该文件，可以看到文件内容如图31所示。



图31hello_world.xlsx文件



3.2.3从Excel工作簿中读取数据

本节为读者提供了样例工作簿sample.xlsx，其中包含了一些亚马逊在线商店的商品评价数据。读者可以在章节对应的附件中找到这个文件，并放置在实验代码的根目录下。之后的样例程序将在该样例工作簿的基础上进行演示。

准备好数据文件后，就可以在Python命令行终端中尝试打开并读取一个Excel工作簿了。在命令行中输入Python命令，进入Python命令行终端，接下来的操作代码如下所示。

1>>> from openpyxl import load_workbook  

2>>> workbook = load_workbook(filename="sample.xlsx")  

3>>> workbook.sheetnames  

4［'Sheet 1'］  

5   

6>>> sheet = workbook.active  

7>>> sheet  

8<Worksheet "Sheet 1">  

9   

10>>> sheet.title  

11'Sheet 1'





为了读取工作簿，需要按照第1处的命令从openpyxl包中导入load_workbook函数。在第2行中，通过调用load_workbook函数并指定路径名，可以得到一个workbook对象。workbook的sheetnames属性为工作簿中所有工作表的名字列表。workbook.active为当前工作簿的默认工作表，我们用sheet变量指向它。sheet的title属性为当前工作表的名称。这个样例是打开工作表的最常见的方式，请读者熟练掌握。

打开工作表后，读者可以检索特定位置的数据，代码如下： 

1>>> sheet［"A1"］  

2<Cell 'Sheet 1'.A1>  

3

4>>> sheet［"A1"］.value  

5'marketplace'  

6

7>>> sheet［"F10"］.value  

8"G-Shock Men's Grey Sport Watch"  





sheet对象类似于一个字典，可以通过组合行列序号的方式得到对应位置的键，然后使用键在sheet对象中获取相应的值。值的形式为Cell类型的对象，如第1行和第2行所示。如果想要获取相应单元格中的内容，可以通过访问Cell对象的value字段来完成(第4~8行)。除此之外，也可以通过sheet对象的cell()方法获取特定位置的Cell对象和对应的值，代码如下所示。

>>> sheet.cell(row=10, column=6)  

<Cell 'Sheet 1'.F10>  



>>> sheet.cell(row=10, column=6).value  

"G-Shock Men's Grey Sport Watch"  




尽管在Python中索引的序号总是从0开始，但对Excel表单而言，行号和列号总是从1开始的，在使用cell（）方法时需要留意这一点。

3.2.4迭代访问数据

本节将会讲解如何遍历访问工作表中的数据，openpyxl提供了十分方便的数据选取工具，而且使用方式十分接近Python语法。依据不同的需求，有如下几种不同的访问方式。

第一种方式是通过组合两个单元格的位置选择一个矩形区域的Cell，代码如下所示。

>>> sheet［"A1:C2"］  

((<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>),  

(<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>))




第二种方式是通过指定行号或列号选择一整行或一整列的数据，代码如下所示。

>>> # Get all cells from column A  

>>> sheet［"A"］  

(<Cell 'Sheet 1'.A1>,  

<Cell 'Sheet 1'.A2>,  

...  

<Cell 'Sheet 1'.A99>,  

<Cell 'Sheet 1'.A100>)  



>>> # Get all cells for a range of columns  

>>> sheet［"A:B"］  

((<Cell 'Sheet 1'.A1>,  

<Cell 'Sheet 1'.A2>,  

...  

<Cell 'Sheet 1'.A99>,  

<Cell 'Sheet 1'.A100>),  

(<Cell 'Sheet 1'.B1>,  

<Cell 'Sheet 1'.B2>,  

...  

<Cell 'Sheet 1'.B99>,  

<Cell 'Sheet 1'.B100>))  



>>> # Get all cells from row 5  

>>> sheet［5］  

(<Cell 'Sheet 1'.A5>,  

<Cell 'Sheet 1'.B5>,  

...  

<Cell 'Sheet 1'.N5>,  

<Cell 'Sheet 1'.O5>)  



>>> # Get all cells for a range of rows  

>>> sheet［5:6］  

((<Cell 'Sheet 1'.A5>,  

<Cell 'Sheet 1'.B5>,  

...  

<Cell 'Sheet 1'.N5>,  

<Cell 'Sheet 1'.O5>),  

(<Cell 'Sheet 1'.A6>,  

<Cell 'Sheet 1'.B6>,  

...  

<Cell 'Sheet 1'.N6>,  

<Cell 'Sheet 1'.O6>))  




第三种方式是通过如下基于Python generator的两个函数来获取单元格： 

（1） iter_rows()。

（2） iter_cols()。

这两个函数都可以接收以下4个参数： 

（1） min_row。

（2） max_row。

（3） min_col。

（4） max_col。

使用方式如下所示。

>>> for row in sheet.iter_rows(min_row=1,  

...max_row=2,  

...min_col=1,  

...max_col=3):  

...print(row)  

(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>)  

(<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>)  





>>> for column in sheet.iter_cols(min_row=1,  

...max_row=2,  

...min_col=1,  

...max_col=3):  

...print(column)  

(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.A2>)  

(<Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.B2>)  

(<Cell 'Sheet 1'.C1>, <Cell 'Sheet 1'.C2>)  




如果在调用函数时将values_only设置为True，将只返回每个单元格的值，代码如下所示。

>>> for value in sheet.iter_rows(min_row=1,  

...max_row=2,  

...min_col=1,  

...max_col=3,  

...values_only=True):  

...print(value)  

('marketplace', 'customer_id', 'review_id')  

('US', 3653882, 'R3O9SGZBVQBV76')  




同时，sheet对象的rows对象和columns对象本身即是迭代器，如果不需要指定特定的行列，而只是想遍历整个数据集，可以使用如下代码访问数据。

>>> for row in sheet.rows:  

...print(row)  

(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>  

...  

<Cell 'Sheet 1'.M100>, <Cell 'Sheet 1'.N100>, <Cell 'Sheet 1'.O100>)





通过使用上述的方法，相信你已经学会如何读取Excel表单中的数据了，以下实例代码展示了一个完整的读取数据并转化为json序列的流程。

import json  

from openpyxl import load_workbook  



workbook = load_workbook(filename="sample.xlsx")  

sheet = workbook.active  



products = {}  



# values_only参数要设为True，因为这里想返回单元格的数值  

for row in sheet.iter_rows(min_row=2,  

min_col=4,  

max_col=7,  

values_only=True):  

product_id = row［0］  

product = {  

"parent": row［1］,  

"title": row［2］,  

"category": row［3］  

}  

products［product_id］ = product  



# 使用json库，以便之后呈现更好的输出格式  

print(json.dumps(products))





3.2.5插入数据

以下为示例代码，当向B10单元格中添加了数据之后，openpyxl会自动插入10行数据，中间未定义的位置的值为None。

>>> def print_rows():  

...for row in sheet.iter_rows(values_only=True):  

...print(row)  



>>> # 在行代码之前，表格中仅有1行  

>>> print_rows()  

('hello', 'world!')  



>>> # 这行代码尝试往第10行添加一个新值  

>>> sheet［"B10"］ = "test"  

>>> print_rows()  

('hello', 'world!')  

(None, None)  

(None, None)  

(None, None)  







(None, None)  

(None, None)  

(None, None)  

(None, None)  

(None, None)  

(None, 'test')




接下来介绍如何插入和删除行列，openpyxl模块提供了以下非常直观的4个函数。

（1） insert_rows()。

（2） delete_rows()。

（3） insert_cols()。

（4） delete_cols()。

每个函数接受两个参数： idx和amount。idx指明了从哪个位置开始插入和删除，amount指明了插入或删除的数量。示例程序如下所示。

>>> print_rows()  

('hello', 'world!')  



>>> # 在已存在的A列后插入新的一列  

>>> sheet.insert_cols(idx=1)  

>>> print_rows()  

(None, 'hello', 'world!')  



>>> # 在B列和C列之间插入新的5列  

>>> sheet.insert_cols(idx=3, amount=5)  

>>> print_rows()  

(None, 'hello', None, None, None, None, None, 'world!')  



>>> # 删掉之前插入的5列  

>>> sheet.delete_cols(idx=3, amount=5)  

>>> sheet.delete_cols(idx=1)  

>>> print_rows()  

('hello', 'world!')  



>>> # 在表格最上面插入新的一行  

>>> sheet.insert_rows(idx=1)  

>>> print_rows()  

(None, None)  

('hello', 'world!')  



>>> # 在表格最上面插入新的3行  

>>> sheet.insert_rows(idx=1, amount=3)  

>>> print_rows()  

(None, None)  

(None, None)  






(None, None)  

(None, None)  

('hello', 'world!')  



>>> # 删掉前4行  

>>> sheet.delete_rows(idx=1, amount=4)  

>>> print_rows()  

('hello', 'world!')





注意，当使用函数插入数据时，插入实际发生在idx参数所指特定行或列的前一个位置，例如调用insert_rows(1)，新插入的行将会在原先的第一行之前，成为新的第一行。


3.3进阶内容
3.3.1为Excel表单添加公式

公式计算可以说是Excel中最重要的功能，也是Excel表单相比其他数据记录工具最为强大的地方。通过使用公式，可以在任意单元格的数据上应用数学方程，得到期望的统计或计量结果。在openpyxl中使用公式和在Excel中编辑公式一样简单，以下示例程序展示了如何查看openpyxl中支持的公式类型。

>>> from openpyxl.utils import FORMULAE  

>>> FORMULAE  

frozenset({'ABS',  

'ACCRINT',  

'ACCRINTM',  

'ACOS',  

'ACOSH',  

'AMORDEGRC',  

'AMORLINC',  

'AND',  

...  

'YEARFRAC',  

'YIELD',  

'YIELDDISC',  

'YIELDMAT',  

'ZTEST'})





向单元格中添加公式的操作与赋值操作非常类似，示例代码如下所示，计算第H列第2~100行的平均值。

>>> workbook = load_workbook(filename="sample.xlsx")  

>>> sheet = workbook.active  

>>> # 给第 H列排序  

>>> sheet［"P2"］ = "=AVERAGE(H2:H100)"  

>>> workbook.save(filename="sample_formulas.xlsx")  




操作后的Excel表单如图32所示。



图32sample_formulas.xlsx


在需要添加的公式中有时候会出现引号包围的字符串，这时需要特别留意。有两种方式应对这个问题： 将最外围改为单引号； 对公式中的双引号使用转义符。例如我们要统计第I列的数据中大于0的个数，代码如下所示。

>>> # 统计第I列中大于0的数据个数 

>>> sheet［"P3"］ = '=COUNTIF(I2:I100, ">0")'  

>>> # or sheet［"P3"］ = "=COUNTIF(I2:I100, ＼">0＼")"  

>>> workbook.save(filename="sample_formulas.xlsx")  




统计结果如图33所示。



图33添加计数统计的sample_formulas



3.3.2为表单添加条件格式

条件格式是指表单根据单元格中不同的数据自动地应用预先设定的不同种类的格式。举一个比较常见的例子，如果想让成绩统计册中所有不及格的学生都被高亮地显示出来，那么条件格式就是最恰当的工具。下面在sample.xlsx数据表上演示示例。

以下代码实现了一个简单的功能： 将所有评分为3以下的行标成红色。

1>>> from openpyxl.styles import PatternFill, colors  

2>>> from openpyxl.styles.differential import DifferentialStyle  

3>>> from openpyxl.formatting.rule import Rule  

4   

5>>> red_background = PatternFill(bgColor=colors.RED)  

6>>> diff_style = DifferentialStyle(fill=red_background)  

7>>> rule = Rule(type="expression", dxf=diff_style)  

8>>> rule.formula = ［"$H1<3"］  

9>>> sheet.conditional_formatting.add("A1:O100", rule)  

10>>> workbook.save("sample_conditional_formatting.xlsx")




第1行openpyxl.style中引入了PatternFill和colors两个对象，这两个对象是为了设定目标数据行的格式属性。在第2行中引入了DifferentialStyle这个包装类，可以将字体、边界、对齐等多种不同的属性聚合在一起。第3行引入了Rule类，通过Rule类可以设定填充属性需要满足的条件。如第5~9行所示，应用条件格式的主要流程为先构建PatternFill对象red_background，再构建DifferentialStyle对象diff_style，diff_style将作为rule对象构建的参数。构建rule对象时，需要指明rule的类型为expression，即通过表达式进行选择。在第8行，指明了rule的公式为满足第H列数值小于3的相应行，此处的公式语法与Excel软件中的公式语法一致。

评分为3以下的条目均被标红，如图34所示。



图34评分为3以下的条目均被标红


为了方便起见，openpyxl提供了以下三种内置的格式，可以让使用者快速地创建条件格式。

（1） ColorScale。

（2） IconSet。

（3） DataBar。

ColorScale可以根据数值的大小创建色阶，使用方法如下所示。

>>> from openpyxl.formatting.rule import ColorScaleRule  

>>> color_scale_rule = ColorScaleRule(start_type="num",  

...start_value=1,  

...start_color=colors.RED,  

...mid_type="num",  

...mid_value=3,  

...mid_color=colors.YELLOW,  

...end_type="num",  

...end_value=5,  

...end_color=colors.GREEN)  



>>> # 将这个梯度加到第H列  

>>> sheet.conditional_formatting.add("H2:H100", color_scale_rule)  

>>> workbook.save(filename="sample_conditional_formatting_color_scale_3.xlsx")





效果如图35所示，单元格的颜色随着评分由高到低逐渐由绿变红。



图35使用ColorScale创建色阶


IconSet可以依据单元格的值来添加相应的图标，代码如下所示，只需要指定图标集合的类别和相应值的范围，就可以直接应用到表格上。完整的图标列表可以在openpyxl的官方文档中找到。

>>> from openpyxl.formatting.rule import IconSetRule  



>>> icon_set_rule = IconSetRule("5Arrows", "num", ［1, 2, 3, 4, 5］)  

>>> sheet.conditional_formatting.add("H2:H100", icon_set_rule)  

>>> workbook.save("sample_conditional_formatting_icon_set.xlsx")




效果如图36所示。



图36添加了图标的表格


DataBar允许在单元格中添加类似进度条一样的条带，直观地展示数值的大小，使用方式如下所示。

>>> from openpyxl.formatting.rule import DataBarRule  



>>> data_bar_rule = DataBarRule(start_type="num",  

...start_value=1,  

...end_type="num",  

...end_value="5",  

...color=colors.GREEN)  

>>> sheet.conditional_formatting.add("H2:H100", data_bar_rule)  

>>> workbook.save("sample_conditional_formatting_data_bar.xlsx")





只需要指定规则的最大值和最小值，以及希望显示的颜色，就可以直接使用了。代码执行后的效果如图37所示。



图37添加了DataBar的表格


使用条件格式可以实现很多功能，这里限于篇幅只展示了一部分样例，读者可以查阅openpyxl的文档获得更多的信息。

3.3.3为Excel表单添加图表

Excel表单可以生成具有表现力的数据图表，包括柱状图、饼图、折线图等，使用openpyxl一样可以实现对应的功能。

在展示如何添加图表之前，需要先构建一组数据作为实例，代码如下所示。

from openpyxl import Workbook  

from openpyxl.chart import BarChart, Reference  



workbook = Workbook()  

sheet = workbook.active  



rows = ［  

［"Product", "Online", "Store"］,  

［1, 30, 45］,  

［2, 40, 30］,  

［3, 40, 25］,  

［4, 50, 30］,  

［5, 30, 25］,  

［6, 25, 35］,  

［7, 20, 40］,  

］  



for row in rows:  

sheet.append(row)





接下来，就可以通过BarChart类对象来为表格添加柱状图，我们希望柱状图展示每类商品的总销量，代码如下所示。

chart = BarChart()  

data = Reference(worksheet=sheet,  

min_row=1,  

max_row=8,  

min_col=2,  

max_col=3)  



chart.add_data(data, titles_from_data=True)  

sheet.add_chart(chart, "E2")  



workbook.save("chart.xlsx")





简洁的柱状图已经生成，并且插入了表格，如图38所示。



图38插入了柱状图的表格


插入图表的左上角将和代码指定的单元格对齐，样例将图表对齐在了E2处。

如果想绘制一个折线图，可以简单修改代码，然后使用LineChart类，代码如下所示。

import random  

from openpyxl import Workbook  

from openpyxl.chart import LineChart, Reference  



workbook = Workbook()  

sheet = workbook.active  



# 创建一些示例销售数据 

rows = ［  

［"", "January", "February", "March", "April",  

"May", "June", "July", "August", "September",  

"October", "November", "December"］,  






［1, ］,  

［2, ］,  

［3, ］,  

］  



for row in rows:  

sheet.append(row)  



for row in sheet.iter_rows(min_row=2,  

max_row=4,  

min_col=2,  

max_col=13):  

for cell in row:  

cell.value = random.randrange(5, 100)  



chart = LineChart()  

data = Reference(worksheet=sheet,  

min_row=2,  

max_row=4,  

min_col=1,  

max_col=13)  



chart.add_data(data, from_rows=True, titles_from_data=True)  

sheet.add_chart(chart, "C6")  



workbook.save("line_chart.xlsx")





效果如图39所示。



图39添加了折线图的表格



3.4数据分析实例
3.4.1背景与前期准备

本实例中使用的数据为Consumer Reviews of Amazon Dataset中的一部分，读者可以在随书的资料中找到名为Consumer_Reviews_of_Amazon.xlsx的文件。Consumer Reviews of Amazon Dataset有超过34000条针对Amazon产品(如Kindle、Fire TV Stick等)的消费者评论，以及Datafiniti产品数据库提供的更多评论。数据集中包括基本产品信息、评分、评论文本等相关信息。本节提供的数据截取了数据集中的一部分，完整的数据集可从Datafiniti的网站获得。

通过这些数据，读者可以了解亚马逊的消费电子产品销售情况，分析每次交易中消费者的评论，甚至可以进一步构建机器学习模型对产品的销售情况进行预测，如： 

（1） 最受欢迎的亚马逊产品是什么？

（2） 每个产品的初始和当前顾客评论数量是多少？

（3） 产品发布后的前90天内的评论与产品价格相比如何？

（4） 产品发布后的前90天内的评论与整个销售周期相比如何？

将评论文本中的关键字与评论评分相对应来训练情感分类模型。

本节主要聚焦于数据的可视化分析，展示如何使用openpyxl读取数据，如何与Pandas、Matplotlib等工具交互，以及如何将其他工具生成的可视化结果重新导回到Excel中。

首先新建一个工作目录，并将Consumer_Reviews_of_Amazon.xlsx复制到当前的工作目录下，然后使用如下命令安装额外的环境依赖。

pip install numpy matplotlib sklearn pandas Pillow





3.4.2使用openpyxl读取数据并转为DataFrame

代码如下所示。

1import pandas as pd  

2from openpyxl import load_workbook  

3

4workbook = load_workbook(filename="Consumer_Reviews_of_Amazon.xlsx")  

5sheet = workbook.active  

6

7data = sheet.values  

8

9# 将第一行作为DataFrame结构的第一列  

10cols = next(data)  

11data = list(data)  

12

13df = pd.DataFrame(data, columns=cols)





在第4行中，加载准备好的文件。在第5行中，获得默认工作表sheet。在第7行中，通过sheet的values属性提取工作表中所有的数据。在第10行中，将data的第一行单独取出，作为Pandas中DataFrame的列名，然后在11行中将data生成器转化为Python List(注意，这里的Python List中不包含原工作表中的第一行，请读者自行思考原因)。最后，在第13行中将数据转化为DataFrame，留作下一步使用。


3.4.3绘制数值列直方图

得到待分析的数据后，通常要做的第一步就是统计各列的数值分布，使用直方图直观地展示出来。下面将自定义一个较为通用的直方图绘制函数，这个函数使用直方图将表中所有数值可枚举(1~50种)的列展示出来，代码如下所示。



1from mpl_toolkits.mplot3d import Axes3D  

2from sklearn.preprocessing import StandardScaler  

3import matplotlib.pyplot as plt # 绘制

4import numpy as np # 线性代数

5import os # 访问目录结构

6   

7# 列数据的柱形分布图  

8def plotPerColumnDistribution(df, nGraphShown, nGraphPerRow):  

9nunique = df.nunique()  

10df = df［［col for col in df if nunique［col］ > 1 and nunique［col］ < 50］］
# 为了显示，选择具有1~50个唯一值的列

11nRow, nCol = df.shape  

12columnNames = list(df)  

13nGraphRow = (nCol + nGraphPerRow - 1) / nGraphPerRow  

14plt.figure(num = None, figsize = (6 * nGraphPerRow, 8 * nGraphRow), dpi = 80, 

facecolor = 'w', edgecolor = 'k')  

15for i in range(min(nCol, nGraphShown)):  

16plt.subplot(nGraphRow, nGraphPerRow, i + 1)  

17columnDf = df.iloc［:, i］  

18if (not np.issubdtype(type(columnDf.iloc［0］), np.number)):  

19valueCounts = columnDf.value_counts()  

20valueCounts.plot.bar()  

21else:  

22columnDf.hist()  

23plt.ylabel('counts')  

24plt.xticks(rotation = 90)  

25plt.title(f'{columnNames［i］} (column {i})')  

26plt.tight_layout(pad = 1.0, w_pad = 1.0, h_pad = 1.0)  

27plt.show()  

28plt.savefig('./ColumnDistribution.png')  

29

30plotPerColumnDistribution(df, 10, 5)





plotPerColumnDistribution函数接受3个参数： df为DataFrame数据集； nGraphShown为图总数的上限； nGraphPerRow为每行的图片数。在第9行中使用Pandas的nunique方法获得每一列的不重复值的总数。在第10行中将不重复值总数
为1~50的列保留，其余剔除。第11~14行计算总行数，并设置Matplotlib的画布尺寸和排布。从第15行开始依次绘制每个子图。绘制过程中需要区分值的类型，如果该列不是数值类型，则需要对各种值的出现数量进行统计，并通过plot.bar()方法绘制到画布上(第18~20行)； 如果该列是数值类型，则只需要调用hist()函数即可完成绘制(第22行)。在第23~25行中设置图题以及坐标轴标签。在第26行和第27行中调整布局后即可通过plt.show()查看绘制结果，如图310所示。



图310ColumnDistribution



3.4.4绘制相关性矩阵

相关性矩阵是表示变量之间的相关系数的表。表格中的每个单元格均显示两个变量之间的相关性。通常在进行数据建模之前需要计算相关性矩阵，主要原因有以下3个。

（1） 通过相关性矩阵图表，可以较为清晰直观地看出数据中的隐藏特征。

（2） 相关性矩阵可以作为其他分析的输入特征。例如，使用相关矩阵作为探索性因素分析、确认性因素分析、结构方程模型的输入，或者在线性回归时用来成对排除缺失值。

（3） 作为检查其他分析结果时的诊断因素。例如，对于线性回归，变量间相关性过高则表明线性回归的估计值是不可靠的。

同样地，本节将会定义一个较为通用的相关性矩阵构建函数，代码如下所示。



1def plotCorrelationMatrix(df, graphWidth):  

2filename = df.dataframeName  

3df = df.dropna('columns')  # 去除值为NaN的列

4df = df［［col for col in df if df［col］.nunique() > 1］］ # 保留超过1个唯一值的列

5if df.shape［1］ < 2:  





6print(f'No correlation plots shown: The number of non-NaN or constant columns
({df.shape［1］}) is less than 2')  

7return  

8corr = df.corr()  

9plt.figure(num=None, figsize=(graphWidth, graphWidth), dpi=80, 

facecolor='w', edgecolor='k')

10corrMat = plt.matshow(corr, fignum = 1)  

11plt.xticks(range(len(corr.columns)), corr.columns, rotation=90)  

12plt.yticks(range(len(corr.columns)), corr.columns)  

13plt.gca().xaxis.tick_bottom()  

14plt.colorbar(corrMat)  

15plt.title(f'Correlation Matrix for {filename}', fontsize=15)  

16plt.show()  

17plt.savefig('./CorrelationMatrix.png')  

18

19df.dataframeName = 'CRA'  

20plotCorrelationMatrix(df, 8)




在第2行中获得当前表名(注意，手动构建的Dataframe需要手动指定dataframeName，见第19行)。在第3行中将表中的空值全部丢弃。在第4行中将所有值都相同的列全部丢弃。这时，如果列数小于2，则无法进行相关性分析，打印警告并直接返回。在第8行中通过corr()方法获得相关性矩阵的原始数据。在第10~17行中设置画布并绘制，最终的效果如图311所示。



图311相关性矩阵



在图311中，颜色越浅则相关性越高。可以看到，用户是否对商品进行打分与是否进行评论的相关性很强。这表明评论与打分是两个关联极强的因素，可以进一步设计模型根据其中一个来预测另一个。

3.4.5绘制散布矩阵

散布矩阵(Scatterplot Matrix)又叫scagnostic，是一种常用的高维度数据可视化技术，最初是由John和Paul Turkey提出的。它将高维度的数据的每两个变量组成一个散点图，再将它们按照一定的顺序组成散点图矩阵。通过这样的可视化方式，能够将高维度数据中所有的变量两两之间的关系展示出来。

下面将介绍如何构建一个简单的散布矩阵函数，代码如下所示。



1def plotScatterMatrix(df, plotSize, textSize):  

2df = df.select_dtypes(include =［np.number］) # 保留类型为数字的列

3# Remove rows and columns that would lead to df being singular  

4df = df.dropna('columns')  

5df = df［［col for col in df if df［col］.nunique() > 1］］ # 保留超过1个唯一值的列

6columnNames = list(df)  

7if len(columnNames) > 10: # 减少矩阵求逆的列数

8columnNames = columnNames［:10］  

9df = df［columnNames］  

10ax = pd.plotting.scatter_matrix(df, alpha=0.75, figsize=［plotSize, plotSize］, diagonal='kde')  

11corrs = df.corr().values  

12for i, j in zip(*plt.np.triu_indices_from(ax, k = 1)):  

13ax［i, j］.annotate('Corr. coef = %.3f' % corrs［i, j］, (0.8, 0.2), xycoords='axes fraction', ha='center', va='center', size=textSize)  

14plt.suptitle('Scatter and Density Plot')  

15plt.show()  

16plt.savefig('./ScatterMatrix.png')  

17   

18plotScatterMatrix(df, 9, 10)  





在第2行中去除所有非数字类型的列。在第4行中将表中的空值全部丢弃。在第5行中将所有值都相同的列全部丢弃。第6、7行截取了前10列进行展示，这是因为如果列数过多会超出屏幕的显示范围，读者可以自行选择需要绘制的特定列。在第10行中通过pd.plotting.scatter_matrix初始化画布。在第11行中获取相关性系数。第12、13行将依次获取不同的列组合，并绘制该组合的相关性图表。在第14~16行中绘制并保存图片。最终的可视化结果如图312所示。



图312散布矩阵



在图312中，左上和右下展示了numHelpful和rating的数据分布，可以看到绝大多数商品的numHelpful数量为0，而其他数量的分布比较平均。绝大部分商品的rating为5分，20%左右的商品是4分，低于4分的数量较少。左下和右上的散点图展示了数据在交叉的两个维度上的分布，绝大部分的numHelpful评价都来源于打分为5分的商品，且分数越低，出现numHelpful评价的概率越小，这符合日常生活的直觉。

3.4.6将可视化结果插入Excel表格

前面的可视化图表都以PNG图片格式存储在工作路径中，下面介绍如何将图片插入Excel工作簿，代码如下所示。

from openpyxl import Workbook    

from openpyxl.drawing.image import Image  



workbook = Workbook()    

sheet = workbook.active  



vis = Image("ScatterMatrix.png")  



# 改变形状，避免logo占据整个表格  

vis.height = 600  

vis.width = 600  



sheet.add_image(vis, "A1")  

workbook.save(filename="visualization.xlsx")





在上述代码中，首先创建了一个新的工作簿，而后通过openpyxl的Image模块加载了已经预先生成的ScatterMatrix.png。在调整了图片的大小后，将其插入A1单元格，最后保存工作簿。流程十分清晰简单，最终的效果如图313所示。



图313visualization.xlsx