python - Selecting columns from pandas.HDFStore table

Question

How can I retrieve specific columns from a pandas HDFStore? I regularly work with very large data sets that are too big to manipulate in memory. I would like to read in a csv file iteratively, append each chunk into HDFStore object, and then work with subsets of the data. I have read in a simple csv file and loaded it into an HDFStore with the following code:

tmp = pd.HDFStore('test.h5')
chunker = pd.read_csv('cars.csv', iterator=True, chunksize=10, names=['make','model','drop'])
tmp.append('df', pd.concat([chunk for chunk in chunker], ignore_index=True))

And the output:

In [97]: tmp
Out[97]:
<class 'pandas.io.pytables.HDFStore'>
File path: test.h5
/df     frame_table (typ->appendable,nrows->1930,indexers->[index])

My Question is how do I access specific columns from tmp['df']? The documenation makes mention of a select() method and some Term objects. The examples provided are applied to Panel data; however, and I'm too much of a novice to extend it to the simpler data frame case. My guess is that I have to create an index of the columns somehow. Thanks!

Answer 1

The way HDFStore records tables, the columns are stored by type as single numpy arrays. You always get back all of the columns, you can filter on them, so you will be returned for what you ask. In 0.10.0 you can pass a Term that involves columns.

store.select('df', [ Term('index', '>', Timestamp('20010105')), 
                     Term('columns', '=', ['A','B']) ])

or you can reindex afterwards

df = store.select('df', [ Term('index', '>', Timestamp('20010105') ])
df.reindex(columns = ['A','B'])

The axes is not really the solution here (what you actually created was in effect storing a transposed frame). This parameter allows you to re-order the storage of axes to enable data alignment in different ways. For a dataframe it really doesn't mean much; for 3d or 4d structures, on-disk data alignment is crucial for really fast queries.

0.10.1 will allow a more elegant solution, namely data columns, that is, you can elect certain columns to be represented as there own columns in the table store, so you really can select just them. Here is a taste what is coming.

 store.append('df', columns = ['A','B','C'])
 store.select('df', [ 'A > 0', Term('index', '>', Timestamp(2000105)) ])

Another way to do go about this is to store separate tables in different nodes of the file, then you can select only what you need.

In general, I recommend again really wide tables. hayden offers up the Panel solution, which might be a benefit for you now, as the actual data arangement should reflect how you want to query the data.