Map Values from one dataframe to another based two criteria-CodePudding

I have one dataframe (df) that contains home field advantage values for various conferences. The first columns represents the home teams and the remaining column headers represent the away teams. I would like to create a new column, df1['hfa'] in the second dataframe (df1) that shows the home field advantage located in df for the respective matchups listed in df1. Below are images of the two dataframes. In excel I can achieve this with a two way lookup via Index Match. I would like to map the values from df to df1 for df1['hfa'] so that that they read: 2.5, 3.5, 4.5, etc. Thank you in advance!

I have the following code:

df1['hfa'] = df1['Home Team'].map(df.set_index('Home Team')[])

I am unsure what to use in the final argument to match the Away Team from df1 to the same column header.

CodePudding user response：

Based on what I undesrstand, this is what I have. See if this is what you looking for. Given Data:

(
    df.melt(id_vars=['Home Team'],
            value_vars=['North','East','South','West'],
            var_name='Away Team',
            value_name='hfa')
)