Opening a 20GB file for analysis with pandas

Question

I am currently trying to open a file with pandas and python for machine learning purposes it would be ideal for me to have them all in a DataFrame. Now The file is 18GB large and my RAM is 32 GB but I keep getting memory errors.

From your experience is it possible? If not do you know of a better way to go around this? (hive table? increase the size of my RAM to 64? create a database and access it from python)

Answer 1

If it's a csv file and you do not need to access all of the data at once when training your algorithm, you can read it in chunks. The pandas.read_csv method allows you to read a file in chunks like this:

import pandas as pd
for chunk in pd.read_csv(<filepath>, chunksize=<your_chunksize_here>)
    do_processing()
    train_algorithm()

Here is the method's documentation

Answer 2

There are two possibilities: either you need to have all your data in memory for processing (e.g. your machine learning algorithm would want to consume all of it at once), or you can do without it (e.g. your algorithm only needs samples of rows or columns at once).

In the first case, you'll need to solve a memory problem. Increase your memory size, rent a high-memory cloud machine, use inplace operations, provide information about the type of data you are reading in, make sure to delete all unused variables and collect garbage, etc.

It is very probable that 32GB of RAM would not be enough for Pandas to handle your data. Note that the integer "1" is just one byte when stored as text but 8 bytes when represented as int64 (which is the default when Pandas reads it in from text). You can make the same example with a floating point number "1.0" which expands from a 3-byte string to an 8-byte float64 by default. You may win some space by letting Pandas know precisely which types to use for each column and forcing the smallest possible representations, but we did not even start speaking of Python's data structure overhead here, which may add an extra pointer or two here or there easily, and pointers are 8 bytes each on a 64-bit machine.

To summarize: no, 32GB RAM is probably not enough for Pandas to handle a 20GB file.

In the second case (which is more realistic and probably applies to you), you need to solve a data management problem. Indeed, having to load all of the data when you really only need parts of it for processing, may be a sign of bad data management. There are multiple options here:

1. Use an SQL database. If you can, it is nearly always the first choice and a decently comfortable solution. 20GB sounds like the size most SQL databases would handle well without the need to go distributed even on a (higher-end) laptop. You'll be able to index columns, do basic aggregations via SQL, and get the needed subsamples into Pandas for more complex processing using a simple pd.read_sql. Moving the data to a database will also provide you with an opportunity to think about the actual data types and sizes of your columns.

2. If your data is mostly numeric (i.e. arrays or tensors), you may consider holding it in a HDF5 format (see PyTables), which lets you conveniently read only the necessary slices of huge arrays from disk. Basic numpy.save and numpy.load achieve the same effect via memory-mapping the arrays on disk as well. For GIS and related raster data there are dedicated databases, which might not connect to pandas as directly as SQL, but should also let you do slices and queries reasonably conveniently.

3. Pandas does not support such "partial" memory-mapping of HDF5 or numpy arrays, as far as I know. If you still want a kind of a "pure-pandas" solution, you can try to work around by "sharding": either storing the columns of your huge table separately (e.g. in separate files or in separate "tables" of a single HDF5 file) and only loading the necessary ones on-demand, or storing the chunks of rows separately. However, you'd then need to implement the logic for loading the necessary chunks, thus reinventing the bicycles already imlpemented in most SQL databases, so perhaps option 1 would still be easier here. If your data comes in a CSV, though, you can process it in chunks by specifying the chunksize parameter to pd.read_csv.

Answer 3

I just had this issue a few days ago! Not sure if this helps in your specific case since you aren't providing so many details, but my situation was to work offline on a 'large' dataset. The data was obtained as 20GB gzipped CSV files from energy meters, time series data at several seconds intervals.

File IO:

data_root = r"/media/usr/USB STICK"
fname = r"meters001-050-timestamps.csv.gz"
this_file = os.path.join(data_root,fname)
assert os.path.exists(this_file), this_file
this_file

Create a chunk iterator directly over the gzip file (do not unzip!)

cols_to_keep = [0,1,2,3,7]
column_names = ['METERID','TSTAMP','ENERGY','POWER_ALL','ENERGY_OUT',]
parse_dates = ['TSTAMP']
dtype={'METERID': np.int32, 
       'ENERGY': np.int32,
       'POWER_ALL': np.int32,
       'ENERGY_OUT': np.int32,
      }
df_iterator = pd.read_csv(this_file, 
                        skiprows=0, 
                        compression='gzip',
                        chunksize=1000000, 
                        usecols=cols_to_keep,
                        delimiter=";",
                        header=None,
                        names = column_names,
                      dtype=dtype,
                     parse_dates=parse_dates,
                     index_col=1,
                     )

Iterate over the chunks

new_df = pd.DataFrame()
count = 0
for df in df_iterator:
    chunk_df_15min = df.resample('15T').first()
    #chunk_df_30min = df.resample('30T').first()
    #chunk_df_hourly = df.resample('H').first()
    this_df = chunk_df_15min
    this_df = this_df.pipe(lambda x: x[x.METERID == 1])
    #print("chunk",i)
    new_df = pd.concat([new_df,chunk_df_15min])
    print("chunk",count, len(chunk_df_15min), 'rows added')
    #print("chunk",i, len(temp_df),'rows added')
    #break
    count += 1

Inside the chunk loop, I am doing some filtering and re-sampling on time. Doing this I reduced the size from 20GB to a few hundred MB HDF5 for further offline data exploration.

Answer 4

In my experience, initializing read_csv() with parameter low_memory=False tends to help when reading in large files. I don't think you have mentioned the file type you are reading in, so I am not sure how applicable this is to your situation though.

Answer 5

If your file is a CSV then you can simply do it in Chunk by Chunk. You can just simply do:

import pandas as pd
for chunk in pd.read_csv(FileName, chunksize=ChunkSizeHere)
(Do your processing and training here)