Pandas DataFrame多条件行内乘法优化：变量名不匹配处理

这是《Internally Multiply all pd.dataframe rows based on multiple conditions》的后续问题，当前数据集结构有变化，导致上一问题的索引方案无法适用。

DataFrame示例（通过Pandas导入xlsx文件） #

Model    Scenario    Region  Variable     Unit    Year1    Year2    ...    Year50
  1        Base        1        GDP      M USD     10       15               20  
  1        Base        2        GDP      M USD     30       35               50  
  1        Base        3        GDP      M USD     20       75               80  
  1        Stress 1    1       GDP 1     % diff    0.48    0.11             0.31  
  1        Stress 1    2       GDP 2     % diff    0.12    0.33             0.89  
  1        Stress 1    3     GDP Comb    % diff    0.76    0.54             0.08  
  1        Stress 2    1       GDP 1     % diff    0.37    0.94             0.13  
  1        Stress 2    2       GDP 2     % diff    0.73    0.76             0.35  
  1        Stress 2    3     GDP Comb    % diff    0.15    0.45             0.37  
  1        Stress 3    1       GDP 1     % diff    0.49    0.14             0.37  
  1        Stress 3    2       GDP 2     % diff    0.14    0.73             0.94  
  1        Stress 3    3     GDP Comb    % diff    0.96    0.26             0.85  

数据说明 #

• 每个压力场景的数值是对应区域基准场景的百分比变化，与上一问题不同的是，压力场景和基准场景的Variable列名称不匹配，行数也不同
• 计算逻辑：基准值 * (1 + 压力值)

补充说明 #

原始DataFrame包含更多模型、场景、区域和变量，但结构统一：所有模型的场景集合一致，所有场景的区域集合一致等。

目标 #

将压力场景的数值转换为与基准场景相同的单位，执行上述乘法计算。

计算公式示例 #

Model    Scenario    ...    Year1          Year2    ...     Year50
  1      Stress 1        10*(1+0.48)    15*(1+0.11)        20*(1+0.31) 

输出示例 #

Model    Scenario    ...    Year1          Year2    ...     Year50
  1      Stress 1           14.8           16.65             26.2 

已尝试方案 #

用df.loc做多条件匹配计算：

test_df.loc[((test_df['Model'] == '1') & (test_df['Scenario'] == 'Stress1') & (test_df['Region'] == "1") & (test_df['Variable'] == 'GDP'))] = test_df.loc[((test_df['Model'] == '1') & (test_df['Scenario'] == 'Base') & (test_df['Region'] == "1") & (test_df['Variable'] == 'GDP'))] * (1 + test_df.loc[((test_df['Model'] == '1') & (test_df['Scenario'] == 'Stress1') & (test_df['Region'] == "1") & (test_df['Variable'] == 'GDP'))])

优化方向 #

这种多条件操作效率不高，基于索引的乘法更合适，这也是上一问题的解决方案。

上一问题的解决方案 #

years = [Year1,Year2,Year50]
df1 = (test_df[(test_df['Scenario'] == 'Base')]
             .set_index(['Model','Region','Variable'])[years])
df2 = (test_df[(test_df['Unit'] == '% diff')]
             .set_index(['Model','Scenario','Region','Variable','Unit'])[years])
df = df2.add(1).mul(df1).reset_index()

当前问题 #

如何修改上述代码片段，适配场景间Variable列名称不匹配的情况？

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