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 : tmp Out: <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!
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'])
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.
You can store the dataframe with an index of the columns as follows:
import pandas as pd import numpy as np from pandas.io.pytables import Term index = pd.date_range('1/1/2000', periods=8) df = pd.DataFrame( np.random.randn(8,3), index=index, columns=list('ABC')) store = pd.HDFStore('mydata.h5') store.append('df_cols', df, axes='columns')
and then select as you might hope:
In : store.select('df_cols', [Term('columns', '=', 'A')]) Out: 2000-01-01 0.347644 2000-01-02 0.477167 2000-01-03 1.419741 2000-01-04 0.641400 2000-01-05 -1.313405 2000-01-06 -0.137357 2000-01-07 -1.208429 2000-01-08 -0.539854
In : df Out: A B C 2000-01-01 0.347644 0.895084 -1.457772 2000-01-02 0.477167 0.464013 -1.974695 2000-01-03 1.419741 0.470735 -0.309796 2000-01-04 0.641400 0.838864 -0.112582 2000-01-05 -1.313405 -0.678250 -0.306318 2000-01-06 -0.137357 -0.723145 0.982987 2000-01-07 -1.208429 -0.672240 1.331291 2000-01-08 -0.539854 -0.184864 -1.056217
To me this isn't an ideal solution, as we can only indexing the DataFrame by one thing! Worryingly the docs seem to suggest you can only index a DataFrame by one thing, at least using
Pass the axes keyword with a list of dimension (currently must by exactly 1 less than the total dimensions of the object).
I may be reading this incorrectly, in which case hopefully someone can prove me wrong!
Note: One way I have found to index a DataFrame by two things (index and columns), is to convert it to a Panel, which can then retrieve using two indices. However then we have to convert to the selected subpanel to a DataFrame each time items are retrieved... again, not ideal.