Pandas中基于公共Position列按可变列数迭代生成跨表合并新DataFrame

场景说明 #

存在3个带公共关联列Position的源DataFrame，需要把三个表中同一维度的跨表列基于Position关联合并，批量生成N个独立的新DataFrame。方案可适配任意正整数N的列数变化，三个源表非关联列数量保持一致即可运行，不需要手动逐列编写合并代码。

首先定义测试用示例源数据：

import pandas as pd

df1 = pd.DataFrame({
    "Position": ["A", "B", "C"], 
    "Team1": ["xyz", "xyy", "xxy"],
    "Team2": ["xxz", "yyx", "yxy"],
    "Team3": ["xzy", "zzy", "zxz"]
})
df2 = pd.DataFrame({
    "Position": ["A", "B", "C"],
    "T1": ["1", "2", "4"],
    "T2": ["3", "5", "2"],
    "T3": ["2","1","4"]
})
df3 = pd.DataFrame({
    "Position": ["A", "B", "C"],
    "T_1": ["IN", "IN", "OUT"],
    "T2": ["IN", "OUT", "OUT"],
    "T3": ["OUT","IN","IN"]
})

实现逻辑 #

• 提取所有源表的非Position业务列，按列存储顺序建立一一对应映射，不依赖具体列名匹配
• 逐组遍历对应列索引，从每个源表中截取Position和当前索引对应的列，基于公共列做内连接合并
• 将所有合并结果存入列表，即可批量得到每个维度对应的新DataFrame

可复用实现代码 #

def batch_merge_by_key(df_list, key_col="Position"):
    # 提取各源表的非关联列，校验列数一致性
    non_key_cols = [df.drop(columns=[key_col]).columns.tolist() for df in df_list]
    col_lengths = {len(cols) for cols in non_key_cols}
    assert len(col_lengths) == 1, "所有源表的非关联列数量必须一致"

    merged_dfs = []
    total_group = col_lengths.pop()
    # 逐组遍历同维度列做合并
    for group_idx in range(total_group):
        # 截取每个源表的关联键和当前组的目标列
        sub_frames = [
            df[[key_col, cols[group_idx]]]
            for df, cols in zip(df_list, non_key_cols)
        ]
        # 基于关联键逐次合并
        merged = sub_frames[0]
        for sub in sub_frames[1:]:
            merged = merged.merge(sub, on=key_col, how="inner")
        merged_dfs.append(merged)
    return merged_dfs

# 调用方式
generated_dfs = batch_merge_by_key([df1, df2, df3])

输出效果说明 #

针对示例数据，调用后会生成3个新DataFrame：

1. 第1组对应列（df1.Team1、df2.T1、df3.T_1）合并结果：

   Position	Team1	T1	T_1
   A	xyz	1	IN
   B	xyy	2	IN
   C	xxy	4	OUT

2. 第2组对应列（df1.Team2、df2.T2、df3.T2）合并结果：

   Position	Team2	T2_x	T2_y
   A	xxz	3	IN
   B	yyx	5	OUT
   C	yxy	2	OUT

3. 第3组对应列（df1.Team3、df2.T3、df3.T3）合并结果：

   Position	Team3	T3_x	T3_y
   A	xzy	2	OUT
   B	zzy	1	IN
   C	zxz	4	IN

注：当多个源表存在同名列时，pandas merge操作会自动为同名列添加_x、_y后缀区分列归属，无需手动重命名

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