I have created an HDFStore.
The HDFStore contains a group df
which is a table with 2 columns.
The first column is a string
and second column is DateTime
(which will be in sorted order).
The Store has been created using the following method:
from numpy import ndarray
import random
import datetime
from pandas import DataFrame, HDFStore
def create(n):
mylist = ['A' * 4, 'B' * 4, 'C' * 4, 'D' * 4]
data = []
for i in range(n):
data.append((random.choice(mylist),
datetime.datetime.now() - datetime.timedelta(minutes=i)))
data_np = ndarray(len(data), dtype=[
('fac', 'U6'), ('ts', 'datetime64[us]')])
data_np[:] = data
df = DataFrame(data_np)
return df
def create_patches(n, nn):
for i in range(n):
yield create(nn)
df = create_patches(100, 1000000)
store = HDFStore('check.hd5')
for each in df:
store.append('df', each, index=False, data_columns=True, format = 'table')
store.close()
Once the HDF5 file is created, i'm querying the table using the following method:
In [1]: %timeit store.select('df', ['ts>Timestamp("2016-07-12 10:00:00")'])
1 loops, best of 3: 13.2 s per loop
So, basically this is taking 13.2 seconds, then I added an index to this column using
In [2]: store.create_table_index('df', columns=['ts'], kind='full')
And then I again did the same query, this time I got the following:-
In [3]: %timeit store.select('df', ['ts>Timestamp("2016-07-12 10:00:00")'])
1 loops, best of 3: 12 s per loop
From the above, it seems to me there isn't a significant improvement in the performance. So, my question is, what else can I do here to make my query faster, or is there something I'm doing wrong?