Pandas合并两个DataFrame并补全参考表中缺失的维度组合行

核心逻辑 #

你需要先构造所有维度的全量组合骨架，再匹配原始数据、标记来源即可得到预期结果，具体步骤如下：

• 提取df中gr1f的所有唯一取值，和reference_df的所有维度行做笛卡尔积，得到所有需要覆盖的行结构
• 将全量维度骨架和原始df左连接，匹配已有的数值列，缺失数据自动填充为NaN
• 新增source列，根据数值列是否为空标记行来源
• 按业务要求排序后输出

完整代码 #

import pandas as pd

# 你的原始数据（可直接复用已有定义）
df = pd.DataFrame({'gr1f': ['A','A','B','A','B','A','B'],
                   'gr2p': ['CC','CC','CC','CC','CC','CC','CC'],
                   'gr3a': ['AL','AL','AL','DEL','DEL','DEL','DEL'],
                   'DP': [ 'l1yrs', 'l10yrs','l10yrs','l1yrs','l1yrs','l10yrs','l10yrs'],
                    'num_vals1' :[
                                1.138044999,
                                1.221786568,
                                1.7,
                                1.159030763,
                                1.2,
                                1.5,
                                1.238341765 ],
                    'num_vals2' :[
                                  0.166637328,
                                  0.04513741,
                                  0.12,
                                  0.141340498,
                                  0.87,
                                  0.033441602,
                                  0.01233441602]})

reference_df = pd.DataFrame({
                   'gr2p': ['CC','CC','CC','CC','DD','DD','DD','DD'],
                   'gr3a': ['AL','AL','AL','AL','DEL','DEL','DEL','DEL'],
                   'DP': ['l1yrs', 'l1yrs', 'l10yrs','l10yrs','l1yrs','l1yrs','l10yrs','l10yrs']}).drop_duplicates()

# 1. 构造全量维度组合（pandas 1.2.0+支持cross参数）
gr1f_df = pd.DataFrame({'gr1f': df['gr1f'].unique()})
full_dim = gr1f_df.merge(reference_df, how='cross')

# 低版本pandas兼容写法，替换上面两行即可
# gr1f_list = df['gr1f'].unique()
# reference_df['tmp'] = 1
# full_dim = pd.DataFrame({'gr1f': gr1f_list, 'tmp':1}).merge(reference_df, on='tmp').drop('tmp', axis=1)

# 2. 左连接匹配原始数据
res = full_dim.merge(df, on=['gr1f', 'gr2p', 'gr3a', 'DP'], how='left')

# 3. 标记数据来源
res['source'] = res['num_vals1'].notna().map({True: 'df', False: 'reference_df'})

# 4. 按要求排序
res = res.sort_values(by=['gr2p', 'gr3a', 'DP', 'gr1f'], ascending=[True, True, False, True]).reset_index(drop=True)

运行后输出的res和你给出的预期结果完全一致。

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