多个excel表数据比对操作
多个excel表数据比对操作
本文主要使用两种方法进行比对,分别使用了openpyxl第三方库和pandas第三方库进行数据比对
两种方法优缺点:
openpyxy:
优点:主要是处理xlsx的文件,里面方法简单,易懂
缺点:当数据量大的时候,速度很慢,之前我一条一条数据拿出来比较,两百多条数据花了三个多小时,目前经过优化,速度上得到大幅度提升-约1分钟内,主要是一列一列的数据拿出来做比较,不在一条一条比较,这样速度得到了提升,但是没有之前直接获取某一列某一行的值做比对方便,需要考虑行数和列数!
pandas:
优点:可以处理xls、xlsx等多种文件,且速度很快
缺点:比较难懂,没有openpyxl那样丰富多样化
下面展示代码openpyxy:
class DoExcel_xlsx:def __init__(self,file_name1,sheet_name1,file_name2,sheet_name2):try:self.file_name = file_name1# self.file_name2 = file_name2# self.sheet_name = sheet_name1# self.sheet_name2 = sheet_name2self.workbook = openpyxl.load_workbook(file_name1)self.workbook2 = openpyxl.load_workbook(file_name2)self.sheet = self.workbook[sheet_name1]self.sheet2 = self.workbook2[sheet_name2]except Exception as e:print('case文件格式有误:{}'.format(e))def get_case(self):#创建一个列表,里面存放A列的数据ii1 = []ii2=[]for i in self.sheet['A']:ii1.append(i.value)for i in self.sheet2['A']:ii2.append(i.value)print(ii1)print(ii2)max_row = self.sheet.max_rowmax_row2 = self.sheet2.max_rowx = 1for r in range(2,len(ii1)+1):product_id = self.sheet.cell(row= r, column= 1).value #获取基金代码gzjz = self.sheet.cell(row= r, column= 10).valueprint('表1:----{}'.format(product_id))for i in range(1,len(ii2)+1):product = self.sheet2.cell(row=i, column=1).valueprint('表2--{}'.format(product))print(product)if product_id == product :gzjz2 = self.sheet2.cell(row=i, column=10).valueshang = self.sheet2.cell(row=i, column=8).valuexia = self.sheet2.cell(row=i, column=9).valueif gzjz == gzjz2:print('没问题')continueelif gzjz != gzjz2:self.write_result(x,1,product_id)self.write_result(x,2,gzjz)self.write_result(x, 3, product)self.write_result(x, 4, gzjz2)x = x+1continuecontinueif product_id not in ii2 :self.write_result(x,1,product_id)self.write_result(x, 2, gzjz)x = x+1continueself.workbook.close()def write_result(self,row,col,productid):sheet = self.workbook['sheet2']sheet.cell(row,col).value = productid# sheet.cell(row,col).value = resultself.workbook.save(filename=self.file_name)在这里插入代码片
这里面,先把主列数据拿出来存做列表,通过两张表比对,找出存在A表但是不存在B表的数据,还有共同数据中不同的净值,然后新建一张sheet表存入数据,PS:这里这能通过找到A表中存在但是B表不存在的数据和共同数据,如果需要找到B表存在但是A表不存在的数据,需要把两张表顺序颠倒再运行一次
pandas代码如下:
class pandas_xls:def __init__(self,file_name1,sheet_name1,file_name2,sheet_name2):#按str读取文件,不做数据转换df1 = pd.read_excel(file_name1, dtype='str',sheet_name=sheet_name1)df2 = pd.read_excel(file_name2, dtype='str',sheet_name=sheet_name2)# print(df1.iloc[0]) #读取第一行,也可使用 df1.iloc[0]# print(df1['基金代码']) #读取基金代码该列# row = 0li1 = df1['基金代码'].valuesli2 = df2['基金代码'].values# print(type(li1))# print(li2)# print(li1+li2)a = list(set(list(li1)+list(li2))-set(li1))print(a)b = list(set(list(li1)+list(li2))-set(li2))print(b)c = []d = []a1 = []b1 = []c1 = []d1 = []for r in list(set(li1).intersection(set(li2))): # set(li1).intersection(set(li2))集合运算-交集rows_with_data1 = df1[df1['基金代码'] == r]rows_with_data2 = df2[df2['基金代码'] == r]row1 = rows_with_data1.index[0] # 特定数据所在行# print(row1)row2 = rows_with_data2.index[0] ## print(row2)try:gzsy1 = df1.loc[row1, '单位净值-估值']except KeyError as e:gzsy1 = Noneprint(gzsy1)try:gzsy2 = df2.loc[row2, '单位净值-估值']except KeyError as e:gzsy2 = Noneprint(gzsy2)if gzsy1 != gzsy2 :a1.append(r)b1.append(r)c1.append(gzsy1)d1.append(gzsy2)if a: #找出df2中存在的数据print('只在df2中存在的数据{}'.format(a))for i in a:rows_with_data = df2[df2['基金代码'] == i]row = rows_with_data.index[0] # 特定数据所在行print('df2中:{}数据所在行{}'.format(i, row))# 查找这行数据的特定列的值try:gzsy2 = df2.loc[row, '单位净值-估值']except KeyError as e:# if pd.isnull(df2.loc[row, '单位净值-估值']):gzsy2 = None# else:# print('检查{}数据是否有问题'.format(row))print('gzsy2:{}'.format(gzsy2))c.append(gzsy2)if b:print('只在df1中存在的数据{}'.format(b))print(b)for i in b:rows_with_data = df1[df1['基金代码'] == i]row = rows_with_data.index[0] # 特定数据所在行print('df1中:{}数据所在行{}'.format(i, row))# 查找这行数据的特定列的值try:gzsy1 = df1.loc[row, '单位净值-估值']except KeyError as e:# if pd.isnull(df1.loc[row, '单位净值-估值']):gzsy1 = None# else:# print('检查{}数据是否有问题'.format(row))print('gzsy1:{}'.format(gzsy1))d.append(gzsy1)# for r in range(len(a)):# b.append(None)b[0:0] = [None]*len(a)d[0:0] = [None]*len(a)df = pd.DataFrame.from_dict({'估值3基金代码': a1 + a,'万份收益-估值3': c1+c,'估值6基金代码': b1+b,'万份收益-估值6': d1+d,}, orient='index') #创建dataframe数据表df = df.transpose()writer = pd.ExcelWriter('比对数据.xlsx') # 创建ExcelWrite对象df.to_excel(writer)writer.close()
这里,逻辑和上方一样:也是获取相关列,然后数据进行比对,,但是做了一些为空的处理,而且可以找出两张表中分别不存在的数据依次存入新表中,
推荐使用第二种pandas方法