Map some columns of a data frame to other based on two column match pandas

Question

I have two data frame as shown below

df1:

Sector     Plot    Price     Count
A          1       250       2
A          2       100       1
A          3       250       3

df2:

Sector     Plot    Usage    Type
A          1       R        Land
A          1       R        Land
A          2       C        Villa       
A          3       R        Plot
A          3       R        Plot
A          3       R        Plot

From the above I would like to add the Usage and Type column from df2 to df1 based on Sector, Plot match.

Expected Output:

Sector     Plot    Price     Count   Usage    Type
A          1       250       2       R        Land
A          2       100       1       C        Villa
A          3       250       3       R        Plot

I tried below code

df3 = pd.merge(df1, df2, left_on = ['Sector', 'Plot'], 
                     right_on = ['Sector', 'Plot'], how = 'inner')

score 4 · Accepted Answer · answered Feb 16 '20 at 12:35

Add DataFrame.drop_duplicates because duplicates in second DataFrame:

df3 = pd.merge(df1, 
               df2.drop_duplicates(['Sector', 'Plot']), 
               on = ['Sector', 'Plot'])
print (df3)
  Sector  Plot  Price  Count Usage   Type
0      A     1    250      2     R   Land
1      A     2    100      1     C  Villa
2      A     3    250      3     R   Plot

Map some columns of a data frame to other based on two column match pandas

1 Answers1