Efficiently processing ~50 million record file in python

user_5517589 0
Viewed 14745

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: 

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

Tested the program on a small set of data, here are the processing times: 

lines: 117929 process time: 236.388447046 sec
lines: 145390 process time: 277.075321913 sec

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!

Questions:

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

Is there a better alternative?

Thank you!

Update: This is processed on an EC2 instance with EBS attached volume.

latency csv lookup arrays python
4 Answers

i think its better to use a database to solve this problem first create a database like MySql or anything else then write your data from file into 2 table and finally use a simple sql query to select rows somthing like: select * from table1 where id in(select ids from table2)

user_58010850
Related