CODEWITHSUNDEEP
pythonpandashdf5lz4
Ron
Ron

Reputation: 91

OverflowError with Pandas to_hdf

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

Upvotes: 9

Views: 2991

Answers (3)

BY Jiang
BY Jiang

Reputation: 21

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)

Upvotes: 2

tetrisforjeff
tetrisforjeff

Reputation: 97

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.).

Upvotes: 3

Artem Snorkovenko
Artem Snorkovenko

Reputation: 31

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')

Upvotes: 3

Related Questions