How can I improve the performance of HDF5 reading data?

user_15393431 0
Viewed 432

What I need is to get the unique date value as soon as possible.

I use the code df = store.df.date.drop_duplicates() to retrieve. This line of code takes 6 seconds. However, if I use mysql and save the same data to mysql, I use mysql for the date column After indexing, using sql:select distinct date from table, it only takes 80ms to retrieve the unique date value, which is 60 times faster than HDF5.

Is there any way to make the function read_unique_date read HDF5 faster than MySQL uses indexes?

My code is as follows:

import pandas as pd
import numpy as np
from itertools import product
from time import time


def generate_data():
    np.random.seed(202108)

    # date = pd.date_range(start="19900101", end="20210723", freq="D")
    #The above is my original code, you can use the following code to speed up the operation.
    date = pd.date_range(start="20210101", end="20210723", freq="D")
    date = pd.DataFrame(date, columns=["date"])

    # code = pd.DataFrame(range(5000), columns=["code"])
    #The above is my original code, you can use the following code to speed up the operation.
    code = pd.DataFrame(range(50), columns=["code"])

    # generate product of the two columns:
    df = pd.DataFrame(product(date["date"], code["code"]), columns=["date", "code"])
    df['data'] = np.random.random(len(df))
    return df


def save_data(filename, df):
    store = pd.HDFStore(filename)
    store['df'] = df
    store.close()
    return


def read_unique_date(file_name):
    store = pd.HDFStore(file_name)
    start = time()
    df = store.df.date.drop_duplicates()
    store.close()
    stop = time()
    print(stop - start)
    return df


def main():
    path = 'd:\\'
    file = 'large data.h5'
    file_name = path + file
    df = generate_data()
    save_data(file_name, df)
    df1 = read_unique_date(file_name)
    print(df1)
    return df1


if __name__ == '__main__':
    main()

The result is:

0.015624761581420898
0       2021-01-01
50      2021-01-02
100     2021-01-03
150     2021-01-04
200     2021-01-05
           ...    
9950    2021-07-19
10000   2021-07-20
10050   2021-07-21
10100   2021-07-22
10150   2021-07-23
Name: date, Length: 204, dtype: datetime64[ns]

%timeit df1 = read_unique_date(file_name)
16.9 ms ± 200 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

The result of my original code:

%timeit df1 = read_unique_date(file_name)
4.89 s ± 119 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
pandas dataframe hdf5 python
3 Answers

The short answer is that HDF5 simply does not have indexes apart from the keys to datasets (dataset names) and contiguous array indices. So the only lookup operations directly from a file that you can expect to perform sublinearly is retrieving the Nth value in an dataset. To find unique values directly from a file HDF5 has to read the whole file. You might be able to cook up something messy but functional with HDF5 groups and references but then you're just implementing indexing yourself and I don't advise going that route.

On the otherhand Pandas uses hashtables, trees and binary search to speed up various lookup operations. You could load the data into a dataframe for the queries. But generally you want to move to a proper database at some point. Pandas and HDF5 can only take you so far.

user_55489350

pandas has a method named read_hdf documented here. You can simply read your stored .h5 saying:

read_df = pd.read_hdf('./large data.h5')

and you can access read_df as a pandas data frame.

Now if you'd ask me, it would not be a great idea to store a file and read it again for periodical loop use. I'd keep the data in the form of a data frame and write it only when necessary (i.e. I need the space in the memory or I need to update the database with some shared access). I would say strategizing the write and read should be considered to optimize the code run.

btw, your code crashed my debug at:

    df = pd.DataFrame(product(date["date"], code["code"]), columns=["date", "code"])

you might want to look into it again.

user_115896670

Your code is running as-is so it is difficult to tell what you are trying to get at here without an example of the output you desire. Your nearly 1.8 GB of data that is generated by this code is not relevant to your issue unless I am missing something and this is really a performance related question. If the following does not solve your issue, I recommend you significantly pare down your generated data and provide an example of the desired output so people can help you without having to assume what you are trying to get to.

Taking a guess as to what you are wanting, I think the issue lies in how you are eliminating duplicates. You are dropping duplicates from the series of dates contained within the dataframe setting df equal to this pared down series. Instead, I believe what you want is to drop duplicates from the dataframe using df.drop_duplicates() and specifying the date column as the subset.

Example: df.drop_duplicates(subset="date").

Does this solve your issue?

user_129263580
Related