用Python写一个Excel汇总和比对小程序

用Python写一个Excel汇总和比对小程序

---

前言

         最近由于工作需要，要多次比对两张Excel表里面的内容变化（有相同的行列索引），并将变化的单元格标注出来，所以想自己写个比对程序出来。
         虽然网上有类似工具，Python也有company库用来比对，但是都不怎么符合我的要求，索引还是自己写一个。这里我不详细介绍所有代码，重点介绍一下比对的思路和开发过程中遇到的一些坑。

一、错误思路

         我需要的是比对每一个单元格里面的内容是否发生变化，所以一开始我的想法就是根据行列索引遍历每个单元格进行比对，就像下面这样：

import openpyxl
import time

wb1 = openpyxl.load_workbook("./cs/1.xlsm")
wb2 = openpyxl.load_workbook("./cs/2.xlsm")

sheet1 = wb1.active
sheet2 = wb2.active

nrows = sheet1.max_row
ncols = sheet1.max_column
print(nrows)
print(ncols)

num = 1

start = time.time()
for row in range(1,nrows):
    for col in range(1,ncols):
        if sheet1.cell(row,col).value == sheet2.cell(row,col).value: #这里两张表是复制的，所以用相等
            # print("旧：%s---新：%s"%(sheet1.cell(row,col).value,sheet2.cell(row,col).value)) 
            pass
        print(num)
        num += 1
print(time.time()-start)

         我用的Excel表数据是3000行32列的，可以看出总共遍历循环96000次，用时1s多，但是我每次比对数据至少在120000条左右，也就是说我光循环比对至少40s左右，这还不算读取和最后写入的时间，效率太低。
         而且，这种方法还要一个前提，我必须提前将数据表处理好，使每一行都相互对应，如果出现新增和删减就没法得出结果，所有这种方法显然行不通。

二、使用Pandas的矢量化操作加速

1.先明白pandas处理大型数据集的一些经验法则

• 尝试尽可能使用矢量化操作，而不是在df 中解决for x的问题。如果你的代码是许多for循环，那么它可能更适合使用本机Python数据结构，因为Pandas会带来很多开销。
• 如果你有更复杂的操作，其中矢量化根本不可能或太难以有效地解决，请使用.apply方法。
• 如果必须循环遍历数组（确实发生了这种情况），请使用.iterrows()或.itertuples()来提高速度和语法。
• Pandas有很多可选性，几乎总有几种方法可以从A到B。请注意这一点，比较不同方法的执行方式，并选择在项目环境中效果最佳的路线。
• 一旦建立了数据清理脚本，就可以通过使用HDFStore存储中间结果来避免重新处理。
• 将NumPy集成到Pandas操作中通常可以提高速度并简化语法。

这里建议去看看《还在抱怨pandas运行速度慢？这几个方法会颠覆你的看法》这篇文章，对pandas 使用有很大启发。

2.正确的比对思路

1. 首先我们需要清楚要找出的不一样的内容包括哪些

这里假如有新旧两个表，且这两个表中的列名完全一致，也都有相同的行索引【唯一标识码】，相同的【唯一标识码】对应相同的数据。

因为涉及到增加和减少，那么两个表中就可能出现三种情况：

• 数据减少了，那么旧表有新表没有的【唯一标识码】
• 数据增加了，那么新表有旧表没有的【唯一标识码】
• 【唯一标识码】相同的数据，里面内容发生变化了

2.得到两个表之间的交集、差集

import pandas as pd
import time

df1 = pd.read_excel("./cs/1.xlsm",index_col="唯一标识码")
df2 = pd.read_excel("./cs/2.xlsm",index_col="唯一标识码")

#将【唯一标识码】列设置为索引
df1.index = df1["唯一标识码"]
df2.index = df2["唯一标识码"]

index1 = df1.index
index2 = df2.index

jiaoJi = list(set(index1).intersection(set(index2)))  #两个列表中相同的项
chaJi1 = list(set(index1).difference(set(index2)))    #旧表不同于新表的数据，既旧表有，新表没有
chaJi2 = list(set(index2).difference(set(index1)))    #新表不同于旧表的数据，既新表有，旧表没有

print(jiaoJi[:10])
print(chaJi1[:10])
print(chaJi2[:10])

1.xlsx表中【唯一标识码】从1-3000,2.xlsx表中【唯一标识码】从1501-4500。

得出差集1和差集2就可以很容易通过行索引得出新增和减少的数据了

df_chaji1 = df1.loc[chaJi1]
df_chaji2 = df2.loc[chaJi2]
print(df_chaji1)
print(df_chaji2)

3.重点：【唯一标识码】相同部分怎么判断里面内容是否发生变化

1.举个例子

这里先给大家举个简单的案列，这里有如下的表格数据，我们要找出列1和列2数据不同的地方

df = pd.read_excel("./test.xlsx",index_col="唯一标识码")
res = df["列1"] == df["列2"]
print(res)

大家可以发现，在dataframe数据可以直接使用列与列进行比较，返回bool值，很方便。
那么我们两个表中的数据怎么进行列与列的比较呢？

2.对两个表的列名进行处理

首先，我们将两个表的列名进行重设，表1的列名就是【列名-1】，表2的列名就是【列-2】

columns = df1.columns.tolist()
columns1 = list(map(lambda x: x+"-1",columns))
columns2 = list(map(lambda x: x+"-2",columns))

df_jiaoji1 = df1.loc[jiaoJi]
df_jiaoji2 = df2.loc[jiaoJi]

df_jiaoji1.columns = columns1
df_jiaoji2.columns = columns2

print(df_jiaoji1)
print(df_jiaoji2)

3.将两个表进行横向合并

df_jiaoji_total = pd.concat([df_jiaoji1,df_jiaoji2],axis=1)  #axis=1表示横向合并
print(df_jiaoji_total[["分类编码-1","分类编码-2"]])

怎么样，看到这里知道我为什么要举上面的例子了把，就是为了能通过列直接比较。思路清楚了那就直接上代码：

4.通过dataframe直接进行相同列名的数据比对

start = time.time()
#比对所有同种类型的列的数据是否相等，得到一个bool值的series的迭代对象，并保存到ser_list列表中
ser_list = map(lambda x,y : df_jiaoji_total[x] == df_jiaoji_total[y] ,self.columns1,self.columns2)

#将每个series转换成dataframe数据类型，并用最早的列名进行设置（也可以不用，但是为了方便观察结果还是设置）
df_list = map(lambda x,y : x.to_frame(y),ser_list,self.columns)

#横向拼接所有的dataframe数据，数据为TRUE表示这个单元格比对结果相同，False表示不同
compare_df = pd.concat(df_list,axis=1)

#过滤所有数据都是TRUE的行的索引值，表示比对后前后结果相同，不需要记录
compare_index = list(filter(lambda x : bool(1-all(compare_df.loc[x])) , compare_df.index))

#根据最终得到的索引值可以确定哪些数据比对结果存在不同
#compare_bool用于定位哪些单元格出现差异
#compare_result则是存在差异的原始数据
self.compare_bool = compare_df.loc[compare_index]
self.compare_result = self.df2.loc[compare_index]

#将compare_bool的行、列索引设置成序号类型，方便写入时定位单位元格位置
nrows = compare_bool.shape[0]
ncols = compare_bool.shape[1]
compare_bool.index = range(nrows)
compare_bool.columns = range(ncols)

print(compare_bool)
print(compare_result)
print(time.time()-start)

大家可以很明显发现，遍历确定1500行31列的数据差异只需要0.25s，相较于之前的1.2s快了不知多少，而且在数据量越大的情况下越明显，有兴趣的可以测试一下。

5.数据的写入

这里我用openpyxl库来进行写入，主要是设置单元格格式比较简单，因为我们要将变化的数据的单元格填充为黄色

import openpyxl
from openpyxl.styles import PatternFill
start = time.time()
wb = openpyxl.Workbook()
ws_jiaoJi = wb.create_sheet("相同资产标识码内容发生变化",0)
# #创建单元格样式,填充为黄色,solid表示填充实色，不加不显示
fill = PatternFill("solid",fgColor="FFFF00")

#先把列名写入
ws_jiaoJi.append(columns)

#用compare_bool作为遍历对象，如果值为FALSE，表示数据发生变化了，单元格就填充为黄色
for i,r in compare_bool.iterrows():  
    for x,y in enumerate(r):
        if y == False:
            ws_jiaoJi.cell(row=i+2,column=x+1).fill = fill
        #用compare_result来遍历写入具体数据
        ws_jiaoJi.cell(row=i+2,column=x+1,value=compare_result.iloc[i,x])

wb.save("./compare.xlsx")
 
print(time.time()-start)

可以看到正确将比对结果输出为Excel表了

但这里不得不吐槽一下，写入的速度是真的慢，希望有高人指点一下可以怎么加快。
另外，如果大家还有更好的办法，希望可以不吝赐教。

源码下载