如何在Python中实现Excel的Index/Match功能合并DataFrame

问题根源 #

你之前的merge仅基于Sector Code单条件匹配，未结合df1中的SP500/SP400/SP600标识，导致每个行业代码对应的所有指数代码都被关联，产生多行冗余结果。正确做法是同时匹配Sector Code和指数标识两个条件，确保每行只得到对应指数的代码。

以下分几种常见数据结构场景给出解决方案：

场景1：df1是宽格式（含SP500/SP400/SP600布尔列），df2是宽格式（对应各指数代码列） #

df1通过布尔值标记每行所属指数，df2每行对应一个行业代码的全量指数代码：

import pandas as pd
import numpy as np

# 示例数据
df1 = pd.DataFrame({
    'RIC': ['ABC.N', 'DEF.N', 'GHI.N'],
    'Sector Code': [101, 102, 101],
    'SP500': [True, False, False],
    'SP400': [False, True, False],
    'SP600': [False, False, True]
})

df2 = pd.DataFrame({
    'Code': [101, 102],
    'Name': ['科技', '金融'],
    'SP500_Code': ['XLV', 'XLF'],
    'SP400_Code': ['IJK', 'IJL'],
    'SP600_Code': ['LMN', 'LMO']
})

# 把df2转成行业代码到指数代码的映射字典
code_map = df2.set_index('Code').to_dict('index')

# 根据布尔列匹配对应指数代码
conditions = [df1['SP500'], df1['SP400'], df1['SP600']]
values = [
    df1['Sector Code'].map(lambda x: code_map[x]['SP500_Code']),
    df1['Sector Code'].map(lambda x: code_map[x]['SP400_Code']),
    df1['Sector Code'].map(lambda x: code_map[x]['SP600_Code'])
]

df1['匹配指数代码'] = np.select(conditions, values, default=np.nan)

场景2：df1是长格式（含指数标识列），df2是长格式（每行对应行业+指数的代码） #

df1有一列直接标记所属指数名称，df2每行是行业代码+指数名称的唯一组合：

# 示例数据
df1 = pd.DataFrame({
    'RIC': ['ABC.N', 'DEF.N', 'GHI.N'],
    'Sector Code': [101, 102, 101],
    '指数标识': ['SP500', 'SP400', 'SP600']
})

df2 = pd.DataFrame({
    'Code': [101, 101, 101, 102, 102, 102],
    '指数名称': ['SP500', 'SP400', 'SP600', 'SP500', 'SP400', 'SP600'],
    '指数代码': ['XLV', 'IJK', 'LMN', 'XLF', 'IJL', 'LMO']
})

# 同时匹配行业代码和指数标识两个条件
merged_df = df1.merge(
    df2,
    left_on=['Sector Code', '指数标识'],
    right_on=['Code', '指数名称'],
    how='left'
)

# 清理多余列
merged_df = merged_df.drop(columns=['Code', '指数名称'])

场景3：df1是宽格式，df2是长格式 #

先将df1转成长格式，再执行匹配：

# 把df1的宽格式转成长格式，只保留标记为True的指数
df1_long = df1.melt(
    id_vars=['RIC', 'Sector Code'],
    value_vars=['SP500', 'SP400', 'SP600'],
    var_name='指数名称',
    value_name='是否匹配'
)
df1_long = df1_long[df1_long['是否匹配']].drop(columns='是否匹配')

# 双条件merge
merged_df = df1_long.merge(
    df2,
    left_on=['Sector Code', '指数名称'],
    right_on=['Code', '指数名称'],
    how='left'
)

# 如需转回宽格式：
merged_df = merged_df.pivot(
    index=['RIC', 'Sector Code'],
    columns='指数名称',
    values='指数代码'
).reset_index()

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