Identify value based on matching Rows and Columns across two Dataframes-CodePudding

I'm very new to python and I have two dataframes... I'm trying to match the "Names" aka the columns of dataframe 1 with the rows of dataframe 2 and collect the value for the year 2022 with the hopeful output looking like Dataframe 3... I've tried looking through other queries but not found anything to help, any help would be greatly appreciated!

Dataframe 1 - Money          Dataframe 2             Dataframe 3
Date Alex Rob Kev  Ben       Name                    Name   Amount
2022  29  45  65   12        James                   James  
2021  11  32  11   19        Alex                    Alex   29
2019  45  12  22   76        Carl                    Carl 
                             Rob                     Rob    45
                             Kev                     Kev    65

CodePudding user response：

There are many different ways to achieve this.

One option using map:

s = df1.set_index('Date').loc[2022]

df2['Amount'] = df2['Name'].map(s)

output:

    Name  Amount
0  James     NaN
1   Alex    29.0
2   Carl     NaN
3    Rob    45.0
4    Kev    65.0

Using merge:

s = df1.set_index('Date').loc[2022]

df3 = df2.merge(s.rename('Amount'), left_on='Name', right_index=True, how='left')