copy column from other dataframe based on matching values

user_7475879 0
Viewed 926

I have two dataFrames, one with iso codes and country names and the other one with only country names.

I would like to add a new column to df2 with the country names from df1 if the values df1.iso == df2.id match.

df1

Country      iso    
Afghanistan  AFG       
Afghanistan  AFG       
Afghanistan  AFG       
...

and df2

id      
AFG     
AFG     
AFG     
AFG
...

I tried this:

post['country'] = pre['Country'].where(pre['iso'] == post['id'])

But I got an error

ValueError: Can only compare identically-labeled Series objects

pandas dataframe where-clause python-3.x
1 Answers

You can use DataFrame.merge to left merge df2 with df1 after dropping duplicate values from df1:

df2 = df2.merge(df1.drop_duplicates(), left_on='id',
                right_on='iso', how='left').drop('iso', 1)

Or, you can use Series.map to map the Country from df1 to df2 based on iso code:

df2['Country'] = df2['id'].map(df1.drop_duplicates().set_index('iso')['Country'])

Result:

print(df2)
    id      Country
0  AFG  Afghanistan
1  AFG  Afghanistan
2  AFG  Afghanistan
3  AFG  Afghanistan
user_128331660
Related