0

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:

enter image description here

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?

Minfetli
  • 303
  • 3
  • 12
  • What did you try? Did you search for other solutions? This question is featured in the pandas tag's wiki page: https://stackoverflow.com/questions/53645882/pandas-merging-101 – Paul H Dec 07 '21 at 16:27

1 Answers1

1

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