How to merge two data frames and handling duplicates

Question

I have two data frames df1 with two columns [firstname] and [City], 100 row df2 with two columns [City] and [Continent], 50 row

i want to merge between the two data frames on the [City] column in order to have this result Firstname/City/Continent and 100 row (same rows as the df1), the remain names without Comun City in both dataframes should be blank

it is like df2 being the dictionary for df1, each time we have a match on the [City] takes the values from [Continent] column in df2 and put in df1

thank you

score 0 · Answer 1 · answered Jul 06 '21 at 13:29

As referenced in the comment, you should use merge. Here's how you would do it with your example:

df1 = pd.DataFrame({
    "name": ["a1", "b1", "c1", "d1"],
    "city" : ["a", "b", "c", "d"]
})
df2 = pd.DataFrame({
    "city": ["a", "b", "c"],
    "continent" : ["CONTINENT1", "CONTINENT2", "CONTINENT1"]
})

df1.merge(df2, on='city', how='outer') # This is what you need

df1


  name  city    continent
0 a1    a       CONTINENT1
1 b1    b       CONTINENT2
2 c1    c       CONTINENT1
3 d1    d       NaN

How to merge two data frames and handling duplicates

1 Answers1