实验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）请找出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所示。

图8-2 设置条件格式方法2：1）缺失值定位。

“开始”→“编辑/ 查找和选择”→“定位”对话框→“定位条件”按钮。

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

图8-3 “转到（G）…”下拉菜单命令和“定位条件”对话框（2）绘制“income”属性箱线图和点比例图。

高级筛选出异常值。

“加载项/ PHStat”→“Descriptive Statistics”→“Boxplot…”或”Dot Scale Diagram…“命令。

如图8-4所示。

注意选中“income”属性，如果数据区域包括标题行（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高级筛选，筛选出大于Qu，小于QL的离群点。

1）设置条件区域，如图8-5所示。

图8-5 高级筛选“条件区域”2）设置高级筛选。

2．数据探索之数据特征分析建立“2-数据特征分析.xlsx”文件，导入“0-bank_data.xlsx”文件，请你用EXCEL对其进行数据特征分析。

【要求】（1）针对age属性进行分布分析。

填写8-2和8-3表。

表8-2 年龄分布分析表表8-3 年龄数据统计表（2）根据上一步的age属性分组，分别用EXCEL分布工具和PHSTAT软件绘制直方图。

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

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

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

1）EXCEL数据分析“数据”选项卡→“数据分析”按钮→“直方图”选项→“直方图”对话框。

注意，输入区域为：age数据，接收区域为：分组数据，输出区域为：分析结果放置区域。

如图8-6（a）（b）（c）所示。

图8-6（a）“数据分析”路径（b）“数据分析”对话框（c）“直方图”对话框结果如表8-6和图8-7所示。

表8-6 数据分析工具直方图分析结果表接收频率累积 %接收频率累积 %3419532.50%5021435.67%5021468.17%3419568.17%67191100.00%67191100.00%其他0100.00%其他0100.00%图8-7 数据分析工具绘制的直方图2）PHSTAT软件操作步骤如下：图8-8 PHSTAT软件直方图路径注意，PHSTAT软件绘制直方图过程中，作图区的数据不能包含公式。

选中age属性（B2:B601）,分组数据属性（V14:V16），组中值属性（W14:16）绘制直方图。

图8-9 PHSTAT软件“直方图”对话框分析结果如下：表8-7 PHSTAT软件直方图分析结果表Frequency Distribution for Valuefor ValueBins Frequency Percentage Cumulative Pctage.Midpts.0003419532.50%32.50%--5021435.67%68.17%266719131.83%100.00%420058.5图8-10 PHSTAT 软件绘制的直方图（4）仿照上例，计算年龄收入汇总和购买理财计划汇总，填写下表表8-8 收入数据统计数据个数最大值最小值平均值标准差全距组数组距收入数据统计注意：这里收入汇总的公式应该使用sumif 函数。

如，青年的收入汇总应该为：=SUMIF($B$2:$B$601,"<=" & P94,$E$2:$E$601)。

其中，$B$2:$B$601表示年龄区域，&是字符串拼接符号，P94是青年的分组数据。

$E$2:$E$601为income 区域。

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

如，青年的PEP 汇总应该为：=COUNTIFS($B$2:$B$601,"<=" & P94, $L$2:$L$601,"=YES")。

其中，$L$2:$L$601为PEP 区域。

（5）给出自己的分析观点，例如：从人数看，…，从收入上看…，从购买理财年龄段年龄区间分组数据频数收入汇总PEP汇总青年中年老年薪资阶层阶层区间分组数据阶层频数PEP频数平民阶层中产阶层富豪阶层计划的年龄结构看，…。

初步对数据和直方图观测，…是黄金客户，而…可能是潜在客户群。

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 离群点统计表（2）众数求解方法1：建立一个新列，去除空值的current_act列，将新列用if函数转换为1或0后，用mode.sngl函数求解。

由于mode.sngl函数参数必须是数值，所以需要将YES转换为1，NO转换0 。

在N2单元格输入公式：=IF(M2="YES",1,0)，向下填充。

根据N列的1和0，使用mode.sngl函数。

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

YES个数。

公式为：=COUNTIF(J2:J601,"=YES")。

值为442NO个数。

公式为：=COUNTIF(J2:J601,"=NO")。

值为145空值个数。

公式为：=COUNTBLANK(J2:J601)。

值为134. 数据变换【要求】（1）将插补后的“3-数据插补.xlsx”文件另存为“4-bank_data.csv”文件。

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

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

图8-11 Weka登录界面图8-12 Weka导入CSV文件前图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所示。

图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）前的“+”号，选择“NumericT oNominal”，并将first-last 改为6，单击"Apply"按钮，将children类型变成Nominal。