0

For the life of me I can not figure out how to implement the following solution:

Suppose I have a dataframe called df1

   ID  Name  Gender
   0   Bill    M
   1   Adam    M
   2   Kat     F
   1   Adam    M

Then I have another dataframe called df2

   ID  Name   Age
   5as   Sam  34
   1as   Adam 64
   2as   Kat  50

All I want to do is check if ID from df1 is in ID in df2, if so grab the corresponding Age column and attache it to df1.

Ideal Solution:

   ID  Name  Gender  Age
   0   Bill    M
   1   Adam    M     64
   2   Kat     F     50
   1   Adam    M     64

I have implement the following solution which at first I thought it works but realized it was missing matching a lot of values at the end of df. Not sure if it is because of what I wrote or the size of my CSV which is large.

y_list = df2.ID.dropna().unique()

for x in df1.ID.unique():
   if x in y_list:
        df1.loc[df1.ID == x, 'Age'] = df2.Age

Any help is appreciated!

olive
  • 171
  • 1
  • 2
  • 12

2 Answers2

1

Here's what you can do 

df3 = df1.join(df2.set_index('ID'), on='ID', lsuffix='_left')

if you want to join on the 'ID' column.
If however you are looking to join on 'Name', you can change on='Name'. An alternative option is to use merge,

df1.merge(df2, on='Name', how='left')

Output 

    ID  Name_x  Gender  Name_y  Age
0   0   Bill    M   NaN NaN
1   1   Adam    M   Adam    64.0
2   2   Kat F   Kat 50.0
3   1   Adam    M   Adam    64.0

Here's the output when using caller.set_index('ID').join(other.set_index('ID'), lsuffix='_left')

    Name_left   Gender  Name    Age
ID              
0   Bill    M   NaN NaN
1   Adam    M   Adam    64.0
1   Adam    M   Adam    64.0
2   Kat F   Kat 50.0
Vishakha Lall
  • 1,178
  • 12
  • 33
0

You can do as below

name_age_dict = dict(zip(df2['Name'], df2['Age']))
df1['Age'] = df1['Name'].map(name_age_dict).fillna('')

Another method

df1['Age'] = df1['Name'].map(df2.set_index('Name')['Age']).fillna('')

Output

    ID  Name    Gender  Age
0   0   Bill    M   
1   1   Adam    M       64
2   2   Kat     F       50
3   1   Adam    M       64
moys
  • 7,747
  • 2
  • 11
  • 42