如何整理数据？

大家好，欢迎来到IT知识分享网。

最近，我正在读《深入浅出数据分析》这本书。

上期跟大家分享的书中主题是「初涉关系数据库」。

今天，我来聊聊「整理数据」。

乱糟糟的数据毫无用处。

许多数据搜集者需要花大量时间整理数据。

不整齐的数据无法进行分割、无法套用公式，甚至无法阅读。

只要眼前清楚地浮现出希望看到的数据外观，再用上一些文本处理工具，就能抽丝剥茧地整理数据，化腐朽为神奇。

一、案例背景

1、Head First 猎头公司从停业的竞争对手那里搞到了一份求职人员名单，这份名单是一个金矿，但是数据乱糟糟的，无法使用。你能帮上忙吗？

2、表格下载地址：

https://resources.oreilly.com/examples/46/-/blob/master/hfda_ch13_raw_data.csv

3、数据分析有一个不可告人的秘密——作为数据分析师，你花在数据整理上的时间多过数据分析上的时间。到手的数据往往算不上井井有条，因此，需要做一些繁重的文字处理工作，使数据格式符合分析的需要。

4、该怎么从头开始处理这些乱哄哄的数据呢？看看下面几种可能的办法：

• 开始重新输入：很费时间，容易出错，尽量不要。
• 问问客户整理数据的目的：就该这么做，知道客户对数据的意图后，就必定能把数据整理成他们需要的格式。
• 写出一个公式，打造数据：一旦我们了解客户对数据格式的要求，使用一两个公式来整理肯定有帮助。

5、让我们先问问客户。看来他们只想抽取姓名和电话号码。

6、按照希望看到的数据格式，画一张图，显示数据列和数据样例。

二、数据整理的步骤

1、保存原始数据副本。和做其他数据工作一样，整理数据必须首先从复制原始数据开始，这样才方便回头检查。

2、设想数据集的最终外观。一旦你确定了你自己想要得到的数据外观，就可以继续从混乱中分辨出数据模式。

3、区分混乱数据中重复出现的模式。要是能够识别重复出现的混乱符号，就能写出公式和函数，然后利用各种模式整理数据。

4、整理并重新构造。利用数据模式开始认真修复数据，你会发现，这个过程常常会反复发生。

5、使用最终数据。一次又一次地重新构造和整理数据，直到得到所需要的结果。

三、拆分数据域

1、所有的数据域都挤在A列中。每个域之间有一个字符：# 。

2、将 # 号作为分隔符进行「分列」。Excel有一个称手的工具，当各个数据域以某个分隔符（即将域与域隔开的字符）分隔时，这个工具可以将数据拆分为几个列。

3、具体操作如下：

选择A列数据，按下「数据」选项卡下的「分列」；

现在，向导已经启动。第一步先告诉Excel数据以分隔符分开；

第二步告诉Excel分隔符即 # 字符，然后单击「完成」。

4、Excel通过分隔符将数据分成多个列。只要各个数据域之间有分隔符隔开，使用Excel的「分列」会非常方便。

5、不过这些数据仍然有问题。例如，姓和名的域中都有一些多余的符号，必须想个办法除掉这些多余的符号！

四、文本处理函数

1、每个名字的开头位置都有一个“^”字符。我们需要弄掉这些符号，得到纯粹的姓。

2、列举几个常用的文本函数：

• FIND 告诉你在单元格中的哪个位置查找搜索字符串
• LEFT 取单元格左边的字符
• RIGHT 取单元格右边的字符
• TRIM 删除单元格中的空格
• LEN 求单元格的长度
• CONCATENATE 取两个值，然后合并在一起
• VALUE 求以文本格式存储的数字的数值
• SUBSTITUTE 以指定的新文本替代单元格中不需要的文本

3、用SUBSTITUTE替换“^”字符。

在单元格格H2中输入下面公式可修复FirstName域：

=SUBSTITUTE(B2,”^”,””)

复制这个公式，在H列从头到尾粘贴这个公式。

4、为了永远删除原来的“姓”数据，复制H列，然后执行“选择性粘贴-数值”，将这些值转变成纯文本，而不再是公式输出结果。随后删除原FirstName列，这样就看不到讨厌的“^”符号了。

五、修复LastName列

1、使用嵌套文本公式处理

=CONCATENATE(LEFT(C2,FIND(“(“,C2)-1),RIGHT(C2,LEN(C2)-FIND(“)”,C2)))

公式行得通，但是太复杂，晦涩难懂。

2、用R软件中的正则表达式处理复杂的数据模式。正则表达式是一种编程工具，你可以用这个工具指定复杂的模式以便匹配和替换文本字符串，R在这方面非常好用。

3、将数据加载到R中，看看head指令得出的结果。

hfhh<-read.csv("C:/Users/admin/Downloads/hfda_ch13_raw_data.csv",header=TRUE) head(hfhh)

4、运行以下正则表达式指令，然后检查一下工作成果。

NewLastName <- sub("\\(.*\\)","",hfhh$LastName) head(NewLastName)

5、用sub指令整理“名”。sub指令用空格替换所发现的所有指定模式，有效地删除了LastName列中的每一个插入文本字符串。

让我们看看语法：

只要能在混乱数据中找到一个模式，就能写出并利用正则表达式得到自己想要的数据结构。

六、把工作成果写成CSV文件

1、从hfhh数据框架中删除旧的LastName矢量。

hfhh$LastName <- NULL

2、在hfhh中增加新的LastName矢量。

hfhh["LastName"]<-NewLastName

3、将结果写入CSV文件。

write.csv(hfhh,file = "hfhh.csv")

4、这个文件会出现在R的工作目录下，可使用getwd()指令查看该工作目录。

getwd()

七、处理重复数据

1、为数据排序，让重复数值集中出现。如果数据量很大，则发现重复数值颇为不易，给名单排个序的话就容易多了。

2、在R软件中，通过子集括号中的order函数可以对数据框架排序。

hfhhSorted <- hfhh[order(hfhh$PersonID),] head(hfhhSorted,n=50)

3、若手头数据非常混乱，就应该大胆地排序，尤其是在记录量很大的情况下，要一次性看清所有的数据往往很难，而按照不同的域对数据进行排序则能够以直观的方式为数据分组，从而发现重复现象或其他疑义。

4、这些数据有可能来源于某个关系数据库。由于你了解RDBMS架构，你知道，我们之所以看到这些重复现象，是因为查询返回数据的方式，而不是因为数据质量低劣。所以，你现在可以放心地删除这些重复的名称，而不必担心数据中存在本质错误。

5、删除重复名字。既然你已经有了除去这些烦人的重复名字的工具，就让我们整理名单，然后交给客户吧。

创建一个新数据框架，显示唯一出现的记录：

hfhhNamesOnly <- hfhhSorted

删除CallID和Time域，这些域使名字出现重复，而客户并不需要这些域：

hfhhNamesOnly$CallID <- NULL hfhhNamesOnly$Time <- NULL 

使用unique函数删除重复的名称：

hfhhNamesOnly <- unique(hfhhNamesOnly)

看一看结果，将结果写入一个新的CSV文件：

head(hfhhNamesOnly,n=50) write.csv(hfhhNamesOnly,file = "hfhhNamesOnly.csv")

6、你创建了美观、整洁、具有唯一性的记录。这些数据看起来无懈可击：没有挤在一起的数据列，没有混乱的字符，没有重复现象。这都是按照下列整理混乱数据的基本步骤进行操作的结果。

7、事实证明，你整理的数据集收效奇特。凭借这份活色生香的名单，HeadFirst猎头公司客户盈门，没有你的数据整理技术，他们绝不可能走到这一步。干得漂亮！

END

其他比较受欢迎的内容，希望对你有帮助：

如何给老板发工资？这样做节税15.6万

一次通关！全网最全CPA综合备考攻略

考CPA有什么用？告诉你不一样的答案

我的5年CPA考证之旅

如何用回归分析预测未来？

看完点个赞，以后分享更多。