How to replace the pandas column value based on others dataframe columns

Question

I have 2 pandas dataframe as below

df1:-

 col1   col2 col3 
   aa      b   c
   aa      d   c
   bb      d   t
   bb      b   g
   cc      e   c
   dd      g   c

and 2nd dataframe:-

 col1  col2 
  aa    b
  cc    e
  bb    d

And I want to change the value of col3 of dataframe1 to 'cc'. like below. based on 2nd dataframe column col1 and col2.

 col1   col2 col3 
   aa      b   cc
   aa      d   c
   bb      d   cc
   bb      b   g
   cc      e   cc
   dd      g   c

In short, I want to map 2nd dataframe columns(col1,col2) with 1st dataframe of columns(col1,col2) and change the column(col3) of 1st dataframe where it matches.

score 2 · Accepted Answer · answered Jul 01 '20 at 13:19

2

Use DataFrame.merge with left join and indicator parameter for helper column, compare by Series.eq for == with both and last set values in DataFrame.loc:

m = df1.merge(df2, on=['col1','col2'],indicator=True, how='left')['_merge'].eq('both')

df1.loc[m, 'col3'] = 'cc'
print (df1)
  col1 col2 col3
0   aa    b   cc
1   aa    d    c
2   bb    d   cc
3   bb    b    g
4   cc    e   cc
5   dd    g    c

answered Jul 01 '20 at 13:19

jezrael

822,522
95
1,334
1,252

can you please explain how df1.loc[m,'col3'] is working. – vishwajeet Mane Jul 01 '20 at 13:53
1

@vishwajeetMane - Sure, there is used this [solution](https://stackoverflow.com/a/44311454) – jezrael Jul 01 '20 at 13:54

score 1 · Answer 2 · answered Jul 01 '20 at 13:37

You can use pd.concat and drop_duplicates after assign a value for 'col3' on dataframe, df2 :

df = pd.concat([df2.assign(col3='cc'), df1]).drop_duplicates(['col1','col2']).reset_index(drop=True)
df

Output:

  col1 col2 col3
0   aa    b   cc
1   cc    e   cc
2   bb    d   cc
3   aa    d    c
4   bb    b    g
5   dd    g    c

How to replace the pandas column value based on others dataframe columns

2 Answers2