基于列名与匹配条件将DataFrame值替换为另一DataFrame对应值

核心思路 #

将第二个映射DataFrame的规则按列拆分，区分离散值匹配和区间匹配两类规则，分别对原DataFrame的对应列做值替换：

• 离散值规则（Var0、Var1这类）直接转成映射字典，用map方法匹配
• 区间规则（Var2这类）先将区间字符串转为pandas的Interval对象，构建区间映射后匹配值

完整可运行代码 #

import pandas as pd

# 构造第一个原数据DataFrame
df = pd.DataFrame({
    'Var0': [1,1,0,1],
    'Var1': ['a','a','c','d'],
    'Var2': [1,5,8,15]
})

# 构造第二个映射规则DataFrame
rule_df = pd.DataFrame({
    'Variable': ['Var1','Var1','Var1','Var1','Var2','Var2','Var2','Var0','Var0'],
    'Cutoff': ['a','b','c','d','(1, 5]','(6, 10]','(11, 20]','1','0'],
    'BBB': [0.2,0.3,0.8,0.1,0.8,0.1,0.3,0.3,0.5]
})

# 按变量分组处理规则
rule_dict = {}
for var, group in rule_df.groupby('Variable'):
    # 判断是否是区间规则：Cutoff包含括号即为区间
    first_cutoff = group['Cutoff'].iloc[0]
    if '(' in first_cutoff or '[' in first_cutoff:
        # 解析区间字符串为Interval对象
        intervals = []
        bbb_vals = []
        for _, row in group.iterrows():
            # 把字符串如(1,5]转成Interval
            left_str, right_str = row['Cutoff'].strip('()[]').split(',')
            left = float(left_str.strip())
            right = float(right_str.strip())
            closed = 'right' if row['Cutoff'].endswith(']') else 'left'
            intervals.append(pd.Interval(left, right, closed=closed))
            bbb_vals.append(row['BBB'])
        # 构建区间到BBB的映射
        rule_dict[var] = pd.Series(bbb_vals, index=pd.IntervalIndex(intervals))
    else:
        # 离散值直接转字典，同步匹配原列的数据类型避免匹配失败
        rule_dict[var] = dict(zip(group['Cutoff'].astype(type(df[var].iloc[0])), group['BBB']))

# 替换原DataFrame的值
result = df.copy()
for col in result.columns:
    if col in rule_dict:
        rule = rule_dict[col]
        if isinstance(rule, pd.Series) and isinstance(rule.index, pd.IntervalIndex):
            # 区间匹配
            result[col] = result[col].map(lambda x: rule[rule.index.contains(x)].iloc[0])
        else:
            # 离散值匹配
            result[col] = result[col].map(rule)

print(result)

运行输出 #

Var0  Var1  Var2
0   0.3   0.2   0.8
1   0.3   0.2   0.8
2   0.5   0.8   0.1
3   0.3   0.1   0.3

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