Loop over list and use values based on condition in dataframe

Question

I have the following:

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)

which results in the following output:

    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

Separately I have a list of three series.

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

which looks like that

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

I want to create a column in the data frame called "result" where a formula is applied. Rating (from the dataframe) * match rating (from the list) + const (from the list) When "value" in dataframe is 1 the first set of elements from the list should be applied, i.e. 50 and 2. When "value" from dataframe is 2 the second set of elements should be applied, i.e. 48 and 2 and so forth.

Desired result is like that:

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

Thanks in advance

BENY · Accepted Answer · 2019-08-28 19:58:56Z

3

Here is one solution using merge after concat

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

answered Aug 28, 2019 at 19:58

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

harpan · Accepted Answer · 2019-08-28 20:24:56Z

You can do it with 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']]

Output:

      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 DataFrame off of the list in the question.

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"])]))

Andy L. · Accepted Answer · 2019-08-28 21:22:48Z

You could do it with a custom dictionary from the list and map and str accessor to do calculation:

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

Collectives™ on Stack Overflow

Loop over list and use values based on condition in dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related