How to divide the selected element with the sum values in the row, except the values on the diagonal PANDAS

Question

I am doing some statistics.

I have data frame:

tag a   b    c   d   e   f   
 a  5   2    3   2   0   1
 b  2   4    3   2   0   1
 c  3   4    3   2   0   3
 d  2   4    3   2   0   1
 e  0   4    3   2   0   8
 f  1   4    3   2   0   1

I want to create new dataframe:

tag a   b    c   d   e   f   
 a  0   x    
 b      0        
 c           0       
 d               0      
 e                   0   Y  
 f                       0

where x would be equal the correspodenting element on that place, divided with the sum of elements in that row, except the element on the diagonal. So the X is: X = 2/(2+3+2+0+1)

And for the example Y = 8/(0+4+3+2+8)

After that I need to add one more column which will be calculated: -sum[each element in the row * log (of that element)]

I am sorry for this trivial question, I used to work in R, and for this task I need to work in pandas.

Answer 1

Use np.fill_diagonal to mask diagonal elements, then perform index aligned division using DataFrame.div:

u = df.set_index('tag')
np.fill_diagonal(u.values, 0)

v = u.div(u.sum(axis=1), axis=0)
v 

        a         b         c         d    e         f
tag                                                   
a    0.00  0.250000  0.375000  0.250000  0.0  0.125000
b    0.25  0.000000  0.375000  0.250000  0.0  0.125000
c    0.25  0.333333  0.000000  0.166667  0.0  0.250000
d    0.20  0.400000  0.300000  0.000000  0.0  0.100000
e    0.00  0.235294  0.176471  0.117647  0.0  0.470588
f    0.10  0.400000  0.300000  0.200000  0.0  0.000000

---

"After that I need to add one more column which will be calculated: -sum[each element in the row * log (of that element)]"

You can do this with

v['log_sum'] = -np.ma.masked_invalid(v * np.log(v)).sum(1)
v

        a         b         c         d    e         f   log_sum
tag                                                             
a    0.00  0.250000  0.375000  0.250000  0.0  0.125000 -8.965402
b    0.25  0.000000  0.375000  0.250000  0.0  0.125000 -8.965402
c    0.25  0.333333  0.000000  0.166667  0.0  0.250000 -8.461294
d    0.20  0.400000  0.300000  0.000000  0.0  0.100000 -9.560926
e    0.00  0.235294  0.176471  0.117647  0.0  0.470588 -9.708363
f    0.10  0.400000  0.300000  0.200000  0.0  0.000000 -9.560926

Answer 2

numpy.eye + a bit of arithmetic

---

u = df.iloc[:, 1:].values
x, _ = df.shape
m = 1 - np.eye(x)
n = u * m
n / n.sum(1, keepdims=1)

array([[0.   , 0.25 , 0.375, 0.25 , 0.   , 0.125],
       [0.25 , 0.   , 0.375, 0.25 , 0.   , 0.125],
       [0.25 , 0.333, 0.   , 0.167, 0.   , 0.25 ],
       [0.2  , 0.4  , 0.3  , 0.   , 0.   , 0.1  ],
       [0.   , 0.235, 0.176, 0.118, 0.   , 0.471],
       [0.1  , 0.4  , 0.3  , 0.2  , 0.   , 0.   ]])

---

To maintain the original frame:

pd.DataFrame(index=df.tag, data=n / n.sum(1, keepdims=1), columns=df.columns[1:])

        a         b         c         d    e         f
tag
a    0.00  0.250000  0.375000  0.250000  0.0  0.125000
b    0.25  0.000000  0.375000  0.250000  0.0  0.125000
c    0.25  0.333333  0.000000  0.166667  0.0  0.250000
d    0.20  0.400000  0.300000  0.000000  0.0  0.100000
e    0.00  0.235294  0.176471  0.117647  0.0  0.470588
f    0.10  0.400000  0.300000  0.200000  0.0  0.000000