Check if multiple rows exist in another dataframe

Question

I have a two dataframes. I want to see if specific rows (in its entirety) exist in the other dataframe. Example rows from df_subset:

id    category    value    date
1     A           10       01-01-15
3     C           10       03-01-15

The other df_full:

id    category    value    date
1     A           10       01-01-15
2     B           10       02-01-15
3     C           10       03-01-15
4     D           16       04-01-15

Is there someway to check if the rows of one dataframe exists in another? Something like this (obviously this does not work): df_subset in df_full, exist?

> True

jezrael · Accepted Answer · 2016-11-09 19:22:46Z

4

I think you can use merge with inner join (by default) with DataFrame.equals for compare with df_subset:

print (pd.merge(df_subset,df).equals(df_subset))
True

edited Nov 9, 2016 at 19:22

answered Nov 9, 2016 at 19:07

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MaxU - stand with Ukraine · Accepted Answer · 2016-11-09 19:06:28Z

3

you can use merge(..., indicator=True) method:

In [14]: pd.merge(df1, df2, indicator=True, how='outer')
Out[14]:
   id category  value      date      _merge
0   1        A     10  01-01-15        both
1   3        C     10  03-01-15        both
2   2        B     10  02-01-15  right_only
3   4        D     16  04-01-15  right_only

answered Nov 9, 2016 at 19:06

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Comments

piRSquared · Accepted Answer · 2016-11-09 21:58:41Z

3

using numpy

(df_subset.values[:, None] == df_full.values).all(2).any(1).all()

True

timing

explanation

# using [:, None] to extend into new dimension at
# take advantage of broadcasting
a1 = df_subset.values[:, None] == df_full.values

     # ━> third dimension ━>
     # ━━━━> axis=2 ━━━>
# 1st dim
---->[[[ True  True  True  True]   # │
       [False False  True False]   # │ second dimension
       [False False  True False]   # │ axis=1
       [False False False False]]  # ↓

 # axis=0
 ---->[[False False  True False]   # │ 
       [False False  True False]   # │ second dimension
       [ True  True  True  True]   # │ axis=1
       [False False False False]]] # ↓

# first row of subset with each row of full 
[[[ True  True  True  True]  <-- This one is true for all 
  [False False  True False]
  [False False  True False]
  [False False False False]]

# second row of subset with each row of full 
 [[False False  True False] 
  [False False  True False]
  [ True  True  True  True]  <-- This one is true for all
  [False False False False]]]

a2 = a1.all(2)

#   ┌─ first row of subset all equal
[[ True False False False]
 [False False  True False]]
#               └─ second row of subset all equal

a3 = a2.any(1)

#  ┌─ first row of subset matched at least one row of full
[ True  True]
#        └─ second row of subset matched at least one row of full

a3.all()

True

ALL ROWS OF df_subset ARE IN df_full

edited Nov 9, 2016 at 21:58

answered Nov 9, 2016 at 20:07

piRSquared

296k68 gold badges509 silver badges654 bronze badges

2 Comments

piRSquared Over a year ago

I extended array to third dimension. all(2) is along third axis. I'll update post to make it clearer

jezrael Over a year ago

Ya, it is really complicated. Numpy is for me complicated, I prefer pandas.

Collectives™ on Stack Overflow

Check if multiple rows exist in another dataframe

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related