数据采集与处理 课件 任务2 认知数据清洗

项目三数据预处理认知任务2认知数据清洗一、数据清洗概述数据清洗是指对数据进行重新审查和校验的过程中，发现并纠正数据文件中可识别的错误，按照一定的规则把错误或冲突的数据洗掉，包括检查数据一致性，处理无效值、缺失值和冗余值等，数据清理一般是由计算机而不是人工完成。二、数据清洗的方法缺失数据一般在数据表中表现为空白单元格或错误标识符（#div/0!）。检测方法：在Excel软件中可单击“开始”选项卡的“编辑”功能区，通过“定位”→“定位条件”→“空值”→“确定”（或者使用【Ctrl+G】组合键），将缺失数据一次性选定。（一）缺失数据的清洗符号产生原因解决方法#DIV/0!①公式中的除数使用了指向空白单元格或包含零值的单元格的引用②输入的公式中包含明显的除数零①修改单元格引用，或在用作除数的单元格中输入不为零的值②将零改为非零值二、数据清洗的方法（一）缺失数据的清洗处理方法：①忽略缺失值：将有缺失数据的记录保留，仅在相应的分析中做必要的排除。当调查的样本量比较大，缺失数据的数量又不是很多，而且变量之间也不存在高度相关的情况下，采用这种方式处理缺失数据比较可行。②删除缺失值：将有缺失数据的记录删除，删除记录会导致样本量减少，所以此方法不适于小样本量的数据集。③填充缺失值：如果可以推断出正确数值，则填充为逻辑正确值如果无法推断出正确数值，则可以填充“未知”，或者用样本统计量代替缺失数据，最典型的做法是使用变量的平均值替代。替代后由于该变量的平均值会保持不变，因此其他的统计量（如标准差和相关系数等）也不会受很大的影响。二、数据清洗的方法（一）缺失数据的清洗练习题：整理某店铺9月的销售数据，其中9月25日的支付金额和客单价数据缺失。考虑到每天的客单价相对稳定，因此可以用平均客单价代替缺失数据。步骤1：选中D列，在Excel窗口下方的状态栏中可以看到平均值为152.7。

步骤2：在D7单元格中输入152.7，然后选择B7单元格，在编辑栏中输入公式"=C7*D7"按【Enter】键确认，得出支付金额数据。二、数据清洗的方法二、数据清洗的方法（二）重复数据的清洗为了保证数据的一致性，我们需要对重复数据进行处理。在完成重复数据的查找后，即可删除重复数据。实体重复字段重复所有字段完全重复某一个或多个不该重复的字段重复，如Name字段重复重复数据

的类型二、数据清洗的方法（二）重复数据的清洗选择“开始”｜“条件格式”｜“突出显示单元格规则”｜“重复值”命令把重复的数据及其所在单元格标为不同颜色以便识别条件格式法重复数据的查找高级筛选法函数法数据透视表法利用Excel的“高级筛选”功能，选择不重复的记录选择不重复的记录该函数的语法规则：COUNTIF(range,criteria)range：计算其中非空单元格数目的区域criteria：以数字、表达式或文本形式定义的条件利用COUNTIF函数对重复数据进行识别拖动选择相应字段，设置“计数”汇总，其中出现两次及以上的数据就属于重复项统计各数据出现的频次二、数据清洗的方法（二）重复数据的清洗通过按钮删除重复项通过排序删除重复项通过筛选删除重复项单击“数据”选项卡下的“删除重复项”按钮，将显示有多少重复值被删除，有多少唯一值被保留在利用COUNTIF函数对重复数据进行识别的基础上，对有重复标记的列进行降序排列，删除数值大于1的项在利用COUNTIF函数对重复数据进行识别的基础上，对有重复标记的列进行筛选，筛选出数值不等于1的项删除重复数据的方法二、数据清洗的方法（二）重复数据的清洗练习题：利用第三方采集工具从京东商城采集关于玻璃杯的商品数据，其中包含了多条重复的数据，请用不同方法对重复数据进行识别，并删除重复数据。二、数据清洗的方法（二）重复数据的清洗练习题：COUNTIF函数法：1.在“京东价”左侧插入一列，标题设为“第几次出现”2.在C2单元格输入公式“=COUNTIF($B$2:B2,B2)”,该公式用于对“商品ID”进行计数3.筛选出大于1的数据，即为重复数据（也可排序找出重复数据，但顺序会乱）二、数据清洗的方法实操练习：在采集某商品的库存数据时，由于部分SKU（商品最小存货单位）的期初库存和期末库存数据无法采集，导致对应的库存周转率结果错误，同时考虑到这些数据无法手动修改。因此，这里仅需要对表格中的错误信息进行处理，即对于数据不完整的SKU，其库存周转率用“/”号来表示，最后再对整个表格的数据进行去重检查与处理。1选择单元格区域、设置公式2修改错误、删除重复数据二、数据清洗的方法3扩展选定区域、设置检查项4提示已经删除了重复数据专家点拨：

IFERROR

函数的语法格式为"

IFERROR

(

Value

,

Value

_

If

_

Error

)"，该函数可用于捕获和处理公式中的错误。如果公式的计算结果为错误值，则

IFERROR

函数将返回参数"

Value

_

If

_

Error

"所设置的值，否则将返回参数"

Value

"的值。二、数据清洗的方法二、数据清洗的方法（三）错误数据的清洗错误原因查找方法清洗方法手工录入错误条件格式标注错误值错误数据清洗输入不符合要求其他原因COUNTIF函数纠正错误二、数据清洗的方法1.逻辑错误数据的清洗练习题:(1)被调查者输入的信息不符合要求在进行问卷调查时，多项选择题最多可选3项，但被调查者选择了4项或4项以上。对于这种情况，我们可以结合COUNTIF函数，根据指定的条件利用IF函数来判断其真假，然后根据逻辑计算的真假值（TRUE或FALSE）返回相应的内容。以客户满意度调查中的多项选择题为例，介绍如何找出不符合要求的数据。二、数据清洗的方法1.逻辑错误数据的清洗练习题：方法1-使用COUNTIF函数完成检验步骤1：针对"题目1"设置了

A

、

B

、

C

、

D

、

E

、

F

、

G

共7个选项，这7个选项共有4条相关记录，这4条记录分别代表了4个客户对"题目1"的回答，可以看出"客户2"选择了4项，不符合最多可选3项的要求。二、数据清洗的方法1.逻辑错误数据的清洗练习题：方法1-使用COUNTIF函数完成检验步骤2：选择I3单元格，在编辑栏中输入公式=COUNTIF(B3:H3,“<>0),该公式用于判断被调查者是否选择了3项以上。步骤3：选中检验列，“条件格式”-“突出显示单元格规则”-“大于”-3，对所有大于3的单元格突出显示二、数据清洗的方法1.逻辑错误数据的清洗练习题：方法2-使用COUNTIF和IF函数完成检验步骤1：针对"题目1"设置了

A

、

B

、

C

、

D

、

E

、

F

、

G

共7个选项，这7个选项共有4条相关记录，这4条记录分别代表了4个客户对"题目1"的回答，可以看出"客户2"选择了4项，不符合最多可选3项的要求。二、数据清洗的方法1.逻辑错误数据的清洗练习题：方法2-使用COUNTIF和IF函数完成检验步骤2：选择J3单元格，在编辑栏中输入公式"=IF(COUNTIF(B3:H3,“<>0”)>3,"错误","正确")",该公式用于判断被调查者是否选择了3项以上。如果超过3项，表达式即为TRUE，返回"错误"；否则为

FALSE

，返回"正确"。二、数据清洗的方法1.逻辑错误数据的清洗练习题:(2)手工录入错误在手动录入的调查问卷中，出现了0和1之外的数据。对于这种情况，我们可以采用条件格式，利用OR函数标记错误。该函数的语法规则为：OR(logicall,logical2,…)。在其参数组中，任何一个参数逻辑值为TRUE即返回TRUE；所有参数的逻辑值为FALSE才返回FALSE。二、数据清洗的方法1.逻辑错误数据的清洗练习题:(2)手工录入错误步骤1：在手动录入调查问卷时，录入除0和1之外的数据，从表中可以看出，单元格中出现了3、2、3这样的数字。步骤2：选中B3:H6单元格区域，在“开始”选项卡下单击“条件格式”下拉按钮，在弹出的下拉列表中选择“新建规则”选项，弹出“新建格式规则”对话框，选择“使用公式确定要设置格式的单元格”选项，然后输入公式“=OR(B3=1,B3=0)=FALSE“，表示既不为1也不为0的单元格会被标记出来。步骤3：单击“格式”按钮，在弹出的“设置单元格格式”对话框中选择“填充”选项卡，此时错误数据被自动标记出来。二、数据清洗的方法二、数据清洗的方法2.格式错误数据的清洗数据格式问题主要有以下几类：（一）时间、日期、数值、半全角等显示格式不一致。直接将数据转换为一类格式即可，该问题一般出现在多个数据源整合的情况下。（二）内容中有不该存在的字符。如在身高字段下有些变量带单位，有些没有，这时去除不需要的字符即可。（三）数据格式不满足要求。在不同的计算方式下对数据格式要求不同，可根据分析要求进行格式调整。二、数据清洗的方法练习：某企业采集了竞争对手的相关数据，发现一级类目下的数据表达格式未统一，且客单价与销售额的格式也不相同，需要进行整理，其具体操作如下。1输入查找与替换的内容2确认替换一级类目统一替换为：女装/女士精品二、数据清洗的方法4选择多个单元格区域5设置数据类型3查找与替换其他数据二、数据清洗的方法（四）异常数据的清洗检测方法：（1）定义法：检测数据与平均值的偏差，与检测数据两倍标准差比较大小（2）箱形图：由数据的上边缘、上四分位数、中位数、下四分位数和下边缘组成的图形，其中上边缘和下边缘线所代表的就是临界值，超过上下边界的离群点则为需要关注的异常值。操作要求：在excel表格中检测“消费次数”的异常值。运用函数：AVERAGE（平均值函数）、ABS（绝对值函数）、STDEV.S（标准差函数）二、数据清洗的方法（四）异常数据的清洗处理方法：①参考后续的数据分析模型，选择删除或者保留异常值。②用一个样本统计量去代替异常值，比如平均值、中位数、众数等。③分箱法，即通过考察相邻数据的取值对异常值进行平滑处理，可视为一种局部平滑方法。首先将异常值所在指标下的所有数据按照大小排序，并适当分组（也称作分“箱”），然后用组内数据的平均值、中位数或边界值来代替异常值。分组时，如果每个“箱”的数据个数相同就为等深分箱；如果每个“箱”内数据值的区间范围是一个常量就为等宽分箱。三、数据加工数据加工是指对数据进行抽取、转换、计算等操作，使经过加工后的数据成为简洁、规范、清晰的样本数据。一般情况下，当数据经过清洗之后仍然不能满足用户的分析需求时，就需要利用数据加工对数据进行进一步的处理，形成分析所需要的一系列新的字段。数据加工环节直接影响数据质量的价值性、可用性和准确性。因此在进行数据加工时，要根据数据特点合理选择数据加工方法，以实现数据质量的最优化。三、数据加工（一）数据抽取数据抽取就是对数据库中现有字段进行整合加工，使能够形成分析所需要的数据。1.从某个字段中抽取部分数据字段拆分就是为了截取某一字段中的部分信息，将该字段拆分成两个或多个字段。三、数据加工（一）数据抽取Excel的“快速填充”功能一般来说，没有规律的数据都需要手动输入，但对于一些相同或有规律的数据，如员工编号、部门名称等，则可通过填充的方式快速输入。三、数据加工三、数据加工（一）数据抽取2.从不同的数据表中抽取多个字段VLOOKUP函数打开素材文件“2数据抽取-素材”，其中包括两个表，第一个表为近期店铺方可排名前10的商品表，包括商品标题、商品ID、商品货号等属性，第二个表为店铺商品信息表，记录了店铺中所有商品的信息，运营者需要为这些商品添加相应的商品货号。三、数据加工三、数据加工（一）数据抽取练习：字段拆分打开素材文件“3数据抽取-素材”，已知某公司实习员工的信息表，将员工的出生年月日字段拆分成年、月、日三个字段；三、数据加工（一）数据抽取练习：字段拆分分隔符号根据实际情况选择或输入三、数据加工（一）数据抽取练习：字段拆分请大家尝试用YEAR、MONTH、DAY、MID、LEFT、RIGHT等函数抽取信息；用VLOOKUP函数将考核得分抽取到基本信息表中。三、数据加工（一）数据抽取3.字段合并字段合并就是将若干字段合并成为一个新的字段，或者将字段值与文字、数字等组合形成新的字段。字段合并可以利用CONCATENATE函数或者“&”（逻辑与）运算符。三、数据加工（一）数据抽取3.字段合并请将基本信息表中员工的工号和姓名字段合并，合并成“**的工号是**”，例如“周慧的工号是1801201”。（1）利用&运算符合并三、数据加工（一）数据抽取3.字段合并（2）利用CONCATENATE函数合并三、数据加工（二）数据转换由于不同来源的数据可能存在不同的结构，数据转化主要是将数据转换成规范、清晰、易于分析的结构。数据转换包括数据行列转换、数据类型转换、数据排序和数据分组。三、数据加工1.数据行列转换在进行数据报表分析时，常常要从不同的维度观察数据。例如，从时间的维度查看汇总数据，从地区的维度观察汇总数据，这样需要对行列数据进行转换（又称转置）。

例如，下图的数据转换，可以将原数据复制，然后利用"选择性粘贴"对话框中的"转置"选项完成这个操作。三、数据加工2.数据类型转换（1）数值转字符在Excel中输入数据的时候，会默认使用数值型数据；若是数字太长，会变成用科学记数法表示的数，不利于查看数据。我们可以利用“数据”选项卡中的“分列”功能进行转换。步骤：1）选择要转换的数字所在的单元格，随后单击“分列”按钮，在“文本分列向导”对话框中，使用默认设置，连续单击“下一步”按钮；2）进入“文本分列向导”第3步，选中“列数据格式”栏中的“文本”单选项，单击“完成”按钮即可完成设置；3）设置完成后，返回Excel数据表，数据前有小三角符号，代表已转换成功。三、数据加工2.数据类型转换（2）字符转数字在进行数据统计时，有时获取的原始数据是以文本字符形式展现的，虽然这不影响数据展现，但无法进行计算。直接选中要转换的数据列，单击数据列前出现的提醒符号，在给出的选项中选择“转换为数字”选项，即可将文本字符转换为数值。三、数据加工（三）数据排序数据排序是指将数据按照方便我们处理分析的顺序进行有规则的排列，如按照分数降序排序、按照日期升序排序等。（1）添加条件将数据按照“访客数”和“下单买家数”两个字段进行排序，均为降序排序。（2）自定义排序将数据根据流量来源进行排序。1）选择“文件”菜单中的“选项”命令，弹出“Excel选项”对话框，切换到“高级”选项卡，单击“编辑自定义列表”按钮；2）在弹出的“自定义序列”对话框中输入序列（即按照我们希望该字段排列的顺序输入），单击“添加”按钮，将其添加到自定义序列中，单击“确定”按钮完成添加三、数据加工三、数据加工（四）数据分组数据分组是根据统计研究需要，将原始数据按照某种标准划分成不同的组别，分组后的数据称为分组数据。在Excel中我们可以通过VLOOKUP函数快速实现数据分组。我们想将员工的“考核得分”按照分组要求显示考核等级。三、数据加工（四）数据分组（1）在数据表中的