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

資深大佬 : fishspecial 25

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

大佬有話說 (100)

資深大佬 : 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
資深大佬 : calmzhu

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

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

@calmzhu #35 我的思路跟你类似，不太清楚上挺多人为啥不直接用 set 去重。另外如果主的 Excel 文件格式都相对规范的话可以用 pandas 直接读取，然后对每一列直接 col.str.contains(“1d{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 一把梭