categories in HDFStore don't filter correctly

[michaelaye]

Code Sample, a copy-pastable example if possible

obsids = ['ESP_012345_6789', 'ESP_987654_3210']
imgids = ['APF00006np', 'APF0001imm']
data = [4.3, 9.8]
df = pd.DataFrame(dict(obsids=obsids, imgids=imgids, data=data))
df.to_hdf('testdf_no_cats.hdf', 'df',format='t', data_columns=True)
df.obsids = df.obsids.astype('category')
df.imgids = df.imgids.astype('category')
df.to_hdf('testdf_with_cats.hdf', 'df',format='t', data_columns=True)
print("No categories:")
print(pd.read_hdf('testdf_no_cats.hdf', 'df', where='obsids=B'))
print("With categories:")
print(pd.read_hdf('testdf_with_cats.hdf', 'df', where='obsids=B'))

Actual Output:

No categories:
Empty DataFrame
Columns: [data, imgids, obsids]
Index: []
With categories:
   data      imgids           obsids
0   4.3  APF00006np  ESP_012345_6789

Expected Output

Empty DataFrame for both cases.

output of pd.show_versions()

INSTALLED VERSIONS

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

pandas: 0.13.1+5155.g9baf452
nose: None
pip: 8.1.1
setuptools: 21.2.1
Cython: 0.24
numpy: 1.11.0
scipy: 0.17.1
statsmodels: None
xarray: 0.7.2
IPython: 4.2.0
sphinx: 1.4.1
patsy: None
dateutil: 2.5.3
pytz: 2016.4
blosc: None
bottleneck: None
tables: 3.2.2
numexpr: 2.5.2
matplotlib: 1.5.1
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: 4.4.1
html5lib: 0.9999999
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.8
boto: None
pandas_datareader: None

[jreback]

ok the issue is here:

> /Users/jreback/pandas/pandas/computation/pytables.py(198)convert_value()
-> result = metadata.searchsorted(v, side='left')
(Pdb) p metadata
array(['ESP_012345_6789', 'ESP_987654_3210'], dtype=object)
(Pdb) l
193             elif kind == u('timedelta64') or kind == u('timedelta'):
194                 v = _coerce_scalar_to_timedelta_type(v, unit='s').value
195                 return TermValue(int(v), v, kind)
196             elif meta == u('category'):
197                 metadata = com._values_from_object(self.metadata)
198  ->             result = metadata.searchsorted(v, side='left')
199                 return TermValue(result, result, u('integer'))
200             elif kind == u('integer'):
201                 v = int(float(v))
202                 return TermValue(v, v, kind)
203             elif kind == u('float'):
(Pdb) n
> /Users/jreback/pandas/pandas/computation/pytables.py(199)convert_value()
-> return TermValue(result, result, u('integer'))
(Pdb) p result
0
(Pdb) p metadata
array(['ESP_012345_6789', 'ESP_987654_3210'], dtype=object)
(Pdb) p metadata.searchsorted(v, side='left')
0

We are searching for 'B' in the categories. And we get 0 when searched from the left, which actually means its not there (or IS the first element).

So you need to check if it IS the first element

(Pdb) p metadata.searchsorted('ESP_012345_6789', side='left')
0

If not, then prob easiest to just return -1 (which is not a valid code, rather than 0 which IS)

want to do a PR?

[michaelaye]

Sorry, not this time, I'm teaching for the first time this month.

[jreback]

np thanks for the report

[michaelaye]

I don't understand the comments with "IS the first element".
The query "obsids=B" should only return on equality, and shouldn't care where something is positioned in the categories?

[jreback]

In [5]: arr = np.array(['ESP_012345_6789', 'ESP_987654_3210'], dtype=object)

In [6]: arr.searchsorted(arr[0], 'left')
Out[6]: 0

In [7]: arr.searchsorted(arr[1], 'left')
Out[7]: 1

In [8]: arr.searchsorted('foo', 'left')
Out[8]: 2

In [9]: arr.searchsorted('B', 'left')
Out[9]: 0