CODEWITHSUNDEEP
pythonpandas
j1897
j1897

Reputation: 1557

How to transpose specific columns in a dataframe and get counts for other columns in Pandas

I have a dataframe like this

id a1  a2 a3    b1  b2
1   1   0  0    0   1
2   0   0  0    1   0
3   1   1  0    0   1
4   1   0  1    1   1
5   0   1  1    0   0

Now, I have to transpose columns beginning with prefix 'a' into rows and get counts for corresponding columns with prefix 'b'. The counts are basically the number of times that 'a' and 'b' co-occurred in an id. Co-occurrence is only if both the values are '1' for that id.

    b1 b2 
a1  1  3
a2  0  1
a3  1  1

In the above example, a1,b2 pair co-occurred in 3 ids (in id 1,3 and 4) and hence the value is 3.

How to do this in Pandas?

Upvotes: 0

Views: 112

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150745

Matrix multiplication (with python 3.5+)

df[['a1', 'a2','a3']].T @ df[['b1','b2']]

Update: more generally 

 df.filter(like='a').T @ df.filter(like='b')

Or

 df.iloc[:,:3].T @ df.iloc[:,3:]

Upvotes: 2

Related Questions