0
$\begingroup$

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.

$\endgroup$
1
$\begingroup$

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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