Computing between groups in a Pandas multiindex data frame

Question

Suppose I create a multi-index data frame as follows:

arrays = [np.array(['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux']),
          np.array(['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two'])]
df = pd.DataFrame(np.random.randn(8, 4), index=arrays)

                0          1            2           3
bar one -0.155088   -0.177214   -0.761230   -0.106045
    two  1.930298   -0.309573   -0.051878   -0.388760
baz one  0.111287    1.374426    0.408575    1.555659
    two -0.809201   -0.168658    0.055037    1.871289
foo one  0.286833   -0.988538    0.918153    0.841016
    two  0.348741    0.403747    0.584992   -1.838409
qux one  1.212017   -0.224872    0.616604    1.080590
    two  0.494800   -0.089214    0.829222    2.005217

How to create a new column, which is the relationship between the groups “one” and “two” in their value of column No. 3 (for example, the first element will be -0.106045 / -0.388760)?

How can I show it in combination with the current data frame?

Answer 1

With different random numbers. Use transform :

In [11]: df.groupby(level=0)[3].transform(lambda x: x[0]/ x[1])
Out[11]:
bar  one   -1.391651
     two   -1.391651
baz  one   -1.688734
     two   -1.688734
foo  one   -1.128344
     two   -1.128344
qux  one   -2.170493
     two   -2.170493
Name: 3, dtype: float64

to show this, set it as a column:

In [12]: df["ratio"] = df.groupby(level=0)[3].transform(lambda x: x[0]/ x[1])