Quick way to find the duplicate cell in a certain column of data frame in python-pandas?
<p>Given a data frame <code>df</code> in the following form:</p>

<pre><code>item        attr
1           {1, 2, 3, 4}
2           {2, 4, 3, 2, 10}
3           {4, 37}
4           {1, 2, 3, 4}
</code></pre>

<p>I want to find the item-pair with same <code>attr</code>, like, <code>item 1</code> and <code>item 2</code>. Please notice that the <code>df</code> has <code>200,000</code> items totally. And I want a fastest way to find them. Do you know how to do it? Thanks in advance!</p>

<p>You can first convert <code>set</code> to <code>tuple</code> and then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.aggregate.html" rel="nofollow"><code>aggregate</code></a> <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.SeriesGroupBy.nunique.html" rel="nofollow"><code>nunique</code></a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.SeriesGroupBy.unique.html" rel="nofollow"><code>unique</code></a>. Last use <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a>:</p>

<pre><code>df = pd.DataFrame({'item':[1,2,3,4],
                  'attr':[set({1, 2, 3, 4}),set({2, 4, 3, 2, 10}),
                          set({4, 37}), set({1, 2, 3, 4})]})

print (df)
            attr  item
0   {1, 2, 3, 4}     1
1  {3, 10, 2, 4}     2
2        {4, 37}     3
3   {1, 2, 3, 4}     4

df.attr = df.attr.apply(tuple)
print (df)
            attr  item
0   (1, 2, 3, 4)     1
1  (3, 10, 2, 4)     2
2        (4, 37)     3
3   (1, 2, 3, 4)     4

df1 = df.item.groupby(df['attr']).agg(['nunique', 'unique'])
df1 = df1[df1['nunique'] == 2]
print (df1)
              nunique  unique
attr                         
(1, 2, 3, 4)        2  [1, 4]
</code></pre>

<p>Another solution if there are only one or pair values in <code>DataFrame</code> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.duplicated.html" rel="nofollow"><code>duplicated</code></a>:</p>

<pre><code>df = pd.DataFrame({'item':[1,2,3,4],
                  'attr':[set({1, 2, 3, 4}),set({4, 37}),
                          set({4, 37}), set({1, 2, 3, 4})]})

print (df)
           attr  item
0  {1, 2, 3, 4}     1
1       {4, 37}     2
2       {4, 37}     3
3  {1, 2, 3, 4}     4

df.attr = df.attr.apply(tuple)


df1 = df[df.duplicated('attr', keep=False)]
df1 = df1.groupby('attr')['item'].apply(lambda x: x.tolist())
print (df1)
(1, 2, 3, 4)    [1, 4]
(4, 37)         [2, 3]
Name: item, dtype: object
</code></pre>

<p>EDIT by comment:</p>

<p>Use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.melt.html" rel="nofollow"><code>melt</code></a> for reshaping:</p>

<pre><code>df = pd.DataFrame({'item':[1,2,3,4,5],
                  'attr1':[set({1, 2, 3, 4}),set({4, 37}),set({4, 37}), 
                           set({1, 2, 3, 4}), set({4,8})],
                  'attr2':[set({1, 2 }),set({4, 37}),
                           set({4, 3}), set({1, 2}), set({4,8})]})

print (df)
          attr1    attr2  item
0  {1, 2, 3, 4}   {1, 2}     1
1       {4, 37}  {4, 37}     2
2       {4, 37}   {3, 4}     3
3  {1, 2, 3, 4}   {1, 2}     4
4        {8, 4}   {8, 4}     5

df = pd.melt(df, id_vars='item', value_name='attr').drop('variable', axis=1)
df.attr = df.attr.apply(tuple)
print (df)
   item          attr
0     1  (1, 2, 3, 4)
1     2       (4, 37)
2     3       (4, 37)
3     4  (1, 2, 3, 4)
4     5        (8, 4)
5     1        (1, 2)
6     2       (4, 37)
7     3        (3, 4)
8     4        (1, 2)
9     5        (8, 4)
</code></pre>

python pandas python-3.5