select rows by comparing columns using HDFStore

Question

How can I select some rows by comparing two columns from hdf5 file using Pandas? The hdf5 file is too big to load into memory. For example, I want to select rows where column A and columns B is equal. The dataframe is save in file 'mydata.hdf5'. Thanks.

import pandas as pd
store = pd.HDFstore('mydata.hdf5')
df = store.select('mydf',where='A=B')

This doesn't work. I know that store.select('mydf',where='A==12') will work. But I want to compare column A and B. The example data looks like this:

A B C 
1 1 3
1 2 4
. . .
2 2 5
1 3 3

Answer 1

You cannot directly do this, but the following will work

In [23]: df = DataFrame({'A' : [1,2,3], 'B' : [2,2,2]})

In [24]: store = pd.HDFStore('test.h5',mode='w')

In [26]: store.append('df',df,data_columns=True)

In [27]: store.select('df')
Out[27]: 
   A  B
0  1  2
1  2  2
2  3  2

In [28]: store.select_column('df','A') == store.select_column('df','B')
Out[28]: 
0    False
1     True
2    False
dtype: bool

This should be pretty efficient.