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

[fishspecial]

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

谢谢大家的回答！加班到现在的小弟心里充满感激。v友很多回答都很实用，基本的代码还能看懂，但代码的实现实在力不从心了。如果有v友能提点一些关键代码，感激不尽！ 所长说假如相关脚本写成功之后，要保存在所里以后继续使用。能帮到的老哥可以把自己的id写到注释里的author 里，这样湖北某个派出所里可能每个到这里工作的人都得用着你的脚本并且看到脚本作者，也算有一点点的荣誉感吧？当然也可能没有…至少可以跟别人吹吹水了。

一晚过去，有这么多 v 友帮忙，甚至有老哥在我以往发的交易帖中找到了我的小绿来特地帮助我处理数据。
这种感觉，跟今年年初在武汉留守时经历的许多感动一样。
因为白天工作太忙，只有晚上下班的时候才有空回复各位 v 友希望见谅！
这里回答一下回答 v 友的几个疑问:
- 为什么不用数据库来做？
所里除了我之外，没有人有相关经验，只会使用公安系统软件和基本的文档操作，而我也早就将这些知识还给了我的大学老师，实在自惭形秽。之前遇到数量级在万的时候，都是所长带着几个辅警人工统计复制粘贴，但这次的数据实在超出人力范围。
- 楼主是不是黑产？帮忙的各位需不需要负法律责任？
不是，没有。（技术像我这么差的黑产还是坠楼谢罪吧。）脚本完成的时候我会拍穿警服跑脚本的照片，并且鸣谢各位可爱的 v 友
- 为什么不找外包
因为没有这种支出，花钱有理，所以没法办到。公私分明，这也不是每次自己掏钱可以解决的事情。
- 为什么我某某时候求助了某某警察，结果不了了之？
很多道理我这里就不说了，只能建议你认真看看相关的法律不至于被老民警忽悠，然后去找那些年轻的正式民警，他们都有一腔热血，并且由正规警校考进来的，绝大多数愿意为你尽力解决，也更好跟他们沟通。
有一个让我很不舒服的留言，但是我发现 v2 无法删除别人的留言，只能在这里说明，要无端的引战抱怨，滚到外面抱怨，这里友好的环境不欢迎你，我也不欢迎你。

[PublicUser]

我觉得你申请一下办公经费，找个外包更靠谱

[dji38838c]

上面说的没错，一个人干一个人的活，不要想着把所有人的工作都做了

[EscYezi]

提一个比较简单粗暴的方法，遍历每一个文件每一行每一个单元格，然后把所有 1 开头的字符串提取出来，去重后再过滤掉含有非数字字符的字符串，最后统计个数。

[calmzhu]

支持民警
不算复杂的需求，我应该可以做。

但是需求有点不清楚 。
意思是 19000 个 excel(可能有重复)，然后从每个 excel 的 sheet1 中提取出电话号码并去重计算次数吗？

有个问题。第二张图里面。一行看有多个电话号码的。这种是算一个记录还是每个不重复的算一个。

[hankai17]

手机号正则匹配

[fishspecial]

@PublicUser 乡镇派出所不可能有这个经费的，我现在加班的夜宵都是自己掏钱。

[fishspecial]

@dji38838c 到时候我也逃不脱的哈哈哈，觉得做这个事情很蠢。

[ys0290]

妄加揣摩一下，成了以后这种事都得你搞，不成就得顶得住压力。外包吧……

[calmzhu]

然后我记得 excel 好像是类似 xml 的标记文本格式。
如果需求跟上面我说的匹配，直接把 excel 当文本，然后用正则把所有电话号码过滤出来，再去重

[fishspecial]

@calmzhu 谢谢你的回答！ 19000 左右的 excel 中，因为被多次保存，有些 excel 是一模一样的数据，我就通过计算 excel 的行数来保证没有相同行数的 excel （相同行数的数据不同的 excel 这种情况忽略不计）达成去重的目的。因为不管哪个 excel 文件，每一行数据都会包含手机号，也就是手机号的条数就是 excel 的行数，但是这样就会导致，假如一个手机号在多个文件里出现就引起重复计算，所以的确没想好怎么提取 19000 个 excel 的所有手机号来进行总的去重。一假如可以不花太多功夫，一行中有多个手机号的每个不重复的算一个，但假如太麻烦那就统共算一条吧。毕竟数据量太大了，平均一个文件都有 10000 行左右。

[fishspecial]

@ys0290 我过几个月估计就调回去了。不过这不是在公司跟资本家做事，打击犯罪分子的话，做起来肯定是会当自己的事情去处理的，毕竟也的确是自己的工作任务。（当然自己技术实在有限哈哈哈）

[fishspecial]

@calmzhu 我怕出 memoryerror，而且这么长时间没接触代码了，数据结构和算法都忘的一干二净。今天写了一个很简单的 demo 对相同文件进行去重后，统计所有 excel 的行数，最后算来有一亿多条，这还是跑到 14000 千个文件左右就爆出 memoryerror 的情况之下。实在是数据量太大了。现在就想有 v2 能帮助下，有大概的代码改改我就可以解决问题就好。

[calmzhu]

@calmzhu 看过了，直接打开乱码。看来不是直接的文本了。。

[fishspecial]

@EscYezi 应该是爆内存了...可能优化之后能行？我技术实在太菜了 /无奈

[fishspecial]

@calmzhu 是的，txt 转为 excel 文件是可行的，但是 excel 无法直接转成文本格式

[binux]

分两步
1. 提取出电话，一行一个输出 txt 。直接单元格正则就 OK 了。
2. sort -u

[waytoshine]

很好解决的问题啊，最笨的办法，你不是说内存会爆，你就把所有 Excel 多分几堆，一堆放一个文件夹，然后 Python 肯定有操作 Excel 文件的库啊，用那个库，把所有的 Excel 的文字提取出来放在 txt 里，放 txt 的时候用逗号或者分号分隔，然后再用正则去匹配手机号，然后插入数据库里，完事处理完所有 Excel 的数据之后，再用 Python 操作数据库，把正则匹配到的手机号插入数据库一个表，然后 select distinct phonenumer from telephone，整个流程都不需要什么高深的技术，就很顺其自然的要这么做

[manhere]

没必要去重，以行数计就行。

[clrss]

库么听说过 xlwing, 没实际用过.

[fishspecial]

@waytoshine 谢谢指点！过程我能理解，但是代码上还是觉得困难了点…唉

[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 格式不统一，拿数据的时候被绕进去了。

[guoyida]

可以用 powerbi 做，应该很快，图形化界面不涉及编程

[binux]

@nuk 并不会 https://stackoverflow.com/questions/930044/how-could-the-unix-sort-command-sort-a-very-large-file

[pocketmiddle]

我不会 python 。但感觉这事不需要 py，vba 就能搞定了。

[ShuoHui]

jz 还是 wa 啊

[cfwyy]

只需要 统计 手机号条数就可以？
python 也不敢说是老手，我的基本思路是：
正则表达式 匹配出手机号，add 到集合 set 中，set 会自动去重；
单进程跑 19K 个文件太慢的话，可能还要用多进程来跑。
手上没有文件也不能测试，仅供参考。

[huanghaozi]

已经搞出整合的 Excel 的话，统计次数可以用数据透视表鸭（但不清楚这么多数据会不会卡死）

[xinghen57]

数据库吧，这数据量对 excel 太大了。

[xinghen57]

再提供个思路：
xlxs -> csv -> 合并 -> 去重
每个环节都有现成软件，百度一下就行

[frdspro]

感觉表格的格式很乱，并且表格里的内容也很乱，大大小小啥样的都有⁽˙³˙⁾

[funcookies]

感觉要的是现成的代码啊。话说楼主有啥进展吗，对运行时间，还有最终生成的数据有啥要求吗。还需要做吗。

[swsh007]

遍历一圈 2w 个文件，
然后写到 10 个文件里
去重 python 或者 db 都可以
估计有 2 天咋都完了
要是人工就太折腾

[sadfQED2]

Python 遍历所有文件所有行，通过正则把手机号匹配出来

[sadfQED2]

@sadfQED2 数据量大可以考虑存到 mysql，通过唯一索引去重

[xingyuc]

首先证明你这些内容不是用作黑产

[nightv2]

搜了一下 知乎有个文章“小工具--Excel 批量转 csv”，用 VBA 转换为文本后就有很多方法来处理了

[reymond3]

@xingyuc 我认为这句话很有道理

[calmzhu]

@xingyuc
@reymond3
除非表述有明显不合理之处，没必要使用有罪推定

[xcheng]

用 C#做过 EXCEL 数据遍历统计，你这个数据量有些多，提取后用数据库来处理会更方便（例如以后要统计其它数据）。
有需要可留个联系方式

[winterbells]

手机号读出来，每 1w 个分成一个文件
之后挨个文件内去重
再两两文件比对去重
之后合并或者直接加总数

[efaun]

@xingyuc #54
@reymond3 #56
我可能知道了大陆为什么有这么多假案冤案错案了

[Mithril]

直接批量转成 CSV 然后 grep + sort 就完了。。
用不着写代码啊。。。

[xingyuc]

@calmzhu @efaun 技术是把双刃剑，我过分担忧了

[fuchunliu]

表格稍微修改一下，导入数据库，就可以直接去重了，而且也不存在内存不够的问题了

[fuchunliu]

@fuchunliu 当我没说，19k 个 excel😂😂😂

[funcookies]

@xingyuc 搞 hc 的这点技术还是有的，况且只要能搞，下游就有人清洗这些数据。楼主这样的看起来不大像

[nuk]

@binux 看来我记错了。。。

[laydown]

万一是黑产，楼上各个“帮忙”的都有责任。

[dhkjenfbfu]

@efaun 法学界不是早就有结论，因为没有司法责任制度，然后 15 年的时候就补上了啊，虽然实际执行还没什么力度。

[CoCoMcRee]

南京千奇晨宇
军理工博士带队, 长期接活, 欢迎合作 vx: 18505101236

[fishspecial]

@Juszoe 怎么可能嫌弃呢？很欢迎你的帮助！
@inframe 是的，尤其是这次 excel 没有统一的格式，导入数据库正则手机号数据超过了我的技术范围了。
@also24 谢谢你的代码！我今晚下班后试试！
@yzkcy 谢谢你的思路！
@T0m008 侵犯公民信息，主要是股市和房产的。
@Wait845 谢谢不吝赐教！我的小绿 V2ViX1NhbXVyYWk=
@calmzhu 不好意思才回复你，我的小绿 V2ViX1NhbXVyYWk= 白天上班没有时间回复消息。谢谢老乡的帮忙，武汉欢迎你！我一定接待。
@guoyida 我晚上看看这个软件，谢谢！
@huanghaozi 已经卡死了，而且导出的整合 excel 只有文件名和该文件的总行数两列数据
@funcookies 十分需要！晚上我试试其他热心 v 友的代码，运行时间肯定希望尽量在一天以内，时间长了怕电脑崩溃。
@nightv2 好的 我这就去搜一下这篇文章，谢谢！
@dhkjenfbfu 感谢你的专业回答！这些回复我一般不怎么理的。谢谢可爱的 v 友

[waytoshine]

@CoCoMcRee #69 人家小民警自己加班解决问题，也说了自己单位小，不会有预算请人来外包，楼上都是热心真想帮忙解决问题的发了那么多楼，你这冷不丁来打个广告，就你这阅读理解能力和情商，还什么博士，虚头巴脑的，真是笑了。

[NonClockworkChen]

为什么有人会怀疑黑产，有能力做黑产的，怎么可能连做这事情的成本都付不出。

[ZSeptember]

可以系统学习下 python，感觉会对以后的工作也会很有帮助。

[nznd]

大概试了一下，直接用 set 完全能存下的，用 permutations 生成了 1 亿条长度为 12 的数字 （首位 1 不存了） 不过我猜测老哥的 MemoryError 是因为电脑内存不够 并不是因为 python 的性能问题（毕竟吃了大概 18g 左右内存...

https://washingpatrick.cn/wp-content/uploads/2020/08/memory.png

我的目前想法是，先写一个脚本 读取当前目录下所有 excel 文件，正则取出电话号码，并且去掉首位 1，然后存到一个 csv，内存不够可以分批跑脚本，每次控制 excel 的个数就行，最后把所有 csv 再放到一个文件夹，然后写第二个脚本，读取所有 csv，并且用 set() 去重，如果老哥觉得可行 并且需要帮助我可以周末尝试摸一个出来 x

[7654]

office 有个 power query 数据源可以是目录，查询合并成一个 excel 这个很简单
接着配合 Access，直接查询去重，导出结果
没有代码能力要求，这样每个人都会操作

[XiaoBaiYa]

@NonClockworkChen 是的,做黑产的平时看起来和正常公司无异,产品,测试,开发,设计,应有尽有

[Mithril]

@fishspecial 个人认为你这个思路是有些问题的。你的目标是“解决问题”，而不是“用程序解决问题”。
对于这种一次性需求来说，我认为更好的办法是程序加人工操作。比如你觉得 19K 文件人工处理麻烦，但是能一次性处理 19K 文件对于程序来说需要大量内存和性能消耗，所以可能要用数据库等办法去解决。这只是一个思路，如果你的程序以后会被重用的话，当然用这个办法会很好。
但我认为大概率你这需求以后也不一定有，那么更好的办法是你写个一次能处理 1K 文件的程序，然后人工重复 19 次。这样程序写起来很容易，人工操作也不是特别麻烦。
所以我的建议是直接全转成 CSV，然后系统内置的正则加上去重命令解决。虽然没办法一次处理所有文件，但是手工分几次处理完再合并去重一次就行了。
远比你写完并调试好一个很复杂的程序再双击运行花的时间短。

[27]

pandas 或者 xlrd 读取转成 csv 输出，直接 awk+grep 正则，再接个去重就可以。预计两小时工作量，不过我想愿意给 lz 帮忙的人应该已经很多了

[27]

去重可以用 awk 'a[$1]++' ，比 sort | uniq 快很多

[Leee]

哇，民警也有这类工作的话，我想去做诶。。

另外，加油！！！

[ilunny]

写个遍历文件的类都花了些时间，希望有点帮助。。
https://pastebin.com/ibCp6hgX

[NeezerGu]

突然好奇民警招数据分析师吗

[Juszoe]

@fishspecial #70 啊这，好多大佬出手了，好像没我帮忙的必要了，有需要随时联系我

[janxin]

去重复看纬度，根据你目前的情况就是根据手机号 /身份证对应到具体人即可。

这样可以使用 pandas 或者其他 excel 读取库读取数据，遍历读取、存储唯一结果即可。

[nznd]

@nznd #74 我错了 人傻了 手机号码长度原来是 11 位 砍掉首位那只有 10 位 内存 16g 应该就存的下了

[ty89]

首先用正则把手机号匹配出来，然后输出到文本文件，每行一个。 最后用 ilnux 自带的 uniq 就可以去重

[fhsan]

纯数据，当然 pandas+数据库，反正 xlsx 挺坑的，

[kemistep]

民警招数据分析师，5 年经验了，可以应聘嘛；

这个的答案是：
def 遍历文件；
def 使用 pandas 读取 excel 文件，加载到内存中；
def 使用 apply 对每一列数据遍历，判定是否为手机号； 这个就直接用是否是 11 位判定；
def 将处理后的数据 和 表名 存入到 mysql 数据库，便于后续核对；(也可以不用)
def 对手机号去重统计；

[xuewuchen]

所有的 EXCEL 格式是否是相同的？ 如果是相同的就好办了很多
1.遍历所有 EXCEL 文件，将 EXCEL 文件的所有行列读入内存
2.读入的时候就可以根据规则进行去重操作，比如名称一样的，手机号一样的之类
3.读取完成后直接显示到外部界面表格
4.支持将表格导出到 EXCEL 表

其实用 C#，JAVA 之类的，如果是格式相同的 EXCEL 应该很快就能做出来，python 很长时间不玩了忘的差不多了。

[okonogi]

提取手机号后的操作可以用上位运算的技巧呀。末 10 位当成数字，用单个 bit 置 1 表示手机号。末 10 位最多 10^10-1 bit 的数据，不超过 1.17GB，堆分配个这么多空间，然后得到 1 个手机号就对对应 bit 置 1 。全部处理完后就是数有几个置 1 的 bit 。这样处理内存压力应该就少很多了。

[ilunny]

需要先 pip install openpyxl
用 openpyxl 读取 xlsx 文件，然后把号码放入数据库，最后读取号码数量。。
试了下内存占用不多，可能处理时间会有点慢？半小时？数据库文件大小估计有 1 个多 G：
https://pastebin.com/3V5E5s1a

[vandort]

你要不飞 Q 传给我吧，我在你们省厅有个工位，公安网视频网都能连

[ExplorerLog]

xlsx 格式可以解压，解压后 xl 文件夹下有个 shareStrings.xml xm 字段正则匹配一下

[lysS]

@PublicUser 外包这些数据二次泄露了咋办？

[CrazyMoon]

1，正则匹配出手机号
2，把手机号按号码段提取到不同的文本文件里
3，依次对各个文本文件做去重处理
4，合并、统计
-----
个人的想法。。

[encro]

for f in files:
for s in sheets:
for row in rows:
for col in row:
get_mobile_from_col
save_mobile_to_file

get_unique_mobile_from_file

[encro]

pandas 的话直接更方便点，但是学习要时间啊

[encro]

原来 26 楼已经有人写了 @also24

[meiya6duxh]

你好，我这边愿意免费帮你处理这个问题

如果有需要请联系我

微信 Thanatos-XH

[Liyiw]

转成 csv，然后 pandas 一把梭