用pandas高效比对两张Excel表格：从数据清洗到差异报告

1. 为什么“比对两张Excel”这件事值得认真对待1.1 从一次真实的数据核对需求说起上个月某公司的运营A同学找到我说他们部门每周都要做一次“数据核对”总部发来一份商品价格表分部也有一份自己维护的价格表两张表都有几千行列结构看起来差不多但总有一些商品的名称、编码、价格对不上。以前的做法是把两张表都打开用眼睛一行一行扫或者用Excel自带的“条件格式”标红但数据量一大就卡得不行而且经常漏看。这个场景太典型了。用pandas比对Excel表格把不同数据列出来本质上解决的就是“两份结构相似的数据集如何快速、准确、可复现地找出差异”这个问题。它适合所有需要做数据核对的人财务对账、运营比价、库存盘点、名单核对、版本对比等等。你不需要是程序员只要会一点Python基础就能把原本一上午的活压缩到几秒钟。我自己的经验是Excel自带的比对功能在数据量小于500行、列数少于10列时还能凑合一旦超过这个规模要么卡死要么公式写错一个单元格就全盘皆输。而pandas的优势在于它把“比对”这件事变成了可编程、可复用、可追溯的流程。你写一次脚本以后每周把新文件往目录里一扔运行一下差异报告自动生成。这才是真正的一劳永逸。1.2 核心思路把“比对”拆成三个可执行的动作很多人一上来就想写一个“万能比对脚本”结果越写越乱。我的建议是先把问题拆开动作一对齐。两张表要能比首先得有一个“主键”比如商品编码、订单号、员工工号。没有主键就得先构造一个。对齐的目的是让两张表的每一行都能找到“对手”。动作二比较。对齐之后逐列比较。哪些列需要比数值列比大小文本列比内容日期列比先后。比较的结果无非三种只在左表有、只在右表有、两边都有但值不同。动作三输出。把差异整理成一张清晰的表最好能直接发给别人看。输出格式很关键不能只扔一个“True/False”要让人一眼看出“哪一行、哪一列、左边是什么、右边是什么”。这三个动作对应到pandas里就是merge、逐列条件判断、to_excel。下面我会一步步拆开讲每个参数为什么这么设踩过哪些坑都会说清楚。1.3 一个容易被忽略的前提数据清洗比比对本身更重要我见过太多人拿着两张“脏表”就开始比结果比出来的差异全是格式问题左边是“1000”右边是“1,000”左边是“张三 ”右边是“张三”左边日期是“2024-01-01”右边是“2024/1/1”。这些差异不是业务差异是格式差异但脚本可不管它会老老实实全部列出来导致真正的业务差异被淹没。所以我的习惯是在比对之前先花80%的时间做数据清洗。具体包括去除字符串首尾空格df[列名].str.strip()统一大小写df[列名].str.upper()或.str.lower()数值列转成数值类型pd.to_numeric(df[列名], errorscoerce)日期列转成统一格式pd.to_datetime(df[列名], errorscoerce)处理缺失值明确空值代表什么是“没有”还是“未知”统一成NaN或空字符串这一步做扎实了后面的比对结果才可信。否则你得到的差异列表里一半都是“假差异”还得人工二次过滤反而更累。2. 环境准备与数据读取的细节2.1 安装与版本选择别小看版本差异pandas的安装很简单pip install pandas openpyxl这里重点说两个点。第一读写Excel需要额外的引擎。.xlsx文件用openpyxl.xls老格式用xlrd。如果你只装了pandas没装openpyxl运行read_excel时会报错。我建议直接装openpyxl它对新格式支持最好。第二pandas版本会影响API。比如append方法在1.4版本之后被废弃了改用concat。如果你在网上抄的代码跑不通先检查版本。用pd.__version__看一下我目前用的是2.x版本本文所有代码都基于2.x。提示如果你在公司环境里没有权限装包可以用pip install --user装到用户目录或者用虚拟环境。别直接动系统Python容易把环境搞乱。2.2 读取Excel时最容易踩的五个坑读取看似简单但细节决定成败。我列一个表把常见问题和解决方案对照一下问题现象原因解决方案列名变成Unnamed: 0Excel第一行不是表头或者有多余空列用header0指定表头行用usecols限定列范围数字变成字符串单元格里有空格或特殊字符读取后统一astype或to_numeric日期变成数字Excel日期序列号未转换用parse_dates参数或读取后to_datetime中文乱码编码问题多见于CSVExcel一般无此问题CSV用encodingutf-8-sig大文件读取慢默认读取所有列和行用usecols只读需要的列用nrows先测试我自己的习惯是读取时就把列名和数据类型定好import pandas as pd left pd.read_excel(左表.xlsx, sheet_nameSheet1, dtype{商品编码: str}) right pd.read_excel(右表.xlsx, sheet_nameSheet1, dtype{商品编码: str})这里dtype{商品编码: str}很关键。商品编码如果是纯数字pandas可能读成int但有时候编码前面有0比如“00123”读成int就变成123了比对时就会出错。强制指定为字符串避免这种问题。2.3 先看一眼数据比对的“侦察阶段”在正式比对前我强烈建议先做三件事看形状left.shape和right.shape知道有多少行多少列。看列名left.columns.tolist()和right.columns.tolist()确认两边列名是否一致。不一致的话要么重命名要么只比共同的列。看主键唯一性left[商品编码].duplicated().sum()如果大于0说明主键有重复比对时会出问题需要先处理重复。这三步花不了两分钟但能避免后面90%的报错。我见过有人直接merge结果因为主键重复数据量膨胀了十倍自己还不知道为什么。3. 核心比对逻辑的完整实现3.1 主键对齐merge的四种连接方式怎么选merge是pandas比对的核心。它的how参数有四个值inner、outer、left、right。很多人搞不清什么时候用哪个我用一个表说清楚how值含义适用场景inner只保留两边都有的主键只关心共同数据的差异outer保留所有主键需要找出“只在左表”和“只在右表”的记录left保留左表所有主键以左表为基准看右表缺了什么right保留右表所有主键以右表为基准看左表缺了什么做差异比对最常用的是outer。因为它能一次性把三种差异都找出来只在左、只在右、两边都有。用inner的话你就丢失了“单边存在”的信息。merged pd.merge(left, right, on商品编码, howouter, suffixes(_左, _右), indicatorTrue)这里有两个参数很重要suffixes(_左, _右)当两边有同名列时自动加后缀区分。不加的话pandas会默认加_x和_y可读性差。indicatorTrue新增一列_merge标记每行来源值为both、left_only、right_only。这一列是后面分类差异的关键。3.2 逐列比较为什么不能直接用!对齐之后很多人会想直接merged[价格_左] ! merged[价格_右]不就行了理论上可以但实际会踩坑NaN比较NaN ! NaN结果是True但业务上两个空值应该算“相同”。所以要用fillna先填充或者用equals方法。类型不一致左边是int右边是float1 ! 1.0结果是False但如果你先转成字符串再比1 ! 1.0就是True了。所以比较前要统一类型。浮点数精度0.1 0.2 ! 0.3浮点数比较要用np.isclose或设定容差。我的做法是写一个通用的比较函数import numpy as np def compare_columns(df, col_left, col_right, tolerance1e-6): 比较两列返回差异布尔序列 s1 df[col_left] s2 df[col_right] # 数值列用容差比较 if pd.api.types.is_numeric_dtype(s1) and pd.api.types.is_numeric_dtype(s2): both_nan s1.isna() s2.isna() diff ~np.isclose(s1.fillna(0), s2.fillna(0), atoltolerance) return diff ~both_nan # 文本列先填充再比较 s1_filled s1.fillna().astype(str).str.strip() s2_filled s2.fillna().astype(str).str.strip() return s1_filled ! s2_filled这个函数处理了空值、类型、精度三个问题。你可以根据实际列的类型选择调用哪个分支。3.3 差异分类把结果整理成“人话”比对完之后原始结果是一堆布尔值没法直接看。我习惯把差异分成三类分别输出类型一只在左表存在。merged[_merge] left_only。这些记录右表没有可能是新增的也可能是右表漏了。类型二只在右表存在。merged[_merge] right_only。同理。类型三两边都有但值不同。merged[_merge] both且至少有一列比较结果为True。对于类型三我还会进一步细化具体是哪一列不同左边值是什么右边值是什么这样输出才直观。# 找出所有需要比较的列排除主键和_merge compare_cols [c for c in left.columns if c ! 商品编码] # 逐列标记差异 for col in compare_cols: col_left f{col}_左 col_right f{col}_右 if col_left in merged.columns and col_right in merged.columns: merged[f{col}_差异] compare_columns(merged, col_left, col_right)这样每一列都有一个对应的“差异标记列”后面可以快速筛选。4. 输出差异报告的三种实用格式4.1 格式一明细清单适合逐条核对最直接的方式是把差异行全部输出到一个Excel文件每个差异类型一个sheetwith pd.ExcelWriter(差异报告.xlsx, engineopenpyxl) as writer: # 只在左表 left_only merged[merged[_merge] left_only] left_only.to_excel(writer, sheet_name只在左表, indexFalse) # 只在右表 right_only merged[merged[_merge] right_only] right_only.to_excel(writer, sheet_name只在右表, indexFalse) # 值不同 both_diff merged[(merged[_merge] both) (merged[[c for c in merged.columns if c.endswith(_差异)]].any(axis1))] both_diff.to_excel(writer, sheet_name值不同, indexFalse)这种格式适合需要人工逐条确认的场景。我一般会在“值不同”的sheet里把差异列高亮方便快速定位。4.2 格式二汇总统计适合给领导看领导不关心具体哪一行不同只关心“有多少处不同”。所以我会额外生成一个汇总表summary pd.DataFrame({ 差异类型: [只在左表, 只在右表, 值不同], 数量: [len(left_only), len(right_only), len(both_diff)] }) summary.to_excel(writer, sheet_name汇总, indexFalse)如果值不同的记录里还能按列统计比如“价格列有5处不同名称列有3处不同”那就更有价值了col_diff_counts {} for col in compare_cols: diff_col f{col}_差异 if diff_col in merged.columns: col_diff_counts[col] merged[diff_col].sum()4.3 格式三并排对比适合快速浏览有时候不需要导出文件直接在控制台看。我会把差异行的关键列并排打印pd.set_option(display.max_rows, 100) pd.set_option(display.width, 200) print(both_diff[[商品编码, 名称_左, 名称_右, 价格_左, 价格_右]].head(20))这种格式适合调试阶段快速确认脚本逻辑对不对。注意输出Excel时如果数据量很大超过10万行openpyxl会非常慢。这时候可以考虑输出CSV或者用xlsxwriter引擎速度会快一些。5. 常见问题与排查技巧实录5.1 问题速查表问题可能原因排查方法解决方案merge后行数变多主键有重复df[主键].duplicated().sum()先去重或改用其他主键差异全是“假差异”格式未统一抽样看两边原始值先做数据清洗数值列比较结果异常类型不一致df.dtypes统一转成数值类型日期列比较出错日期格式不同看两边原始字符串to_datetime统一输出Excel报错引擎未安装看报错信息pip install openpyxl中文列名乱码编码问题看列名是否正常读取时指定编码内存不够数据量太大看df.memory_usage()只读需要的列分块处理5.2 三个我踩过的坑坑一主键重复导致数据膨胀。有一次我比对两张订单表主键是“订单号”结果左表里同一个订单号出现了三次因为一个订单有多个商品merge之后变成了九行。解决办法是先用groupby聚合或者改用“订单号商品编码”作为联合主键。坑二浮点数精度导致误报。价格列左边是19.9右边是19.900000000000002直接比较会报差异。后来我加了np.isclose设定atol0.01问题解决。业务上价格精确到分就够了不需要那么高的精度。坑三空值处理不一致。左边空值是NaN右边空值是空字符串比较时一个NaN ! 就是True。后来我统一用fillna()两边都变成空字符串再比就一致了。5.3 一个提升效率的小技巧把比对脚本封装成函数如果你每周都要做同样的比对别每次都改代码。把逻辑封装成一个函数参数化文件路径和主键列名def compare_excel(file_left, file_right, key_col, output_file): left pd.read_excel(file_left, dtype{key_col: str}) right pd.read_excel(file_right, dtype{key_col: str}) # ... 比对逻辑 ... # ... 输出报告 ... return summary # 使用 compare_excel(本周左表.xlsx, 本周右表.xlsx, 商品编码, 本周差异.xlsx)这样以后只需要改文件名一键运行。我甚至把它做成了一个带命令行参数的小工具同事不会写代码也能用。6. 进阶场景当两张表结构不一致时怎么办6.1 列名不同但含义相同有时候左表叫“商品名称”右表叫“产品名称”其实是同一个东西。这时候不能直接merge需要先重命名right right.rename(columns{产品名称: 商品名称})如果列很多可以维护一个映射字典col_mapping {产品名称: 商品名称, 单价: 价格, 数量: 库存} right right.rename(columnscol_mapping)6.2 列的顺序不同这个最简单pandas的merge是按列名对齐的顺序不影响。但输出时如果想统一顺序可以用reindexmerged merged.reindex(columnsdesired_order)6.3 一张表有而另一张表没有的列如果左表有“备注”列右表没有merge之后右表的“备注”列全是NaN。这时候比较“备注”列没有意义应该只比较两边都有的列。我的做法是取交集common_cols [c for c in left.columns if c in right.columns and c ! key_col]只对common_cols做比较避免无意义的差异。6.4 多列联合主键如果单一列不能唯一标识一行就用多列联合主键merged pd.merge(left, right, on[订单号, 商品编码], howouter, suffixes(_左, _右))这时候indicator依然有效逻辑不变。7. 性能优化当数据量达到十万行以上7.1 读取阶段优化用usecols只读需要的列减少内存占用。用dtype指定类型避免pandas自动推断推断很慢。如果只需要比对不需要全部列读取时就过滤。7.2 比对阶段优化避免在循环里反复筛选DataFrame尽量用向量化操作。如果列很多可以只对关键列做比较其他列跳过。用category类型存储重复度高的文本列减少内存。7.3 输出阶段优化大数据量输出CSV比Excel快得多。如果必须输出Excel用xlsxwriter引擎并关闭一些格式设置。可以分块输出比如每1万行一个sheet。我实测过10万行、20列的数据完整比对加输出Excel大概需要15秒左右。如果优化得当可以压到5秒以内。对于每周一次的任务来说这个速度完全可以接受。8. 我个人的几点实操体会第一别追求一步到位。我刚开始写比对脚本时总想一次性把所有情况都处理了结果代码越写越复杂bug越来越多。后来我改成先跑通最简单的场景主键唯一、列名一致、格式干净再逐步加异常处理。这样每一步都可验证出了问题也好定位。第二差异报告要让人看懂。技术人容易陷入“我输出了一堆数据你自己看”的思维。但实际上看报告的人可能不懂技术。所以我在输出时会尽量把列名改成中文把“_左”“_右”改成“总部”“分部”把布尔值改成“是/否”。这些细节不费事但能大大提升报告的可用性。第三保留中间结果。比对过程中我会把merge后的中间结果也保存一份。万一后面发现逻辑有问题不用重新跑直接拿中间结果调试就行。这个习惯帮我省了很多时间。第四版本控制。比对脚本我会用git管理每次修改都提交。这样如果某次比对结果异常可以回滚到上一个版本看看是不是脚本改动导致的。对于经常需要复现的比对任务这一点特别重要。最后分享一个小技巧如果你经常需要比对可以做一个“配置表”把文件路径、主键列、需要比较的列、输出路径都写在Excel里脚本读取配置表来执行。这样非技术人员也能通过改配置来完成比对你只需要维护脚本本身。这个思路我已经在好几个项目里用过效果很好。