Python fill column based on values in other column (EXCEL INDEX MATCH FOR ROW AND COLUMN LOOKUP)-CodePudding

I would like to fill a new column in a dataframe1 by looking up the values in dataframe2, however the lookup is based on the values in the column of dataframe1.

for example the two columns in dataframe1 that determine the lookup in dataframe2 are:

Lookup	security_type
Country!not_in_list	Equity
Country!empty	FX Forward

columns of dataframe2:

Lookup	Equity	FX Forward
Country!not_in_list	0,561686374	0,194140542
Country!empty	0,743154272	0,684586895

dataframe1 should have a new column called Score with the values looked up in dataframe 2:

Lookup	security_type	Score
Country!not_in_list	Equity	0,561686374
Country!empty	FX Forward	0,684586895

current code:

n=0
for i in dataframe1["Lookup"]:
    dataframe1["Score"]=""
    dataframe1.loc[n, "Score"] = dataframe2.loc[dataframe1["Lookup"][n], 
    [dataframe1["security_type"][n]]].values
    n  = 1

CodePudding user response：

You can melt and merge:

out = df1.merge(df2.melt(id_vars='Lookup',
                         var_name='security_type', value_name='Score'),
                on=['Lookup', 'security_type']
                )

output:

                Lookup security_type        Score
0  Country!not_in_list        Equity  0,561686374
1        Country!empty    FX Forward  0,684586895