Pandas - Create a symmetric matrix that counts the number of records

Question

I have a dataframe that looks like the one below

ID  |  Value
1   |  A
1   |  B
1   |  C
2   |  B
2   |  C

I want to create a symmetric matrix of based on Value:

    A   B  C
 A  1   1  1
 B  1   2  2
 C  1   2  2

This basically indicates how many people have both values (v1,v2). I am currently using for loops to scan the dataframe for every combination but was wondering if there was an easier way to do it using pandas.

jezrael · Accepted Answer · 2019-03-08T14:30:34.210

4

Use merge with cross join by ID column with crosstab and DataFrame.rename_axis for remove index and columns names:

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)
   A  B  C
A  1  1  1
B  1  2  2
C  1  2  2

edited Mar 08 '19 at 14:30

answered Mar 08 '19 at 14:28

jezrael

822,522
95
1,334
1,252

1

'Value_x' and 'Value_y' are to be specified or are they inbuilt keywords? Thanks for the help. – Nivi Mar 08 '19 at 14:30
1

Got it. Thanks! – Nivi Mar 08 '19 at 14:46
1

@Nivi - It is `index` and `columns` names created from columns names after `crosstab` – jezrael Mar 08 '19 at 14:46

Pandas - Create a symmetric matrix that counts the number of records

1 Answers1

Linked