使用pandas的compare()方法做excel数据对比_pandas两个表格对比

目录

背景

思路梳理

代码详细讲解

常见报错

---

背景

        最近在测试工作中遇到开发做数据迁移，迁移前后表结构都没变，但是需要对迁移前后的几个表的数据进行校验比对，因为表的字段非常多，用sql和网上的数据对比工具看个人觉得看差异值都不是很直观。因此想到了python的pandas库写个脚本进行数据对比，并输出差异值。在实际工作中检验过了，用于数据对比是真的省时省力太多了。

        这里我只是将mysql表数据导出成excel，再对两份excel数据进行比对。如果可以直连数据库，本脚本也适用，只需要将读取excel部分代码改成连接数据库就行了。

思路梳理

1. 读取excel数据
2. 存入DataFrame中（pandas的二维数据结构）
3. 对数据进行排序
4. df.compare()方法对比两份数据
5. 导出差异数据，并将两份源数据和差异数据放到同一个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列的顺序是否一致、行数是否相等。

好了，如果你也觉得本文章对你有用的话，留个小赞再走吧~~~