BUG: pd.HDFStore(file, mode='a') increases file size unnecessarily when file exists and dataframe contains string-based columns

[brianjzhang]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import os
import warnings
import numpy as np
import pandas as pd

warnings.filterwarnings("ignore")  # PerformanceWarning

iterations = 10

print("'w' mode'")
file = "w.h5"
for i in range(iterations):
    # Create a large random DataFrame
    df = pd.DataFrame(np.random.rand(1000, 100))
    df = df.assign(label="hellothere")

    # Save and reload the DataFrame with 'w' mode
    with pd.HDFStore(file, mode="w") as store:
        store.put("data", df)

    with pd.HDFStore(file, mode="r") as store:
        read_df = store.get("data")

    assert df.equals(read_df)

    print(f"'{file}' size: {os.path.getsize(file)}")

print("'a' mode'")
file = "a.h5"
for i in range(iterations):
    # Create a large random DataFrame
    df = pd.DataFrame(np.random.rand(1000, 100))
    df = df.assign(label="hellothere")

    # Save and reload the DataFrame with 'a' mode
    with pd.HDFStore(file, mode="a") as store:
        store.put("data", df)

    with pd.HDFStore(file, mode="r") as store:
        read_df = store.get("data")

    assert df.equals(read_df)

    print(f"'{file}' size: {os.path.getsize(file)}")

print("'a' mode, only numerical'")
file = "a_numerical.h5"
for i in range(iterations):
    # Create a large random DataFrame
    df = pd.DataFrame(np.random.rand(1000, 100))

    # Save and reload the DataFrame with 'a' mode
    with pd.HDFStore(file, mode="a") as store:
        store.put("data", df)

    with pd.HDFStore(file, mode="r") as store:
        read_df = store.get("data")

    assert df.equals(read_df)

    print(f"'{file}' size: {os.path.getsize(file)}")

print("'a' mode, only strings'")
file = "a_strings.h5"
for i in range(iterations):
    # Create a large random DataFrame
    df = pd.DataFrame({"label": ["hellothere"] * 1000})

    # Save and reload the DataFrame with 'a' mode
    with pd.HDFStore(file, mode="a") as store:
        store.put("data", df)

    with pd.HDFStore(file, mode="r") as store:
        read_df = store.get("data")

    assert df.equals(read_df)

    print(f"'{file}' size: {os.path.getsize(file)}")

Issue Description

When saving dataframes to HDF5 using pd.HDFStore(file, 'a') and the dataframe to be stored contains string data columns, the size of the resulting file will increase if the file already existed even if it should not. This behavior impacts a situation where an existing .h5 file is being updated with more data. This is a low-priority bug, since the issue is avoidable in this situation by using the 'w' open mode instead.

Example output:

'w' mode'
'w.h5' size: 3974104
'w.h5' size: 3974104
'w.h5' size: 3974104
'w.h5' size: 3974104
'w.h5' size: 3974104
'a' mode'
'a.h5' size: 3974104
'a.h5' size: 4776152
'a.h5' size: 5578200
'a.h5' size: 6380248
'a.h5' size: 7182296
'a' mode, only numerical'
'a_numerical.h5' size: 815240
'a_numerical.h5' size: 815312
'a_numerical.h5' size: 815312
'a_numerical.h5' size: 815312
'a_numerical.h5' size: 815312
'a' mode, only strings'
'a_strings.h5' size: 1070008
'a_strings.h5' size: 1076152
'a_strings.h5' size: 1080248
'a_strings.h5' size: 1084344
'a_strings.h5' size: 1088440

As we can see, as identically-sized data is being written into a.h5 and a_strings.h5, the size of the file increases.

Apologies in advance for not providing a fix, but since the workaround is very easy I didn't want to spend much time digging into it. Also sorry if it's written in documentation somewhere to avoid using the 'a' option.

Expected Behavior

Opening an HDFStore in 'a' mode and overwriting keys with dataframes containing string values does not lead to unnecessarily increasing file size.

Installed Versions

INSTALLED VERSIONS

commit : 0691c5c
python : 3.12.3
python-bits : 64
OS : Linux
OS-release : 6.8.0-52-generic
Version : #53-Ubuntu SMP PREEMPT_DYNAMIC Sat Jan 11 00:06:25 UTC 2025
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8
pandas : 2.2.3
numpy : 2.2.2
pytz : 2025.1
dateutil : 2.9.0.post0
pip : 25.0
Cython : None
sphinx : None
IPython : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
blosc : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : 3.1.5
lxml.etree : None
matplotlib : None
numba : None
numexpr : 2.10.2
odfpy : None
openpyxl : None
pandas_gbq : None
psycopg2 : None
pymysql : None
pyarrow : None
pyreadstat : None
pytest : 8.3.4
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.15.1
sqlalchemy : None
tables : 3.10.2
tabulate : None
xarray : None
xlrd : None
xlsxwriter : None
zstandard : None
tzdata : 2025.1
qtpy : None
pyqt5 : None

[rhshadrach]

Thanks for the report, in the User Guide is this note:

Please note that HDF5 DOES NOT RECLAIM SPACE in the h5 files automatically. Thus, repeatedly deleting (or removing nodes) and adding again, WILL TEND TO INCREASE THE FILE SIZE.

I wonder if something similar is occurring here.

https://pandas.pydata.org/docs/user_guide/io.html#delete-from-a-table

[PedroM4rques]

take

[PedroM4rques]

From what i could understand, this behavior is exactly what had been observed by @rhshadrach in the previous message. The HDF5 file does not reclaim space in the situation described, https://pandas.pydata.org/docs/user_guide/io.html#delete-from-a-table .

However, one trick i found while looking at this bug is that by copying it to a temporary file, replacing the original, and reopening it (like in the commit i did) u can 'refresh' the file and clean its size.

[rhshadrach]

As this is expected behavior and documented, closing.