Identify value based on matching Rows and Columns across two Dataframes

Question

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

melt df1 and merge, or convert df1 to Series using the Date you want and merge — mozway
– mozway, Commented Apr 16, 2022 at 19:44

Alias Cartellano · Accepted Answer · 2022-04-17 15:17:42Z

3

There are many different ways to achieve this.

One option is 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

Another option is using merge:

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

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

edited Apr 17, 2022 at 15:17

Alias Cartellano

3621 gold badge3 silver badges13 bronze badges

answered Apr 16, 2022 at 19:54

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Identify value based on matching Rows and Columns across two Dataframes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related