Pandas: Multiindex query

[wcbeard]

Code Sample, a copy-pastable example if possible

If I have a Df with a MultiIndex:

import numpy.random as nr
nr.seed(0)
d = DataFrame(nr.randint(0, 5, (4, 2)),
        columns=pd.MultiIndex.from_product([['L1'], ['A', 'B']]))
d
'''   L1   
       A  B
    0  4  0
    1  3  3
    2  3  1
    3  3  2'''

and want to use the query method to get the following subset:

d[d.L1.A == 3]
'''  
  L1   
   A  B
1  3  3
2  3  1
3  3  2'''

query doesn't seem to know about the column:

try:
    d.query('L1.A == 3')
except Exception as e:
    print(e)
# => name 'L1' is not defined

Expected Output

'''  
      L1   
       A  B
    1  3  3
    2  3  1
    3  3  2'''

Am I referencing the column incorrectly, or is this just not possible at the moment?

output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.5.1.final.0
python-bits: 64
OS: Darwin
OS-release: 15.3.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.18.1
nose: None
pip: 8.1.2
setuptools: 21.2.1
Cython: 0.24
numpy: 1.11.0
scipy: 0.17.1
statsmodels: None
xarray: None
IPython: 3.2.3
sphinx: None
patsy: 0.4.1
dateutil: 2.5.3
pytz: 2016.4
blosc: None
bottleneck: None
tables: None
numexpr: 2.5.2
matplotlib: 1.5.1
openpyxl: None
xlrd: 0.9.4
xlwt: None
xlsxwriter: None
lxml: None
bs4: 4.4.1
html5lib: 0.999
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.8
boto: 2.40.0
pandas_datareader: None

[jreback]

this only partially supported, see the docs here

I'll mark it as a bug and if you'd like to submit a PR to fix, great.

[wcbeard]

Would the dot syntax I used above be the preferred way? (.query('L1.A == 3'))

[jreback]

it might be very tricky to implement the dot syntax (and it's not standard)
maybe better to accept tuples

"(L1,A) == 3"

[jreback]

@harshul1610 that is not valid python

[harshul1610]

Don't know why, but this one also works print d.query('@d.L1.A==3')

[jreback]

@skaterdonza pushing a fix yourself would get this in
we have 2600 issues