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>using <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#the-query-method-experimental" rel="nofollow">.query()</a> method:</p>

<pre><code>In [9]: df1.query('col2 not in @df2.col1')
Out[9]:
   col1  col2
2     2     4
</code></pre>

<p>Timing for bigger DFs:</p>

<pre><code>In [44]: df1.shape
Out[44]: (30000000, 2)

In [45]: df2.shape
Out[45]: (20000000, 1)

In [46]: %timeit (df1[~df1['col2'].isin(df2['col1'])])
1 loop, best of 3: 5.56 s per loop

In [47]: %timeit (df1.query('col2 not in @df2.col1'))
1 loop, best of 3: 5.96 s per loop
</code></pre>

python pandas indexing dataframe condition