Is pandas read_csv really slow compared to python open?
<p>my requirement is to remove duplicate rows from csv file, but the size of the file is 11.3GB. So I bench marked the pandas and python file generator.</p>

<p><strong>Python File Generator:</strong></p>

<pre><code>def fileTestInPy():
    with open(r'D:\my-file.csv') as fp, open(r'D:\mining.csv', 'w') as mg:
        dups = set()
        for i, line in enumerate(fp):
            if i == 0:
                continue
            cols = line.split(',')
            if cols[0] in dups:
                continue
            dups.add(cols[0])
            mg.write(line)
            mg.write('\n')
</code></pre>

<p><a href="http://i.stack.imgur.com/0HLDH.png" rel="nofollow"><img src="http://i.stack.imgur.com/0HLDH.png" alt="Python File Generator"></a> </p>

<p><strong>Using Pandas read_csv:</strong></p>

<pre><code>import pandas as pd
df = pd.read_csv(r'D:\my-file.csv', sep=',', iterator=True, chunksize=1024*128)
def fileInPandas():
    for d in df:
        d_clean = d.drop_duplicates('NPI')
        d_clean.to_csv(r'D:\mining1.csv', mode='a')
</code></pre>

<p><a href="http://i.stack.imgur.com/Pvu3I.png" rel="nofollow"><img src="http://i.stack.imgur.com/Pvu3I.png" alt="Pandas read_csv"></a></p>

<p><strong>Details:</strong>
Size: 11.3 GB
rows: 100 million, but in this 50 million are duplicate
Python Version: 3.5.2
Pandas Version: 0.19.0
RAM: 8GB
CPU: Core-i5 2.60GHz</p>

<p>What I'm observed here, 643 sec took when I use the python file generator, but 1756 took when I use the pandas.</p>

<p>Even my system was not hanged when I used the python file generator, but when I used the pandas my system was hanged.</p>

<p>Am I using correct way in pandas ?
Even I want to do sorting on 11.3GB file, how to do that ?</p>

<p>Pandas is not a good choice for this task. It reads the entire 11.3G file into memory and does string-to-int conversions on all of the columns. I'm not surprised that your machine bogged down!</p>

<p>The line-by-line version is much leaner. It doesn't do any conversions, doesn't bother looking at unimportant columns and doesn't keep a large dataset in memory. It is the better tool for the job.</p>

<pre><code>def fileTestInPy():
    with open(r'D:\my-file.csv') as fp, open(r'D:\mining.csv', 'w') as mg:
        dups = set()
        next(fp) # &lt;-- advance fp so you don't need to check each line
                 # or use enumerate
        for line in fp:
            col = line.split(',', 1)[0]  # &lt;-- only split what you need
            if col in dups:
                continue
            dups.add(col)
            mg.write(line)
            # mg.write('\n')  # &lt;-- line still has its \n, did you
                              # want another?
</code></pre>

<p>Also, if this is python 3.x and you know your file is ascii or UTF-8, you could open both files in binary mode and save a conversion.</p>

python windows python-3.x pandas python-3.5