我们平时处理CSV文件数据量一大就会遇到各种窝火的事明明看起来差不多的数据加载进来就是排查不出问题去重后行数对不上数据量反而更乱了。干这行时间久了我最大的体会是——清洗CSV这件事很多问题出在顺序上。先做哪一步后做哪一步结果天差地别。今天想跟你聊聊我在本地处理CSV时的一套小流程先标准化再去重最后保留一份可检查的报告。顺序一旦对了后面所有步骤都会顺畅很多。这套方法适合所有经常跟表格数据打交道的人不管你是数据分析师、运营还是开发甚至只是偶尔用Excel整理数据。不需要很重的平台本地几行Python脚本就能搞定重点是思路——把“清洗”这个模糊的词拆成“标准化、去重、出报告”三个动作每个动作都做扎实整个数据处理过程才是可控的。1. 为什么顺序必须是“先标准化再去重”先说结论如果你不先标准化直接去重大概率去不干净甚至会把不该删的行误删掉。1.1 不标准化的数据去重等于自欺欺人这个例子我讲过很多次但真的值得反复讲同一家客户的名称在Excel里可能写成“深圳市某某科技有限公司”在另一行写成“深圳某某科技公司”在第三行成了“某某科技深圳有限公司”。如果只看这一列三行数据肉眼能看出是同一家但字符串比较的时候完全不一致。程序去重的时候会认为这是三个不同的值一行都去不掉。反过来更糟糕。如果你用某个“聪明”的规则去匹配比如只取前四个字那“深圳华强北电子有限公司”和“深圳华强北贸易有限公司”会被当成同一家误删掉真实存在的两条记录。这种错删一旦发生后面找原因特别费劲因为原始文件已经被你覆盖了。所以第一步永远应该是标准化。把数据变成统一格式之后再去判断“是否重复”才有意义。标准化不是把数据变好看而是给后续的去重和对比提供一个稳定、一致的基准。1.2 标准化到底在解决什么问题我见过很多“清洗项目”失败根本原因就是没搞清楚自己要解决什么。标准化要解决的核心问题有三个同一实体在不同行里有不同的表示形式需要把形式统一。同一格式在不同工具里表现不一致比如Excel里看到的日期在Python里读出来是时间戳。同一个值受到空格、换行、全角半角字符的干扰导致比较失败。这三个问题不解决去重永远是在沙滩上盖楼。我常举一个生活化的类比你想检查一堆快递单里有没有重复的收件人但有的单子写“北京市朝阳区”有的写“北京朝阳”有的写“朝阳区北京”你肉眼能认出来机器认不出来。管理员不先统一地址格式就去重最后要么漏掉重复件要么把不同地址误判成同一个。1.3 先标准化对后续报告也有好处这一点很多人没意识到。如果你先保留原始数据再生成标准化后的版本和去重后的版本最后写报告时就能清楚对比“哪些地方变了、为什么变”。如果一上来就去重等发现结果可疑想复盘都没法做。所以我的处理顺序永远固定为备份原始文件 → 标准化 → 去重 → 输出报告。每一步都留下痕迹每一步都可以单独复查。这也让整套流程变成了可审计的过程而不只是一次性跑完拉倒。2. 本地CSV标准化的核心实操细节标准化的具体内容取决于你的字段类型和数据来源。但有几样是每次都要检查的。2.1 编码和文件级格式检查用Python读CSV第一步就可能翻车最常见的坑就是编码。Windows环境的CSV经常是GBK或者ANSI编码而macOS和Linux环境默认UTF-8。如果你不指定编码直接pd.read_csv(文件.csv)大概率报UnicodeDecodeError或者出现一堆乱码。我的标准化流程里第一步永远是先用二进制方式读出文件头检测编码再做后续处理。检测工具可以用chardet也可以直接用办法去试。不过我的经验是呆板地依赖自动检测也会出问题——有时候文件本身混着多种编码自动检测会给出错误结论。更可靠的办法是观察文件开头几行和报错信息。处理完编码后还要处理行分隔符。Windows下CSV的行分隔符是\r\nLinux和macOS下是\n。如果两个平台的文件混用读取时会出各种怪问题。好在pandas.read_csv和Python内置的csv模块基本能自动处理但如果你自己逐行读文件做校验一定要留意。我常用的读取代码如下# 标准化前先探测编码 import chardet def detect_encoding(file_path): with open(file_path, rb) as f: raw f.read(10000) result chardet.detect(raw) return result[encoding] # 用检测到的编码读取 import pandas as pd file_path 原始数据.csv encoding detect_encoding(file_path) df pd.read_csv(file_path, encodingencoding)注意chardet检测到的编码不一定百分百正确特别是文件很短的时候。我的习惯是先看检测结果再手动打开文件确认中文没有乱码然后再往下走。这一步虽然烦但它决定了后面所有结果对不对。2.2 字段级标准化大小写、空格、全角半角字段级标准化最琐碎但效果最直观。首先是去空格。注意我这里说的不只是去掉字符串两边的空格还包括内部多余的空格。比如“北京 朝阳”中间如果有两个空格和“北京 朝阳”在严格比较时也不一样。处理办法可以是用str.strip()去掉首尾空格再用正则把内部的连续空格替换成单个空格。然后是大小写。英文名称、邮箱、URL这类字段大小写不一致也是去重的干扰项。统一转成小写是比较推荐的做法展示的时候如果需要原始大小写再另说。处理逻辑很直接# 标准化去空格、统一小写 df[客户名称] ( df[客户名称] .astype(str) .str.strip() .str.replace(r\s, , regexTrue) ) df[邮箱] df[邮箱].astype(str).str.strip().str.lower()接着要处理全角半角。这点非常隐蔽中文输入法下很容易混入全角字符。全角的逗号“”和半角逗号“,”在显示上都是逗号但ASCII码完全不同。我是吃过亏的去重后发现漏掉了几十行一排查原来是全角空格和半角空格混在一起。处理全角半角的方案是写一个映射表常用的两个库是unicodedata和ftfy但我一般用自己写的替换逻辑因为更可控def full_width_to_half_width(s): result [] for char in s: code ord(char) if code 0x3000: code 0x20 elif 0xFF01 code 0xFF5E: code - 0xFEE0 result.append(chr(code)) return .join(result)这个函数会把全角空格转成半角空格把全角字母数字符号转成半角。如果不需要清理全角字符只想去掉特殊空格那直接清理\u00a0这些不可见字符也行。2.3 日期和数字的标准化策略日期是最容易“看起来一样、实际不一样”的字段。2024/01/05、2024年1月5日、2024-01-05甚至还有Excel序列号日期43470代表2024年1月5日这些在CSV里都能出现。标准化的策略是让它统一成ISO格式YYYY-MM-DD这样字符串排序、比较、去重都没有歧义。数字字段也一样。有的数值被读成了字符串比如“1,000.00”这样的千分位表示有的数字带单位比如“10万”“1.5亿”。在做标准化时最好是只保留纯数值并且把类型转成float或int方便后续做聚合和去重。日期标准化的示例# 日期标准化统一成 YYYY-MM-DD df[下单日期] pd.to_datetime( df[下单日期], errorscoerce ).dt.strftime(%Y-%m-%d)这里errorscoerce的意思是解析失败就置为NaT不会让整行报错。后面我会专门讲这个坑——解析失败的数据会被置空如果你不去检查可能静悄悄地丢掉很多行。3. 去重的策略选择不要只会drop_duplicates标准化做完数据长什么样基本可控了。接下来就可以处理重复问题了。这里有一个很重要的观点需要转变去重并不是“把所有重复行删掉只剩一行”那么简单。3.1 明确“重复”的定义再做精确去重先问一个问题什么算重复是按所有列完全一致算重复还是只按某个关键字段算重复比如订单表里同一订单号可能有两行但这两行可能是订单拆分的结果金额不同、商品不同这时候如果按订单号去重就会误删数据。所以我处理时永远先写一版“唯一性检查”看看按哪些字段判断重复重复了多少条保留哪些行。然后才决定怎么去重。如果确认是要按整行完全一致去重直接使用drop_duplicates()就行# 完全重复行去重 df_dedup df.drop_duplicates().copy()如果按指定列去重需要指定subset参数# 按“订单ID”去重保留第一次出现的行 df_dedup df.drop_duplicates(subset[订单ID], keepfirst).copy()这里keepfirst的意思是保留第一次出现的行。但实际业务里保留哪一行往往有讲究。比如两行同一用户一行的注册日期是2023年另一行是2024年你想保留注册日期最早的那条就需要先排序再去重# 按“注册日期”排序然后按用户ID去重保留注册日期最早的记录 df_sorted df.sort_values(注册日期) df_dedup df_sorted.drop_duplicates(subset[用户ID], keepfirst)这些细节都决定了去重结果是否符合预期。所以千万不要把去重想成一个无脑操作它是在“保留信息最多的一条记录”和“避免重复污染统计”之间做权衡。3.2 模糊去重何时需要如何实现有时候重复不是完全一样而是相似。相似重复最常见的场景就是公司名前面举的例子就是。这种模糊去重如果展开讲能写一本书本地轻量场景下我的建议是不要一上来就上机器学习聚类先做基于“标准化后分词的相似度匹配”。核心做法是把要判断的字段拆成几个关键词比如对公司名做分词然后比较两组关键词是否高度重叠。实现上可以用difflib.SequenceMatcher也可以用fuzzywuzzy或者rapidfuzz。数据量不大时快速两两比较完全可行数据量大时就需要用Blocking分组来减少比较次数。举个例子from rapidfuzz import fuzz, process data [深圳某某科技有限公司, 深圳某某科技公司, 某某科技深圳有限公司] # 两两比较相似度 for i in range(len(data)): for j in range(i1, len(data)): score fuzz.token_set_ratio(data[i], data[j]) if score 85: print(data[i], --, data[j], 相似度:, score)相似度低于85我一般都不建议自动合并人工介入更靠谱。低相似度合并带来的误删风险远比重复带来的统计偏差大。自动去重追求“高准确率、零误删”模糊去重追求“召回潜在重复”这两者的目标完全不同不要混在一起做。3.3 去重后保留哪一行按规则决策最后再展开讲一下保留哪一行的问题。我整理过一个简单的优先级表供你参考场景保留规则实现思路同一用户多个注册记录保留注册日期最早的按日期排序 drop_duplicates同一订单多条状态记录保留状态最新的按时间列排序 drop_duplicates同一商品多价格记录保留最近一次维护的价格按更新时间排序 drop_duplicates同一客户多条地址记录保留填写最完整的先计算填写完整度再排序这里“填写完整度”可以简单地用非空字段数量来衡量也可以加权处理比如手机号字段权重高、备注字段权重低。规则定了之后代码就只是排序加去重的组合而已。这一步的经验是规则宁可一开始复杂一点也要想清楚因为它直接影响业务口径。4. 保留可检查的报告清洗过程留痕很多人清洗完数据就只输出一个干净的CSV中间过程一概不管。等到某个下游报表的数据对不上想追根溯源发现原文件已经被覆盖瞬间心态就崩了。我现在不管项目多小都会生成一套“清洗报告”让整个过程可以复查。4.1 报告里必须有的三类信息我的清洗报告一般分为三个文件汇总信息原始行数、唯一行数、删除行数、每个步骤变动了多少行。重复记录明细哪些行被判为重复对应保留的是哪一行判断依据是什么。标准化变更明细哪些字段发生了值的变化从什么值变成了什么值。这些文件不一定要多花哨最好是纯文本或CSV格式方便后续用任何工具打开。没必要为了一次清洗操作搞一个报表系统稳定、可复制、可追溯才是重点。汇总信息可以这样生成report_lines [] report_lines.append(f原始数据行数: {len(df)}) report_lines.append(f标准化之后行数: {len(df_standardized)}) report_lines.append(f去重之后行数: {len(df_dedup)}) report_lines.append(f共删除重复行数: {len(df_standardized) - len(df_dedup)})这些数字看起来简单但等你要跟别人对口径的时候它们就是最硬的证据。4.2 用MD5记录文件版本防止事后说不清你可能要问保留原文件不就行了吗理论上是的但实际中“原文件”可能被反复修改过也可能被别人重新保存过。为了确认“我处理的这个版本究竟和原始文件是否一致”MD5校验值非常有用。对CSV文件做MD5校验很简单md5sum 原始数据.csv或者在Python里import hashlib def file_md5(file_path): hash_md5 hashlib.md5() with open(file_path, rb) as f: for chunk in iter(lambda: f.read(4096), b): hash_md5.update(chunk) return hash_md5.hexdigest()我会在报告里记录原始文件的MD5、标准化文件的MD5、去重后文件的MD5。这样即使有人把文件复制来复制去我们也能通过MD5确认哪个文件对应哪个清洗阶段。这个细节成本极低却在后续排查时能省大量时间。4.3 给每行重复数据一个“裁决表”我的习惯是生成一个“重复记录裁决表”专门记录每一组重复的处理情况组ID重复行号清单保留行号保留理由处理人G00112, 45, 8812注册时间最早资料最全本人G00234, 6534订单状态为已支付另一行为已取消本人如果你只是个人用这个表可能显得有点重。但一旦数据要交给别人甚至要对接业务系统这个裁决表就是你的护身符——它清清楚楚地表明“我为什么删了这几行保留了那一行”而不是拍脑袋处理。这个习惯我强烈建议你保留。4.4 报告里还要记录标准化规则报告里除了记录“变了什么”还要记录“用了什么规则变的”。也就是说把标准化的规则代码或伪代码写到报告里。比如我可能这么写[标准化规则记录] 1. 客户名称去除首尾空格内部连续空格替换为单空格。 2. 客户名称全角字母数字转半角。 3. 下单日期统一格式为 YYYY-MM-DD非法日期置为NULL。 4. 金额去除千分位逗号转float。这样做的价值在于三个月后你回来看这份报告还能回忆起当时的处理逻辑。如果你什么都没记录下个月你自己都不一定记得当时是怎么清洗的。5. 常见问题与排查技巧实录最后分享几个我在实际操作中经常遇到的问题如果你也踩过类似的坑直接抄答案就行。5.1 为什么文件用Excel打开正常Python读进来就是乱码或报错这个经典问题根源几乎都是编码。Excel为了兼容老旧软件默认保存CSV时常常使用GBK或ANSI编码。而Python的open()和pandas默认用UTF-8读取。解决方案是用chardet检测编码然后指定编码读取。还有一种情况是文件里包含BOM头UTF-8 BOM开头的文件在pandas读取时列名会带上\ufeff如果你发现第一列列名奇怪可以尝试用encodingutf-8-sig读取。5.2 为什么去重后行数比预期少很多一个非常常见的坑日期或文本字段里包含不可见字符。比如从PDF或网页复制出来的数据里面可能含有\xa0不间断空格或\u200b零宽空格肉眼完全看不见但去重时它们会让“看起来一样”的字符串被判为不同。你以为重复只有几十行结果删掉了几百行——因为这些不可见字符在标准化时没被处理干净。排查的方法是打印出可疑字段的repr()结果print(repr(df.iloc[0][客户名称]))如果看到\xa0之类的字符就需要在标准化阶段把它们统一替换掉。5.3 标准化日期时用了errorscoerce结果一堆NULLerrorscoerce确实让程序不报错但它会把解析不了的日期变成NaT。如果源数据里有一些格式特别奇怪的日期这一操作会把这些记录的时间字段全部置为缺失导致后续按日期筛选时误伤大量数据。我的做法是分两步# 第一步先强制转换 df[日期解析] pd.to_datetime(df[下单日期], errorscoerce) # 第二步找出解析失败的行并人工检查 failed df[df[日期解析].isna()] print(failed[[下单日期]])看到具体哪几行解析失败再决定是修规则还是单独处理而不是无脑把解析失败的数据全部置空。5.4 去重后行数没错但关键字段对不上这种情况多半是重复的定义选错了或者保留规则选错了。比如按订单号去重保留了第一行但第一行恰恰是错误状态真正有效的是第二行。解决办法就是我前面说的先排序再去重把有效行排到最前面。比如你要保留状态为“成功”的记录# 状态字段成功排最前失败排最后 status_priority {成功: 0, 待处理: 1, 失败: 2} df[优先级] df[状态].map(status_priority) df.sort_values([用户ID, 优先级], inplaceTrue) df_dedup df.drop_duplicates(subset[用户ID], keepfirst)这种事如果没想清楚数据表面上去了重实际业务统计上可能全是错的。5.5 检查报告生成后建议再做一次“反向核对”最后一步我会做一次反向核对把清洗后的数据重新执行一次“标准化去重”确认结果跟上一轮完全一致。如果发现不一致说明数据里还有随机性因素比如某个字段的值在两次读取时不稳定或者你的标准化规则里用了随机逻辑。这一步看似多余但它能发现很多隐蔽问题。我在实际操练中养成的习惯是清洗流程写成脚本数据文件放在固定目录报告输出到独立文件夹原始文件始终不动。这样无论什么时候回看都能复现当时的处理过程。CSV清洗不是一次性的手艺活它是可以沉淀成工具和流程的工程活顺序对了细节做到位后面就越做越顺手。
