Mapping dataframe values from another dataframe with condition

Question

I have df1

df1 = {'Name':['Tom', 'nick', 'krish', 'jack'], 'Age':[20, 21, 19, 18]} 
df1 = pd.DataFrame(df1)

and I have another df2

df2 = {'Name':['krish', 'jack','Tom', 'nick',]}
df2 = pd.DataFrame(df2)

df2['Name'] is exactly same with df1. However, they are in a different order.

I want to fill df2['Age'] based on df1.

If I used df2['Age'] = df1['Age'] the value of is filled but wrong.

How to map those values on df2 from df1 correctly?

Thank you

gbruenjes · Accepted Answer · 2020-01-15 09:07:53Z

1

Use:

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

    Name  Age
0  krish   19
1   jack   18
2    Tom   20
3   nick   21

answered Jan 15, 2020 at 9:07

gbruenjes

4,2251 gold badge18 silver badges32 bronze badges

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

Comments

yatu · Accepted Answer · 2020-01-15 09:18:01Z

0

Set Name as index and reindex based on df2:

df1.set_index('Name').reindex(df2.Name).reset_index()

   Name   Age
0  krish   19
1   jack   18
2    Tom   20
3   nick   21

Or for a better performance, we can use pd.Categorical here:

df1['Name'] = pd.Categorical(df1.Name, categories=df2.Name)
df1.sort_values('Name', inplace=True)

answered Jan 15, 2020 at 9:07

yatu

88.7k12 gold badges93 silver badges148 bronze badges