基于多条件匹配使用Pandas填充DataFrame字段的技术求助

现有三个Pandas DataFrame：df1、df2、df3，数据结构如下：

df1 #

year    state   bound
2027    CA      low_stat
2027    CA      low_re
2027    NY      med_stat
2027    NY      med_re

df2 #

year    qtr    state type   low_stat low_re med_stat med_re high_stat   high_re
2027    2027Q1  NY   AA     5        6      0        1      3           4
2027    2027Q1  CA   AA     1        4      5        4      1           4
2027    2027Q2  NY   AA     3        6      4        16     56          1
2027    2027Q2  CA   AA     11       2      3        2      3           2
2027    2027Q1  NY   BB     1        2      3        4      3           2
2027    2027Q1  CA   BB     9        3      2        2      3           2
2027    2027Q2  NY   BB     3        1      4        1      5           6
2027    2027Q2  CA   BB     9        5      2        5      3           2

df3（待填充） #

year    state    qtr      low_stat_AA low_re_AA low_stat_BB low_re_BB med_stat_AA med_re_AA med_stat_BB med_re_BB
2027    CA       2027Q1                             
2027    CA       2027Q2                             
2027    NY       2027Q1                             
2027    NY       2027Q2 

期望填充结果 #

year    state   qtr     low_stat_AA low_re_AA low_stat_BB low_re_BB med_stat_AA med_re_AA   med_stat_BB med_re_BB
2027    CA      2027Q1  1           4         9           3             
2027    CA      2027Q2  11          2         9           5             
2027    NY      2027Q1                                              0           1            3          4
2027    NY      2027Q2                                              4           16           4          1

需求说明 #

根据df1的year、state、bound，结合df2的year、qtr、state、type匹配，将df2对应bound列的值填充到df3的对应列（如low_stat_AA对应type为AA、bound为low_stat的值）。此前尝试的pd.merge(df2,df3, on = 'year','state','type')未达预期，需可行的Pandas解决方案。

无需使用OpenPyXL，纯Pandas即可实现，步骤如下：

1. 提取各州需保留的bound字段
   从df1按state分组，获取每个州对应的bound列表：

state_bounds = df1.groupby('state')['bound'].apply(list).to_dict()
# 输出：{'CA': ['low_stat', 'low_re'], 'NY': ['med_stat', 'med_re']}

2. 重塑df2结构，适配df3列名格式
   将df2的type转为列后缀，生成与df3匹配的列名：

# 将df2转为长格式，提取bound和对应值
df2_melted = df2.melt(
    id_vars=['year', 'qtr', 'state', 'type'],
    var_name='bound',
    value_name='value'
)
# 拼接bound与type，生成df3的目标列名
df2_melted['col_name'] = df2_melted['bound'] + '_' + df2_melted['type']
# 转回宽格式，此时列名与df3完全对应
df2_wide = df2_melted.pivot_table(
    index=['year', 'state', 'qtr'],
    columns='col_name',
    values='value'
).reset_index()

3. 按规则过滤并填充df3
   通过合并与列过滤，实现按需填充：

# 合并df3与处理后的df2，确保行对齐
merged = df3.merge(df2_wide, on=['year', 'state', 'qtr'], how='left')

# 遍历每个州，仅保留该州需要的列值，其余列置空
for state, bounds in state_bounds.items():
    # 生成当前州需要保留的列名（每个bound对应AA、BB后缀）
    keep_cols = [f"{b}_{t}" for b in bounds for t in ['AA', 'BB']]
    # 获取当前州的行索引
    state_rows = merged['state'] == state
    # 非保留列置空
    for col in merged.columns:
        if col not in ['year', 'state', 'qtr'] + keep_cols:
            merged.loc[state_rows, col] = None

# 调整列顺序与原df3一致，得到最终结果
df3_final = merged[df3.columns]

执行后，df3_final即为期望的填充结果。

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