Query Hdf5 In Pandas

I have following data (18,619,211 rows) stored as a pandas dataframe object in hdf5 file: date id2 w id 100010 1980-03-31 10

Solution 1:

here are the docs for querying on non-index columns.

Create the test data. It is not clear how the original frame is constructed, e.g. whether its unique data and the ranges, so I have created a sample, with 10M rows, and a multi-level date range with the id column.

In [60]: np.random.seed(1234)

In [62]: pd.set_option('display.max_rows',20)

In [63]: index = pd.MultiIndex.from_product([np.arange(10000,11000),pd.date_range('19800101',periods=10000)],names=['id','date'])

In [67]: df = DataFrame(dict(id2=np.random.randint(0,1000,size=len(index)),w=np.random.randn(len(index))),index=index).reset_index().set_index(['id','date'])

In [68]: df
Out[68]: 
                  id2         w
id    date                     
100001980-01-017120.3713721980-01-02718 -1.2557081980-01-03581 -1.1827271980-01-04202 -0.9474321980-01-05493 -0.1253461980-01-067520.3802101980-01-07435 -0.4441391980-01-08128 -1.8852301980-01-094251.6036191980-01-104490.103737
...               ...       ...
109992007-05-0980.6245322007-05-106690.2683402007-05-119180.1348162007-05-12979 -0.7694062007-05-13969 -0.2421232007-05-14950 -0.3478842007-05-1549 -1.2848252007-05-16922 -1.3139282007-05-17347 -0.5213522007-05-183530.189717

[10000000 rows x 2 columns]

Write the data to disk, showing how to create a data column (note that the indexes are automatically queryable, this allows id2 to be queryable as well). This is de-facto equivalent to doing. This takes care of opening and closing the store (you can accomplish the same thing by opening a store, appending, and closing).

In order to query a column, it MUST BE A DATA COLUMN or an index of the frame.

In [70]: df.to_hdf('test.h5','df',mode='w',data_columns=['id2'],format='table')

In [71]: !ls -ltr test.h5
-rw-rw-r-- 1 jreback users 430540284 May 26 17:16 test.h5

Queries

In [80]: ids=[10101,10898]

In [81]: start_date='20010101'

In [82]: end_date='20010301'

You can specify dates as string (either in-line or as variables; you can also specify Timestamp like objects)

In [83]:pd.read_hdf('test.h5','df',where='date>start_date&date<end_date')Out[83]:id2widdate100002001-01-02  972-0.1461072001-01-03  9541.4204122001-01-04  5671.0776332001-01-05   87-0.0428382001-01-06   79-1.7912282001-01-07  7441.1104782001-01-08  237-0.8460862001-01-09  998-0.6963692001-01-10  266-0.5955552001-01-11  206-0.294633.........109992001-02-19  616-0.7450682001-02-20  577-1.4747482001-02-21  990-1.2768912001-02-22  939-1.3695582001-02-23  621-0.2143652001-02-24  396-0.1421002001-02-25  492-0.2049302001-02-26  4781.8392912001-02-27  6880.2915042001-02-28  356-1.987554

[58000rowsx2columns]

You can use in-line lists

In [84]:pd.read_hdf('test.h5','df',where='date>start_date&date<end_date&id=ids')Out[84]:id2widdate101012001-01-02  7221.6205532001-01-03  849-0.6034682001-01-04  635-1.4190722001-01-05  3310.5216342001-01-06  7300.0088302001-01-07  706-1.0064122001-01-08   591.3800052001-01-09  6890.0178302001-01-10  788-3.0908002001-01-11  704-1.491824.........108982001-02-19  530-1.0311672001-02-20  652-0.0192662001-02-21  4720.6382662001-02-22  540-1.8272512001-02-23  654-1.0201402001-02-24  328-0.4774252001-02-25  871-0.8926842001-02-26  1660.8941182001-02-27  8060.6482402001-02-28  824-1.051539

[116rowsx2columns]

You can also specify boolean expressions

In [85]:pd.read_hdf('test.h5','df',where='date>start_date&date<end_date&id=ids&id2>500&id2<600')Out[85]:id2widdate101012001-01-12  534-0.2206922001-01-14  596-2.2253932001-01-16  5960.9562392001-01-30  513-2.5289962001-02-01  572-1.8773982001-02-13  569-0.9407482001-02-14  5411.0356192001-02-21  571-0.116547108982001-01-16  5910.0825642001-02-06  5860.4708722001-02-10  531-0.5361942001-02-16  5860.9499472001-02-19  530-1.0311672001-02-22  540-1.827251

To answer your actual question I would do this (their is really not enough information, but I'll put some reasonable expectations):

• Do't loop over queries, unless you have a very small number of absolute queries
• Read the biggest chunk into memory that you can. Usually this is accomplished by selecting out the biggest ranges of data that you need, even if you select MORE data than you actually need.
• Then subselect using in-memory expressions, which will generally be orders of magnitude faster.
• List elements are limited to about 30 elements in total (this is current an implementation limit on the PyTables side). It will work if you specify more, but what will happen is that you will read in a lot of data, then it will be reindexed down (in-memory). So user needs to be aware of this.

So for example say that you have 1000 unique ids with 10000 dates per as my example demonstrates. You want to select say 200 of these, with a date range of 1000.

So in this case I would simply select on the dates then do the in-memory comparison, something like this:

df = pd.read_hdf('test.h5','df',where='date=>global_start_date & date<=global_end_date')
df[df.isin(list_of_ids)]

You also might have dates that change per ids. So chunk them, this time using a list of ids.

Something like this:

output = []
for i inlen(list_of_ids) % 30:
    ids = list_of_ids[i:(i+30)]
    start_date = get_start_date_for_these_ids (global)
    end_date = get_end_date_for_these_ids (global)
    where = 'id=ids & start_date>=start_date & end_date<=end_date'
    df = pd.read_hdf('test.h5','df',where=where)
    output.append(df)

 final_result = concat(output)

The basic idea then is to select a superset of the data using the criteria that you want, sub-selecting so it fits in memory, but you limit the number of queries you do (e.g. imagine that you end up selecting a single row with your query, if you have to query this 18M times that is bad).