Diego - 1 year ago 120
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 Source

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())
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download