Refactoring pandas using an iterator via chunksize

Question

This question was also asked on Stack Overflow

Bioinformatics rationale eggNOG files can be very big and sump all available RAM for regular to medium sized desktops.

---

I am looking for advice on using a pandas iterator.

I performed a parsing operation using Python pandas, the size of the input files is eggNOG a program used in metagenomic gene function identification but has broader application. The files are large and parsing is resulting in 'RAM bottleneck' phenomenon and will not processing/parse the file.

The obvious solution is to shift to an iterator, which for pandas is the chunksize option

import pandas as pd
import numpy as np

df = pd.read_csv('myinfile.csv', sep="\t", chunksize=100)

Whats changed with the original code is the chunksize=100 bit, forcing an iterator.

The next step is just to perform a simple operation, dropping a few columns and moving all '-' characters to np.nan then writing the whole file to disk.

df.drop(['score', 'evalue', 'Description', 'EC', 'PFAMs'],axis=1).replace('-', np.nan)
df.to_csv('my.csv',sep='\t',index=False)

How is the step performed under an iterator?

Answer 1

The solution posted by @el_oso and @Corralien here was as follows:

if you're already having memory issues reading the file, the below will actually read and write to the new file in chunks.

cols_to_keep = {'col1', 'col2', 'col3'}
df = pd.read_csv('myinfile.csv', chunksize=100, usecols=cols_to_keep)
with open('my.csv', 'w') as f:
    f.write('\t'.join(cols_to_keep) + '\n')
    for chunk in df:
        #do your processing here, appending that chunk to the file
        chunk.to_csv(f, header=None, sep='\t') 

f is _io.TextIOWrapper i.e. a stream you can consistently write to in small memory chunks

---

This is very elegant because there are two components which are both cool and highlight why pandas is so widely used.

1. Instead of loading a large file and then dumping stuff thats not wanted: don't load junk at point source.
2. Simply open the write file outside the chunking loop will then stream the chunks to straight to disk and once the final chunking loop completes the file snaps shut.

The process is

• Load a chunk of data, but don't load any 'junk'.

• Process the chunk of data and write it to disk.

• Load another chunk of data repeating the above two steps.

• Continue until the entire file is processed.

The "do processing" is an OO module (there's alot going on) and the chunk is actually self.chunk. None of the original code needs to change its simply restructured inside the open loop to stream chunks to disk.

The beauty of this solution is chunksize is a variable, here 100 is just the example. Python's iterator called yield is one line at a time iteration, but this the automates a defined 'chunk' at a time. Thus the runtime speed can be tuned against the "RAM bottleneck".

Testing The chunked outfile was then reduced via groupby to remove the "duplicates" and frequencies summed (code not shown). The chunking OO module output file is identical to "non-chunked" OO module and written back into the module.

Its worth upvoting the SO answers.