Adding level to multiIndex dataframe

Question

I have a MultiIndex dataframe:

iterables = [['bar', 'baz', 'foo', 'qux'], ['one', 'two']]
Index = pd.MultiIndex.from_product(iterables, names=['first', 'second'])
s  = pd.DataFrame(np.random.randn(8,2), index=Index)

It yield dataframe as below:

                     0         1
first second                    
bar   one     0.619602 -0.422137
      two    -0.372906  0.581697
baz   one    -0.968921 -0.014957
      two    -0.470649 -1.706410
foo   one     0.834609 -0.600675
      two     0.005306  0.109989
qux   one    -0.713642 -0.173100
      two    -1.155766 -0.365946

Now, under the "second" level of the multiIndex, I want to add "three", and make it equals the difference between "one" and "two"

In below, A1 will be equal to 1.674156-(-1.061293)

A2 will be equal to -1.380391-(-0.620890)

something like this:

                     0         1
first second                    
bar   one     1.674156 -1.380391
      two    -1.061293 -0.620890
      three   A1        A2
baz   one     0.839065 -1.985679
      two    -2.086971 -1.415384
      three  
foo   one    -1.673192 -0.559783
      two     0.135445 -1.101833
      three  
qux   one    -0.605042  1.814256
      two     0.182851 -1.819808
      three

How can I do this? I know I can unstack the level1, do the diff, stack back. Just curious any better solution?

If my answer was helpful, feel free to upvote as well. It's appreciated. Thanks — piRSquared
– piRSquared, Commented Mar 28, 2017 at 14:54

piRSquared · Accepted Answer · 2017-02-28 02:12:12Z

1

use stack/unstack/assign

d = s.stack().unstack(1)
d.assign(three=d.one - d.two).stack().unstack(1)

                     0         1
first second                    
bar   one     0.877453  0.214777
      two    -0.741437  0.185339
      three   1.618890  0.029437
baz   one     1.358314 -0.315129
      two     1.881061  0.253034
      three  -0.522747 -0.568162
foo   one     1.663033 -0.879386
      two    -0.658539 -0.331162
      three   2.321572 -0.548224
qux   one    -0.171216 -0.510144
      two     0.855199 -0.653881
      three  -1.026415  0.143737

answered Feb 28, 2017 at 2:12

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding level to multiIndex dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related