Automatic detection of HDF5 dataset identifier fails when data contains categoricals

[chrish42]

We use HDF5 to store our pandas dataframes on disk. We only store one dataframe per HDF5, so the feature of pandas.read_hdf() that allows omitting the key when a HDF file contains a single Pandas object is very nice for our workflow.

However, said feature doesn't work when the dataframe saved contains one or more categorical columns:

import pandas as pd

df = pd.DataFrame({'col1': [11, 21, 31], 'col2': ['a', 'b', 'a']})

# This works fine.
df.to_hdf('no_cat.hdf5', 'data', format='table')
df2 = pd.read_hdf('no_cat.hdf5')
print((df == df2).all().all())

# But this produces an exception.
df.assign(col2=pd.Categorical(df.col2)).to_hdf('cat.hdf5', 'data', format='table')
df3 = pd.read_hdf('cat.hdf5')

# ValueError: key must be provided when HDF file contains multiple datasets.

It looks like this is because pandas.read_hdf() doesn't ignore the metadata used to store the categorical codes:

print(pd.HDFStore('cat.hdf5'))

<class 'pandas.io.pytables.HDFStore'>
File path: cat.hdf5
/data                                     frame_table  (typ->appendable,nrows->3,ncols->2,indexers->[index])             
/data/meta/values_block_1/meta            series_table (typ->appendable,nrows->2,ncols->1,indexers->[index],dc->[values])

it'd be nice if this feature worked even when some of the columns are categoricals. It should be possible to ignore that metadata that pandas creates when looking if there is only one dataset stored, no?

[chrish42]

Cc @laufere.

[jreback]

yeah this detection needs to be a bit smarter to consider the uniques of the top-level groups (rather than just multiple keys). should be a straightforward fix.

[jreback]

pull-requests welcome!

[pfrcks]

@jreback While looking in the code, it seems that in such a case the list returned by store.keys() is empty which causes it to produce error.
However as you mention just checking the keys will not be a good approach.

store.groups()

[/data (Group) ''
children := ['table' (Table), 'meta' (Group)], /data/meta/values_block_1/meta (Group) ''
children := ['table' (Table)]]

Running the above code yields the results as shown above. Now if we compare this with a HDF5 file which actually has two datasets we get something like this:

import pandas as pd
df = pd.DataFrame({'col1': [11, 21, 31], 'col2': ['a', 'b', 'a']})
df2 = pd.DataFrame({'col1': [11, 21, 31], 'col2': ['a', 'b', 'acc']})
df.to_hdf('no_cat.hdf5', 'data', format='table')
df2.to_hdf('no_cat.hdf5', 'data2', format='table')
df3 = pd.read_hdf('no_cat.hdf5')
Traceback (most recent call last):
File "", line 1, in
File "pandas/io/pytables.py", line 336, in read_hdf
raise ValueError('key must be provided when HDF file contains '
ValueError: key must be provided when HDF file contains multiple datasets.
pd.HDFStore('no_cat.hdf5').groups()
[/data (Group) ''
children := ['table' (Table)], /data2 (Group) ''
children := ['table' (Table)]]

As we can see, if there are actually two datasets, instead of meta(Group) , we get /data2(Group) where data2 is the key provided when writing to the file.
Maybe we can leverage this?

[pfrcks]

@jreback One more thing comes to mind along the lines of what you suggested.

pd.HDFStore('cat.hdf5').keys()
['/data', '/data/meta/values_block_1/meta']

Now we can use these keys to get the unique values

df = pd.HDFStore('cat.hdf5').select('/data')
np.unique(df)
array([11, 21, 31, 'a', 'b'], dtype=object)

df = pd.HDFStore('cat.hdf5').select('/data/meta/values_block_1/meta')
np.unique(df)
array(['a', 'b'], dtype=object)

We can see, that the uniques produced by giving /data/meta/values_block_1/meta as a key is a subset of when we provide /data. But if we go down this road we will also have to consider the key name while making a decision because it might happen that there are two dataframes in the hdf5 file where the uniques of one is a subset of other.

Am I missing something here?

[jreback]

@chrish42 you can just iterate over the groups with tables. look at how .keys() is implemented.

[pfrcks]

@jreback did you want to refer to me or to chrish42 only?

[jreback]

oh sorry meant that as a general comment

[pfrcks]

@jreback Using the approach you suggested, I can get key names and then use them to get the individual tables as well, but my question, like I asked in a comment before, is that using those tables, even if we get unique values from both the tables, how can we be certain that one of them has meta information just because the unique values of one of the tables is a subset of another?

[pfrcks]

@jreback any comments?Can you guide me in the right direction?