实验8-1 数据分析

实验8-1 数据分析

一、实验目的

1. 理解数据挖掘的一般流程。 2. 掌握数据探索和预处理的方法。

3. 使用PHSTAT软件，结合Excel对给定的数据进行手工预处理。 4. 使用WEKA软件，对给定的数据进行预处理。

二、实验内容

在D盘中以“班级-学号-姓名”命名一个文件夹，将下发的数据拷贝到该文件夹下，根据不同要求，对下发的文件进行相应的数据分析和处理。

0. 数据集介绍

银行资产评估数据bank-data.xlsx，数据里有12个属性，分别是id（编号）, age（年龄）, sex（性别）, region（地区）, income（收入）,married（婚否）, children（子女数）, car（是否有私家车）, save_act（是否有定期存款）, current_act（是否有活期账户）, mortgage（是否有资产抵押）, pep（目标变量，是否买个人理财计划Personal Equity Plan）。

1．数据探索之数据质量分析

新建“1-数据质量分析.xlsx”文件，导入“0-bank_data.xlsx”文件数据，请你用EXCEL对其进行数据质量分析。

【要求】

1

（1）请找出bank_data.xlsx表中的含有缺失值的记录。

（2）请你用PHSTAT软件绘制“income（收入）”属性的箱线图和点比例图，筛选出异常值。

（3）计算Whisker上限、Whisker下限，并利用高级筛选，找出该属性的异常值记录。

【提示】

（1）请找出bank_data.xlsx表中的含有缺失值的记录。 方法1：条件格式法 1）选取A1：L601区域。

2）开始 --> 条件格式 --> 新建规则（N）...，在\新建格式规则\对话框中，选择空值。如图8-1所示。

图8-1 “新建格式规则”对话框

3）点击“格式(F)…”按钮，设置特殊格式，高亮显示。如图8-2所示。

2

图8-2 设置条件格式

方法2：

1）缺失值定位。“开始”?“编辑 / 查找和选择”?“定位”对话框 ?“定位条件”按钮。

2）“定位条件”对话框 ? 选择“空值”，如图8-3所示。

图8-3 “转到（G）…”下拉菜单命令 和“定位条件”对话框

（2）绘制“income”属性箱线图和点比例图。高级筛选出异常值。 “加载项 / PHStat”? “Descriptive Statistics”?“Boxplot…”或”Dot Scale Diagram…“命令。如图8-4所示。注意选中“income”属性，如果数据

3

区域包括标题行（E1单元格），则勾选下面的First cell contains label选项，否则，不用勾选First cell contains label选项。

（a） （b）

（c） （d）

图8-4 PHSTAT 软件的“箱线图”和“点比例图”绘制

计算income属性的最小值、最大值、第一四分位数、中位数、第三四分位数。以及Whisker下限和上限。

表8-1 四分位数表

箱线图 最小值 公式 =MIN(E2:E601) 数值 5014.21 4

或=QUARTILE.INC(E2:E601,0) 最大值 =MAX(E2:E601) 或=QUARTILE.INC(E2:E601,4) 第一分位数 第二分位数 （中位数） 第三分位数 =QUARTILE.INC($E$2:$E$601,1) 17264.5 =MEDIAN($E$2:$E$601) 或=QUARTILE.INC(E2:E601,2) =QUARTILE.INC(E2:E601,3) 36172.675 -11097.763 64534.9375 24925.3 78843.21 Whisker上限 =QL-1.5*（Qu-QL） Whisker下限 =Qu+1.5*(Qu-QL) 高级筛选，筛选出大于Qu，小于QL的离群点。 1）设置条件区域，如图8-5所示。

income <-11097.7625 >64534.9375 图8-5 高级筛选“条件区域”

2）设置高级筛选。

2．数据探索之数据特征分析

建立“2-数据特征分析.xlsx”文件，导入“0-bank_data.xlsx”文件，请你用EXCEL对其进行数据特征分析。 【要求】

（1）针对age属性进行分布分析。填写8-2和8-3表。

5

表8-2 年龄分布分析表 表8-3 年龄数据统计表

年龄数据统计数据个数最大值最小值平均值标准差全距组数组距年龄段青年中年老年年龄区间分组数据组中值频数累积频数累积百分比 （2）根据上一步的age属性分组，分别用EXCEL分布工具和PHSTAT软件绘制直方图。

（3）同理，将收入属性分组，计算各年龄段收入汇总和购买理财计划汇总，找出购买理财计划的主要群体。

【提示】

（1）age分为三组（青年组、中年组和老年组），填写age数据统计，如表8-4所示。

表8-4 年龄数据分布公式表

年龄数据分布 公式 数据个数 最大值 最小值 平均值 标准差 全距 组数 =COUNT(B2:B601) =MAX(B2:B601) =MIN(B2:B601) 值 600 67 18 所在单元格 O4 O5 O6 =AVERAGE(B2:B601) 42.395 O7 =STDEV.P(B2:B601) =INT(O5-O6) 14.413 O8 49 3 O9 O10 6

组距 表8-5 年龄统计公式表

年龄段 N13 青年 N14 中年 N15 老年 N16 =O5 年龄区间 O13 分组数据 P13 =O6+O11 =INT(O9/O10) 16 O11 组中值 Q13 =（O6+P14）/2 频数 R13 {=FREQUENCY(B2:B601,P14:P16)} 累积频数 S13 累积百分比 T13 =R14+S13 =S14/$O$4 =R15+S14 =S15/$O$4 =R16+S15 =S16/$O$4 =P14+O11 =（P14+P15）/2 =（P15+P16）/2 （2）数据分析工具分析。 1）EXCEL数据分析

“数据”选项卡 ? “数据分析”按钮?“直方图”选项?“直方图”对话框。注意，输入区域为：age数据，接收区域为：分组数据，输出区域为：分析结果放置区域。 如图8-6（a）（b）（c）所示。

图8-6（a）“数据分析”路径（b）“数据分析”对话框（c）“直方图”对话框

结果如表8-6和图8-7所示。

表8-6 数据分析工具直方图分析结果表

7

接收345067其他频率1952141910累积 2.50h.170.000.00%接收503467其他频率2141951910累积 5.67h.170.000.00%

图8-7 数据分析工具绘制的直方图

2）PHSTAT软件操作步骤如下：

图8-8 PHSTAT软件直方图路径

注意，PHSTAT软件绘制直方图过程中，作图区的数据不能包含公式。 选中age属性（B2:B601）,分组数据属性（V14:V16），组中值属性（W14:16）

8

绘制直方图。

图8-9 PHSTAT软件“直方图”对话框

分析结果如下：

表8-7 PHSTAT软件直方图分析结果表

Frequency Distribution for Valuefor ValueBins345067Frequency01952141910Percentage032.505.671.83umulative Pctage.032.50h.170.00%--264258.5Midpts. 9

图8-10 PHSTAT软件绘制的直方图

（4）仿照上例，计算年龄收入汇总和购买理财计划汇总，填写下表

表8-8 收入数据统计

收入数据统计数据个数最大值最小值平均值标准差全距组数组距年龄段青年中年老年薪资阶层平民阶层中产阶层富豪阶层阶层区间分组数据阶层频数PEP频数年龄区间分组数据频数收入汇总PEP汇总 注意：这里收入汇总的公式应该使用sumif函数。如，

青年的收入汇总应该为：=SUMIF($B$2:$B$601,\。其中，$B$2:$B$601表示年龄区域，&是字符串拼接符号，P94是青年的分组数据。$E$2:$E$601为income区域。

青年的PEP汇总应该使用countifs函数。如，

青年的PEP汇总应该为：=COUNTIFS($B$2:$B$601,\& P94, $L$2:$L$601,\。其中，$L$2:$L$601为PEP区域。

（5）给出自己的分析观点，例如：从人数看，…，从收入上看…，从购买理财

10

计划的年龄结构看，…。初步对数据和直方图观测，…是黄金客户，而…可能是潜在客户群。

3. 数据清洗

建立“3-数据插补.xlsx”文件，导入“0-bank_data.xlsx”文件，请你用EXCEL对异常值和缺失值进行插补。 【要求】

（1）请对income属性的离群点，用均值法替换修正。 （2）请将current_act属性的空值，用众数法替换补值。 【提示】

（1）income属性的离群点（异常值）

根据实验8-1的第一小题实验可知income属性的离群点（异常值），可以用公式计算去除异常值后的均值，并用均值替换异常点的income属性。如表8-9所示。设置O4单元格为\，P4单元格为whisker下限，O5单元格为\，P5单元格为whisker上限。

表8-9 离群点统计表

离群点统计 样本总数 样本income总和 异常点数量 均值 公式 =COUNT(E2:E601) =SUM(E2:E601) =COUNTIF(E2:E601,O4&P4)+COUNTIF(E2:E601,O5&P5) 值 600 16535521 2 143641.5 异常点income总和 =SUMIF(E2:E601,O4&P4)+SUMIF(E2:E601,O5&P5) =（样本总和-异常点income总和）/（样本总数-异常点数量） 27411.17 （2）众数求解

方法1：建立一个新列，去除空值的current_act列，将新列用if函数转换为1或0后，用mode.sngl函数求解。

11

由于mode.sngl函数参数必须是数值，所以需要将YES转换为1，NO转换0 。在N2单元格输入公式：=IF(M2=\，向下填充。

根据N列的1和0，使用mode.sngl函数。公式为：=MODE.SNGL(N2:N588) 方法2：可以用countif函数，分别求出YES的个数、NO的个数和空值的个数，比较找出众数。

YES个数。公式为：=COUNTIF(J2:J601,\。值为442 NO个数。公式为：=COUNTIF(J2:J601,\。值为145 空值个数。公式为：=COUNTBLANK(J2:J601)。值为13 4. 数据变换 【要求】

（1）将插补后的“3-数据插补.xlsx”文件另存为“4-bank_data.csv”文件。 （2）启动Weka软件，导入“4-bank_data.csv”文件，对待挖掘的数据进行属性规约和数据离散化后，另存为“4-bank_data.arff”文件。

【提示】

（1）现将xlsx文件另存为csv文件，然后再导入到Weka中，如图8-11~13所示。

12

图8-11 Weka登录界面

图8-12 Weka导入CSV文件前

13

图8-13 Weka导入CSV文件后

（2）对数据进行数据变换。

① 首先，数据规约，删除无用的属性，通常对于数据挖掘任务来说，ID这样的属性信息是无用的，故此，我们将之删除。如图14所示。

图8-14 删除ID属性

② 其次，连续属性的离散化。数据集中，age属性、income属性和children属性都是数值型数据，需要将其转换为标称型数据。

离散化age属性和income属性。勾选age属性和income属性?“choose”? discretize，然后，单击”Discretize-B10-M-0.1-R first-last“，在弹出的对话框中，将分组bin改为3，将”first-last”改为1,4，如图15~17所示。

14

图8-15 Discretize-B10-M-0.1-R first-last 图8-16 bin值改为3

图8-17 修改后的属性类型

③ children属性只有4个取值：0,1,2,3。勾选children属性，单击“choose”按钮，再弹出的下拉列表中，展开无监督学习（unsupervised）前的“+”号和属性（attribute）前的“+”号，选择“NumericToNominal”，并将first-last改为6，单击\按钮，将children类型变成Nominal。如图8-18~20所示。

15

图8-18 离散化children属性 8-19 无监督学习下的NumericToNominal”

图8-20 离散化后的children属性

（3）保存为arff格式，并用UltraEdit打开该文件，手工修改晦涩的标识，如-inf-34.333333改为0…34（-inf用最小值替换、inf用最大值替换）。

5. 绘制不等宽的柱形图 【要求】

（1）统计各年龄层次购买PEP的人数。

（2）绘制购买PEP的各年龄层次的不等宽柱形图。如下图所示。

图8-15 购买PEP的年龄结构

【提示】

（1）分组临界点可参考本节第二小题，统计函数可以使用COUNTIF和

16

COUNTIFS，如表8-10所示。

表8-10年龄分布表

年龄段 青年 中年 老年 临界点 34 50 67 人数 71 95 108 （2）不等宽柱形图，可采用错行数据，绘制堆积柱形图的方法实现（青年71行、中年95行，老年108行）。如图8-16所示。

中年123…1234…12345…717171……95959595……108108108108108…青年老年 图8-16 错行任意宽度柱形图的画法

17

本文来源：https://www.bwwdw.com/article/snda.html