任务二　数据的采集、清洗与抽样

任务概述

要进行数据分析，离不开有效数据的支持。首先通过数据采集获得原始数据，然后对原始数据进行整合与处理，再根据实际需要将数据集合，才能挖掘原始数据中潜在的重要商业信息，从而掌握真正的核心数据。因此，在进行数据分析之前，首先需要进行数据的采集、清洗和随机抽样等工作，本任务将分别对其进行详细介绍。

任务重点与实施

一、数据的采集

数据采集主要针对定性数据和定量数据。定性数据主要采用问卷调研和用户访谈的方式来获取，而定量数据分为内部数据和外部数据两部分。

其中，内部数据可以通过网站日志、业务数据库来获取；外部数据则主要采用网络爬虫抓取的方式来获得，通过编写脚本语言，按照一定的规则来获取网络上的相关信息。当然，外部数据的采集也可以借助第三方统计平台来实现，从某种意义上来说，通过第三方统计平台可以降低中小企业数据采集的门槛。图1-17所示为数据采集方法。

图1-17　数据采集方法

1.问卷调研

一般在进行用户画像调研时，都会用到问卷调研工具。在进行问卷调研时，首先需要考虑样本的容量，其次是内容的设计，并按照“调研目标→设计问卷→投放问卷→问卷收集汇总→调研结果分析”的步骤展开调研。

2.用户访谈

在访谈之前，首先要确定访谈目标，然后设计访谈提纲，选择访谈对象，并对访谈及相应情况进行记录，最后针对访谈结果进行分析。在具体分析访谈结果时，可以采用关键词提炼法，针对每个用户对每个问题的回答进行关键词的提炼，并按照关键词的频次进行排序，对共性词进行汇总。

3.网站日志

通过网站日志对用户数据及相应行为进行分析，其优点是保证用户的使用行为可以查询，如用户访问数、页面浏览数、停留时长等点击流数据均可通过网站日志获得，同时针对用户的一些误操作还可以通过日志文件进行恢复。在实际运用中，由于从打印日志到处理日志，再到输出统计结果，整个过程很容易出错，所以经常出现业务运行和统计分析两个数据流相分离的情况。

4.业务数据库

互联网产品后端都有业务数据库，其中存储了销售量、订单量、购买用户数等指标数据。一般通过业务数据库获得的数据都是实时的、准确的，可以直接用于衡量网站的绩效和目标。

5.第三方统计平台

对于电商企业来说，一般可以将其用户行为分成四类。

（1）纯粹的浏览行为。

（2）收藏、下载等轻度交互行为。

（3）注册、登录等重度交互行为。

（4）添加购物车、填写地址、下单等与交易相关的行为。

对于第三方统计平台，又可分为无埋点、有埋点和“有埋点+无埋点”三种情况，如表1-1所示。

表1-1　第三方统计平台

二、数据的清洗

通过数据分析获得的结果，不仅依赖于算法，同时还依赖于数据的质量。正所谓“磨刀不误砍柴工”，好数据胜过复杂的模型，所以在进行数据分析之前对采集到的数据进行清洗尤为重要。

数据清洗是指发现并纠正数据文件中可识别错误的最后一道程序，是对数据的完整性、一致性和准确性进行重新审查和校验的过程。一般不符合要求的数据主要有缺失数据、重复数据、错误数据三类，也就是所谓的“脏数据”。而数据清洗的任务就是按照一定的规则将这些“脏数据”洗掉，目的在于删除重复的数据信息，纠正数据中存在的错误，提供数据的一致性。

下面围绕数据清洗这个目的，从缺失数据、重复数据和错误数据三个方面出发，通过运用一些简单的统计学检验和Excel表格工具发现“脏数据”，并对其进行清洗。

1.缺失数据的清洗

在数据采集过程中，缺失值常表示为空值或错误标识符（#DIV/0！）。为了保证数据的完整性，用户可以运用一些统计学方法查找缺失数据并对其进行处理。在查找缺失数据时，利用【Ctrl+G】组合键打开Excel的定位功能，选择其中的错误单元格或空值单元格，就可以进一步查找数据表中的错误值和空白值。

对缺失值的处理一般有以下四种方法。

（1）用一个样本统计量的值代替缺失值。

（2）用一个统计模型计算出来的值代替缺失值。

（3）直接将有缺失值的记录删除。

（4）将有缺失值的记录保留。

下面以采用一个样本统计量的值代替缺失值的方法为例来进行说明，具体操作方法如下。

Step 01　按【Ctrl+A】组合键全选数据表单元格区域，按【Ctrl+G】组合键，打开“定位”对话框，单击“定位条件”按钮，如图1-18所示。

图1-18　“定位”对话框

Step 02　弹出“定位条件”对话框，选中“空值”单选按钮，然后单击“确定”按钮，如图1-19所示。

Step 03　此时即可自动选中表格中的空单元格，如图1-20所示。

图1-19　“定位条件”对话框

图1-20　定位空单元格

在实际操作中，如果样本较大，缺失值较多，可以定位样本中的所有空值，然后利用【Ctrl+Enter】组合键在选中的空值单元格中一次性输入样本的平均值。当缺失数据较少时，也可以通过选取数据前后若干天的数据取平均值作为缺失数据进行填充。

图1-20中显示的是9月下旬网站的销售情况，其中人均消费额为总销售额除以购买人数。由于9月27日的总销售额缺失，导致相应的人均消费额无法计算，考虑到每天的人均消费额保持相对恒定，可以使用其他日期人均消费额的平均值36.80元来进行填充，进而计算得到该日的总销售额为55421元；也可以简单地使用27日前后两天总销售额取平均值进行计算，得到27日的总销售额为45251元，进而计算出人均消费额为30.05元。

2.重复数据的清洗

重复数据一般可以分为以下两类。

·实体重复：即所有字段完全重复。

·字段重复：某一个或多个不该重复的字段重复了，如“姓名”字段重复。

（1）查找重复数据

为了保证数据的一致性，需要对重复数据进行处理。对于重复数据的查找，一般采用以下四种方法。

①条件格式法

利用Excel条件格式中的突出显示重复值功能，可以将重复的数据及所在单元格标为不同的颜色进行识别，具体操作方法如下。

Step 01　选中A列数据，在“开始”选项卡下“样式”组中单击“条件格式”下拉按钮，选择“突出显示单元格规则”|“重复值”选项，如图1-21所示。

Step 02　弹出“重复值”对话框，在“设置为”下拉列表框中选择一种格式，如“浅红填充色深红色文本”，然后单击“确定”按钮，即可标记所有重复的会员编号，如图1-22所示。

图1-21　选择“重复值”选项

图1-22　设置突出显示重复值

②高级筛选法

利用Excel的“高级筛选”功能可以快速筛选出不重复的记录，具体操作方法如下。

Step 01　选择任一数据单元格，在“数据”选项卡下“排序和筛选”组中单击“高级”按钮，弹出“高级筛选”对话框，程序会自动选中所有的数据区域，选中“选择不重复的记录”复选框，然后单击“确定”按钮，如图1-23所示。

Step 02　此时即可筛选出所有不重复的记录，且重复记录被自动隐藏，如图1-24所示。

图1-23　设置高级筛选

图1-24　查看筛选结果

③函数法

COUNTIF函数可以对指定区域中满足某个指定条件的单元格进行计数。下面利用该函数对会员编号的出现次数进行计数，以此对重复数据进行识别。具体操作方法为：在“会员编号”列右侧插入列，在B2单元格中输入公式“=COUNTIF($A$2:A2,A2)”，然后利用填充柄向下填充公式，即可得出相应的编号第几次出现，如图1-25所示。

图1-25　标记重复次数

④数据透视表法

拖动相应的字段，利用数据透视表也可以统计数据出现的频次，其中出现两次及两次以上的数据属于重复项，具体操作方法如下。

Step 01　选择“插入”选项卡，在“表格”组中单击“数据透视表”按钮，如图1-26所示。

Step 02　弹出“创建数据透视表”对话框，选中“新工作表”单选按钮，然后单击“确定”按钮，如图1-27所示。

图1-26　单击“数据透视表”按钮

图1-27　“创建数据透视表”对话框

Step 03　此时，即可创建一个空的数据透视表。在“数据透视表字段列表”窗格中将“会员编号”字段分别拖入“行标签”和“数值”区域，即可对会员编号进行计数，大于1的即为重复数据，如图1-28所示。

Step 04　对“计数项：会员编号”列进行降序排序，查看重复的会员编号，如图1-29所示。

图1-28　添加报表字段

图1-29　排序计数项

（2）删除重复数据

在完成重复数据的查找后，接下来便是删除重复数据。删除重复数据主要有以下三种方法。

①通过删除重复项功能删除重复数据

Excel提供了“删除重复项”功能，可以快速删除重复数据，具体操作方法如下。

Step 01　选择“数据”选项卡，在“数据工具”组中单击“删除重复项”按钮，在弹出的“删除重复项”对话框中选中包含重复项的列，然后单击“确定”按钮，如图1-30所示。

Step 02　弹出提示信息框，显示有多少个重复值被删除，有多少个唯一值被保留，如图1-31所示。

图1-30　“删除重复项”对话框

图1-31　重复项删除完成

②通过排序删除重复数据

在利用条件格式对重复数据进行识别的基础上，可以对重复项标记列进行排序，进而删除重复项，具体操作方法如下。

Step 01　采用前面介绍的方法，利用条件格式突出显示重复项。选择“数据”选项卡，在“排序和筛选”组中单击“筛选”按钮，即可在各字段显示筛选按钮，如图1-32所示。

Step 02　单击“会员编号”筛选按钮，选择“按颜色排序”选项，在其子菜单中选择单元格填充颜色，如图1-33所示。

图1-32　单击“筛选”按钮

图1-33　设置按颜色排序

Step 03　此时即可将所有重复项显示在最上方，直接删除不需要的记录即可，如图1-34所示。

图1-34　查看排序结果

③通过筛选删除重复数据

在利用COUNTIF函数对重复数据进行识别的基础上，可以对重复项标记列进行筛选，筛选出“数值不等于1”的项并进行清除，具体操作方法如下。

Step 01　单击“数据标记”筛选按钮，选择“数字筛选”|“不等于”选项，也可在下方数字列表中取消选中“1”复选框，然后单击“确定”按钮，如图1-35所示。

Step 02　在弹出的“自定义自动筛选方式”对话框中将筛选条件设置为“不等于1”，然后单击“确定”按钮，如图1-36所示。

图1-35　选择“不等于”选项

图1-36　“自定义自动筛选方式”对话框

Step 03　此时即可筛选出所有重复的数据，拖动鼠标选中数据单元格区域，如图1-37所示。

图1-37　查看筛选结果

Step 04　按【Alt+;】组合键，选中可见单元格。鼠标右键单击选中的单元格，在弹出的快捷菜单中选择“删除行”命令，即可删除重复数据，如图1-38所示。单击“筛选”按钮，取消筛选状态。

图1-38　删除重复数据

3.错误数据的清洗

除了缺失数据和重复数据外，其他可能出现数据不规范的情况还有很多，如存在错误数据等。为了保证数据的准确性，需要对错误数据进行处理。

一般情况下，错误数据经常表现为以下两种情况。

（1）输入的信息不符合要求

例如，在进行问卷调查时，涉及多项选择题最多可选择三项，而答题者却选择了四项或四项以上。对于这种情况，可以结合COUNTIF函数，根据指定的条件利用IF函数判断其“真”（true）、“假”（false），然后根据逻辑计算的真假值返回相应的内容。

下面以客户满意度调查中的多项选择题为例，判断不符合要求的输入信息。

针对“题目1”共设置了A、B、C、D、E、F、G七个选项，围绕这七个选项共有八条记录，这八条记录分别代表了8个不同客户对“题目1”的回答，而且第二条和第五条记录中有超过三项的记录。在I2单元格中输入公式“=IF(COUNTIF(B2:H2,"<>0")>3,"错误","")”，并将公式填充到下方的单元格中，判断被调查者是否选择了三项以上。如果超过三项，则返回“错误”，如图1-39所示。

图1-39　检查不符合要求的记录

（2）手工输入错误

在使用0和1输入多选题信息时，若出现了0和1之外的数据，可以采用条件格式利用OR函数标记错误。若出现了10和11这样的数字，明显是错误的。下面使用条件格式标记出错误的数据，具体操作方法如下。

Step 01　选择B2:H9单元格区域，在“开始”选项卡下“样式”组中单击“条件格式”下拉按钮，选择“突出显示单元格规则”|“其他规则”选项，如图1-40所示。

图1-40　选择“其他规则”选项

Step 02　弹出“新建格式规则”对话框，选择“使用公式确定要设置格式的单元格”选项，在下方输入公式“=OR(B2=1,B2=0)=FALSE”，单击“格式”按钮，将单元格格式设置为红底白字，然后单击“确定”按钮，如图1-41所示。

Step 03　此时即可将输入错误的单元格标记出来，如图1-42所示。

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

图1-42　标记输入错误的数据

三、数据的随机抽样

数据抽样是指从数据样本中按照随机原则选取一部分对象作为样本进行分析，并以此推论总体状况的一种分析方法。在数据抽样中，常用的是RAND函数和“抽样”分析工具。

1.使用RAND函数随机抽样

RAND函数能够返回大于等于0及小于1的均匀分布随机数，每次计算工作表时都将返回一个新的随机数。若要随机抽取0~100之间的数值，只需将随机数公式更改为“=RAND()*100”即可；若要计算a~b之间的随机数，则需要将公式更改为“=RAND()*(b-a)+a”。

下面在50个店铺会员中随机抽取10人发放奖励金，具体操作方法如下。

Step 01　鼠标右键单击“会员编号”所在的A列，在弹出的快捷菜单中选择“插入”命令，如图1-43所示。

Step 02　在“会员编号”列左侧插入新列，为50个会员编号填充1~50的序号，如图1-44所示。

图1-43　插入新列

图1-44　填充序号

Step 03　在D2单元格中输入公式“=INT(1+RAND()*50)”，然后使用填充柄将公式复制到下方的D3:D11单元格区域，其中INT函数用于取随机数的整数部分，如图1-45所示。

Step 04　在E2单元格中输入公式“=VLOOKUP(D2,$A$1:$B$51,2)”，利用VLOOKUP函数生成随机数所对应的会员编号，此时可以看到D列中的随机数将重新计算，如图1-46所示。

图1-45　计算随机序号

图1-46　计算对应会员编号

Step 05　通过单击行号或列标之间的分割线，也可自动重算随机数。要将随机结果进行固定，可选择D2:D11单元格区域，按【Ctrl+C】组合键进行复制，然后单击“粘贴”下拉按钮，选择“值”选项，如图1-47所示。

Step 06　除了利用辅助序号列计算随机数外，还可以使用INDIRECT函数与RANDBETWEEN函数实现随机抽取。不必插入“序号”列，直接在C2单元格中输入公式“=INDIRECT("A"&RANDBETWEEN(2,51))”，然后利用填充柄向下复制公式即可，如图1-48所示。

图1-47　粘贴为值

图1-48　直接抽取随机编号

2.使用“抽样”分析工具随机抽样

除了使用RAND函数计算随机数外，还可以使用Excel加载项中的“抽样”工具随机抽样，具体操作方法如下。

Step 01　打开“Excel选项”对话框，在左侧区域中选择“自定义功能区”选项，在右侧区域中选中“开发工具”选项卡，然后单击“确定”按钮，如图1-49所示。

Step 02　选择“开发工具”选项卡，在“加载项”组中单击“加载项”按钮，如图1-50所示。

图1-49　“Excel选项”对话框

图1-50　单击“加载项”按钮

Step 03　弹出“加载宏”对话框，选中“分析工具库”复选框，然后单击“确定”按钮，如图1-51所示。

Step 04　新建工作表，在A1:J10单元格区域中输入员工编号，并选中该单元格区域，如图1-52所示。

图1-51　“加载宏”对话

图1-52　输入员工编号

Step 05　按【Ctrl+1】组合键，打开“设置单元格格式”对话框，在“分类”列表框中选择“自定义”分类，在右侧区域中输入类型代码，然后单击“确定”按钮。由于“抽样”工具无法应用于文本数据，所以不能直接输入编号，需要对其自定义数字格式，如图1-53所示。

图1-53　“设置单元格格式”对话框

Step 06　此时，即可自定义会员编号的显示格式。选择“数据”选项卡，在“分析”组中单击“数据分析”按钮，在弹出的“数据分析”对话框中选择“抽样”选项，然后单击“确定”按钮，如图1-54所示。

图1-54　“数据分析”对话框

Step 07　弹出“抽样”对话框，设置输入区域、样本数及输出区域，然后单击“确定”按钮，如图1-55所示。

图1-55　设置“抽样”对话框

Step 08　此时即可从100个编号中随机抽取10个作为样本，如图1-56所示。

图1-56　编号随机抽样结果