CODEWITHSUNDEEP
pythonpandasdataframeboolean
jayjay87
jayjay87

Reputation: 21

Find which rows of a dataframe exist in another dataframe

I want to perform a lookup using df1 against df2 to see if the column combination for each row in df1 exist in df2 and return a boolean. Is that possible? Thanks!

df1:

**First**     **Last**
Bob          Anza
Jim          Roberts
Tom          Phillips

df2:

**First**     **Last**
Bob          Phillips
Jim          Roberts
Tom          Anza

Return:

df3:

**First**     **Last**  **Match**
Bob          Anza          False
Jim          Roberts       True
Tom          Phillips      False

Upvotes: 2

Views: 134

Answers (3)

timgeb
timgeb

Reputation: 78780

I'm assuming it's a coincidence that in your sample data the row positions of the corresponding equal rows match.

You can construct the 'Match' column via:

df1['Match'] = df1.index.isin(df1.reset_index().merge(df2)['index'])

Per default DataFrame.merge uses the intersection of the column labels to merge on, so the line above is equivalent to:

df1['Match'] = df1.index.isin(df1.reset_index().merge(df2, on=['First', 'Last'])['index'])

In similar problems where you only want to compare "partial rows" for equality, provide the on, left_on or right_on arguments explicitly.

Upvotes: 1

jfaccioni
jfaccioni

Reputation: 7529

Try this:

df3 = df1.copy()
df3['**Match**'] = (df1 == df2).all(axis=1)
print(df3)

output:

  **First**  **Last**  **Match**
0       Bob      Anza      False
1       Jim   Roberts       True
2       Tom  Phillips      False

Note that this also checks if the values in the first column of df1 and df2 are equal.

Upvotes: 0

NYC Coder
NYC Coder

Reputation: 7604

Try this:

df3 = df1.merge(df2, on=['First'])
df3['match'] = df3['Last_x']==df3['Last_y']
print(df3)

  First    Last_x    Last_y  match
0   Bob      Anza  Phillips  False
1   Jim   Roberts   Roberts   True
2   Tom  Phillips      Anza  False

Upvotes: 1

Related Questions