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.
Advertisement
Answer
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