让人欢喜让人愁

python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗

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


熟悉而陌生的字符提取

python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗

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


python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗_03

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


python进行文本文件清洗去掉用户手机号和隐私_数据清洗_04

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


python进行文本文件清洗去掉用户手机号和隐私_自定义_05

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


超越Excel的世界尽头

python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗

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


python进行文本文件清洗去掉用户手机号和隐私_数据_07

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


python进行文本文件清洗去掉用户手机号和隐私_数据清洗_08

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


python进行文本文件清洗去掉用户手机号和隐私_数据_09

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


python进行文本文件清洗去掉用户手机号和隐私_数据_10

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


python进行文本文件清洗去掉用户手机号和隐私_数据清洗_11

英文数字提取

python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗_12

为了想要提取产品型号中前面的英文字符和数字组成的产品名称。例如ZX032一级磁吸静音门(19年大促)的产品名称为ZX032;JO018X一级磁吸静音门(19年大促)的产品名称为JO018X;大致我们可以总结规律为提取文本中的前面的英文和数字。但是在这里面有特殊的存在,例如:JZ004(新)普通单开门,它的产品名称是

JZ004(新)。还有个百叶普通单开门,它的产品名称是 百叶门。所以我们就不能单纯的只提取文本的前面的英文字符和数字。那么这个我们该如何处理呢?在提取字符之前,我们先要对原始数据进行空格、空值等处理。 删除空值


python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗_13


空格替换


python进行文本文件清洗去掉用户手机号和隐私_数据_14


中文括号替换为英文括号


python进行文本文件清洗去掉用户手机号和隐私_数据_15

python进行文本文件清洗去掉用户手机号和隐私_数据清洗_16


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


python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗_17

我们在添加的自定义列的窗口里输入我们提取字符的函数。首先,我们先单独把百叶门和

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十个数字。

python进行文本文件清洗去掉用户手机号和隐私_数据_18


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 不及格   官方介绍如下

python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗_19


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

python进行文本文件清洗去掉用户手机号和隐私_数据_20

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


python进行文本文件清洗去掉用户手机号和隐私_数据_21

汉字提取

python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗_12

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

龟”了。所以我们可以使用"一".."龟"来表示我们的2万多个中文汉字。

python进行文本文件清洗去掉用户手机号和隐私_数据_23

python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗_24

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


python进行文本文件清洗去掉用户手机号和隐私_数据_25

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


python进行文本文件清洗去掉用户手机号和隐私_excel数据清洗_26

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


python进行文本文件清洗去掉用户手机号和隐私_pivot行转列 汉字字符列_27


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