循环列表并根据datafram中的条件使用值

Question

我有以下几点：

dictionary = {'col1': ["England", "USA","Germany","Canada","Cuba","Vietnam"], 'col2': 
          ["France", "Spain","Italy","Jamaica","Bulgaria","South Korea"],
          "rating":[2,4,-5,2,1,2],"value":[1,1,2,2,3,3]}

table=pd.DataFrame(dictionary)

从而产生以下输出：

    col1    col2    rating  value
0   England France      2    1
1   USA     Spain       4    1
2   Germany Italy      -5    2
3   Canada  Jamaica     2    2
4   Cuba    Bulgaria    1    3
5   Vietnam South Korea 2    3

我有三个系列的单子。你知道吗

list([pd.Series([50,2],["const","Match Rating"]),
  pd.Series([48,2],["const","Match Rating"]),
  pd.Series([47,2.5],["const","Match Rating"])])

看起来是这样的

[const  50
 Match Rating     2
 dtype: int64, 
 const  48
 Match Rating     2
 dtype: int64, 
 const   47
 Match Rating     2.5
 dtype: int64]

我想在数据框中创建一个名为“result”的列，其中应用了一个公式。Rating (from the dataframe) * match rating (from the list) + const (from the list) 当dataframe中的“value”为1时，应应用列表中的第一组元素，即50和2。当dataframe的“value”为2时，应应用第二组元素，即48和2，依此类推。你知道吗

预期结果如下：

col1    col2      rating value  result
England France      2     1       54
USA     Spain       4     1       58
Germany Italy      -5     2       38
Canada  Jamaica     2     2       52
Cuba    Bulgaria    1     3       49.5
Vietnam South Korea 2     3       52

提前谢谢

Answer 1

您可以使用列表中的自定义词典和map和str访问器进行计算：

l = [pd.Series([50,2],["const","Match Rating"]),
     pd.Series([48,2],["const","Match Rating"]),
     pd.Series([47,2.5],["const","Match Rating"])]

d = {i: s.to_dict() for i, s in enumerate(l, 1)}
s = table.value.map(d)
table['result'] = table.rating * s.str['Match Rating'] +  s.str['const']    

Out[811]:
      col1         col2  rating  value  result
0  England       France       2      1    54.0
1      USA        Spain       4      1    58.0
2  Germany        Italy      -5      2    38.0
3   Canada      Jamaica       2      2    52.0
4     Cuba     Bulgaria       1      3    49.5
5  Vietnam  South Korea       2      3    52.0

Answer 2

你可以用assign+merge来做。你知道吗

table.assign(val2 = table['value'] - 1)
            .merge(lookup.reset_index(), left_on='val2', right_on='index', how='left')
            .assign(result = lambda x: x['rating'].mul(x['Match Rating']).add(x['const']))
                  [['col1', 'col2', 'rating', 'value', 'result']]

输出：

      col1         col2  rating  value  result
0  England       France       2      1    54.0
1      USA        Spain       4      1    58.0
2  Germany        Italy      -5      2    38.0
3   Canada      Jamaica       2      2    52.0
4     Cuba     Bulgaria       1      3    49.5
5  Vietnam  South Korea       2      3    52.0

lookup问题列表中的数据帧。你知道吗

lookup = pd.DataFrame(list([pd.Series([50,2],["const","Match Rating"]),
  pd.Series([48,2],["const","Match Rating"]),
  pd.Series([47,2.5],["const","Match Rating"])]))

Answer 3

下面是一个在concat之后使用merge的解决方案

s=pd.concat(l,keys=range(1,len(l)+1),axis=1).T.rename_axis('value').reset_index()
table=table.merge(s).assign(result=lambda x : x['Match Rating']*x['rating']+x['const'])
Out[732]: 
      col1         col2  rating  value  const  Match Rating  result
0  England       France       2      1   50.0           2.0    54.0
1      USA        Spain       4      1   50.0           2.0    58.0
2  Germany        Italy      -5      2   48.0           2.0    38.0
3   Canada      Jamaica       2      2   48.0           2.0    52.0
4     Cuba     Bulgaria       1      3   47.0           2.5    49.5
5  Vietnam  South Korea       2      3   47.0           2.5    52.0