Count of rows where given columns of a DataFrame are non-zero

Question

I have a Pandas DataFrame that looks like this:

MemberID    A    B    C    D
1           0.3  0.5 0.1   0
2           0    0.2 0.9   0.3
3           0.4  0.2 0.5   0.3
4           0.1  0   0     0.7

I would like to have another matrix which gives me the number of non-zero elements for the intersection of every column except for MemberID.

For example, the intersection of columns A and B would be 2 (because MemberID 1 and 3 have non-zero values for A and B), intersection of A and C would be 2 as well (because MemberID 1 and 3 have non-zero values for A and C).

The final matrix would look like this:

    A    B    C    D
A   3    2    2    2
B   2    3    3    2
C   2    3    3    2
D   2    2    2    3

As we can see, it should be a symmetric matrix, similar to a correlation matrix, but not the correlation matrix.

Intersection of any 2 columns = # of MemberID having non-zero values in both columns.

I would show some initial code here but I feel like there would be a simple function to do this task that I don't know of.

Here's the code to create the DataFrame:

df = pd.DataFrame([[0.3, 0.5,  0.1, 0],
                   [0,  0.2,  0.9, 0.3],
                   [ 0.4,  0.2,  0.5, 0.3],
                   [ 0.1, 0, 0,  0.7]],
                  columns=list('ABCD'))

Any pointers would be appreciated. TIA.

df.A has one element that is zero. shouldn't final.loc['A', 'A'] == 3 — piRSquared
– piRSquared, Commented Jul 18, 2016 at 21:28
Since you want MemberId to be treated as the index not a regular column, either do pd.read_csv(..., index_col=...) when you read in the dataframe, or else do df.set_index(..., inplace=True) — smci
– smci, Commented Sep 16, 2019 at 23:48
It's unhelpful to try to unilaterally redefine "intersection of columns" in a way that conflicts with every other definition out there. I would just call this "Count of rows where columns x,y respectively of a DataFrame are both non-zero", which is what it is. — smci
– smci, Commented Sep 16, 2019 at 23:50

piRSquared · Accepted Answer · 2016-07-18 21:27:09Z

4

This should to it:

z = (df != 0) * 1
z.T.dot(z)

answered Jul 18, 2016 at 21:27

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count of rows where given columns of a DataFrame are non-zero

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related