Finding number of common elements between different columns of a DataFrame

Question

I have a dataframe which has almost 70-80 columns. Each column consists of 100-150 rows in which values are stored as strings.

I would like to find, for each column, what is the number of common elements present in the rest of the columns of the DataFrame.

For example: say I have a dataframe like:

       0      1      2        3
0    cat     ox   bull    horse
1   lion    dog    cat    zebra
2   bull     ox  horse    tiger
3  horse  eagle   bull  giraffe

Starting with first column, I need answer as 2,3 as 2 seems to have most number of common elements with the column 0 after that column 3 as it has comparatively less number of common elements.

Answer 1

You can build something like below. You can leverage set().intersection() to find the intersection between list. You need to loop one column with other columns.

You would Notice that I changed the values of df columns into a list in order to use a set

import pandas as pd

df = pd.DataFrame({'A': ['cat','lion','bull','horse'], 'B': ['ox','dog','ox','eagle'],
                   'C': ['bull','cat','horse','bull'],'D': ['horse','zebra','tiger','giraffe']})

df_out = pd.DataFrame()

for col in df.columns:
    # making a list of all column which has to be compared with col 
    other_col = [x for x in df.columns if x!=col]
    for oCol in other_col:
        #using a set we can find a intersection between 2 list and count them
        count = len(set(df[col].values.tolist()).intersection(df[oCol].values.tolist()))
        #storing all count with their respective column in separate df
        df_out = df_out.append([(col,oCol,count)],sort=True,ignore_index=True)


df_out.columns = ['Column','Comparison','Count']

df_out.sort_values(by=['Column','Count'],ascending=[True,False],inplace=True)

Output would look something like this:

Column Comparison Count 1 A C 3 2 A D 1 0 A B 0 3 B A 0 4 B C 0 5 B D 0 6 C A 3 8 C D 1 7 C B 0 9 D A 1 11 D C 1 10 D B 0