Pandas dataframes - Match two columns in the two dataframes to change the value of a third column

user_8423718 0
Viewed 623

I have two dataframes df1 and df2. x,y values in df2 is a subset of x,y values in df1. For each x,y row in df2, I want to change the value of knn column in df1 to 0, where df2[x] = df1[x] and df2[y] = df1[y]. In the example below x,y values (1,1) and (1,2) are common therefore knn column in df1 will change to [0,0,0,0]. The last line in the code below is not working. I would appreciate any guidance.

import pandas as pd

df1_dict = {'x': ['1','1','1','1'],
        'y': [1,2,3,4],
        'knn': [1,1,0,0]
        }

df2_dict = {'x': ['1','1'],
        'y': [1,2]
        }

df1 = pd.DataFrame(df1_dict, columns = ['x', 'y','knn'])
df2 = pd.DataFrame(df2_dict, columns = ['x', 'y'])
df1['knn']= np.where((df1['x']==df2['x']) and df1['y']==df2['y'], 0)
pandas dataframe python
2 Answers

You can use merge here:

u = df1.merge(df2,on=['x','y'],how='left',indicator=True)
u = (u.assign(knn=np.where(u['_merge'].eq("both"),0,u['knn']))
     .reindex(columns=df1.columns))

print(u)

   x  y  knn
0  1  1    0
1  1  2    0
2  1  3    0
3  1  4    0
user_98406370

You can use MultiIndex.isin:

c = ['x', 'y']
df1.loc[df1.set_index(c).index.isin(df2.set_index(c).index), 'knn'] = 0

   x  y  knn
0  1  1    0
1  1  2    0
2  1  3    0
3  1  4    0
user_128331660
Related