根据其他数据框中的列值在熊猫数据框中创建列

我有两个熊猫数据框

import pandas as pd 
import numpy as np
import datetime

data = {'group'      :["A","A","B","B"],
        'val': ["AA","AB","B1","B2"],
        'cal1'     :[4,5,7,6],
        'cal2'     :[10,100,100,10]
       } 

df1 = pd.DataFrame(data) 
df1

    group   val    cal1   cal2
0   A       AA     4      10
1   A       AB     5      100
2   B       B1     7      100
3   B       B2     6      10

data = {'group'      :["A","A","A","B","B","B","B", "B", "B", "B"],
        'flag' : [1,0,0,1,0,0,0, 1, 0, 0],
        'var1': [1,2,3,7,8,9,10, 15, 20, 30]
       } 

# Create DataFrame 
df2 = pd.DataFrame(data) 
df2

    group   flag    var1
0   A       1       1
1   A       0       2
2   A       0       3
3   B       1       7
4   B       0       8
5   B       0       9
6   B       0       10
7   B       1       15
8   B       0       20
9   B       0       30

Step 1: CReate columns in df2(with suffix "_new") based on unique "val" in df1 like below:

unique_val = df1['val'].unique().tolist()
new_cols = [t + '_new' for t in unique_val]
for i in new_cols:
    df2[i] = 0
df2
    group   flag    var1    AA_new  AB_new  B1_new  B2_new
0   A       1        1       0      0       0        0
1   A       0        2       0      0       0        0
2   A       0        3       0      0       0        0
3   B       1        7       0      0       0        0
4   B       0        8       0      0       0        0
5   B       0        9       0      0       0        0
6   B       0        10      0      0       0        0
7   B       1        15      0      0       0        0
8   B       0        20      0      0       0        0
9   B       0        30      0      0       0        0

步骤2：对于标志= 1的行，AA_new将计算为var1（来自df2）*对于组“ A”为df1中的“ cal1”值，而val“ AA” *对于“ A”组则为df1中的“ cal2”值”和val“ AA”，类似地，将AB_new计算为var1（来自df2）*对于组“ A”来自df1的'cal1'值，以及val“ AB” * d“对于组” A“从df1获得的'cal2'的值val“ AB”

我的预期输出应如下所示：

    group   flag    var1    AA_new  AB_new  B1_new   B2_new
0   A       1       1       40.0    500.0   0.0      0.0
1   A       0       2       0.0     0.0     0.0      0.0
2   A       0       3       0.0     0.0     0.0      0.0
3   B       1       7       0.0     0.0     4900.0   420.0
4   B       0       8       0.0     0.0     0.0      0.0
5   B       0       9       0.0     0.0     0.0      0.0
6   B       0       10      0.0     0.0     0.0      0.0
7   B       1       15      0.0     0.0     10500.0  900.0
8   B       0       20      0.0     0.0     0.0      0.0
9   B       0       30      0.0     0.0     0.0      0.0

下面基于其他堆栈流问题的解决方案部分起作用：

df2.assign(**df1.assign(mul_cal = df1['cal1'].mul(df1['cal2']))
                .pivot_table(columns='val',
                             values='mul_cal',
                             index = ['group', df2.index])
                .add_suffix('_new')
                .groupby(level=0)
               .apply(lambda x: x.bfill().ffill()) 
                .reset_index(level='group',drop='group')
                .fillna(0)
                .mul(df2['var1'], axis=0)
                .where(df2['flag'].eq(1), 0)
)