Let us generate the following pandas.DataFrame:
np.random.seed(42)
mi = pd.MultiIndex(levels=[[0, 1, 2, 3, 4, 5, 6], [0, 1]],
labels=[[0, 0, 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6],
[0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1]],
names=['day_of_week', 'cat'])
X = pd.DataFrame(np.random.randint(1,100,size=14), mi, ['count'])
I would like to add a column which will hold the ratios between the values for each (day_of_week, cat) and the sum of the two values for the day_of_week. For example, the first two rows of the needed column would be: [52/(52+93), 93/(52+93),...]
I know how to compute the sum for each day: X.sum(level=0), but I don't know how to proceed. I could iterate and loop my way out, but this won't be pythonic.