Filtering values in pandas Dataframe by condition on index column

Question

Given that I have the following pandas DataFrame:

arrays = [np.array(['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux']),
          np.array(['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']),
          np.array([0.01, 0.2, 0.3, -0.5, 0.6, -0.7, -0.8, 0.9])]

tuples = list(zip(*arrays))
df_index = pd.MultiIndex.from_tuples(tuples, names=['A', 'B', 'measure'])

df = pd.DataFrame(np.random.randn(8, 4), index=df_index)
print(df)

How can I filter all the values where for example the measure column (which is part of the index) is greater than 0.2?

I have tried:

df.loc[:,:,0.1:0.9]

(and other variations of this, but I get the error "IndexingError: Too many indexers"

Thanks, Gerald

MaxU - stand with Ukraine · Accepted Answer · 2017-10-18 14:35:10Z

6

In [3]: df.query("measure > 0.2")
Out[3]:
                        0         1         2         3
A   B   measure
baz one 0.3      0.623507  0.602585 -0.792142  2.066095
foo one 0.6      0.138192 -0.159108 -1.796944  1.668463
qux two 0.9     -0.162210 -2.293951  0.602990  1.622783

or

In [6]: df.loc[pd.IndexSlice[:,:,0.200001:], :]
Out[6]:
                        0         1         2         3
A   B   measure
baz one 0.3      0.623507  0.602585 -0.792142  2.066095
foo one 0.6      0.138192 -0.159108 -1.796944  1.668463
qux two 0.9     -0.162210 -2.293951  0.602990  1.622783

answered Oct 18, 2017 at 14:35

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Comments

BENY · Accepted Answer · 2017-10-18 14:37:30Z

4

Something like get_level_values

df[df.index.get_level_values(2)>0.2]
Out[35]: 
                        0         1         2         3
A   B   measure                                        
baz one 0.3     -0.235196  0.183122 -1.620810  0.912996
foo one 0.6     -1.456278 -1.144081 -0.872170  0.547008
qux two 0.9      0.942656 -0.435219 -0.161408 -0.451456

answered Oct 18, 2017 at 14:37

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

zipa · Accepted Answer · 2017-10-18 14:40:17Z

2

This does the trick:

df.iloc[df.index.get_level_values(2) >= 0.2]

Or if you like:

df.iloc[df.index.get_level_values('measure') >= 0.2]

answered Oct 18, 2017 at 14:40

zipa

28k6 gold badges45 silver badges62 bronze badges

Comments

Phik · Accepted Answer · 2017-10-18 14:58:56Z

0

In line with your initial approach, you could use IndexSlice

df.sort_index().loc[pd.IndexSlice[:, :, 0.2:], :]

answered Oct 18, 2017 at 14:58

Phik

4343 silver badges16 bronze badges

Collectives™ on Stack Overflow

Filtering values in pandas Dataframe by condition on index column

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related