19

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!

Zelazny7
  • 39,946
  • 18
  • 70
  • 84

3 Answers3

14

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.

Jeff
  • 125,376
  • 21
  • 220
  • 187
13

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 [8]: store.select('df_cols', [Term('columns', '=', 'A')])
Out[8]: 
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

Where:

In [9]: df
Out[9]: 
                   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 axes:

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.

Andy Hayden
  • 359,921
  • 101
  • 625
  • 535
  • Should the index be datetime? I used chars as index, and proceeded the same way you did. However, when I came by the select statement in your code (in[8]). It retrieved the whole dataframe. Am I missing something here ?? – JustInTime Dec 25 '12 at 23:48
  • see my answer below; your are basically storing a transposed frames. the axes argument is not needed here – Jeff Dec 30 '12 at 02:22
  • 3
    Most things in this example works for me except this line: `store.append('df_cols', df, axes='columns')` It raises the error `ValueError: No axis named c for object type ` If you replace that line with: `store.append('df_cols', df, data_columns=True)` then the error goes away and the rest of the example works. – XValidated May 03 '15 at 19:18
  • @karenyng as Jeff says above, the axes argument is not needed. I think it's actually been deprecated (and removed) since this answer was posted... :s Thanks! – Andy Hayden May 03 '15 at 20:19
0

From now on，U can use query expression instead Term construction. e.g: store.select('df', "index > Timestamp('20000105')")

Eagle Liang
  • 3
  • 2