卑微民警在线求助使用 Python 的 excel 数据分析

[fishspecial]

在邻市的乡镇派出所基层锻炼期间，手上有一个关于侵犯公民个人信息的案子，取证出来的数据大概有 19k 左右个 excel 文件，要对其进行清洗去重得出有多少条个人信息（按 excel 行数计），但是导出的文件名、excel 行列都没有统一格式。但是只有 sheet1 有数据，并且需要统计其中的手机号的条数就可以。最主要的是要对最后的结果进行去重，一个手机号出现多次也只能算一次。
太久没用 py，粗略的拉稀代码跑跑了一下，最后结果有一亿多条，这是在对文件去重（因为是从后台拖出来的数据，有的时候一个文件保存了多次，通过 md5 去重了）情况下得出来的。但是没有对手机号来进行总的去重。条数涉及证据严谨和对犯人的量刑，本来所里是打算一个半月发动全所之力来复制粘贴的，我心里觉得这真的是对警力资源的浪费。现在想来求助大家给点小民警可以 ctrl c/v 的代码来尽量解决这个问题。
//没有钱。可能仅有的好处就是有点打击黑产的光荣感？ 有 v 友对体制和公考感兴趣的，也可在下面留言，我也会知无不言。
谢谢大家！

[fishspecial]

@binux 谢谢指点！老哥可以点一下相关的操作函数吗？

[ys0290]

感觉是简单问题碰上了大量数据，如果按手机号码前三位来存到不同文件，对每个文件单独去重，会不会速度快一点，而且不同文件不会有重复

[caola]

直接转为 csv 格式，然后直接读取 csv

[Juszoe]

支持民警，学生党没事做，可以帮忙写代码，也算是做点贡献（不嫌弃的话）

[inframe]

1 亿条每行 100 字节 大概占用内存 9 个 gb，导到数据库的话就一句 SELECT DISTINCT count(id) from table...统计完毕
操作上估计还是数据的格式化比较麻烦一点

[also24]

提供一个简单粗暴的思路（不是最优解，但胜在方便）

遍历每一个文件，针对每个文件，执行下面的操作：

第一步，使用 xlrd 可以遍历所有单元格 ，大致代码类似
wb = xlrd.open_workbook("test_w.xls")

for sh in wb.sheets():
for r in range(sh.nrows):
for c in range(sh.ncols):
cell = sh.cell_value(r, c)

第二步，使用正则判断单元格是否匹配手机号规则

第三步，如果匹配，就写入与 xls 同文件名的 txt 文件中


经过以上步骤，你应该已经获得了未经去重的所有手机号，接下来直接使用 bash 下的 sort | uniq 进行去重即可。

命令类似于 cat *.txt | sort | uniq > res.txt

[yzkcy]

看你说的意思是以一个独立的手机号为一条个人信息计算么？也就是说只要统计出总手机号条数（去重后）就行了吧。

非程序员且编程渣，想的流程如下：
1.获取当前文件夹下所有文件名，存入列表
2.遍历列表，打开每个文件名的文件
3.正则判断该文件第一行第一列是否为手机号，否-》就判断第一行第二列-》否-》判断第一行第三列，以此类推，找到存手机号的那一列
4.获取存手机号的那一列的每一行的手机号，追加到某文件

然后每个存 excel 数据的目录都运行一遍脚本。（嫌麻烦的话，就上面的步骤再套一层，优化为当前目录下存在文件夹就自动遍历子目录。或者你把所有 excel 拷到一个目录下也成）
最后把每个目录生成的手机号的文件汇总，然后去重（推荐使用 EmEditor，编辑大文本、去重干嘛的都很好用）

[T0m008]

这么多数据的，可以读了写进数据库，唯一键设好自动去重了，也不用担心内存问题了。
这么多数据是传销案么？

[crab]

导出 csv，正则提取出手机号码，再过滤。

[Wait845]

确实，这么大的数据存到数据库效率应该会高一些。楼主可以留个联系方式

[nuk]

老哥不用去重啊，直接保存 gdbm 就行，python 不是自带么
一亿记录小 case 啦，目测最多两个小时就可以，无非就是花时间而已。

[nuk]

另外提醒一下，sort 那个肯定不行的，内存会爆

[HongJay]

不懂帮顶

[MCVector]

用 pandas 应该可以直接读 excel.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

如果只是统计条数的话把所有电话号码插入到一个 set 里然后统计里面的元素个数就行了。set 是可以自动去除重复的元素的。

[calmzhu]

迫于买不起 excel 。。用 wps 测了一下。格式应该一样的，可以参考，
然后发现是老乡啊。更要支持一下

试一下这段代码（ gist 需翻墙）
https://gist.github.com/calmzhu/0a93716db202df95366f47f1beef50c9

这段代码的功能是
1.对当前目录所有后缀为 xls/xlsx 的文件做 md5 检查。然后获得一个去重的文件名列表
2.对于所获得的列表中的每一个 excel 。遍历第一张 sheet 的所有 cell.然后用正则去检查这个 cell 里面的内容是不是刚好 11 位纯数字并且第一为数字为 1.如果是则存入列表。
（因为单个文件不会很大。所以直接放内存了。存入列表后）
单个 excel 遍历完成之后，对列表先去一次重，然后追加写入到 phone_records.txt 文件
3. 继续遍历下一个 excel 。

这样当前目录的所有 excel 里面的电话号码都提取到 phone_records.txt 文件里面了。再去重就好处理了。

然后文件还挺多的。如果比较慢，可以文件分以下每个电脑放一点。然后再用现在的逻辑处理。最后汇总成一个大的 txt.

单个或若干全是电话号码的 txt 去重就比较好处理了。

[calmzhu]

@calmzhu
比较坑的一点要注意的是，excel 里面电话号码我用 xlrd 导入到 python 变成了浮点类型。所以要用 cell 的 ctype 判断一下。

然后你要用 excel 测一下 excel 有的数字变成 1.123131E +10 这种表士了在 python 里面导出的值

[noqwerty]

@calmzhu #35 我的思路跟你类似，不太清楚楼上挺多人为啥不直接用 set 去重。另外如果楼主的 Excel 文件格式都相对规范的话可以用 pandas 直接读取，然后对每一列直接 col.str.contains("1\d{10}") 或者用 apply 函数去处理，这些向量化的函数会比一个单元格一个单元格那样速度快很多。

[594duck]

一个 access 就可以干完的活搞这么复杂

[calmzhu]

然后刚才差了下，Excel 其实是 zip 将多个 xml 打包的格式。

所以如果有 linux 环境的化。批量解压缩成 xml 直接 grep 正则估计会很方便。。

[calmzhu]

@noqwerty
嗯，这个需求算简单，思路应该基本都有共识，不过迫于楼主是我老乡想要点参考代码所以写了一个～
pandas 到不知道。搜了一下 python excel 处理的第一个例子是 xlrd 就用 xlrd 了。

然后应该不是不知道用 set 去重。而是 Excel 量过多+ excel 格式不统一，拿数据的时候被绕进去了。