查找两个熊猫之间的值，如果找到则进行计算

Question

我有两个数据帧，一个是选择标准，另一个是结果。我需要查找结果，看看它们是否匹配选择，如果这样执行计算，我相信我可能使用了错误的方法，而它让我接近答案，我需要第二行应该有16.0的结果，但显示0.0,PS希望我用代码格式化正确，然后为结果块引号?欢呼声

import pandas as pd
import numpy as np
Sel = {'ID':[123,123,123,123,123],
'M': [2,2,2,2,2],
'R': [1,1,3,4,5],
'S': [10,10,1,2,3],
'BT': ['W','P','W','W','W'],
'A': [10.0,10.0,10.0,10.0,10.0]

}
df_sel = pd.DataFrame(Sel, columns = ['ID','M', 'R', 'S','BT','A'])
print (df_sel)

ID  M  R   S BT     A
0  123  2  1  10  W  10.0
1  123  2  1  10  P  10.0
2  123  2  3   1  W  10.0
3  123  2  4   2  W  10.0
4  123  2  5   3  W  10.0

Res = {#'ID':[123,123,123,123,123],
'M': [2,2,2,2,2],
'R': [1,3,1,4,5],
'S': [10,10,10,7,6],
'BT': ['W','P','P','W','W'],
'A': [3.6,1.6,1.5,5.6,6.0]
}
df_res = pd.DataFrame(Res, columns = ['M', 'R', 'S', 'BT','A'])
print (df_res)

M  R   S BT    A
0  2  1  10  W  3.6
1  2  3  10  P  1.6
2  2  1  10  P  1.5
3  2  4   7  W  5.6
4  2  5   6  W  6.0

# create a list of our conditions
conditions = [
(df_sel['M'] == df_res['M']) & (df_sel['R'] == df_res['R']) & (df_sel['BT'] == df_res['BT']) & (df_sel['S'] == df_res['S']),
(df_sel['M'] == df_res['M']) & (df_sel['R'] == df_res['R']) & (df_sel['BT'] == df_res['BT']) & (df_sel['S'] != df_res['S'])
]
# create a list of the values we want to assign for each condition
values = [df_sel['A']*df_res['A'],0.0]
# create a new column and use np.select to assign values to it using our lists as arguments
df_sel['result'] = np.select(conditions, values)
# display updated DataFrame
df_sel.head()

ID   M   R   S   BT  A   result
0 123 2   1   10  W   10.0    36.0
1 123 2   1   10  P   10.0    0.0
2 123 2   3   1   W   10.0    0.0
3 123 2   4   2   W   10.0    0.0
4 123 2   5   3   W   10.0    0.0

Answer 1

您可以对所有列使用与DataFrame.merge的左连接，因此对于不匹配的缺失值，在Series.mul方法中用0替换，使用DataFrame.pop并删除列A_:

df = df_sel.merge(df_res, on=['M','R','BT','S'], how='left', suffixes=('','_'))
df['result'] = df['A'].mul(df.pop('A_'), fill_value=0)
print (df)
ID  M  R   S BT     A  result
0  123  2  1  10  W  10.0    36.0
1  123  2  1  10  P  10.0    15.0
2  123  2  3   1  W  10.0     0.0
3  123  2  4   2  W  10.0     0.0
4  123  2  5   3  W  10.0     0.0