Incorrect behavior of hdfstore.select for categorical columns (pandas, hdf5)

[moskalev]

The following example replicates the issue:

import pandas as pd

df = pd.DataFrame(["a","b","c","a"], columns=['col1'])

cats = ["c","b","a"]
df['col1'] = pd.Categorical(df['col1'], categories=cats)

df

col1
0	a
1	b
2	c
3	a

store = pd.HDFStore('bug.h5', complevel=9, complib='blosc:blosclz')
store.append('df', df, data_columns=df.columns, expectedrows=4)
store.select('df', "col1='a'")

col1
2	c

while I expected to get

col1
0	a
3	a

Sorting the categories list before calling pd.Categorical solves the issue for now.

Modules:

pytables                  3.4.3            py36h02b9ad4_0    anaconda
pandas                    0.23.4           py36hf8a1672_0    conda-forge
hdf5                      1.10.1               h9caa474_1  

[FrancescAlted]

Are you sure PyTables is the faulty part here? Any possibility that this is an actual issue in pandas?

[moskalev]

I'm not sure. I do not know why this bug happens and where the issue resides.

[PaulAmosKreiner]

I know am late to the party but google still links here prominently when looking for info & best practices around storing categories in the stack pandas > pytables > HDF.

1. yes, this is reproducible. especially does it not depend on the non-minimal components of the provided code: choosing "fixed" format over "table" in HDFStore(), using compression in HDFStore(), using expectedrows in append().
2. AFAIK, categories are still not officially supported for use in pandas. (https://pandas.pydata.org/pandas-docs/dev/user_guide/io.html#storing-types) therefore the only issue that I see here is for sure not in PyTables and would be that pandas does not warn you from using this untested dtype. (Or, if they mean to support the dtype, they should update their IO guide...) I submitted an issue there with reference to here: https://github.com/pandas-dev/pandas/issues/38131
3. the workaround that works for me is never to provide explicit categories to the pandas.Categorical. Inferred categories work – at least they never failed me and they don't fail in the provided code example.

import pandas as pd

df = pd.DataFrame(["a","b","c","a"], columns=['col1'])

df['col1'] = pd.Categorical(df['col1']) # don't provide categories here, let pandas infer them
cats = df.col1.dtype.categories # grab the inferred categories for reference

store = pd.HDFStore('bug.h5', complevel=9, complib='blosc:blosclz')
store.append('df', df, data_columns=df.columns)
store.select('df', "col1='a'")

(providing categories in order of occurence also works. but that's just risky and error-prone. the method above seems to be doing the job reproducibly)

[avalentino]

@moskalev I'm incline to think that this is not a PyTables issue.
Please provide a clear example to reproduce the issue using the PyTables API.