Most efficient way to exclude indexed rows in pandas dataframe

pandas, python

Solution

This is a Python issue, not a `pandas` one: `'state' != 'Colorado'` is True, so what `pandas` gets is `data.ix[[True]]`.

You could do

>>> data.loc[data.index != "Colorado"]
number    one  two  three
state                    
Ohio        0    1      2
New York    6    7      8

[2 rows x 3 columns]

or use `DataFrame.query`:

>>> data.query("state != 'New York'")
number    one  two  three
state                    
Ohio        0    1      2
Colorado    3    4      5

[2 rows x 3 columns]

if you don't like the duplication of `data`. (Quoting the expression passed to the `.query()` method is one of the only ways around the fact that otherwise Python would evaluate the comparison before `pandas` ever saw it.)

Problem

I'm relatively new to Python & pandas and am struggling with (hierachical) indexes. I've got the basics covered, but am lost with more advanced slicing and cross-sectioning. For example, with the following dataframe ``` import pandas as pd import numpy as np data = pd.DataFrame(np.arange(9).reshape((3, 3)), index=pd.Index(['Ohio', 'Colorado', 'New York'], name='state'), columns=pd.Index(['one', 'two', 'three'], name='number')) ``` I want to select everything except the row with index 'Colorado'. For a small dataset I could do: ``` data.ix[['Ohio','New York']] ``` But if the number of unique index values is large, that's impractical. Naively, I would expect a syntax like ``` data.ix[['state' != 'Colorado']] ``` However, this only returns the first record 'Ohio' and doesn't return 'New York'. This works, but is cumbersome ``` filter = list(set(data.index.get_level_values(0).unique()) - set(['Colorado'])) data[filter] ``` Surely there's a more Pythonic, verbose way of doing this?

Original source