最近在学陈旸老师的专栏SQL必知必会里面学到如何用SQL对数据进行清洗这一节时,在实操的时候遇到了一些问题,这里记录一下其中对各种问题的解决办法,希望对后面学习的朋友也有帮助!(PS: 我这里是WIN10系统下使用的MySQL-8.0.15版本!!!)
- 第一步,创建数据表titanic_train(陈老师是直接用的Navicat工具创建的,而小白我为了锻炼自己的能力,决定直接用MySQL操作):
由于我的MySQL数据库并没有这个表格,所以需要我先创建这个表格,下面是创建titanic_train表格的MySQL语句:
DROP TABLE IF EXISTS `titanic_train`;
CREATE TABLE `titanic_train` (
`PassengerId` varchar(255),
`Survived` varchar(255),
`Pclass` varchar(255),
`Name` varchar(255),
`Sex` varchar(255),
`Age` varchar(255),
`SibSp` varchar(255),
`Parch` varchar(255),
`Ticket` varchar(255),
`Fare` varchar(255),
`Cabin` varchar(255),
`Embarked` varchar(255)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
OK,数据表已经成功创建好了,下面就是导入外部数据。
-
第二步,导入外部数据集。于是小白成功的迎来了第一个问题:
-
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
这个问题的具体解决办法已经写在了前面那篇博客,请自行前往了解,谢谢:
如何解决MySQL8.0外部文件导入的权限问题? -
好不容易把第一个外部文件导入的权限问题解决了,数据成功导入了,接下来第三步:
我们就要用MySQL对数据进行清洗:
首先对数据集进行一个概览:
可以看到正如陈老师文中所写,数据存在缺失。根据陈老师的清洗四字准则---->“完全合一”,我们先检查完整性(即检查字段是否存在空值):
陈老师用的是这个命令:
SELECT COUNT(*) as num FROM titanic_train WHERE Age IS NULL;
但是我这里却没有效。。。这就是第二个问题:查询空值用IS NULL无效!
于是我去翻看MySQL8.0的官方文档,发现还有一种查看空值的写法:
于是我也改成这样的命令来查询空值数量,结果就生效了:
SELECT COUNT(*) as num FROM titanic_train WHERE Age = '';
同样对多字段的空值进行统计的命令如下:
- 对缺失值进行处理,这里采用的是均值填充(缺失值还有其他处理方法,改天专门写一篇来讲):
按照陈老师的方法是先建立一张临时表titanic_train2,再对原表titanic_train进行数据填充的修改。所以这里我们要先创建一张临时表,使用如下命令:
CREATE TEMPORARY TABLE titanic_train2 SELECT * FROM titanic_train;
然后对titanic_train进行修改并填充:
UPDATE titanic_train SET age = (SELECT ROUND(AVG(age),1) FROM titanic_train2) WHERE age = '';
修改成功你会看的下面的change为177:
(PS:如果你的UPDATE命令虽然成功执行了,但是change是0,像下面这种情况:
那么恭喜你,遇到了跟我一样的第三个问题:UPDATE命令无效?
一般出现这种情况的原因有两种:
1. 要更新的数据跟原始数据一样;
2. 要更新的数据并不存在;
而小白我的情况后来通过排查发现是第二种,因为我创建临时表的时候并没有把数据全部复制过去,于是导致了临时表虽然存在,但是里面没有数据,这就尴尬了😂。所以解决办法也很简单,就是重新创建临时表,然后再次执行上面的UPDATE命令即可)
然后我们可以对数据集进行查看来确认我们的数据填充是成功的:
OK,接下来我们继续对其他字段进行清洗,跟着陈老师的步骤是Cabin和Embarked这两个字段,这里没有问题,直接上图:
- 好了,直到上面我们对缺失值的处理已经结束,完整性已经ok,下面是检查全面性:
这里就是观察数值情况并检查字段类型,显然一开始我们设置的表格字段类型是不太全面规范的,需要进行修改。陈老师这里也给出了解决的办法,一般创建数据表应该是这样的才对,所以我们也对字段进行规范修改。
- 到这里全面性也检查完了,然后是合法性和唯一性的检查。经过检查,这个数据集不存在这两个问题,所以就不需要操作。
- 最后就是对清洗完的数据进行可视化,这里用的MySQL For Excel 插件工具,除了安装这里要注意一下:需要先安装 一个微软VS Tools 的工具 才可以:
这里是微软工具的下载链接:
https://www.microsoft.com/en-us/download/details.aspx?id=48217
你可选择下载中文版,方便你安装。
接着再安装陈老师说的那两个工具,然后去Excel里面设置一下登录账号和密码,就可以把刚刚清洗完的MySQL数据导入Excel:
最后就是成功的了解到用户生存情况(Survived)与 Embarked 字段的关系:
总的操作到这里就结束了,这次实操遇到了三个问题+一个注意事项:
1. MySQL8.0外部文件导入的权限问题;
2. 第二个问题:查询空值用IS NULL无效;
3. UPDATE命令执行成功却Change为0,无效更新;
4. 注意事项:需要先安装 一个微软VS Tools 的工具才可以用MySQL For Excel插件。
总的来说,个人觉得MySQL进行数据清洗没有直接用Python操作更方便,虽然陈老师也建议可以用存储过程进行处理,但是个人还是更喜欢用Python来进行数据清洗,下一篇写写Python来进行数据清洗的方法。
谢谢阅读,如有错误,请指正。
以上数据来源 Kaggle: https://www.kaggle.com/c/titanic/data
文中课程资料来自陈旸老师的:SQL必知必会之SQL项目实战3讲的第一讲:如何用SQL对数据进行清洗