String "contains"-slicing on Pandas MultiIndex

user_2171758 0
Viewed 1569

How can I slice a MultiIndex by its string content? I.e. whether that particular index contains a certain string?

In [12]: df = pd.DataFrame({'a': ['a', 'ab', 'b'], 
                   'c': ['d', 'd', 'd'], 
                   'val': [1, 2 , 3]}).set_index(['a', 'c'])

In [13]: df

Out[13]:

val
a   c   
a   d   1
ab  d   2
b   d   3

In [15]: df.xs('a', level='a', drop_level=False)

Out[15]:

val
a   c   
a   d   1

In[16]: df.xs(contains('a'), level='a', drop_level=False)

Expected output:

Out[16]: 

a   c   
a   d   1
ab  d   2

Obviously that last bit is not possible.

  • How can this be done elegantly?
  • Can you do it case-insensitive in some way?
pandas python
3 Answers

Another method is to use query:

The DataFrame.index and DataFrame.columns attributes of the DataFrame instance are placed in the query namespace by default, which allows you to treat both the index and columns of the frame as a column in the frame.

>>> df.query('a.str.contains("a")') 
      val
a  c     
a  d    1
ab d    2

which IMO is a little more readable and succinct than

>>> df[df.index.get_level_values('a').str.contains('a')]
user_27057570

When index level is not critical, an alternative is to use df.filter(...) with regular expressions; super helpful when exploring data by either column or row. For example, this will give you the same answer with a bit less code:

df.filter(regex=re.compile('A',re.I),axis=0)

enter image description here

However, this filters at all index levels, df.filter(regex=re.compile('D',re.I),axis=0) will look at index "c" and it show this:

enter image description here

user_57523140
Related