Diego Diego - 9 days ago 6
Python Question

Apply row and column sum to cell in pandas data frame

Can someone suggest an easy way of achieving the result bellow.

Data:

data = np.array([
['','A','B'],
['Yes',44,19],
['No',116,128]
])

df = pd.DataFrame(data=data[1:,1:],
index=data[1:,0],
columns=data[0,1:])

df:
A B
Yes 44 19
No 116 128


For each cell, I'd like to multiply the sum of the row total by the column total and divide by the table total (at this point I don't care about the call's value), so cell Yes\A would be:

63 * 160 / 307 = 32.83

these values come from:

63 = 44 + 19

160 = 44 + 116

307 = 44 + 19 + 116 + 128

any easy way to do so?

Thanks

Edit.
Full desired result (also, this method should work regardless of the number of rows and columns in the dataframe...which could be different)

32.83 30.17
127.17 116.83

Answer

The virtue of this solution compared to the ones above is that it fits in one line, but I'm afraid that's probably where it ends.

df.apply(lambda r: df.sum()[r.name]*df.sum(1)[r.index]/df.sum().sum())
Comments