使用pandas的compare()方法做excel数据对比 |
您所在的位置：网站首页 › excel中如何进行数据对比 › 使用pandas的compare()方法做excel数据对比 |
使用pandas的compare()方法做excel数据对比
|
目录 背景 思路梳理 代码详细讲解 常见报错 背景最近在测试工作中遇到开发做数据迁移，迁移前后表结构都没变，但是需要对迁移前后的几个表的数据进行校验比对，因为表的字段非常多，用sql和网上的数据对比工具看个人觉得看差异值都不是很直观。因此想到了python的pandas库写个脚本进行数据对比，并输出差异值。在实际工作中检验过了，用于数据对比是真的省时省力太多了。 这里我只是将mysql表数据导出成excel，再对两份excel数据进行比对。如果可以直连数据库，本脚本也适用，只需要将读取excel部分代码改成连接数据库就行了。 思路梳理 读取excel数据存入DataFrame中（pandas的二维数据结构）对数据进行排序df.compare()方法对比两份数据导出差异数据，并将两份源数据和差异数据放到同一个excel的不同sheet中先看全部代码 import time import pandas as pd # 数据内容对比 def data_contect_compare(file_path1, file_path2, key): #将文件数据传入到DataFrame中 df1 = pd.DataFrame(file_path1) df2 = pd.DataFrame(file_path2) # 按主键字段进行排序，并且重置索引数据 df1 = df1.sort_values(key, ignore_index=True) df2 = df2.sort_values(key, ignore_index=True) # 差异数据导出 time_now = time.strftime("%Y%m%d%H%M", time.localtime()) with pd.ExcelWriter("对比数据" + time_now + ".xlsx") as writer: df1.to_excel(writer, "excel1") df2.to_excel(writer, "excel2") df1.compare(df2, result_names=("excel1", "excel2")).to_excel(writer, "差异数据") if __name__ == '__main__': # 读取excel excel1 = pd.read_excel('C:\\Users\\HP\\Downloads\\返利应收明细表-旧表.xlsx') # 迁移前文件路径 excel2 = pd.read_excel('C:\\Users\\HP\\Downloads\\返利应收明细表-新表.xlsx') # 迁移后文件路径 # 主键字段 key = ['busi_month','contract_no','contract_version','clause_no','clause_version','supp_code', 'goods_code','acco_basis','goods_code','taskvolume_group_code','suit_scope_code','purc_company_code'] # 数据内容比对 data_contect_compare(excel1, excel2, key) 代码详细讲解1、读取excel pandas.read_excel()函数的语法如下： pd.read_excel("本地文件路径") # 第一个sheet pd.read_excel('本地文件路径', sheet_name=1) # 第二个sheet sheet pd.read_excel('本地文件路径', sheet_name='总结表') # 按sheet的名字sheet_name可以指定Excel文件读取哪个sheet，如果不指定，默认读取第一个。 我的脚本代码： excel1 = pd.read_excel('C:\\Users\\HP\\Downloads\\返利应收明细表-旧表.xlsx') # 迁移前文件路径 excel2 = pd.read_excel('C:\\Users\\HP\\Downloads\\返利应收明细表-新表.xlsx') # 迁移后文件路径2、存入DataFrame中 DataFrame是Pandas定义的一个二维数据结构，我们得将数据存入到DataFrame才能调用pandas的一些方法对数据进行操作，DataFrame的结构如下图：
大概一看，这个结构是不是类似我们的excel和数据库的表，没错，我们就可以这么理解。 横向的称作行（row），我们所说的一条数据就是指其中的一行； 纵向的称作列（column）或者字段，是一条数据的某个值； 第一行是表头，或者叫字段名，类似于Python字典里的键，代表数据的属性； 第一列是索引，就是这行数据所描述的主体，也是这条数据的关键； 在一些场景下，表头和索引也称为列索引和行索引；在df.compare()方法中行索引和列索引顺序是非常重要的，因为df.compare()方法它的原理就是对行索引和列索引确定的唯一值逐一进行比对，如果两份数据的行和列排序不一致，那么你的脚本的输出结果是不可信的。 原文中代码： df1 = pd.DataFrame(file_path1) df2 = pd.DataFrame(file_path2)3、对数据进行排序 sort_values()是pandas里用来进行数据排序的常用方法，它是按数据值的排序，数字按大小顺序，字符按字母顺序，默认排序是升序 # 主键字段 key = ['busi_month','contract_no','contract_version','clause_no','clause_version','supp_code', 'goods_code','acco_basis','goods_code','taskvolume_group_code','suit_scope_code','purc_company_code'] df1 = df1.sort_values(key, ignore_index=True) df2 = df2.sort_values(key, ignore_index=True)这里我是按表的主键先进行排序，再按行索引排序。 ignore_index=True的含义就是对行索引进行升序排序。 4、df.compare()方法对比两份数据 这个没什么多说的，语法也很简单，就是在DataFrame上使用compare()传入对比的DataFrame可进行数据对比，如： df1 = pd.DataFrame({'a': [1, 2], 'b': [5, 6]}) df2 = pd.DataFrame({'a': [0, 2], 'b': [5, 7]}) # 对比数据 df1.compare(df2) ''' a b self other self other 0 1.0 0.0 NaN NaN 1 NaN NaN 6.0 7.0 '''NaN就是空，代表没有差异 5、导出差异数据 将DataFrame导出为Excel格式也很方便，使用DataFrame.to_excel方法即可。要想把DataFrame对象导出，首先要指定一个文件名，这个文件名必须以.xlsx或.xls为扩展名，生成的文件标签名也可以用sheet_name指定。语法如下： # 导出，可以指定文件路径 df.to_excel('path_to_file.xlsx') # 指定sheet名，不要索引 df.to_excel('path_to_file.xlsx', sheet_name='Sheet1', index=False) # 指定索引名，不合并单元格 df.to_excel('path_to_file.xlsx', index_label='label', merge_cells=False)我的脚本代码： # 差异数据导出 time_now = time.strftime("%Y%m%d%H%M", time.localtime()) with pd.ExcelWriter("对比数据" + time_now + ".xlsx") as writer: df1.to_excel(writer, "excel1") df2.to_excel(writer, "excel2") df1.compare(df2, result_names=("excel1", "excel2")).to_excel(writer, "差异数据")最后导出的excel效果
我这里将迁移前、迁移后表数据、差异数据放到了同一个excel的不同sheet中，这样可以方便查看源表数据。大家还可以根据最左侧一列的编号能精准的查到源表的行数据，是不是很方便呢。 常见报错ValueError: Can only compare identically-labeled DataFrame objects 这是大家运行这个脚本时很有可能会遇到的问题
这种报错大部分情况是因为两个表的行索引和列索引不一致，所以如果出现这种报错，你首先应该排查两个excel列的顺序是否一致、行数是否相等。 好了，如果你也觉得本文章对你有用的话，留个小赞再走吧~~~ |
【本文地址】
今日新闻 |
推荐新闻 |