# Finding the duplicate values between all columns and sort in new column with Pandas?

I have this DataFrame:

    CL1 CL2 CL3 CL4
0   a   a   b   f
1   b   y   c   d
2   c   x   d   s
3   x   s   x   a
4   s   dx  s   s
5   a   c   d   d
6   s   dx  f   d
7   d   dc  g   g
8   f   x   s   t
9   c   x   a   d
10  x   y   y   a
11  c   a   x   y
12  f   s   d   s
13  d   d   w   a


Intention:

• With help Pandas

1- I wanna search and find the similar values between all columns (CL1-CL4) and sort in a new column (SIM).

2- I wanna find the non-similar values between columns and sort in another column (NON-SIM).

## What i want

How can i do that? With df.pivot_table i was not successful.

• Can you clarify what you mean with similar and non-similar values, which columns do you want to compare? What output do you expect given the input you provided? Jan 30 at 18:40
• i wanna search and find the similar vlaue (e.g. 'a') in all columns & sort in these similar values in a new columns. and the same for non-similar values. Jan 30 at 18:44
• Can you give an example of the output you would expect? Jan 30 at 18:48
• The way I understand it now is you want to get the values which occur twice or more in the same row, is this correct? If so, why is the value 'g' not included in the similar list? Jan 30 at 19:04
• @Oxbowerce because you cant find 'g' in 'CL1' and 'CL2'. here's the thing: A value is to be added in SIM if are in all columns otherwise is NON-SIM Jan 30 at 19:11

Given your input data is saved in a variable df, I count the values which occur in all 4 unique columns as follows:

import pandas as pd
import numpy as np

output = (
df
.melt()
.drop_duplicates()
.groupby("value")
.agg(count=("value", "count"))
.reset_index()
)
output["SIM"] = np.where(output["count"] == 4, "SIM", "NON-SIM")
output = output.pivot(columns="similarity", values="value")

print(output)

similarity  NON-SIM SIM
0           NaN     a
1           b       NaN
2           c       NaN
3           NaN     d
4           dc      NaN
5           dx      NaN
6           f       NaN
7           g       NaN
8           NaN     s
9           t       NaN
10          w       NaN
11          x       NaN
12          y       NaN

• unfortunatly is not what i want :-( Jan 30 at 19:57

This may be simpler using sets. Assuming your dataframe is df, first get each column's unique values as a set:

import pandas as pd
from functools import reduce
cols = df.agg(set)
print(cols)


This gives a pandas Series of python set objects:

CL1             {c, s, d, a, x, f, b}
CL2        {c, dc, y, s, d, a, x, dx}
CL3    {c, s, y, d, a, x, g, f, w, b}
CL4             {s, y, d, a, g, t, f}
dtype: object


Then you can combine these using set operations to get your desired results:

sim = reduce(set.intersection, cols)
non_sim = reduce(set.union, cols) - sim
print(sim, non_sim, sep='\n')


This gives the results as two set objects:

{'d', 'a', 's'}
{'c', 'dc', 'y', 'x', 'g', 't', 'f', 'dx', 'w', 'b'}

• I receive the error "Set type is unordered" after running! I don't know why Feb 1 at 9:16
• Depending on what you want to do with the results, you may need to make the set's into list's (e.g. using sorted(sim)) or pandas Series objects. For debugging types of questions, the main stackoverflow site may be better suited than here. Feb 1 at 17:18
• Actually i want to use it in supervised learning for clustering. This is only a sample of my DataSet. In real is 2e9x3x9. I will that my algorithm recognize correctly the word "Hello World" if see "Hello Worlld"mor "Hellooo WoRld". Feb 1 at 17:59
• @Jsmoka you should probably post a new question about your larger problem then. (To avoid the "XY problem".) Feb 2 at 2:44