数据比对python自动化+tkinter操作界面配置

本文示例程序的功能是将来自两个系统的每台设备的多个相同指标进行比对，筛出不一致的项。使用python编写自动化比对程序、经过几个版本的完善优化、评估应该可以在后续稳定执行后，再通过tkinter配置图形化操作界面：选择输入文件、维护参数、指定结果输出路径，点击执行等待执行成功即可，类似一个小工具、方便交接给其他非技术背景的同事。仅做个人积累记录使用（mac OS系统，其他文章如未特意说明，则均默认windows环境），如有侵权或不合规请及时联系处理~ 

因数据涉敏就没法放数据样式了，请自行脑补

---

目录

1、数据比对主程序

2、tkinter操作界面配置

---

1、数据比对主程序

该主程序的工作思路大致分为如下几步：

（1）获取待比对数据清单1、清单2，作为程序的输入；--path_1、path_2，输入文件全路径名称

（2）分别摘出两个清单中唯一标识关联失败的记录，以及关联成功但存在一对多、多对一、多对多的重复记录，这些记录无法直接参与比对，单独摘出后输出两个表格，每个表格两个sheet页，记录两个清单中关联失败记录及重复记录，单独分析处理；--path_4、par1，输出路径、文件命名

（3）针对剩余的一对一关联成功记录，拼接两个清单中的必要字段（主要是两个清单中相同的指标、指标名亦相同需重命名用以区分）、调整字段顺序、创建每对指标值是否一致的校验字段；

（4）根据比对结果清单创建统计表格，统计每个指标比对总条数、一致的条数、不一致的条数、一致率百分比，作为输出结果问题清单的sheet1；--path_4、par2，输出路径、文件命名

（5）读取用以追溯问题根本原因需要用到的辅助业务记录明细清单（可以是任何你想整合的内容），作为输出结果问题清单的sheet2；--path_3，输入文件全路径名称

（6）根据比对结果清单，剔除每对指标值均一致的记录（1条记录多对指标），仅保留存在不一致问题的记录，作为输出结果问题清单的sheet3；

该段主程序代码块，将在下节tkinter操作界面配置程序中被原封不动打包成一个def函数，命名fun()，需特别注意几个参数：path_1、path_2、path_3、path_4、par1、par2，操作界面通过选择文件路径、初始化文件命名等操作，将参数传给底层代码，可以在代码块中找到这几个参数所在的位置。

（如果你并不想配置操作界面、而是直接运行该段程序也是没问题的，将这几个参数申明一下就可以了，path_1、path_2、path_3是pandas读取excel文件的全路径名称，path_4是输出位置上层文件夹的路径，par1、par2是输出文件带后缀的命名，path_4+斜杠+par1或par2即为pandas保存excel文件的全路径名称）

该注释的代码行均进行了简单标注，方便友邻裁剪使用

start =time()

#老系统读数据
t1 = pd.read_excel(path_1,converters={u'线路所属单位编码':str})--读取过程中转换某列数据类型

t1.columns=t1.columns.str.replace(' ','')--删除列名中的空格
bs1=t1['线路代码/安装位置码']+t1['线路名称/安装位置名称']--唯一标识
bs11=t1['线路所属单位编码']+t1['线路代码/安装位置码']+t1['线路名称/安装位置名称']
t1.insert(0,'线路标识1',bs11)--插入第一列
t1.insert(0,'线路标识',bs1)

#新系统读数据
t2= pd.read_excel(path_2,sheet_name='sheet1')--获取表格列
t2.drop(t2.index, inplace=True)

table = pd.read_excel(path_2,sheet_name=None)
sheet_name=list(table.keys())

#依次读取分sheet页保存的数据记录，整合到t2
for j in sheet_name:  
    print("正在读取 " + j)
    #sheetdata=table[j]  
    sheetdata=pd.read_excel(path_2,sheet_name=j,converters={u'局厂代码':str})                        
    t2= pd.concat([t2, sheetdata], ignore_index=True)

bs2=t2['安装位置代码']+t2['安装位置名称']
bs21=t2['局厂代码']+t2['安装位置代码']+t2['安装位置名称']
t2.insert(0,'线路标识1',bs21)
t2.insert(0,'线路标识',bs2)


#---开始比对前检查的分割线---------------------------------------------

#匹配失败检查
mgi=pd.merge(t1['线路标识'].drop_duplicates(),t2['线路标识'].drop_duplicates(),how='inner')--取交集，t1和t2里都有的记录标识
t1_sh3=t1[~(t1['线路标识'].isin(mgi['线路标识']))]--根据交集取反，t1有t2没有，无法参与比对需单独摘出来
t2_sh3=t2[~(t2['线路标识'].isin(mgi['线路标识']))]--根据交集取反，t2有t1没有，无法参与比对需单独摘出来


#重复检查(剔除匹配失败记录后)
v1=t1[~(t1['线路标识'].isin(t1_sh3['线路标识']))]['线路标识'].value_counts()--t1和t2都有的记录标识在t1里统计频次
v2=t2[~(t2['线路标识'].isin(t2_sh3['线路标识']))]['线路标识'].value_counts()--t1和t2都有的记录标识在t2里统计频次

mgo=pd.merge(t1[t1['线路标识'].isin(v1[v1>1].index)]['线路标识'],t2[t2['线路标识'].isin(v2[v2>1].index)]['线路标识'],how='outer')--t1和t2都有的记录标识，分别在t1、t2里出现次数不止一次的记录，即重复记录，取交集
mgo.drop_duplicates(inplace=True)--去重后获取重复记录的标识清单

t1_sh2=t1[t1['线路标识'].isin(mgo['线路标识'])]--t1涉及重复记录的清单，无法参与比对需单独摘出来
t2_sh2=t2[t2['线路标识'].isin(mgo['线路标识'])]--t2涉及重复记录的清单，无法参与比对需单独摘出来

#剩余一对一匹配清单
t1_sh10=t1[~(t1['线路标识'].isin(t1_sh2['线路标识']) | t1['线路标识'].isin(t1_sh3['线路标识']))]
t2_sh10=t2[~(t2['线路标识'].isin(t2_sh2['线路标识']) | t2['线路标识'].isin(t2_sh3['线路标识']))]



#--#用线路标识1再过滤一遍---------------------------------------------------------

#匹配失败检查
mgi1=pd.merge(t1_sh2['线路标识1'].drop_duplicates(),t2_sh2['线路标识1'].drop_duplicates(),how='inner')
t1_sh31=t1_sh2[~(t1_sh2['线路标识1'].isin(mgi1['线路标识1']))]
t2_sh31=t2_sh2[~(t2_sh2['线路标识1'].isin(mgi1['线路标识1']))]

t1_sh3= pd.concat([t1_sh3, t1_sh31], ignore_index=True)
t2_sh3= pd.concat([t2_sh3, t2_sh31], ignore_index=True)


#重复检查(剔除匹配失败记录后)
v11=t1_sh2[~(t1_sh2['线路标识1'].isin(t1_sh31['线路标识1']))]['线路标识1'].value_counts()
v21=t2_sh2[~(t2_sh2['线路标识1'].isin(t2_sh31['线路标识1']))]['线路标识1'].value_counts()

mgo1=pd.merge(t1_sh2[t1_sh2['线路标识1'].isin(v11[v11>1].index)]['线路标识1'],t2_sh2[t2_sh2['线路标识1'].isin(v21[v21>1].index)]['线路标识1'],how='outer')
mgo1.drop_duplicates(inplace=True)

t1_sh21=t1_sh2[t1_sh2['线路标识1'].isin(mgo1['线路标识1'])]
t2_sh21=t2_sh2[t2_sh2['线路标识1'].isin(mgo1['线路标识1'])]


#剩余一对一匹配清单
t1_sh11=t1_sh2[~(t1_sh2['线路标识1'].isin(t1_sh21['线路标识1']) | t1_sh2['线路标识1'].isin(t1_sh31['线路标识1']))]
t2_sh11=t2_sh2[~(t2_sh2['线路标识1'].isin(t2_sh21['线路标识1']) | t2_sh2['线路标识1'].isin(t2_sh31['线路标识1']))]

t1_sh1= pd.concat([t1_sh10, t1_sh11], ignore_index=True)
t2_sh1= pd.concat([t2_sh10, t2_sh11], ignore_index=True)


#疑似重复及关联失败清单，执行比对前需摘出来另外分析处理
file_name1=path_4+'/'+par1
writer1 = pd.ExcelWriter(file_name1, engine='xlsxwriter')

t1_sh21.to_excel(writer1,sheet_name='疑似重复-老系统',index=False)
t2_sh21.to_excel(writer1,sheet_name='疑似重复-新系统',index=False)
t1_sh3.to_excel(writer1,sheet_name='关联失败-老系统',index=False)
t2_sh3.to_excel(writer1,sheet_name='关联失败-新系统',index=False)

writer1.save()
print('已生成 疑似重复及关联失败清单')


#---开始指标比对的分割线------------------------------------------------------

#待比对表格t1里需用到的字段
ls1=['线路标识','线路标识1','元件在用小时数','可用系数','可用小时','运行系数','运行小时','暴露率','计划停运系数','计划停运小时','非计划停运系数','非计划停运小时',
'强迫停运系数','强迫停运小时','设备台年数','计划停运率','计划停运次数','非计划停运率','非计划停运次数','强迫停运率','强迫停运次数','连续可用小时']

#待比对表格t2里需用到的字段
ls2=['线路标识','线路标识1','局厂名称','下属单位名称','安装位置代码','安装位置名称','统计期间小时','可用系数','可用小时','运行系数','运行小时','暴露率','计划停运系数','计划停运小时','非计划停运系数','非计划停运小时',
'强迫停运系数','强迫停运小时','设备百台年数','计划停运率','计划停运次数','非计划停运率','非计划停运次数','强迫停运率','强迫停运次数','连续可用小时']

#输出比对结果清单的字段及排序
ls=['线路标识','线路标识1','局厂名称','下属单位名称','安装位置代码','安装位置名称',
'old统计期间小时','new统计期间小时','old可用系数','new可用系数','可用系数是否一致','old可用小时','new可用小时',
'old运行系数','new运行系数','运行系数是否一致','old运行小时','new运行小时',
'old暴露率','new暴露率','暴露率是否一致',
'old计划停运系数','new计划停运系数','计划停运系数是否一致','old计划停运小时','new计划停运小时',
'old非计划停运系数','new非计划停运系数','非计划停运系数是否一致','old非计划停运小时','new非计划停运小时',
'old强迫停运系数','new强迫停运系数','强迫停运系数是否一致','old强迫停运小时','new强迫停运小时',
'old设备台年数','new设备台年数',
'old计划停运率','new计划停运率','计划停运率是否一致','old计划停运次数','new计划停运次数',
'old非计划停运率','new非计划停运率','非计划停运率是否一致','old非计划停运次数','new非计划停运次数',
'old强迫停运率','new强迫停运率','强迫停运率是否一致','old强迫停运次数','new强迫停运次数',
'old连续可用小时','new连续可用小时','连续可用小时是否一致']

#t1字段重命名
dic1={'线路标识':'线路标识',
      '线路标识1':'线路标识1',
      '元件在用小时数':'old统计期间小时',
      '可用系数':'old可用系数',
      '可用小时':'old可用小时',
      '运行系数':'old运行系数',
      '运行小时':'old运行小时',
      '暴露率':'old暴露率',
      '计划停运系数':'old计划停运系数',
      '计划停运小时':'old计划停运小时',
      '非计划停运系数':'old非计划停运系数',
      '非计划停运小时':'old非计划停运小时',
      '强迫停运系数':'old强迫停运系数',
      '强迫停运小时':'old强迫停运小时',
      '设备台年数':'old设备台年数',
      '计划停运率':'old计划停运率',
      '计划停运次数':'old计划停运次数',
      '非计划停运率':'old非计划停运率',
      '非计划停运次数':'old非计划停运次数',
      '强迫停运率':'old强迫停运率',
      '强迫停运次数':'old强迫停运次数',
      '连续可用小时':'old连续可用小时'
      }

#t2字段重命名
dic2={'线路标识':'线路标识',
      '线路标识1':'线路标识1',
      '局厂名称':'局厂名称',
      '下属单位名称':'下属单位名称',
      '安装位置代码':'安装位置代码',
      '安装位置名称':'安装位置名称',
      '统计期间小时':'new统计期间小时',
      '可用系数':'new可用系数',
      '可用小时':'new可用小时',
      '运行系数':'new运行系数',
      '运行小时':'new运行小时',
      '暴露率':'new暴露率',
      '计划停运系数':'new计划停运系数',
      '计划停运小时':'new计划停运小时',
      '非计划停运系数':'new非计划停运系数',
      '非计划停运小时':'new非计划停运小时',
      '强迫停运系数':'new强迫停运系数',
      '强迫停运小时':'new强迫停运小时',
      '设备百台年数':'new设备台年数',
      '计划停运率':'new计划停运率',
      '计划停运次数':'new计划停运次数',
      '非计划停运率':'new非计划停运率',
      '非计划停运次数':'new非计划停运次数',
      '强迫停运率':'new强迫停运率',
      '强迫停运次数':'new强迫停运次数',
      '连续可用小时':'new连续可用小时'
      }


#一致性校验函数  
def Diff(df,col1,col2,diff):
    df[diff]= np.where((round(df[col2],3)-round(df[col1],3))==0,1,0)--两列指标值保留3位小数后比较是否相同
    return df


#针对新老系统一对一匹配上的数据记录，对老系统表格字段进行筛选、重命名
df10=t1_sh10.loc[:,ls1]
df10.rename(columns=dic1, inplace=True)
df11=t1_sh11.loc[:,ls1]
df11.rename(columns=dic1, inplace=True)

#针对新老系统一对一匹配上的数据记录，对新系统表格字段进行筛选、重命名
df20=t2_sh10.loc[:,ls2]
df20.rename(columns=dic2, inplace=True)
df21=t2_sh11.loc[:,ls2]
df21.rename(columns=dic2, inplace=True)

#union后创建校验字段，对新老系统每对指标进行一致性校验
df00 = pd.merge(df10,df20,how='inner',on='线路标识')
df00=Diff(df00,'new可用系数','old可用系数','可用系数是否一致')
df00=Diff(df00,'new运行系数','old运行系数','运行系数是否一致')
df00=Diff(df00,'new暴露率','old暴露率','暴露率是否一致')
df00=Diff(df00,'new计划停运系数','old计划停运系数','计划停运系数是否一致')
df00=Diff(df00,'new非计划停运系数','old非计划停运系数','非计划停运系数是否一致')
df00=Diff(df00,'new强迫停运系数','old强迫停运系数','强迫停运系数是否一致')
df00=Diff(df00,'new计划停运率','old计划停运率','计划停运率是否一致')
df00=Diff(df00,'new非计划停运率','old非计划停运率','非计划停运率是否一致')
df00=Diff(df00,'new强迫停运率','old强迫停运率','强迫停运率是否一致')
df00=Diff(df00,'new连续可用小时','old连续可用小时','连续可用小时是否一致')
df00.rename(columns={'线路标识1_x':'线路标识1'}, inplace=True)
df00.drop('线路标识1_y',axis=1,inplace=True)

df01 = pd.merge(df11,df21,how='inner',on='线路标识1')
df01=Diff(df01,'new可用系数','old可用系数','可用系数是否一致')
df01=Diff(df01,'new运行系数','old运行系数','运行系数是否一致')
df01=Diff(df01,'new暴露率','old暴露率','暴露率是否一致')
df01=Diff(df01,'new计划停运系数','old计划停运系数','计划停运系数是否一致')
df01=Diff(df01,'new非计划停运系数','old非计划停运系数','非计划停运系数是否一致')
df01=Diff(df01,'new强迫停运系数','old强迫停运系数','强迫停运系数是否一致')
df01=Diff(df01,'new计划停运率','old计划停运率','计划停运率是否一致')
df01=Diff(df01,'new非计划停运率','old非计划停运率','非计划停运率是否一致')
df01=Diff(df01,'new强迫停运率','old强迫停运率','强迫停运率是否一致')
df01=Diff(df01,'new连续可用小时','old连续可用小时','连续可用小时是否一致')
df01.rename(columns={'线路标识_x':'线路标识'}, inplace=True)
df01.drop('线路标识_y',axis=1,inplace=True)


df0= pd.concat([df00, df01], ignore_index=True)


#调整字段顺序
sh1=df0.loc[:,ls]


#创建新老系统指标一致率汇总表格
sh2 = pd.DataFrame(columns=['合计','一致','不一致','一致占比'],index=['可用系数','运行系数','暴露率','计划停运系数','非计划停运系数','强迫停运系数','计划停运率','非计划停运率','强迫停运率','连续可用小时'])
sh2['合计']=sh1['线路标识'].shape[0]--比对总条数

for idx in sh2.index:
    col=idx+'是否一致'
    sh2['一致'][idx]= sh1[col].value_counts()[1]--一致的条数
    sh2['不一致'][idx]= sh2['合计'][idx]-sh1[col].value_counts()[1]--不一致的条数
    sh2['一致占比'][idx]=sh1[col].value_counts()[1]/sh2['合计'][idx]--一致率

sh2.insert(0,'指标',sh2.index)

#筛选新老系统每对指标不一致的数据记录
q1=sh1
v1=q1[q1['可用系数是否一致']==0]
v2=q1[q1['运行系数是否一致']==0]
v3=q1[q1['暴露率是否一致']==0]
v4=q1[q1['计划停运系数是否一致']==0]
v5=q1[q1['非计划停运系数是否一致']==0]
v6=q1[q1['强迫停运系数是否一致']==0]
v7=q1[q1['计划停运率是否一致']==0]
v8=q1[q1['非计划停运率是否一致']==0]
v9=q1[q1['强迫停运率是否一致']==0]
v10=q1[q1['连续可用小时是否一致']==0]

#取并集
q=q1[q1['线路标识'].isin(v1['线路标识']) | q1['线路标识'].isin(v2['线路标识']) | q1['线路标识'].isin(v3['线路标识']) | q1['线路标识'].isin(v4['线路标识']) | q1['线路标识'].isin(v5['线路标识']) | q1['线路标识'].isin(v6['线路标识']) | q1['线路标识'].isin(v7['线路标识']) | q1['线路标识'].isin(v8['线路标识']) | q1['线路标识'].isin(v9['线路标识']) | q1['线路标识'].isin(v10['线路标识'])]


#读取运行事件记录，用以追溯、分析出现不一致问题的原因以及定位哪个系统计算有问题   
y = pd.read_excel(path_3)
    

#保存问题清单excel
file_name2=path_4+'/'+par2
writer2 = pd.ExcelWriter(file_name2, engine='xlsxwriter')
sh2.to_excel(writer2,sheet_name='统计',index=False)--问题清单sheet1
y.to_excel(writer2,sheet_name='运行事件2019',index=False)--问题清单sheet2
q.to_excel(writer2,sheet_name='不一致问题筛选',index=False)--问题清单sheet3

workbook = writer2.book

#设置excel格式并导出，字体、字号、居中  
fmt = workbook.add_format({'font_size': 10, 
                           'font_name': u'宋体',
                           'valign': 'vcenter',
                           'align': 'center'})

#样式
color_fmt = workbook.add_format({'bg_color': '#FFC7CE'})--标红底
percent_fmt = workbook.add_format({'num_format': '0.00%'})--百分比格式
border_fmt = workbook.add_format({'border': 1})--单元格框线


#是否一致校验字段的位置 
l=[10,15,20,23,28,33,40,45,50,55]

#高亮显示是否一致校验字段，方便一眼看到
s1=writer2.sheets['不一致问题筛选']       
for col_num, value in enumerate(q.columns.values.take(l)):
        s1.write(0, l[col_num], value, color_fmt)


s2 = writer2.sheets['统计']

#设置列宽、格式
s2.set_column('A:A', 15, fmt)
s2.set_column('B:E', 10, fmt)

#条件设置高亮，标出一致率小于100%的项
s2.conditional_format('E2:E11',
                      {'type': 'cell',
                       'criteria': '<',
                       'value': 1,
                       'format': color_fmt})


#设置一致率这一列为百分比格式
s2.conditional_format('E2:E11',                  
                      {'type': 'no_blanks',
                       'format': percent_fmt})

#添加单元格框线
s2.conditional_format('A1:E11',
                      {'type': 'no_blanks', 
                       'format': border_fmt})


writer2.save()
print('已生成 问题清单')


end = time()
print('Running time: %s Seconds'%(end-start))

2、tkinter操作界面配置

操作界面经简单设计后配置如下：

对应代码实现如下所示，可自行去搜索了解 Python GUI编程(Tkinter)，本文不做说明。

该注释的代码行均进行了简单标注，方便友邻裁剪使用

import pandas as pd
import numpy as np
from time import *
import os.path
from tkinter import *
from tkinter.filedialog import *


#输入文件1选择，传给数据比对主程序
def selectPath1():
    global path_1
    path_ = askopenfilename()
    path1.set(path_)
    path_1 = path_

#输入文件2选择，传给数据比对主程序
def selectPath2():
    global path_2
    path_ = askopenfilename()
    path2.set(path_)
    path_2 = path_
    
#输入文件3选择，传给数据比对主程序
def selectPath3():
    global path_3
    path_ = askopenfilename()
    path3.set(path_)
    path_3 = path_
    
#输出路径4选择，传给数据比对主程序
def selectPath4():
    global path_4
    path_ = askdirectory()
    path4.set(path_)
    path_4 = path_

    
#数据比对主程序    
def fun():
    #上节主程序代码块原封不动放在这里就行，篇幅原因，这里就不放了#
    

#执行成功提示、输出文件名维护
def discriminate():
    global result,par1,par2
    par1 = str(parameter1.get())--获取parameter1，将par1传给数据比对主程序
    par2 = str(parameter2.get())
    fun()    
    result.set('执行结束，结果已输出到指定路径！')
    
#开始执行   
root = Tk()
#标题
root.title('架空线路可靠性指标校验程序')
#不允许改变窗口大小
root.resizable(False, False)
root.focusmodel()
#定义字符串
path1 = StringVar()
path2 = StringVar()
path3 = StringVar()
path4 = StringVar()
parameter1 = StringVar()
parameter2 = StringVar()
result = StringVar()

#第零行
Label(root,text='新老系统单台指标计算结果比对',height = 2,font='Helvetic 15').grid(row = 0,columnspan=4)

#第一行
Label(root,text='老系统单台计算结果表格',height = 2,
      width=25,justify='right').grid(row = 1,column = 0)
Entry(root,textvariable = path1,width =35).grid(row = 1,column = 1)
Label(root,text=' ').grid(row = 1,column = 2)
Button(root,text = '文件选择',command = selectPath1).grid(row = 1,column = 3)
a = Label(root,text=' ').grid(row = 1,column = 4)

#第二行
Label(root,text='新系统单台计算结果表格',height = 2,
      width=25,justify='right').grid(row = 2,column = 0)
Entry(root,textvariable = path2,width =35).grid(row = 2,column = 1)
Label(root,text=' ').grid(row = 2,column = 2)
Button(root,text = '文件选择',command = selectPath2).grid(row = 2,column = 3)
Label(root,text=' ').grid(row = 2,column = 4)

#第三行
Label(root,text='运行事件表格',height = 2,
      width=25,justify='right').grid(row = 3,column = 0)
Entry(root,textvariable = path3,width =35).grid(row = 3,column = 1)
Label(root,text=' ').grid(row = 3,column = 2)
Button(root,text = '文件选择',command = selectPath3).grid(row = 3,column = 3)
Label(root,text=' ').grid(row = 3,column = 4)

#第四行
Label(root,text='比对结果输出路径：',height = 2,
      width=25,justify='right').grid(row = 4,column = 0)
Entry(root,textvariable = path4,width =35).grid(row = 4,column = 1)
Label(root,text=' ').grid(row = 4,column = 2)
Button(root,text = '路径选择',command = selectPath4).grid(row = 4,column = 3)
Label(root,text=' ').grid(row = 4,column = 4)

#第五行
Label(root,text='重复及关联失败表格命名：',height = 2,
      width=25,justify='right').grid(row = 5,column = 0)
Entry(root,textvariable = parameter1,width =35).grid(row = 5,column = 1)
parameter1.set('疑似重复及关联失败清单.xlsx')--设置默认值，也可在界面修改

#第六行
Label(root,text='不一致表格命名：',height = 2,
      width=25,justify='right').grid(row = 6,column = 0)
Entry(root,textvariable = parameter2,width =35).grid(row = 6,column = 1)
parameter2.set('问题清单.xlsx')--设置默认值，也可在界面修改

#第七行
result_str = Label(root,textvariable=result,justify='left').grid(row = 7,column = 1)
Button(root,text = '开始执行',command = discriminate, justify = 'center').grid(row = 7,column = 3)

#第八行
Label(root,text='',height = 1).grid(row = 8,column = 0)

root.mainloop()