OverflowError with Pandas to_hdf

Question

Python newbie here.

I am trying to save a large data frame into HDF file with lz4 compression using to_hdf.

I use Windows 10, Python 3, Pandas 20.2

I get the error “OverflowError: Python int too large to convert to C long”.

None of the machine resources are close to their limits (RAM, CPU, SWAP usage)

Previous posts discuss the dtype, but the following example shows that there is some other problem, potentially related to the size?

import numpy as np
import pandas as pd


# sample dataframe to be saved, pardon my French 
n=500*1000*1000
df= pd.DataFrame({'col1':[999999999999999999]*n,
                  'col2':['aaaaaaaaaaaaaaaaa']*n,
                  'col3':[999999999999999999]*n,
                  'col4':['aaaaaaaaaaaaaaaaa']*n,
                  'col5':[999999999999999999]*n,
                  'col6':['aaaaaaaaaaaaaaaaa']*n})

# works fine
lim=200*1000*1000
df[:lim].to_hdf('df.h5','table', complib= 'blosc:lz4', mode='w')

# works fine
lim=300*1000*1000
df[:lim].to_hdf('df.h5','table', complib= 'blosc:lz4', mode='w')


# Error
lim=400*1000*1000
df[:lim].to_hdf('df.h5','table', complib= 'blosc:lz4', mode='w')


....
OverflowError: Python int too large to convert to C long

Do you indeed expect integer values of order `999999999999999999`? Or does this just happen to be a bad example? If the former, would using floating point values harm the precision? — , Jul 18 '17 at 04:00
"Previous posts discuss the dtype": this problem is also about the dtype, since those integer values are too large to be accommodated by a 4-byte integer. You may want to show the dtype of the dataframe. — , Jul 18 '17 at 04:02
Thank you Evert for your comment. The example meant to illustrate that is it NOT about the integer value or about the data type. There are 500M IDENTICAL rows. Writing less a file with 300M rows works fine. 400M fails. — Ron, Jul 18 '17 at 20:13

score 3 · Answer 1 · edited Oct 28 '20 at 11:41

I experienced the same issue and it seems that it is indeed connected to the size of the data frame rather than to dtype (I had all the columns stored as strings and was able to store them to .h5 separately).

The solution that worked for me is to save the data frame in chunks using mode='a'. As suggested in pandas documentation : mode{‘a’, ‘w’, ‘r+’}, default ‘a’: ‘a’: append, an existing file is opened for reading and writing, and if the file does not exist it is created.

So the sample code would look something like:

batch_size = 1000
for i, df_chunk in df.groupby(np.arange(df.shape[0]) // batch_size):
    df_chunk.to_hdf('df.h5','table', complib= 'blosc:lz4', mode='a')

Hi, I have a similar problem but when I tried this solution it only saves the last batch. is this normal? — Giovanni Maria Strampelli, Jun 02 '21 at 22:17

score 3 · Answer 2 · answered Dec 28 '21 at 13:01

As @Giovanni Maria Strampelli pointed, the answer of @Artem Snorkovenko only saves the last batch. Pandas documentation states the following:

In order to add another DataFrame or Series to an existing HDF file please use append mode and a different a key.

Here is a possible workaround to save all batches (adjusted from the answer of @Artem Snorkovenko):

for i in range(len(df)):
    sr = df.loc[i] #pandas series object for the given index
    sr.to_hdf('df.h5', key='table_%i'%i, complib='blosc:lz4', mode='a')

This code saves each Pandas Series object with a different key. Each key is indexed by i.

To load the existing .h5 file after saving, one can do the following:

i = 0
dfdone = False #if True, all keys in the .h5 file are successfully loaded.
srl = [] #df series object list
while dfdone == False:
    #print(i) #this is to see if code is working properly.
    try: #check whether current i value exists in the keys of the .h5 file
        sdfr = pd.read_hdf('df.h5', key='table_%i'%i) #Current series object
        srl.append(sdfr) #append each series to a list to create the dataframe in the end.
        i += 1 #increment i by 1 after loading the series object
    except: #if an error occurs, current i value exceeds the number of keys, all keys are loaded.
        dfdone = True #Terminate the while loop.

df = pd.DataFrame(srl) #Generate the dataframe from the list of series objects.

I used a while loop, assuming we do not know the exact length of the dataframe in the .h5 file. If the length is known, for loop can also be used.

Note that I am not saving dataframes in chunks here. Thus, the loading procedure is in its current form not suitable for saving in chunks, where the data type would be DataFrame for each chunk. In my implementation, each saved object is Series, and DataFrame is generated from a list of Series. The code I provided can be adjusted to work for saving in chunks and generating a DataFrame from a list of DataFrame objects (a nice starting point can be found in ths Stack Overflow entry.).

BY Jiang · Answer 3 · 2022-12-18T06:56:24.077

1

In addition to @tetrisforjeff 's answer:

If the df contains object types, the reading could lead to error. I would suggest pd.concat(srl) instead of pd.DataFrame(srl)

edited Dec 18 '22 at 06:56

answered Dec 18 '22 at 06:54

BY Jiang

11
2

OverflowError with Pandas to_hdf

3 Answers3