pandas drop rows with duplicates in some columns relative to other columns
<p>consider the dataframe <code>df</code></p>

<pre><code>data = [
    ['a', 'b', 'c', 'd'],
    ['a', 'b', 'e', 'f'],
    ['g', 'h', 'i', 'j'],
    ['k', 'l', 'm', 'n'],
    ['m', 'n', 'o', 'p'],
    ['q', 'r', 's', 't'],
    ['u', 'v', 'w', 'x'],
    ['y', 'z', 'q', 'r'],
]

cols = pd.MultiIndex.from_product([list('AB'), list('XY')])

df = pd.DataFrame(data, columns=cols)

df
</code></pre>

<p><a href="http://i.stack.imgur.com/cXM4G.png" rel="nofollow"><img src="http://i.stack.imgur.com/cXM4G.png" alt="enter image description here"></a></p>

<p>I want to compare all rows of <code>df.A</code> with all rows of <code>df.B</code> and drop the rows that have matches.  I do <strong>not</strong> want to drop rows that have mathces within <code>df.A</code> or <code>df.B</code> by themselves.</p>

<p><strong><em>Visual</em></strong></p>

<p><a href="http://i.stack.imgur.com/5u95A.png" rel="nofollow"><img src="http://i.stack.imgur.com/5u95A.png" alt="enter image description here"></a></p>

<ul>
<li>leave rows <code>[0, 1]</code> alone.</li>
<li>drop rows <code>[3, 4]</code> because <code>'m', 'n'</code> match</li>
<li>drop rows <code>[5, 7]</code> because <code>'q', 'r'</code> match</li>
</ul>

<p>Results should look like</p>

<pre><code>df.loc[[0, 1, 2, 6]]
</code></pre>

<p><a href="http://i.stack.imgur.com/8EFA7.png" rel="nofollow"><img src="http://i.stack.imgur.com/8EFA7.png" alt="enter image description here"></a></p>

<hr>

<p><strong><em>I've tried</em></strong> to <code>stack</code> and <code>drop_duplicates</code></p>

<pre><code>df.stack(0).drop_duplicates(keep=False) \
    .unstack().dropna() \
    .swaplevel(0, 1, 1).sort_index(1)
</code></pre>

<p><a href="http://i.stack.imgur.com/M33AL.png" rel="nofollow"><img src="http://i.stack.imgur.com/M33AL.png" alt="enter image description here"></a></p>

<p>But that catches duplicates in the same sub-columns, which isn't what I want.</p>

<p>try this:</p>

<pre><code>In [248]: df[~(df.A.isin(df.B.to_dict('list')).all(1) | df.B.isin(df.A.to_dict('list')).all(1))]
Out[248]:
   A     B
   X  Y  X  Y
0  a  b  c  d
1  a  b  e  f
2  g  h  i  j
6  u  v  w  x
</code></pre>

<p>Explanation:</p>

<pre><code>In [249]: df.A.isin(df.B.to_dict('list'))
Out[249]:
       X      Y
0  False  False
1  False  False
2  False  False
3  False  False
4   True   True
5   True   True
6  False  False
7  False  False

In [250]: df.A.isin(df.B.to_dict('list')).all(1)
Out[250]:
0    False
1    False
2    False
3    False
4     True
5     True
6    False
7    False
dtype: bool

In [251]: df.B.isin(df.A.to_dict('list'))
Out[251]:
       X      Y
0  False  False
1  False  False
2  False  False
3   True   True
4  False  False
5  False  False
6  False  False
7   True   True

In [252]: df.B.isin(df.A.to_dict('list')).all(1)
Out[252]:
0    False
1    False
2    False
3     True
4    False
5    False
6    False
7     True
dtype: bool
</code></pre>

<p>combining both:</p>

<pre><code>In [253]: df.A.isin(df.B.to_dict('list')).all(1) | df.B.isin(df.A.to_dict('list')).all(1)
Out[253]:
0    False
1    False
2    False
3     True
4     True
5     True
6    False
7     True
dtype: bool
</code></pre>

python pandas