Pandas跨Excel工作表对比列值并新增Yes/No匹配标记列

通过Python脚本自动生成含多个工作表的Excel文件，实现跨工作表列值匹配，在目标表新增列返回匹配结果：

• 目标操作表：SvnUsers，需要在该表新增匹配结果列
• 参照对比表：UserDetails
• 匹配规则：取SvnUsers表第1列accountName的每一行值，与UserDetails表第1列Account的所有值匹配；若值存在于参照列，新增列对应行填Yes，不存在填No，新增列名为Svnaccount?
• 之前尝试过直接写入Excel公式的方案，效果不符合预期，需要补全代码中#here code位置的核心处理逻辑。

初始问题代码 #

import pandas as pd
from timestampdirectory import  createdir
import os
import time
def svnanalysis():

    dest = createdir()
    dfSvnUsers = pd.read_excel(os.path.join(dest, "SvnUsers.xlsx"))
    dfSvnGroupMembership = pd.read_excel(os.path.join(dest, "SvnGroupMembership.xlsx"))
    dfSvnRepoGroupAccess = pd.read_excel(os.path.join(dest, "SvnRepoGroupAccess.xlsx"))
    dfsvnReposSize = pd.read_excel(os.path.join(dest, "svnReposSize.xlsx"))
    dfsvnRepoLastChangeDate = pd.read_excel(os.path.join(dest, "svnRepoLastChangeDate.xlsx"))
    dfUserDetails = pd.read_excel(r"D:\GIT-files\Automate-Stats\SVN_sample_files\CM_UsersDetails.xlsx")

    timestr = time.strftime("%Y-%m-%d-")
    xlwriter = pd.ExcelWriter(os.path.join(dest,f'{timestr}Usage-SvnAnalysis.xlsx'))

    dfUserDetails.to_excel(xlwriter, sheet_name='UserDetails',index = False)
    dfSvnUsers.to_excel(xlwriter, sheet_name='SvnUsers', index = False )
    dfSvnGroupMembership.to_excel(xlwriter, sheet_name='SvnGroupMembership', index = False )
    dfSvnRepoGroupAccess.to_excel(xlwriter, sheet_name='SvnRepoGroupAccess', index = False)
    dfsvnReposSize.to_excel(xlwriter, sheet_name='svnReposSize', index = False)
    dfsvnRepoLastChangeDate.to_excel(xlwriter, sheet_name='svnRepoLastChangeDate',index= False)
    xlwriter.close()

    dfUsageSvnAnalysis = pd.read_excel(os.path.join(dest, f'{timestr}Usage-SvnAnalysis.xlsx'), sheet_name='SvnUsers')
    dfUsageSvnAnalysis = pd.read_excel(os.path.join(dest, f'{timestr}Usage-SvnAnalysis.xlsx'), sheet_name='UserDetails')
    dfUsageSvnAnalysis.insert(2,'SVNaccount?',False)
    #here code
       #compare elements of('SvnUsers') sheet column1 with ('SvnUserDetails) column1 sheet
       #In ('SvnUsers') create column [SVNaccount?] and if the elements of column [users] was found on sheet two return Yes if not return NO
    print(dfUsageSvnAnalysis)
svnanalysis()

原有代码存在两个直接导致逻辑失效的问题：

1. 连续两次给dfUsageSvnAnalysis赋值，第二次读取UserDetails表的操作会覆盖第一次读取的SvnUsers表数据，后续列操作全部作用在错误的表上
2. 先写入Excel再重新读取处理的逻辑冗余，平白增加磁盘IO开销，直接在内存中的DataFrame上完成匹配再写入文件效率更高。

修复后完整代码 #

核心匹配逻辑用pandas原生的成员判断实现，不需要依赖Excel公式，所有计算在Python层完成，不会出现打开Excel公式失效的问题：

import pandas as pd
from timestampdirectory import  createdir
import os
import time
def svnanalysis():

    dest = createdir()
    dfSvnUsers = pd.read_excel(os.path.join(dest, "SvnUsers.xlsx"))
    dfSvnGroupMembership = pd.read_excel(os.path.join(dest, "SvnGroupMembership.xlsx"))
    dfSvnRepoGroupAccess = pd.read_excel(os.path.join(dest, "SvnRepoGroupAccess.xlsx"))
    dfsvnReposSize = pd.read_excel(os.path.join(dest, "svnReposSize.xlsx"))
    dfsvnRepoLastChangeDate = pd.read_excel(os.path.join(dest, "svnRepoLastChangeDate.xlsx"))
    dfUserDetails = pd.read_excel(r"D:\GIT-files\Automate-Stats\SVN_sample_files\CM_UsersDetails.xlsx")

    # ========== 核心匹配逻辑 开始 ==========
    # 提取参照表Account列的有效值存为集合，查询速度远快于列表
    valid_account_set = set(dfUserDetails['Account'].dropna().str.strip().unique())
    # 插入结果列，逐行判断accountName是否在有效账号集合中，映射为Yes/No
    dfSvnUsers.insert(
        loc=2,
        column='Svnaccount?',
        value=dfSvnUsers['accountName'].apply(lambda x: 'Yes' if str(x).strip() in valid_account_set else 'No')
    )
    # ========== 核心匹配逻辑 结束 ==========

    timestr = time.strftime("%Y-%m-%d-")
    output_file = os.path.join(dest, f'{timestr}Usage-SvnAnalysis.xlsx')
    xlwriter = pd.ExcelWriter(output_file, engine='openpyxl')

    dfUserDetails.to_excel(xlwriter, sheet_name='UserDetails', index=False)
    dfSvnUsers.to_excel(xlwriter, sheet_name='SvnUsers', index=False)
    dfSvnGroupMembership.to_excel(xlwriter, sheet_name='SvnGroupMembership', index=False)
    dfSvnRepoGroupAccess.to_excel(xlwriter, sheet_name='SvnRepoGroupAccess', index=False)
    dfsvnReposSize.to_excel(xlwriter, sheet_name='svnReposSize', index=False)
    dfsvnRepoLastChangeDate.to_excel(xlwriter, sheet_name='svnRepoLastChangeDate', index=False)
    xlwriter.close()

    # 打印结果校验
    print(pd.read_excel(output_file, sheet_name='SvnUsers'))

svnanalysis()

逻辑说明 #

• 对参照列和目标列的值都做了strip()去空格、dropna()空值过滤处理，避免因为前后空格、空值导致匹配错误
• 用集合存储参照值，数据量大时匹配速度比直接用列表快数十倍
• 所有计算在写入Excel前完成，不需要依赖Excel端的公式计算，跨设备打开文件不会出现内容缺失、需要启用宏/内容的提示

内容的提问来源于stack exchange，提问作者Champs