.实验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)或=QUARTILE.INC(E2:E601,0)5014.21最大值=MAX(E2:E601)或=QUARTILE.INC(E2:E601,4)78843.21第一分位数=QUARTILE.INC($E$2:$E$601,1)17264.5第二分位数（中位数）=MEDIAN($E$2:$E$601)或=QUARTILE.INC(E2:E601,2)24925.3第三分位数=QUARTILE.INC(E2:E601,3)36172.675Whisker上限=QL-1.5*（Qu-QL）-11097.763Whisker下限=Qu+1.5*(Qu-QL)64534.9375高级筛选，筛选出大于Qu，小于QL的离群点。1）设置条件区域，如图8-5所示。income64534.9375图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 年龄数据分布公式表年龄数据分布公式值所在单元格数据个数=COUNT(B2:B601)600O4最大值=MAX(B2:B601)67O5最小值=MIN(B2:B601)18O6平均值=AVERAGE(B2:B601)42.395O7标准差=STDEV.P(B2:B601)14.413O8全距=INT(O5-O6)49O9组数3O10组距=INT(O9/O10)16O11表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 数据分析工具直方图分析结果表图8-7 数据分析工具绘制的直方图2）PHSTAT软件操作步骤如下：图8-8 PHSTAT软件直方图路径注意，PHSTAT软件绘制直方图过程中，作图区的数据不能包含公式。选中age属性（B2:B601）,分组数据属性（V14:V16），组中值属性（W14:16）绘制直方图。图8-9 PHSTAT软件“直方图”对话框分析结果如下：表8-7 PHSTAT软件直方图分析结果表图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）给出自己的分析观点，例如：从人数看，从收入上看，从购买理财计划的年龄结构看，。初步对数据和直方图观测，是黄金客户，而可能是潜在客户群。3. 数据清洗建立“3-数据插补.xlsx”文件，导入“0-bank_data.xlsx”文件，请你用EXCEL对异常值和缺失值进行插补。【要求】（1）请对income属性的离群点，用均值法替换修正。（2）请将current_act属性的空值，用众数法替换补值。【提示】（1）income属性的离群点（异常值）根据实验8-1的第一小题实验可知income属性的离群点（异常值），可以用公式计算去除异常值后的均值，并用均值替换异常点的income属性。如表8-9所示。设置O4单元格为，P5单元格为whisker上限。表8-9 离群点统计表离群点统计公式值样本总数=COUNT(E2:E601)600样本income总和=SUM(E2:E601)16535521异常点数量=COUNTIF(E2:E601,O4&P4)+COUNTIF(E2:E601,O5&P5)2异常点income总和=SUMIF(E2:E601,O4&P4)+SUMIF(E2:E601,O5&P5)143641.5均值=（样本总和-异常点income总和）/（样本总数-异常点数量）27411.17（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-1113所示。图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，如图1517所示。