Comparing strings in two different dataframe and adding a column

Question

I have two dataframes as follows:

df1 = 

Index        Name       Age
0            Bob1       20
1            Bob2       21
2            Bob3       22

The second dataframe is as follows -

df2 = 

Index       Country      Name
0           US           Bob1
1           UK           Bob123
2           US           Bob234
3           Canada       Bob2
4           Canada       Bob987
5           US           Bob3
6           UK           Mary1
7           UK           Mary2
8           UK           Mary3
9           Canada      Mary65

I would like to compare the names from df1 to the countries in df2 and create a new dataframe as follows:

Index    Country       Name       Age
0         US           Bob1       20
1         Canada       Bob2       21
2         US           Bob3       22

Thank you.

Muhteva · Accepted Answer · 2021-08-11T17:07:15.540

Using merge() should solve the problem.

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

Outcome:

import pandas as pd

df1 = pd.DataFrame({ "Name":["Bob1", "Bob2", "Bob3"], "Age":[20,21,22]})
df2 = pd.DataFrame({ "Country":["US", "UK", "US", "Canada", "Canada", "US", "UK", "UK", "UK", "Canada"], 
                    "Name":["Bob1", "Bob123", "Bob234", "Bob2", "Bob987", "Bob3", "Mary1", "Mary2", "Mary3", "Mary65"]})

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

Comparing strings in two different dataframe and adding a column

1 Answers1