Count number of matches in pairs of pandas dataframe rows

January 24, 2022

I have been trying to count the number of times different values in a row of dataframe matches with column-wise values in other rows and provide an output. To illustrate, I have a dataframe (df_testing) as follows:

import pandas as pd
df_testing = pd.DataFrame([
    [0,23,1, 3, 4,2],
    [1,33,3, 2, 4,3],
    [2,40,1, 2, 4,2]],
    columns=['SN','Age',  'Col_1', 'Col_2', 'Col_3','Col_4'])

which gives the following table:

Index|SN |Age |Col_1|Col_2|Col_3|Col_4|
---- |---|----|---- |-----|-----|-----|
0    |0  |23  |1    |3    |4    |2    |
1    |1  |33  |3    |2    |4    |3    |
2    |2  |40  |1    |2    |4    |2    |

I am looking to count the number of exact matches among rows for values in Col_1 to Col_4. For example, Row 0 has just one match with Row 1 (4 and 4 in Col_3) while Row 0 has 3 matches with Row2 (1,1; 4,4, and 2,2). As such, I am aiming for an output (preferably csv file) with all unique pairs like below (the rightmost column shows the number of counts matched):

SN_A|SN_B|Age_A|Age_B|Matched_Count|
----|----|-----|-----|-------------|
0   |1   |23   |33   |    1        |
0   |2   |23   |40   |    3        |
1   |2   |33   |40   |    2        |

I am thinking that this would require a loop and so far, due to my lack of proficiency, what I have managed to do is nowhere near what I want to achieve. I have somehow managed to get the unique pairs printed with the following lines:

length = len(df_testing)
for x in range(length):
    # print(x)
    for y in range(2,6,1):
        a= df_testing.iloc[x][y]
        for m in range(length):
            if m>x:
                b= df_testing.iloc[m][y]
                print(a,b)

This just prints out the respective values in pairs (e.g. 1,3; 1,1;3,2 etc).

Hence, any guidance to produce the output like shown above will be highly appreciated.

>Solution :

You could use itertools.combinations, a dictionary comprehension and the Series constructor:

from itertools import combinations

df2 = df_testing.set_index(['SN', 'Age'])
out = (pd.Series({(*a, *b): (df2.loc[a]==df2.loc[b]).sum()
                  for a,b in combinations(df2.index, r=2)
                  })
         .rename_axis(('SN_A', 'Age_A', 'SN_B', 'Age_B'))
         .reset_index(name='Matched_Count')
       )

output:

   SN_A  Age_A  SN_B  Age_B  Matched_Count
0     0     23     1     33              1
1     0     23     2     40              3
2     1     33     2     40              2