Conditionally extracting Pandas rows based on another Pandas dataframe
<p>I have two dataframes:</p>

<p><code>df1:</code></p>

<pre><code>col1    col2
1       2
1       3
2       4
</code></pre>

<p><code>df2:</code></p>

<pre><code>col1
2
3
</code></pre>

<p>I want to extract all the rows in <code>df1</code> where <code>df1</code>'s <code>col2</code> <code>not in</code> <code>df2</code>'s <code>col1</code>. So in this case it would be:</p>

<pre><code>col1    col2
2       4
</code></pre>

<p>I first tried:</p>

<pre><code>df1[df1['col2'] not in df2['col1']]
</code></pre>

<p>But it returned:</p>

<blockquote>
  <p>TypeError: 'Series' objects are mutable, thus they cannot be hashed</p>
</blockquote>

<p>I then tried:</p>

<pre><code>df1[df1['col2'] not in df2['col1'].tolist]
</code></pre>

<p>But it returned:</p>

<blockquote>
  <p>TypeError: argument of type 'instancemethod' is not iterable</p>
</blockquote>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.isin.html" rel="nofollow"><code>isin</code></a> with <code>~</code> for inverting boolean mask:</p>

<pre><code>print (df1['col2'].isin(df2['col1']))
0     True
1     True
2    False
Name: col2, dtype: bool

print (~df1['col2'].isin(df2['col1']))
0    False
1    False
2     True
Name: col2, dtype: bool

print (df1[~df1['col2'].isin(df2['col1'])])
   col1  col2
2     2     4
</code></pre>

<p><strong>Timings</strong>:</p>

<pre><code>In [8]: %timeit (df1.query('col2 not in @df2.col1'))
1000 loops, best of 3: 1.57 ms per loop

In [9]: %timeit (df1[~df1['col2'].isin(df2['col1'])])
1000 loops, best of 3: 466 Âµs per loop
</code></pre>

python pandas indexing dataframe condition