Storing pandas DataFrame with mixed data and category into hdf5

Question

I want to store a dataFrame with different columns into an hdf5 file (find an excerpt with data types below).

In  [1]: mydf
Out [1]:
endTime             uint32
distance           float16
signature         category
anchorName        category
stationList         object

Before converting some columns (signature and anchorName in my excerpt above), I used code like following to store it (which works pretty fine):

path = 'tmp4.hdf5'
key = 'journeys'
mydf.to_hdf(path, key, mode='w', complevel=9, complib='bzip2')

But it does not work with category and then I tried following:

path = 'tmp4.hdf5'
key = 'journeys'
mydf.to_hdf(path, key, mode='w', format='t', complevel=9, complib='bzip2')

It works fine, if I remove the column stationList, where each entry is a list of strings. But with this column I got the following exception:

Cannot serialize the column [stationList] because
its data contents are [mixed] object dtype

How do I need to improve my code to get the data frame stored?

pandas version: 0.17.1
python version: 2.7.6 (cannot change it due to compability reasons)

---

edit1 (some sample code):

import pandas as pd

mydf = pd.DataFrame({'endTime' : pd.Series([1443525810,1443540836,1443609470]),
                    'distance' : pd.Series([454.75,477.25,242.12]),
                    'signature' : pd.Series(['ab','cd','ab']),
                    'anchorName' : pd.Series(['tec','ing','pol']),
                    'stationList' : pd.Series([['t1','t2','t3'],['4','t2','t3'],['t3','t2','t4']])
                    })

# this works fine (no category)
mydf.to_hdf('tmp_without_cat.hdf5', 'journeys', mode='w', complevel=9, complib='bzip2')

for col in ['anchorName', 'signature']:
    mydf[col] = mydf[col].astype('category')

# this crashes now because of category data
# mydf.to_hdf('tmp_with_cat.hdf5', 'journeys', mode='w', complevel=9, complib='bzip2')

# switching to format='t'   
# this caused problems because of "mixed data" in column stationList
mydf.to_hdf('tmp_with_cat.hdf5', 'journeys', mode='w', format='t', complevel=9, complib='bzip2')

mydf.pop('stationList')

# this again works fine
mydf.to_hdf('tmp_with_cat_without_stationList.hdf5', 'journeys', mode='w', format='t', complevel=9, complib='bzip2')

---

edit2: In the meanwhile I tried different things to get rid of this problem. One of these were to convert the entries of column stationList to tupels (possible since they shall not be changed) and to also convert it to category. But it did not change anything. Here are the lines I added after the conversion loop (just for completeness):

mydf.stationList = [tuple(x) for x in mydf.stationList.values]
mydf.stationList.astype('category')

Answer 1

You have two problems:

1. You want to store categorical data in a HDF5 file;
2. You're trying to store arbitrary objects (i.e. stationList) in a HDF5 file.

As you discovered, categorical data is (currently?) only supported in the "table" format for HDF5.

However, storing arbitrary objects (list of strings, etc.) is really not something that is supported by the HDF5 format itself. Pandas working around that for you by serializing these objects using pickle, and then storing the pickle as an arbitrary-length string (which is not supported by all HDF5 formats, I think). But that will be slow and inefficient, and will never be supported well by HDF5.

In my mind, you have two options:

1. Pivot your data so you have one row of data by station name. Then you can store everything in a table-format HDF5 file. (This is a good practice in general; see Hadley Wickham on Tidy Data.)
2. If you really want to keep this format, then you might as well save the whole dataframe using to_pickle(). This will have no problem dealing with any kind of object (e.g. list of strings, etc.) you throw at it.

Personally, I would recommend option 1. You get to use a fast, binary file format. And the pivot will also make other operations with your data easier.