Pandas Crosstabulation and counting

Question

I am using Python Pandas. I have got a column with a string and I would like to have the crossing between the columns.

E.g I have got the following input

1: Andi
2: Andi, Cindy
3: Thomas, Cindy
4: Cindy, Thomas

And I would like to have the following output:

Hence, the combination of Andi and Thomas does not appear in the data, but Cindy and Thomas appear twice.

          Andi  Thomas  Cindy
    Andi    1     0      1
    Thomas  0     1      2
    Cindy   1     2      1

Has somebody any idea how I could handle this? That would be really great!

Many thanks and regards,

Andi

ayhan · Answer 1 · 2017-07-10T16:31:25.327

9

You can generate the dummy columns first:

df['A'].str.get_dummies(', ')
Out: 
   Andi  Cindy  Thomas
0     1      0       0
1     1      1       0
2     0      1       1
3     0      1       1

And use that in the dot product:

tab = df['A'].str.get_dummies(', ')

tab.T.dot(tab)
Out: 
        Andi  Cindy  Thomas
Andi       2      1       0
Cindy      1      3       2
Thomas     0      2       2

Diagonal entries will give you the number of occurrences for each person. If you need to set the diagonals to 1, there are several alternatives. One of them is np.fill_diagonal from numpy.

co_occurrence = tab.T.dot(tab)    
np.fill_diagonal(co_occurrence.values, 1)    
co_occurrence
Out: 
        Andi  Cindy  Thomas
Andi       1      1       0
Cindy      1      1       2
Thomas     0      2       1

edited Jul 10 '17 at 16:31

answered Jul 10 '17 at 16:29

ayhan

70,170
20
182
203

1

great answer! (-; – piRSquared Jul 10 '17 at 16:31
@piRSquared Thank you. :) – ayhan Jul 10 '17 at 16:31
2

@ayhan This is the reason why I like Stack Overflow. I learn a great use of `.dot` to calculate crosstab reports from series. +1 – Scott Boston Jul 10 '17 at 16:37
1

@piRSquared perfect and great answer ;) – Andi Maier Jul 11 '17 at 07:47

Pandas Crosstabulation and counting

1 Answers1

Linked