如何编写循环合并同名称工作表的DataFrame字典（左连接）

问题描述 #

我有两个包含多张工作表的Excel文件，已通过pd.read_excel()将它们读取为DataFrame字典：

my_first_file = pd.read_excel(my_path, sheet_name=None, skiprows=2)
my_second_file = pd.read_excel(my_path, sheet_name=None, skiprows=2)

我希望编写一个循环，对两个字典中名称相同的工作表执行左连接（left merge），以此模拟Excel中VLOOKUP的功能，匹配不到的内容显示为NaN。

数据结构 #

• my_first_file的结构：

{'Sheet_1':     ID     Name  Surname  Grade
 0  104  Eleanor    Rigby      6
 1  168  Barbara      Ann      8
 2  450    Polly  Cracker      7
 3   90  Little       Joe     10,
 'Sheet_2':     ID       Name   Surname  Grade
 0  106       Lucy       Sky      8
 1  128    Delilah  Gonzalez      5
 2  100  Christina   Rodwell      3
 3   40      Ziggy  Stardust      7,
 'Sheet_3':     ID   Name   Surname  Grade
 0   22   Lucy  Diamonds      9
 1   50  Grace     Kelly      7
 2  105    Uma   Thurman      7
 3   29   Lola      King      3}

• my_second_file的结构：

{'Sheet_1':     ID     Name  Surname  Grade favourite color    favourite sport
 0  104  Eleanor    Rigby      6            blue  American football
 1  168  Barbara      Ann      8            pink             Hockey
 2  450    Polly  Cracker      7           black      Skateboarding
 3   90  Little      Josy     10          orange            Cycling,
 'Sheet_2':     ID       Name   Surname  Grade favourite color favourite sport
 0  106       Lucy       Sky      8          yellow          Tennis
 1  128    Delilah     Perez      5     light green      Basketball
 2  100  Christina   Rodwell      3           black       Badminton
 3   40      Ziggy  Stardust      7             red          Squash,
 'Sheet_3':     ID   Name   Surname  Grade favourite color favourite sport
 0   22   Lucy  Diamonds      9           brown            Judo
 1   50  Grace     Kelly      7           white       Taekwondo
 2  105    Uma   Thurman      7          purple      videogames
 3   29   Lola   McQueen      3             red            Surf}

预期效果 #

以Sheet_1为例，合并后结果如下：

{'Sheet_1':     ID      Name  Surname  Concatenation  Grade favourite color  
 0  104  Eleanor     Rigby  Eleanor Rigby      6            blue   
 1  168  Barbara       Ann    Barbara Ann      8            pink   
 2  450    Polly   Cracker  Polly Cracker      7           black   
 3   90   Little       Joe     Little Joe     10             NaN   
 
      favourite sport  
 0  American football  
 1             Hockey  
 2      Skateboarding  
 3                NaN  ,

已编写代码 #

# Importing modules
import openpyxl as op
import pandas as pd
import numpy as np
import xlsxwriter
from openpyxl import Workbook, load_workbook

# Defining the two file paths
path_first_file = r'C:\Users\machukovich\Desktop\stack.xlsx'
path_second_file = r'C:\Users\machukovich\Desktop\stack_2.xlsx'

# Loading the files into a dictionary of Dataframes
dfs_first_file = pd.read_excel(path_first_file, sheet_name=None, skiprows=2)
dfs_second_file = pd.read_excel(path_second_file, sheet_name=None, skiprows=2)

# Creating a new column in each sheet to merge later respectively
for sheet_name, df in dfs_first_file.items():
    df.insert(3, 'Concatenation', df['Name'].map(str) + ' ' + df['Surname'].map(str))

for sheet_name, df in dfs_second_file.items():
    df.insert(3, 'Concatenation', df['Name'].map(str) + ' ' + df['Surname'].map(str))

我知道pd.merge()仅适用于DataFrame，不清楚如何对字典中的同名工作表批量执行该操作，恳请帮助。

解决方案 #

直接遍历两个字典的共同工作表名称，对对应DataFrame执行左连接即可，核心代码如下：

# 初始化字典存储合并后的结果
merged_results = {}

# 遍历两个文件中名称相同的工作表
for sheet_name in dfs_first_file.keys() & dfs_second_file.keys():
    # 获取左右侧对应的DataFrame
    left_df = dfs_first_file[sheet_name]
    right_df = dfs_second_file[sheet_name]
    
    # 过滤右侧DataFrame，只保留需要新增的列和连接键（避免重复列冲突）
    # 这里去掉和左侧重复的ID、Name、Surname、Grade列
    filtered_right = right_df.drop(columns=['ID', 'Name', 'Surname', 'Grade'])
    
    # 执行左连接，以Concatenation为匹配键
    merged_df = pd.merge(left_df, filtered_right, on='Concatenation', how='left')
    merged_results[sheet_name] = merged_df

# 将合并结果写入新的Excel文件
with pd.ExcelWriter('merged_output.xlsx') as writer:
    for sheet, df in merged_results.items():
        df.to_excel(writer, sheet_name=sheet, index=False)

关键步骤说明 #

1. 匹配同名工作表：用集合交集操作dfs_first_file.keys() & dfs_second_file.keys()快速获取两个文件共有的工作表名称，确保只处理需要合并的表。
2. 清理重复列：两个原始表存在重复列（如Grade），直接合并会生成Grade_x、Grade_y这类冗余列，所以提前过滤右侧表的重复列，只保留需要补充的信息（favourite color、favourite sport）和连接键。
3. 左连接实现VLOOKUP效果：how='left'参数会保留左侧表的所有行，匹配右侧表中相同Concatenation的记录，匹配不到的字段自动填充NaN，完全模拟VLOOKUP的行为。
4. 保存结果：用pd.ExcelWriter批量将合并后的DataFrame写入新Excel文件，每个工作表对应原名称。

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