CODEWITHSUNDEEP
pythonpandas
Stanley Gan
Stanley Gan

Reputation: 481

Changing a value of first data frame for rows that match with 2nd data frame in python

For example, if first data frame df1 is:

     'a' 'b' 'value'
0     1   2     1
1     2   3     1

and second data frame df2 is

     'a' 'b' 
0     1   2

I would like to get something like

     'a' 'b' 'value'
0     1   2     0
1     2   3     1

For all the rows in df1(excluding 'value' column) that match the rows in df2, I would like to change the 'value' column of these rows in df1 from 1 to 0. I can find the common rows by using df1.merge(df2, on=['a','b']). But how do I change the value?

Upvotes: 1

Views: 52

Answers (2)

akuiper
akuiper

Reputation: 214957

You can specify the indicator parameter to be true when merging, this will generate an extra column specify if the rows come from both sides, and then you can modify the value column based on the _merge column:

df_merge = df1.merge(df2, indicator=True, how = "left")
df_merge["value"] = df_merge["value"].where(df_merge['_merge'] != "both", 0)
df_merge.drop("_merge", axis=1)

#    a   b   value
#0   1   2       0
#1   2   3       1

Another option:

df1 = df1.set_index(['a', 'b'])
df1.loc[df2.set_index(['a', 'b']).index, 'value'] = 0
df1.reset_index()

#   a   b   value
#0  1   2       0
#1  2   3       1

Upvotes: 1

nitinvijay23
nitinvijay23

Reputation: 1841

You can find the common rows using the inner join.

df1.merge(df2, how= "inner", left_index= True, right_index= True)

If you want the output you posted then you can use left join

df2.merge(df1, how="left", left_index= True, right_index= True)

You can replace nan value with zero.

Upvotes: 0

Related Questions