Efficiently filter a large (100gb+) csv file (v1)

Question

EDIT: This question was followed up by this question, with improved code.

I have a set of pretty large files (>2 GB, over 30m rows) containing intraday data in the following format:

TIC, Date, Time, Bid, Offer
AAPL, 20090901, 09:45, 145, 145.5 
AAPL, 20090902, 09:45, 145, 145.5
AAPL, 20090903, 09:45, 145, 145.5

I also have a file that contains exactly those dates that I'm interested in, for each firm, like so:

AAPL, 20090902
AAPL, 20090903

Next I want to extract a subset of the large intra-day files, to only keep those firm-days that occur in the dates of interest file. I solved this by creating a dictionary (one for each Ticker), that contains a list of dates as its values. I iterate over each line, and identify whether the current line's date exists in the dictionary.

Is there a faster way of accomplishing this, such as loading in multiple rows per iteration? Would it help to load it directly into a PostgreSQL server and querying it there? It currently runs through ~ 2 GB of data in 180 seconds. I would also appreciate any other feedback.

ROOT_DIR = "C:/Files"
os.chdir(ROOT_DIR)

with open("firm_dates_of_interest.csv") as f:
    d = {}
    lines = f.readlines()
    for line in lines:
        firm = line.split(",")[1]
        d[firm] = []
    for line in lines:
        firm = line.split(",")[1]
        date = line.split(",")[2]
        d[firm].append(date.strip())

for root, dir, files in os.walk(os.path.join(ROOT_DIR, 'Srcfiles'):
    for file in files:
        basename = os.path.splitext(file)[0]
        filename = file

        start = datetime.datetime.now()
        tokeep = []
        for i, line in enumerate(open(os.path.join(root, filename))):
            line = line.split(",")
            if line[0] in d.keys():
                if line[1] in d[line[0]]:
                    tokeep.append(line)
        elapsed = datetime.datetime.now() - start
        print("This file took me " + str(elapsed.seconds) + ' seconds...')

        with open(os.path.join(root, basename+' EXTRACT' + '.csv'), 'w') as f:
            for i, line in enumerate(tokeep):
                f.write(",".join(line))
        tokeep = []

Answer 1

Write code that does the conversion with a small portion of your data defined in the python module. That makes you focus on the data structures and if they fit to the problem. If you remove the code for file handling, logging - all that is not specific to your conversion problem - only a couple lines of code remain. That is what needs your attention and ours attention.

If that simple conversion work, add code that reads the data from external sources to populate the functions for your conversion code. Instead of splitting strings prefer code that is made for this common format: use built-in csv module. Separate file handling code from any algorithms.

Next create code that writes your intermediate output to external sources - again use csv module.

Separate any part/idea into their own place and give them names that makes readers understand their purpose. This is actually the main problem for any reader of your code.

What will make your code faster?

• use generators to keep memory usage low and list comprehension for beauty and awesome filtering

What will make you solve it right?

• If you split the problem into many parts, you can test any part itself. What makes you think your conversion is done properly at a scale of 2GB input files and large output files? Did you verified every line in output? Apply testing with smaller data, gives you confidence for larger data.

What will make you giant leaps?

• Go get IPython notebook. Write smaller parts and test execution time.

If you have understood your problem, use python libraries made for your problem and write the necessary glue code.

• csvkit, pandas

Learn from similar questions/answers on CodeReview

• Find the peak stock price for each company from CSV data