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

user_7930705 0
Viewed 56

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
pandas crosstab matrix python
2 Answers

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
user_128331660

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+')
user_142777220
Related