Create a symmetric matrix that counts the relational records

Question

I would like to count the number of all possible pairwise relations through a column (Value) based on another column (ID).

Example dataframe:

   ID Value
0   1     A
1   1     A
2   1     A
3   1     B
4   1     C
5   2     B
6   2     C
7   2     C

To generate example dataframe:

import pandas as pd
df = pd.DataFrame({'ID'    : {0:  1,  1: 1,   2: 1,   3: 1,   4: 1,   
                              5:  2,  6: 2,   7: 2}, 
                   'Value' : {0: 'A', 1: 'A', 2: 'A', 3: 'B', 4: 'C',
                              5: 'B', 6: 'C', 7: 'C'} 
                   })

Pairwise count should be performed for ID=1 and ID=2.

Possible pairwises where ID=1

(A,A), (A,A), (A,B), (A,C),
(A,A), (A,A), (A,B), (A,C),
(A,A), (A,A), (A,B), (A,C),
(B,A), (B,A), (B,A), (B,C),
(C,A), (C,A), (C,A), (C,B),

Possible pairwises where ID=2

(B,C), (B,C)
(C,B), (C,C)
(C,B), (C,C)

Expected dataframe:

What I have currently got (see below relationship with other stackoverflow question):

df = pd.merge(df, df, on='ID')
df = pd.crosstab(df['Value_x'], df['Value_y']).rename_axis(None).rename_axis(None, axis=1)
print (df)

The wrong output:

As you might spot that the issue is mainly related with diaoganal side. I assumed that I have to focus on merge side to handle the proposed scenario. However, I could not handle it so far :( Any suggestions ? Thanks in advance!

Related question: There are various similarities with that question. However that question might have slightly wrong expectaions. The case of (A, A) = 0, (B,B) = 0, (C,C) = 0 should be 0 because they are not exist in both case (ID=1 or ID=2) based on that question. If we want to figure out counting only those conditions > AB, AC, BA, BC, CA, CB (from ID=1) and BC, CB (from ID=2) for that question. On the other hand, main difference here is on the diagonal side.

score 4 · Accepted Answer · answered Apr 25 '21 at 17:18

4

Let us try dot after crosstab, then subtract the self pair ~

s = pd.crosstab(df.ID,df.Value)
out = s.T.dot(s)
np.fill_diagonal(out.values, out.values.diagonal() - s.sum())
out
Value  A  B  C
Value         
A      6  3  3
B      3  0  3
C      3  3  2

answered Apr 25 '21 at 17:18

BENY

317,841
20
164
234

score 2 · Answer 2 · answered Apr 25 '21 at 16:16

You can use itertool.permutations but apply it to each group:

from itertools import permutations

out = pd.DataFrame()
for _, g in df.groupby("ID"):
    d = pd.DataFrame(permutations(g["Value"], 2), columns=["x", "y"])
    x = pd.crosstab(d["x"], d["y"]).rename_axis(None).rename_axis(None, axis=1)
    out = out.add(x, fill_value=0)

print(out.astype(int))

Prints:

score 1 · Answer 3 · answered Apr 25 '21 at 16:31

If you need access to the permutations, you can also build all permutations in a frame, then take the entire cross tab.

import pandas as pd
from itertools import permutations

df = pd.DataFrame({'ID': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1,
                          5: 2, 6: 2, 7: 2},
                   'Value': {0: 'A', 1: 'A', 2: 'A', 3: 'B', 4: 'C',
                             5: 'B', 6: 'C', 7: 'C'}
                   })

perms = df.groupby('ID')['Value'] \
    .apply(lambda s: pd.DataFrame(permutations(s, 2), columns=['x', 'y']))

new_df = pd.crosstab(perms.x, perms.y) \
    .rename_axis(None, axis=1) \
    .rename_axis(None, axis=0)

# For Display
print(new_df)
print()
print(perms)

Output
new_df:

Perms:

       x  y
ID         
1  0   A  A
   1   A  A
   2   A  B
   3   A  C
   4   A  A
   5   A  A
   6   A  B
   7   A  C
   8   A  A
   9   A  A
   10  A  B
   11  A  C
   12  B  A
   13  B  A
   14  B  A
   15  B  C
   16  C  A
   17  C  A
   18  C  A
   19  C  B
2  0   B  C
   1   B  C
   2   C  B
   3   C  C
   4   C  B
   5   C  C

Nice solution. With just one `.crosstab` more performant than mine. +1 — Andrej Kesely, Apr 25 '21 at 16:38

Create a symmetric matrix that counts the relational records

3 Answers3