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


  • 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

enter image description here How can i do that? With df.pivot_table i was not successful.

  • $\begingroup$ 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? $\endgroup$
    – Oxbowerce
    Commented Jan 30, 2021 at 18:40
  • $\begingroup$ 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. $\endgroup$
    – Jsmoka
    Commented Jan 30, 2021 at 18:44
  • $\begingroup$ Can you give an example of the output you would expect? $\endgroup$
    – Oxbowerce
    Commented Jan 30, 2021 at 18:48
  • $\begingroup$ 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? $\endgroup$
    – Oxbowerce
    Commented Jan 30, 2021 at 19:04
  • $\begingroup$ @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 $\endgroup$
    – Jsmoka
    Commented Jan 30, 2021 at 19:11

2 Answers 2


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 = (
    .agg(count=("value", "count"))
output["SIM"] = np.where(output["count"] == 4, "SIM", "NON-SIM")
output = output.pivot(columns="similarity", values="value")


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
  • $\begingroup$ unfortunatly is not what i want :-( $\endgroup$
    – Jsmoka
    Commented Jan 30, 2021 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
# df = pd.read_clipboard()
cols = df.agg(set)

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'}
  • $\begingroup$ I receive the error "Set type is unordered" after running! I don't know why $\endgroup$
    – Jsmoka
    Commented Feb 1, 2021 at 9:16
  • $\begingroup$ 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. $\endgroup$
    – GeoMatt22
    Commented Feb 1, 2021 at 17:18
  • $\begingroup$ 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". $\endgroup$
    – Jsmoka
    Commented Feb 1, 2021 at 17:59
  • 1
    $\begingroup$ @Jsmoka you should probably post a new question about your larger problem then. (To avoid the "XY problem".) $\endgroup$
    – GeoMatt22
    Commented Feb 2, 2021 at 2:44
  • $\begingroup$ Please see this post link $\endgroup$
    – Jsmoka
    Commented Feb 2, 2021 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.