Create a square matrix with combinatorial counts of boolean columns in a Pandas dataframe

user_16739053 0
Viewed 97

I have a Pandas dataframe with many long columns of Boolean values.

    Col1    Col2    Col3
0   False   True    True
1   False   True    True
2   False   True    True
3   False   True    False
4   True    False   True

I am able to use GroupBy on each pairwise column combination to get the counts, but I'm hoping there's a more elegant way to do pairwise comparisons of columns and get the results into a table (correlation/coincidence matrix) for easy comprehension. To be clear, I want to count the conditions if both columns in question are True. I'm not too particular about the identity entries (i.e., Col1 vs itself) but in my example, I've just done a count of True conditions for that column.

        Col1    Col2    Col3    
Col1     1       0       1
Col2     0       4       3  
Col3     1       3       4
pandas correlation dataframe
1 Answers

You can use numpy for that. Compare the array with itself using the logical and (&) as operator:

a = df.values
(a.T[:, None]&a.T).sum(axis=2)

output:

array([[1, 0, 1],
       [0, 4, 3],
       [1, 3, 4]])

as dataframe:

pd.DataFrame((a.T[:, None]&a.T).sum(2),
             columns=df.columns,
             index=df.columns)

output:

      Col1  Col2  Col3
Col1     1     0     1
Col2     0     4     3
Col3     1     3     4

Breakdown of the answer:

>>> a.T
array([[False, False, False, False,  True],
       [ True,  True,  True,  True, False],
       [ True,  True,  True, False,  True]])

>>> a.T[:, None]  ## note the extra dimension
array([[[False, False, False, False,  True]],

       [[ True,  True,  True,  True, False]],

       [[ True,  True,  True, False,  True]]])

>>> (a.T[:, None]&a.T)
array([[[False, False, False, False,  True],
        [False, False, False, False, False],
        [False, False, False, False,  True]],

       [[False, False, False, False, False],
        [ True,  True,  True,  True, False],
        [ True,  True,  True, False, False]],

       [[False, False, False, False,  True],
        [ True,  True,  True, False, False],
        [ True,  True,  True, False,  True]]])

>>> (a.T[:, None]&a.T).sum(2)  ## counts the True in the third dimension
array([[1, 0, 1],
       [0, 4, 3],
       [1, 3, 4]])
user_163434640
Related