Another HDFStore error

[jostheim]

After a long run of extracting features for some random forest action I ran into this when serializing the features:

Traceback (most recent call last):
File "XXXXX.py", line 1043, in
write_dataframe("features", all_df, store)
File "XXXXX.py", line 55, in write_dataframe
store[name] = df
File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 218, in setitem
self.put(key, value)
File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 458, in put
self._write_to_group(key, value, table=table, append=append, *_kwargs)
File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 788, in _write_to_group
s.write(obj = value, append=append, complib=complib, *_kwargs)
File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 1837, in write
self.write_array('block%d_values' % i, blk.values)
File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 1639, in write_array
self.handle.createArray(self.group, key, value)
File "/Library/Python/2.7/site-packages/tables-2.4.0-py2.7-macosx-10.8-intel.egg/tables/file.py", line 780, in createArray
object=object, title=title, byteorder=byteorder)
File "/Library/Python/2.7/site-packages/tables-2.4.0-py2.7-macosx-10.8-intel.egg/tables/array.py", line 167, in init
byteorder, _log)
File "/Library/Python/2.7/site-packages/tables-2.4.0-py2.7-macosx-10.8-intel.egg/tables/leaf.py", line 263, in init
super(Leaf, self).init(parentNode, name, _log)
File "/Library/Python/2.7/site-packages/tables-2.4.0-py2.7-macosx-10.8-intel.egg/tables/node.py", line 250, in init
self._v_objectID = self._g_create()
File "/Library/Python/2.7/site-packages/tables-2.4.0-py2.7-macosx-10.8-intel.egg/tables/array.py", line 200, in _g_create
nparr, self._v_new_title, self.atom)
File "hdf5Extension.pyx", line 884, in tables.hdf5Extension.Array._createArray (tables/hdf5Extension.c:8498)
tables.exceptions.HDF5ExtError: Problems creating the Array.

The error is pretty undefined, I know the table I was writing was big, >17000 columns by > 20000 rows. There are lots of np.nan's in the columns.

Since I seem to be one of the few who are serializing massive sets, and I have a 64GB RAM machine sitting next to me, are there some test cases that I can run, or write that would help? Thinking setting up large mixed dataframes etc...

[jreback]

you are actually creating Storer objects here (e.g. this is NOT queryable), is this what you want?

there are some test cases that are skipped, look in pandas\io\tests\test_pytables.py,
named like test_big_.....;

these are pretty big tables, mainly that have lots of rows, but also are a few hundred columns wide.
they don't directly test what you are doing, as never tried to store a VERY wide table (as a put), in theory should work.

are you going to retrieve in its entirety?

can you give a summary picture of your frame that you are storing?
the nans what type of columns are they in?

do you get the performance warning when trying to store this?

and try to store these as tables df.append('df',df), you can pass chunksize= to write it a chunk at a time
http://pandas.pydata.org/pandas-docs/stable/io.html#performance

you might want to take a look at: http://pandas.pydata.org/pandas-docs/stable/io.html#multiple-table-queries

[jostheim]

@jreback

Yes I am creating a store object and putting data in like

store['features'] = df

Thanks for the pointers to the tests, I'll take a look and write some large ones.

Yes I want to retrieve it entirely, I have my 64GB machine to run ML algorithms on and most of those (though not SVM) want the data in a big chunk.

I do not get a performance warning on this store b/c in actuality I try to guarantee it is all floats (again the ML libraries blow up on non-floats). However I do get the performance warnings on other df's I write that are most definitely mixed.

I'll try using append and a chunk size next, unfortunately it takes 8 hours to extract all these features, so playing with my real data is painful. Hence why I want to write some tests...

Thanks again, and I know I still owe you some data on my other ticket.

[jreback]

ok..let me know.....but as a fyi....I would for sure split this into sub-tables (e.g. split by columns), then get them and concat - pytables seems to work better (for me) when you have more row-oriented tables (rather than lots of columns)

[jostheim]

You are surely right, I am just being lazy. I guess I'll write some wrappers to split and reform my tables for me so I can facade away the complexity.

[jreback]

another plug for Tables!, look at append_multiple_tables...this will split for you (you can specify a dict how to split)

[jreback]

could add this same functionailty to storers as well.....

[jostheim]

Awesome, I'll check that out! Thanks again!

[jostheim]

Can't test these yet b/c my big machine is running the random forest (wrote to csv as well as hdf5 after the last failure, so used the csv file to read back in), but here is my code for breaking up a big table:

def write_dataframe(name, df, store):
    ''' Write a set of keys to our store representing N columns each of a larger table '''
    keys = {}
    buffered = []
    for i, col in enumerate(df.columns):
        buffered.append(col)
        if len(buffered == 100):
            keys["{0}_{1}".format(name, i)] = buffered
            buffered = []
    if len(buffered) > 0:
        keys["{0}_{1}".format(name, i)] = buffered
    store.append_to_multiple(keys, df, keys.keys()[0])


def read_dataframe(name, store):
    ''' Read a set of keys from our store representing N columns each of a larger table
     and then join the pieces back into the full table. '''
    keys = []
    i = 0
    while True:
        if "{0}_{1}".format(name, i) in store.keys():
            keys.append("{0}_{1}".format(name, i))
        else:
            break
        i += 1
    return store.select_as_multiple(keys)

Does this look reasonable?

[jreback]

yes...since you are only storing 20000 rows, then each table will be very fast to store, i'd say you could
do 500 columns in each table (try out combinations), assuming this is all similar dtype data, e.g. float64/32?

and btw...I am thinking about wrapping this type of code in a Splitter class, which will save alongside the tables, to make this easier

here is a similar idiom to batch

def create_batches(l, maxn = 250):
    """ create a list of batches, maxed at maxn """
    batches = []
    while(True):
        if len(l) <= maxn:
            if len(l) > 0:
                batches.append(l)
            break
        batches.append(l[0:maxn])
        l = l[maxn:]
    return batches

[jostheim]

If indeed there is a problem with storing large tables, then encapsulation of this inside pandas might be a good idea. My features data is all float64/32, but my other intermediate files are not (they are mixed types), so it might be slow till I get to the actual feature extraction. I don't really care about speed as long as I can be sure that my work in progress is not going to be lost. Without pickle working on large amounts of data I think pandas really needs a reliable alternative for saving work, hopefully this will be it.

I'll test as soon as my random forest finishes...

[jreback]

this should work, and your dataset actually isn't that big (in row-space), you have large column-space;
pytables/hd5f is designed for many rows (like many millions)
mixed types should work as well

It think this solution will work; you maybe also want to give carray a try (its actually by the same author as pytables), but no interface from pandas
https://github.com/FrancescAlted/carray

[jostheim]

Agreed, my data isn't really THAT big, but it is big the wrong way :) If this works then I am a happy camper, I'd like to keep everything in pandas, it was invaluable in joining together all the data into this monstrosity I am serializing.

At the very least this thread will hopefully point out a solution to anyone with the same problems! Once I test I'll close this up.

[jreback]

your use case is interesting (and different) from others I have been seeing later
it's all about data orientation and use case for how HDFStore should be storing things
we have straight stores and tables now, each has different uses
I think yours (and one which is essentially a really giant table on disk)
need to be handled (prob with some hints when u r storing)

btw I think u might be a me to pass index = False to append (as u really don't need indicies and they take some time to create)

also definitely try using a csv file - as I think u may need to experiment a bit to optimize your performance

u can directly email if u would like
jeff@reback.net

[jostheim]

I tried running with the code above and got the following:

time.datetime' has no len()
Traceback (most recent call last):
  File "XxXXX.py", line 803, in get_joined_data
    write_dataframe("{0}joined_{1}".format(prefix, date_prefix), df, store)
  File "XXXX.py", line 74, in write_dataframe
    store.append_to_multiple(keys, df, keys.keys()[0])
  File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 591, in append_to_multiple
    self.append(k, val, data_columns=dc, **kwargs)
  File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 532, in append
    self._write_to_group(key, value, table=True, append=True, **kwargs)
  File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 788, in _write_to_group
    s.write(obj = value, append=append, complib=complib, **kwargs)
  File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 2491, in write
    min_itemsize=min_itemsize, **kwargs)
  File "/Library/Python/2.7/site-packages/pandas/io/pytables.py", line 2254, in create_axes
 raise Exception("cannot find the correct atom type -> [dtype->%s,items->%s] %s" % (b.dtype.name, b.items, str(detail)))
Exception: cannot find the correct atom type -> [dtype->object,items->Index([....object of type 'datetime.datetime' has no len()

Same thing for datetime64's. The columns have nan's expressed as np.nan. Not quite sure what is going on.

[jreback]

assume you are running with 0.10.1
have a look at PR #2752 (first example)

you MUST have datetime64[ns] dtypes in the columns in order to store, you CANNOT have np.nan (this is a float type), and thus the column will be object, instead the nan MUST be NaT

if you do not (and the exception tells you that), then cast them like this (this is a bug which is fixed in the above PR)

df['datetime'] = Series(df['datetime'].values,dtype='M8[ns]')

you also cann't have datetimes, again cast like the above, they will be converted to Timestamps

PyTables cannot store 'object' types efficiently, so we dont' allow it; HDFStore also cannot guess as 'object' generally represents string (but can also be unicode, or other types which cannot be serialized)

if you can post a str(df) would be helpful