Joining or merging two DataFrames based on index and column value-CodePudding

I have two DataFrames as follow:

df1 = pd.DataFrame({'Group1': [0.5,5,3], 'Group2' : [2,.06,0.9]}, index=['Dan','Max','Joe'])
df2 = pd.DataFrame({'Name' : ['Joe','Max'], 'Team' : ['Group2','Group1']})

My goal is to get the right value for the Name of the person considering the the column 'Team'.

So the result should look something like this:

I tried it with a merge but I failed because I don't know how to merge on these conditions.

What's the best way in Python to reach my goal?

CodePudding user response：

You can unstack df1, reset its indices, rename columns and merge on Name and Team:

out = (df1.unstack()
       .reset_index()
       .rename({'level_0':'Team', 'level_1':'Name', 0:'Value'}, axis=1)
       .merge(df2, on=['Name','Team']))

Output:

     Team Name    0
0  Group1  Max  5.0
1  Group2  Joe  0.9