CODEWITHSUNDEEP
python-3.xpandas
vishwajeet Mane
vishwajeet Mane

Reputation: 344

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

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.

Upvotes: 1

Views: 67

Answers (2)

Scott Boston
Scott Boston

Reputation: 153510

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

Upvotes: 1

jezrael
jezrael

Reputation: 863501

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

Upvotes: 2

Related Questions