CODEWITHSUNDEEP
pythonpandas
Aket Lohia
Aket Lohia

Reputation: 31

pandas isin based on common column for 2 different dataframes at row level

I have two dataframes. Example:

DF1:

ID  Val1 VAl2
123  a    d
234  b    e
345  c    f

DF2:

ID  Val1 VAl2
234  b    d
345  c    f
567  k    l
123  b    d

I want to compare rows of DF2 with DF1 based on ID (Index for same ID value is different in both the DFs are different), and get output like:

Output:

ID   Val1 VAl2
NaN  NaN  d
NaN  NaN  NaN
567  k    l
NaN  b    NaN

So, where-ever values are similar based on ID is populated as NaN and the values that are different are populated as it is.

I tried to use:

DF2[~DF2.isin(DF1)]

But it compares based on index of dataframes.

I also tried:

DF2.isin(DF1.values.ravel())

But, it compares at individual value level in dataframe.

Thanks

Upvotes: 2

Views: 128

Answers (1)

BENY
BENY

Reputation: 323316

Set the index then use eq

df1.set_index('ID').eq(df2.set_index('ID'))
      Val1   VAl2
ID               
123  False   True
234   True  False
345   True   True
567  False  False

Upvotes: 1

Related Questions