134

I have the following pd.DataFrame:

Name    0                       1                      ...
Col     A           B           A            B         ...
0       0.409511    -0.537108   -0.355529    0.212134  ...
1       -0.332276   -1.087013    0.083684    0.529002  ...
2       1.138159    -0.327212    0.570834    2.337718  ...

It has MultiIndex columns with names=['Name', 'Col'] and hierarchical levels. The Name label goes from 0 to n, and for each label, there are two A and B columns.

I would like to subselect all the A (or B) columns of this DataFrame.

Improve this question
1

3 Answers 3

Reset to default
157

There is a get_level_values method that you can use in conjunction with boolean indexing to get the the intended result.

In [13]:

df = pd.DataFrame(np.random.random((4,4)))
df.columns = pd.MultiIndex.from_product([[1,2],['A','B']])
print df
          1                   2          
          A         B         A         B
0  0.543980  0.628078  0.756941  0.698824
1  0.633005  0.089604  0.198510  0.783556
2  0.662391  0.541182  0.544060  0.059381
3  0.841242  0.634603  0.815334  0.848120
In [14]:

print df.iloc[:, df.columns.get_level_values(1)=='A']
          1         2
          A         A
0  0.543980  0.756941
1  0.633005  0.198510
2  0.662391  0.544060
3  0.841242  0.815334
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Note that referring to the index level by name, df.columns.get_level_values("Col"), also seems to work, at least on Pandas 1.1.5.
70

Method 1:

df.xs('A', level='Col', axis=1)

for more refer to http://pandas.pydata.org/pandas-docs/stable/advanced.html#cross-section

Method 2:

df.loc[:, (slice(None), 'A')]

Caveat: this method requires the labels to be sorted. for more refer to http://pandas.pydata.org/pandas-docs/stable/advanced.html#the-need-for-sortedness-with-multiindex

Improve this answer

5 Comments

Is there a way to use the .xs() method with more than one column, say, columns A and B?
These solutions seem all overly complicated, there should be a more intuitive way to do this.
Note: xs cannot be used to set values. Slices have to be used for that.
@darXider According to the docstring, df.xs( ('A', 'B'), ...) is supposed to work.
@Seorendip: what is "overly complicated" in df.xs('A', level='Col', axis=1)? This call pretty much only gives the needed information and nothing else (I want the data with "Col" = "A", where you should look among the colum names), so I don't see how one could do better.
42

EDIT* Best way now is to use indexSlice for multi-index selections

idx = pd.IndexSlice
A = df.loc[:,idx[:,'A']]
B = df.loc[:,idx[:,'B']]
Improve this answer

1 Comment

Very elegant method 🤩 thanks for this!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.