CODEWITHSUNDEEP
pythonpandasmatrixcrosstab
cgoldie
cgoldie

Reputation: 123

Is there a simple way to convert a pandas series to a crosstab of ratios for values in the series?

Input

name    score
bob     2           
fred    4           
jim     1           
anne    5

Desired Output (ratio of scores: e.g. bob*fred in row 1 = 2/4, etc.)

name    bob fred    jim anne
bob     1   0.5     2   0.4
fred    2   1       4   0.8
jim     0.5 0.2     1   0.2
anne    2.5 1.25    5   1

Upvotes: 3

Views: 66

Answers (2)

Michael Szczesny
Michael Szczesny

Reputation: 5026

You can use df.corr to compute a custom relation function. Be aware that the diagonal is not computed but set to 1.0.

Selecting the relevant columns is only necessary if you have more than two columns in your dataframe.

df[['name','score']].set_index('name').T.corr(lambda x,y: x/y)

Output

name  bob  fred  jim  anne
name                      
bob   1.0   0.5  2.0   0.4
fred  0.5   1.0  4.0   0.8
jim   2.0   4.0  1.0   0.2
anne  0.4   0.8  0.2   1.0

Dateframe to test this solution

import pandas as pd
import io

t = '''
name    score
bob     2           
fred    4           
jim     1           
anne    5   
'''

df = pd.read_csv(io.StringIO(t), sep='\s+')

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71687

We can try outer np.divide.outer to compute the outer division of score column

n, s = df.to_numpy().T
pd.DataFrame(np.divide.outer(s, s), n, n)

      bob  fred  jim anne
bob   1.0   0.5  2.0  0.4
fred  2.0   1.0  4.0  0.8
jim   0.5  0.25  1.0  0.2
anne  2.5  1.25  5.0  1.0

Upvotes: 5

Related Questions