pandas Multiindex columns method selection returns all columns instead of subset

Question

Hi here is an example of behaviour I don't understand. Here is an example of multiindex in columns

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
          ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])

df = pd.DataFrame(np.random.randn(3, 8), index=['A', 'B', 'C'], columns=index)

Now i'd like to select a subset of df by the first level and return the relevant columns:

df.loc[:, ['bar']].columns

returns

MultiIndex(levels=[['bar'], ['one', 'two']],
           labels=[[0, 0], [0, 1]],
           names=['first', 'second'])

but

df.loc[:, ['bar', 'baz']].columns

returns

MultiIndex(levels=[['bar', 'baz', 'foo', 'qux'], ['one', 'two']],
           labels=[[0, 0, 1, 1], [0, 1, 0, 1]],
           names=['first', 'second'])

Any reason why the second one would return ALL the column names instead of

MultiIndex(levels=[['bar', 'baz'], ['one', 'two']] etc...

And more importantly any quick fix so I can return only the relevant data?

This is made even more relevant as pandas are deprecating Panels (which used to be a very elegant way to store multidimensional data)

jezrael · Accepted Answer · 2017-05-10 13:50:59Z

2

In new pandas version (0.20.1) use MultiIndex.remove_unused_levels:

print (df.loc[:, ['bar', 'baz']].columns)
MultiIndex(levels=[['bar', 'baz', 'foo', 'qux'], ['one', 'two']],
           labels=[[0, 0, 1, 1], [0, 1, 0, 1]],
           names=['first', 'second'])

print (df.loc[:, ['bar', 'baz']].columns.remove_unused_levels())
MultiIndex(levels=[['bar', 'baz'], ['one', 'two']],
           labels=[[0, 0, 1, 1], [0, 1, 0, 1]],
           names=['first', 'second'])

Advanced shown levels in documentation.

edited May 10, 2017 at 13:50

answered May 10, 2017 at 13:46

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Tanguy Bretagne Over a year ago

Thanks very much. This is a very odd behaviour imo given it doesn't behave the same for one or many.

Collectives™ on Stack Overflow

pandas Multiindex columns method selection returns all columns instead of subset

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