excel数据清洗_170万行数据文本处理之字符提取

让人欢喜让人愁

拿到数据在进行分析之前，不可避免的需要先对数据进行清洗，让数据变得适合我们进行分析。因为我们到手的数据绝大多数情况下是不符合我们分析要求。数据清洗的工作需要干很多活，而且在整个数据分析的过程中，数据清洗的时间也占比很高，所以要想让自己的数据分析工作变得更加高效、快速，那么必须要在数据清洗环节下功夫，使用更高效的工具，更便捷的方法来清洗数据，以减少在清洗环节耗费的时间。数据清洗的工作内容包括不限于，比如过滤行列，转换行列，重复值处理，缺失值处理，异常值处理，字符提取，字符替换，维度补充，数据类型转换等等。经过一系列清洗动作之后，我们就可以用这些“干净”的数据进行分析，从而从数据中获取有价值的信息。今天主要说的清洗内容就是文本处理过程中的字符提取。

熟悉而陌生的字符提取

说到字符提取，我们首先想到的可能就是excel里的left、right、mid三兄弟了。如果要处理的文本字符相对规整有规律，我们可以使用它们来完成。但如果我们要处理的文本字符没啥很直白的规律，那就要好好琢磨一番了，可能有人还会用excel的各种函数进行组合来完成这个工作。不过其实在excel2016及以上版本里，有一个非常好用的功能——智能填充(Ctrl+E)，可以帮助我们快速完成字符的提取。

智能填充面对相对复杂的字符提取也能比较优雅的完成任务。

基本这个智能填充已经可以帮助我们完成很多字符提取的工作内容了。比如上的gif的场景。姓名手机号里提取姓名和手机号QQ邮箱账号提取QQ邮箱身份证号提取出生年月……当然，如果我们要提取的字符有规律的分隔符的话，那么最简单的就是分列了。其实分列还可以帮助我们把文本数字转换成数字，把数字转换成文本数字。相信这个转换很多朋友都遇到过。(文本左对齐，数值右对齐哟！)

在excel中能不用一堆公式函数来完成的工作，就不要写那么长的函数公式。大道至简……

超越Excel的世界尽头

上面我们讲的内容都是在excel里操作的，所以就有个前提，数据必须能放进excel里。但，excel空间是有限的，如下图所示总共的数据容量就是不到105万行。而且数据量大了，我们再用公式等操作，会直接卡到你怀疑人生。

要想超越Excel世界的尽头，我们就需要使用Power Query来搞定它。例如我们使用的这个csv的数据，用python的pandas包调取一下数据总量，共计171万行数据。

打开Excel，点击数据菜单，选择新建查询，选择从文件，选择从csv，选择对应文件。然后就会出现文件预览界面

在文件预览界面，我们首先调整csv文件的中文编码，选择无，然后点击转换数据。

转换数据之后，我们就会来到power query的数据处理界面。

英文数字提取

为了想要提取产品型号中前面的英文字符和数字组成的产品名称。例如ZX032一级磁吸静音门(19年大促)的产品名称为ZX032；JO018X一级磁吸静音门(19年大促)的产品名称为JO018X；大致我们可以总结规律为提取文本中的前面的英文和数字。但是在这里面有特殊的存在，例如：JZ004(新)普通单开门，它的产品名称是 JZ004(新)。还有个百叶普通单开门，它的产品名称是 百叶门。所以我们就不能单纯的只提取文本的前面的英文字符和数字。那么这个我们该如何处理呢？在提取字符之前，我们先要对原始数据进行空格、空值等处理。 删除空值

空格替换

中文括号替换为英文括号

然后我们开始字符提取首先我添加一个自定义列，如图。

我们在添加的自定义列的窗口里输入我们提取字符的函数。首先，我们先单独把百叶门和 JZ004(新) 处理了。然后，我们观察产品名称的规律，因为产品名称都是从产品型号的左侧提取的，而且产品名称的字符最多的是8位，所以我们先将产品型号从左取8位字符，避免产品型号右侧的数字混淆。然后就是从这8位中提取英文字符和数字了。在这个过程中我们需要使用到的在power query中的字符提取函数有Text.Start：作用就是excel的leftText.Contains：作用类似excel的search，不过它返回的是true/falseText.Select：看下图官方介绍。它会提取参数2列出的字符。例子中的"a".."z"的意思就是abcdefg……xyz这26个字母。同样的，"A".."Z"代表从A到Z的26个字母。"0".."9"，代表从0到9十个数字。

if then else 结构。跟excel的if(逻辑判断，true时的值，false时的值 )一个意思。跟excel的if函数一样，if then else 结构也是可以嵌套的。 if   then  if  then  else  else if then else 例如。 if 成绩 >= 80 then if 成绩>=90 then 优秀 else 良好 else if 成绩>=60 then 及格 else 不及格   官方介绍如下

那么我们最后得到的函数就是如下图

输入完公式之后，点击确定我们就可以得到最终结果了。

汉字提取

我们的案例中提取的是前面的英文和数字，那么如果我们想提取后面的汉字呢？该怎么办？有两个思路。一、直接提取汉字。二、移除提取的英文和数字在power query中所有字符都是源于unicode编码，同种字符都是连续的，所以我们可以用"A".."Z"表示A-Z的26个大写字母，因为中间不会有其他夹杂。而对于汉字也同样，我们先来看看汉字的列表。汉字起始自“一”，终结于龥(yu四声)。但是看看最后几个生僻字，我们能用到的也就是“ 龟”了。所以我们可以使用"一".."龟"来表示我们的2万多个中文汉字。

我们先来用第一个方案处理下。我们添加自定义列，输入公式Text.Select([产品型号],{"一".."龟"})就可以得到如下结果了

但这个方案在实际执行时，它需要为每一个值去匹配“一”到“龟”这2万多个汉字，因为速度上肯定比较慢(其实也挺快，不过数据量几百万上千万时速度差异就明显了)。我们下面来尝试第二个方案。添加自定义列

我们依然可以得到想要的结果

Power Query——数据处理高效生产力！ 一切皆是信息，万物源自比特！

数字化必定会深刻革命我们的办公和生活！

简道云，中小企业数字化之路的绝佳伴侣！

本公众号将分享数字化的实践、学习、思考。也许涉及信息化系统设计、各种办公软件、数据分析、理论知识、实践案例…… 感谢你与我一同成长……

如果有关企业数字化的疑问、思考和讨论 或者 关于简道云的应用搭建、数据工厂、仪表盘等疑问咨询或者合作，欢迎与我联系。(关注公众号，可以找到我的联系方式)