Insert column in multiindex data frame

Question

I have a Multi-Index data frame and I want to add a column in level 1 and have it grouped in the appropriate level 0 column. When I assign the new column, it appends it to the end of the df.

In [28]: df
Out[28]: 
first        qux                 bar                 foo          
second       one       two       one       two       one       two
A      -0.563477 -0.032948 -0.131031  1.110537 -0.541374  0.760088
B      -1.767642 -1.305016 -0.786291 -0.396981  1.983372 -0.106018
C      -0.471136  0.616730  0.019877  0.910230  0.352304 -0.361370

In [29]: df['qux','three'] = [1,2,3]

In [30]: df
Out[30]: 
first        qux                 bar                 foo             qux
second       one       two       one       two       one       two three
A      -0.563477 -0.032948 -0.131031  1.110537 -0.541374  0.760088     1
B      -1.767642 -1.305016 -0.786291 -0.396981  1.983372 -0.106018     2
C      -0.471136  0.616730  0.019877  0.910230  0.352304 -0.361370     3

What I WANT it to look like is

first        qux                 bar                 foo           
second       one       two three      one       two       one       two
A      -0.563477 -0.032948     1 -0.131031  1.110537 -0.541374  0.760088
B      -1.767642 -1.305016     2 -0.786291 -0.396981  1.983372 -0.106018 
C      -0.471136  0.616730     3  0.019877  0.910230  0.352304 -0.361370

I tried df.sort_index(axis=1,level=0), which at least grouped the qux's together, but it alphabetized my level 0 headings. How can I get it to group the common column names without alphabetizing them?

Ami Tavory · Accepted Answer · 2016-03-02 19:26:00Z

2

Simply use:

df = df[['qux', 'bar', 'foo']]

Example (Different DataFrame)

Using a modification of the documentation for MultiIndex, here is something similar to your problem:

import pandas as pd
import numpy as np

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

# Here is your insertion
df['foo', 'three'] = range(4)

>>> df[['bar', 'qux', 'foo']]
    bar     qux     foo
    one     two     one     two     one     two     three
0   0.450777    -1.386835   0.423801    -0.386144   0.362138    2.566733    0
1   0.844537    2.466605    -0.093472   0.226886    0.633393    2.167570    1
2   1.655898    0.995926    0.097128    -0.351759   0.138233    1.099168    2
3   0.409964    -1.232129   1.112228    0.700660    -0.860548   0.219503    3

answered Mar 2, 2016 at 19:26

Ami Tavory

76.7k13 gold badges152 silver badges196 bronze badges

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

1 Comment

Katie R Over a year ago

Nifty!! Exactly what I needed.

Collectives™ on Stack Overflow

Insert column in multiindex data frame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related