5 Best Ways to Convert Pandas DataFrame to HDF5

by

💡 Problem Formulation: When working with large datasets in Python, efficient storage and retrieval become pivotal. One common task is converting a pandas DataFrame into HDF5 format—a binary file format designed for storing large quantities of numerical data. The input in this case is a pandas DataFrame object, and the desired output is an HDF5 file containing the same data for persistent storage and efficient access.

Method 1: Using to_hdf() Basic Approach

The to_hdf() function in pandas is the standard way of saving a DataFrame to an HDF5 file. It offers various parameters to control aspects such as mode of writing, format, data compression, and handling of pandas-specific data types.

Here’s an example:

import pandas as pd
import numpy as np

# Create a simple DataFrame
df = pd.DataFrame(np.random.rand(4,2), columns=['A', 'B'])

# Save it to HDF5
df.to_hdf('data.h5', key='random_data')

Output: HDF5 file named ‘data.h5’ containing the DataFrame under the storage key ‘random_data’.

This code snippet first creates a pandas DataFrame df with random data, then uses to_hdf() to save it to an HDF5 file named ‘data.h5’. The key parameter specifies the identifier within the HDF5 file.

Method 2: Specifying the Format Option

Using the format parameter with to_hdf() allows you to specify storage format options ‘fixed’ or ‘table’. ‘table’ format enables queries on the file, but it is less space-efficient.

Here’s an example:

import pandas as pd

df = pd.DataFrame({'A': range(1, 6), 'B': range(10, 15)})

# Save DataFrame with table format to enable queries
df.to_hdf('data.h5', key='df', format='table')

Output: HDF5 file named ‘data.h5’ containing the DataFrame in a queryable table format.

The snippet saves a DataFrame to HDF5 using the ‘table’ format which enables the stored data to be queried efficiently using pd.read_hdf() with query strings.

Method 3: Compressing Data with complib

Using the complib parameter in conjunction with to_hdf(), one can compress the stored data to reduce the file size. There are multiple compression libraries supported, such as ‘blosc’, ‘zlib’, ‘lzo’, ‘bzip2’, and ‘xz’.

Here’s an example:

import pandas as pd

df = pd.DataFrame({'A': np.random.rand(1000), 'B': np.random.rand(1000)})

# Save DataFrame with BLOSC compression
df.to_hdf('data.h5', key='compressed_data', complib='blosc', complevel=9)

Output: Compressed HDF5 file ‘data.h5’ with DataFrame data, reducing the disk space usage significantly.

The example demonstrates the use of BLOSC compression to significantly reduce the size of the HDF5 file holding the DataFrame.

Method 4: Appending DataFrames to HDF5

When dealing with streaming or chunked data, one might need to store multiple DataFrames in a single HDF5 file incrementally. This can be achieved using append=True in to_hdf() along with the ‘table’ format.

Here’s an example:

import pandas as pd

# Assuming df_chunk is a new chunk of data in DataFrame format
df_chunk = pd.DataFrame({'A': [5, 6], 'B': [50, 60]})

# Append DataFrame to an existing HDF5 file
df_chunk.to_hdf('data.h5', key='df', format='table', append=True)

Output: HDF5 file ‘data.h5’ appended with new chunk of DataFrame data under the same key ‘df’.

This code appends a chunk of data to an HDF5 file, which is useful for large datasets that are processed and stored in parts.

Bonus One-Liner Method 5: Using pd.HDFStore()

For a more controlled environment when persisting DataFrames to HDF5, use pd.HDFStore() which allows multiple operations in a file context—similar to Python’s built-in open().

Here’s an example:

import pandas as pd

df = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})

# Save DataFrame to HDF5 using HDFStore context manager
with pd.HDFStore('data.h5') as store:
    store.put('df', df, format='table')

Output: HDF5 file ‘data.h5’ safely containing the DataFrame ‘df’ with context management.

This concise code utilizes the context manager to safely write a DataFrame to an HDF5 file, ensuring that the file is properly closed after the operation completes.

Summary/Discussion

  • Method 1: to_hdf() Basic Approach. Straightforward usage for single DataFrame storage. No additional features like compression or querying.
  • Method 2: Specifying the Format Option. Enables querying capabilities; however, it may increase file size compared to fixed format.
  • Method 3: Compressing Data with complib. Reduces file size considerably. Potential performance costs during compression and decompression.
  • Method 4: Appending DataFrames to HDF5. Ideal for large datasets processed in chunks. Requires ‘table’ format, potentially increasing file size.
  • Method 5: Using pd.HDFStore(). Provides a more granular control; use for complex I/O operations. Slightly more verbose than to_hdf().