pandas multiindex - how to select second level when using columns?

Also using John's data sample:

Using xs() is another way to slice a MultiIndex:

df               0stock1 price   1       volume  2stock2 price   3       volume  4stock3 price   5       volume  6df.xs('price', level=1, drop_level=False)              0stock1 price  1stock2 price  3stock3 price  5

Alternatively if you have a MultiIndex in place of columns:

df  stock1        stock2        stock3          price volume  price volume  price volume0      1      2      3      4      5      6df.xs('price', axis=1, level=1, drop_level=False)  stock1 stock2 stock3   price  price  price0      1      3      5

Using @JohnZwinck's data sample:

In [132]: dfOut[132]:               0stock1 price   1       volume  2stock2 price   3       volume  4stock3 price   5       volume  6

Option 1:

In [133]: df.loc[(slice(None), slice('price')), :]Out[133]:              0stock1 price  1stock2 price  3stock3 price  5

Option 2:

In [134]: df.loc[pd.IndexSlice[:, 'price'], :]Out[134]:              0stock1 price  1stock2 price  3stock3 price  5

UPDATE:

But what if for the 2nd Index, I want to select everything but priceand there are multiple values so that enumeration is not an option. Isthere something like slice(~'price')

first let's name the index levels:

df = df.rename_axis(["lvl0", "lvl1"])

now we can use the df.query() method:

In [18]: df.query("lvl1 != 'price'")Out[18]:               0lvl0   lvl1stock1 volume  2stock2 volume  4stock3 volume  6

df.unstack() will "tear off" the last level of your MultiIndex and make your DataFrame a lot more conventional, with one column per type of data. For example:

index = pd.MultiIndex.from_product([['stock1','stock2','stock3'],['price','volume']])df = pd.DataFrame([1,2,3,4,5,6], index)print(df.unstack())

Gives you:

           0              price volumestock1     1      2stock2     3      4stock3     5      6