Efficiently processing ~50 million record file in python
<p>We have a file with about 46 million records in CSV format. Each record has about 18 fields and one of them is a 64 byte ID. We have another file with about 167K unique IDs in it. The records corresponding to the IDs needs to be yanked out. So, we have written a python program that reads the 167K IDs into an array and processes the 46 million records file checking if the ID exists in each one of the those records. Here is the snippet of the code:  </p>

<pre><code>import csv
...
csvReadHandler = csv.reader(inputFile, delimiter=chr(1))
csvWriteHandler = csv.writer(outputFile, delimiter=chr(1), lineterminator='\n')
for fieldAry in csvReadHandler:
    lineCounts['orig'] += 1
    if fieldAry[CUSTOMER_ID] not in idArray:
        csvWriteHandler.writerow(fieldAry)
        lineCounts['mod'] += 1
</code></pre>

<p>Tested the program on a small set of data, here are the processing times: </p>

<pre><code>lines: 117929 process time: 236.388447046 sec
lines: 145390 process time: 277.075321913 sec
</code></pre>

<p>We have started running the program on the 46 million records file (which is about 13 GB size) last night ~3:00am EST, now it is around 10am EST and it is still processing!</p>

<p>Questions:</p>

<ol>
<li>Is there a better way to process these records to improve on processing times?  </li>
<li>Is python the right choice? Would awk or some other tool help?  </li>
<li>I am guessing 64 byte ID look-up on 167K array in the following statement is the culprit:<br>
<code> if fieldAry[CUSTOMER_ID] not in idArray: </code>  </li>
</ol>

<p>Is there a better alternative?</p>

<p>Thank you!</p>

<p>Update: This is processed on an EC2 instance with EBS attached volume.</p>