EDIT: This question is followed up by this question.
I'm in the process of filtering some very(!) large files (100gb+): I can't download files with a lower granularity. This is a followup from this question.
The problem is as follows: I need to filter large files that look like the following (3b+ rows).
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 filter based on TICKER+DATE combinations found in an external file. I have, on average, ~ 1200 dates of interest per firm for ~ 700 firms. The large file contains all dates for the firms of interest, for which I want to extract only a few dates of interest. The big files are split by month (2013-01, 2013-02 etc.).
AAPL, 20090902 AAPL, 20090903
A few changes were made since the previous post:
- I used the CSV module, as was suggested.
- I write the the rows to be retained to disk after each 5m rows.
- I iterate over the files using a try except statement.
I'm currently at 6 minutes of processing time for 30 million rows (1% of the file); I tested a few files and it works properly. However, with about 3 billion rows per file, that puts it at ~10 hours for one 120gb file. Seeing as I have about twelve files, I'm very curious whether I can get significant performance improvements by doing things differently.
Any tips are greatly appreciated.
import os
import datetime
import csv
import re
ROOT_DIR = "H:/ROOT_DIR/"
SOURCE_FILES = os.path.join(ROOT_DIR, "10. Intradayfiles (source)/")
EXPORT_DIR = os.path.join(ROOT_DIR, "11. CSV Export (step 1 Extract relevant firmdates)/")
DATES_FILE = os.path.join(ROOT_DIR, "10. Dates of interest/firm_date_of_interest.csv")
# Build the original date dict
# For example:
# d['AAPL'] is a list with ['20140901', '20140902', '20140901']
with open(DATES_FILE, "r") as csvfile:
d = {}
reader = csv.reader(csvfile)
reader.next()
for line in reader:
firm = line[1]
date = line[2]
if firm in d.keys():
d[firm].append(date)
else:
d[firm] = [date]
def main():
for root, dir, files in os.walk(SOURCE_FILES):
num_files = len(files)
for i, file in enumerate(files):
print('File ' + str(i+1) + '/' + str(num_files) + '; ' + file)
basename = os.path.splitext(file)[0]
filepath = os.path.join(root, file)
# Annotate files with 'DONE' after succesful processing: skip those
if re.search("DONE", basename):
continue
start = datetime.datetime.now()
rows_to_keep = []
# Read the file, append only rows for which the dates occurs in the dictionary for that firm.
with open(filepath, 'rb') as csvfile:
startfile = datetime.datetime.now()
reader = csv.reader(csvfile)
saved = 0
for i, row in enumerate(reader):
# Every 5 million rows, I save what we've extracted so far.
if i % 5000000 == 0:
if rows_to_keep:
with open(os.path.join(EXPORT_DIR, basename+' EXTRACT' + str(saved) + '.csv'), 'wb') as csvfile:
writer = csv.writer(csvfile, quoting=csv.QUOTE_NONNUMERIC)
for k, line in enumerate(rows_to_keep):
writer.writerow(line)
saved += 1
rows_to_keep = []
file_elapsed = datetime.datetime.now() - startfile
print("Took me " + str(file_elapsed.seconds) + ' seconds... for ' + str(i) + ' rows..')
# See if row[1] (the date) is in the dict, based on row[0] (the ticker)
try:
if row[1] in d[row[0]]:
rows_to_keep.append(row)
except KeyError:
continue
except IndexError:
continue
os.rename(os.path.join(root, file), os.path.join(root, os.path.splitext(file)[0]+'- DONE.csv'))
elapsed = datetime.datetime.now() - start
print("Took me " + str(elapsed.seconds) + ' seconds...')
return rows_to_keep
if __name__ == "__main__":
main()
