求问数据清洗的简易方法

[TimG]

数据量在亿级别，主要清洗需求包括对特定字段去空格、统一日期格式、计算日期差、特定数值求和（数值都在行中）、查对字典得到对应值等等。需求虽然五花八门，好在所需的数据都在同一行，不需要进行聚合，也不会跨行查询。不过如遇到无法处理的数据需要及时找到并剔除，反馈并等待重新收集。这种情况需要进行语言交流和重传数据，所以会耗费一定时间。
目前的方法是导入 PostgreSQL 进行清洗，遇到不容易通过 SQL 实现的逻辑，比如统一日期格式，使用 C#编写了程序去处理。曾尝试使用 pandas 处理，但数据量太大无法载入内存遂放弃。

目前想要解决的问题是：
- 剔除问题数据后，获取到修复的数据，为了防止混乱，我会把每次新获得的数据存在新表中单独再清洗一遍。但是清洗流程太多（有很多 SQL 语句，因为一条 SQL 只能清洗一列，列很多，还有 C#处理的部分），数据问题反馈后还有问题也是常见情况，数据库中就会有大量的表，虽然有做视图，但也大大增加了管理压力。尽管如此我也不想把各数据全部整理在一张表，总是会害怕越整越乱。
- 因为不需要对数据进行聚合，也不会跨行查数，如果顺应人脑的思维逻辑的话，就是对每行的各个列字段应用特定的方法，然后逐行执行就可以。这样还可以多线程执行以及查看进度。遇到出问题的行也可以快速定位到。当然这个不是必要的，但我感觉会比数据库要简单直观。

想问下各位前辈遇到这种情况一般如何处理？有没有比较合适的软件可以使用？

[lambdaq]

脏活儿就是这样了。你想容易，那得加钱。

[TimG]

@lambdaq 只是觉得目前了解的程序不太适合处理这类问题，openrefine 也看了一下，好像也是针对列处理的，还得学习就不如继续用 SQL 了。这种需求想想也不算少，应该在我认知之外有合适的程序吧。不行就只能扩写那个 C#程序了......

[renmu]

pandas 处理，没必要一口气处理完，分而治之

[TimG]

@renmu 确实是这样，可以按照反馈数据的最小单元去拆分，导出成清洗完成和未完成两个文件夹，然后每次遍历未完成的文件夹，有新数据就替换掉未完成文件夹的旧数据。很有启发，谢谢。

[wxf666]

为啥看第一行需求，觉得用纯 SQL 写，也没啥难的呢。。

可以给几行数据（敏感数据用其他字符串代替就好）看看吗？

[NoOneNoBody]

@wxf666 #5
刚遇到一个，你看看， 数据目标意义为日期
年月日
日月年
月日年
6 位数
7 位数
8 位数
4 位数：只有年、或只有月日
2 位数：年龄
23.3.24 或 11.12.13 （搞不清年月日分别是哪个）
……
以上情况混杂都有

[wxf666]

@NoOneNoBody 老老实实 CASE WHEN 各种情况呢？

分不清年月日的，就标记为错误？（反正换人来识别，也是标错呀）

然后不断审查标为错误的日期，看有啥情况会被遗漏，完善 WHEN ？

[NoOneNoBody]

@wxf666 #7
肯定是逐个 case 处理
我的意思是 sql 做这个是挺难的，不如 pandas 当成字串用正则，可以用向量或者 numba/c++处理
至于那些逻辑不对的，只能先转 8 位，并打 tag“日期异常”交给下家判断，清洗人员只做基础逻辑判断，数据实际意义也做判断就是“僭越”了

那些异常数据其实可以猜测，就是原始记录是有入库时间的，可以通过入库时间计算，只是人家本来就没有日期查询和展示的需求，可能只有月日或者年龄的需求，所以入库时就顺便变了形式，汇总(采集)时不会去拿那个入库时间，自然格式就不对了

[512357301]

SQL 做起来也不难，只不过 postgresql 或者 MySQL 不适合数据清洗，你得找列式数据库，比如 clickhouse 之类的。
编程思维，那就用 pandas 或者类似的代码框架实现。
数分思维，肯定直接上 SQL ，Python 之类的编程语言只是帮忙拼接 SQL ，调度 SQL 而已。
具体看你的倾向。

[TimG]

@512357301 感谢回复。我查到 clickhouse 不擅长更新数据，文档中也写明 update 是繁重操作。不过就原理上如果只查询+修改同一列，效率应该比行式数据库更高的。因为数据清洗势必牵扯到大量数据更新操作，不希望在这里产生意外。如果列式数据库在这方面不自信，我还是用回传统方法跟稳妥一些。

[noqwerty]

能接受 pandas 处理的话建议看看 polars ，API 更符合直觉，速度也远比 pandas 快

[TimG]

@wxf666 如您所说，只用 SQL 确实可以解决问题，但是难以迅速定位错误。这个项目的时间其实大部分会花费在打回数据重新收集这里，所以迅速的发现数据问题、修复可以修复的，一遇到无法修复的情况迅速反馈是非常重要的。因为数据五花八门，使用 SQL 的话，每次增加新的 case when 都要全部运行后才能知道清洗结果（而且一次还只能清洗一列）。如果编写程序以行去遍历，除了可以用多核加速和显示进度以外，还能：
1. 实时查看当前错误，数据第一次跑完，整个表的例外情况就可以掌握的差不多，简单的情况很多都已经改好程序了。而 SQL 的话，运行的时候人只能闲着，甚至连什么时候能跑完都不知道。
2. 记录修改前后数据，保存进日志或者存进数据库，如果出现意外可以 rollback 。
3. 对于复杂的逻辑可以 debug ，尽管只是 if else ，写多了也会混乱，也能美美地单元测试。对于这个量级的数据，在我的小电脑上跑是真的不想再来第二次。

[TimG]

@noqwerty 谢谢推荐，这个没听说过我会去了解一下。之前因为内存不够的问题也试过用 Vaex 替换 pandas ，结果好像不完整支持 apply ？折腾了好久最后无奈直接用 C#读数据库了。

[cccvno1]

将数据表封装成一个 c#类，用 dapper+sqlreader 多线程分块读取数据表
转换方法都可以定义成一个个 transfer 方法，传入和传出的对象都是表对应的模型
将一组 transfer 方法串联起来不断转换就完事了
最后再将转换后的数据写进新表，最好是几万条批量写入一次
这样可以美滋滋的单元测试

[EndlessMemory]

用 Python 试试吧

[dbak]

graylog 的 pipeline

[TimG]

@cccvno1 一开始确实是这么做的，但是当时没有给我一次交代所有需求，导致先写了 C#程序清洗，后期有新的列清洗需求，就图方便用 SQL 处理了，结果需求越加越多，SQL 也越来越多，成了这种人不人鬼不鬼的样子......这次痛定思痛，不再奢求一次全跑完，先把大表拆了再跑吧，并且尽量用一种方式去清洗，不然两者的优势都不沾哈哈

[TimG]

@dbak 感谢推荐，简单了解了一下感觉这个比较接近理想流程了。不过我是 Windows ，这个程序好像没有提供 Windows 支持，害怕虚拟机会增加无谓的性能损耗，我还是研究下别的方案吧。

[SmartTom]

阿里的 DataX 吧，搭建也方便。功能基本满足。

[dif]

我都是根据实际情况做清洗，有些是用 sql,有些用 python,spark 之类的， 也没有一个完美的工具。

[flmn]

像你说的，行与行没关系，可以拆分成多份分别处理再合并呀，可以看看 Spark 。感觉用文件存储比数据库合适。

[cccvno1]

@TimG 需求要不断增加的话能不用 sql 就别用 sql

[MoYi123]

pg 里可以写 python 之类的很多编程语言.

[catamaran]

@TimG #10 如果用 clickhouse, 我是这么做的：客户端用 python 处理，一次读取 100 万行（看硬件情况），处理完成后写入新表。主要弄好分区键，clikchouse 我记得翻页会越来越慢。

[WuDiHaiTai]

你可以放一小段数据上来可以看的更直观一些，这样大家更好提建议嘛，文字版的不太好想象你的数据是什么样的。

之前挂壁做 AI 标注的时候常常因数据清洗破防，如果有规则的话就写个小 python 脚本就搞定。无规则的密集排列我个人是会想办法找它们的共通处，想办法搞插入符号之类的，将数据做一些划分之后，更直观，更容易检索然后再想办法。也没有处理过亿级别的，不太好说。我这人逻辑能力一般，数据清洗我觉得还是得靠一些思维逻辑上的能力，算得上是八仙过海，各有各的招。

[tikazyq]

https://github.com/pola-rs/polars