Fast extraction of chunks of lines from large CSV file
<p>I have a large CSV file full of stock-related data formatted as such:</p>

<blockquote>
  <p>Ticker Symbol, Date, [some variables...]</p>
</blockquote>

<p>So each line starts of with the symbol (like "AMZN"), then has the date, then has 12 variables related to price or volume on the selected date.  There are about 10,000 different securities represented in this file and I have a line for each day that the stock has been publicly traded for each of them.  The file is ordered first alphabetically by ticker symbol and second chronologically by date.  The entire file is about 3.3 GB.</p>

<p>The sort of task I want to solve would be to be able to extract the most recent <em>n</em> lines of data for a given ticker symbol with respect to the current date.  I have code that does this, but based on my observations it seems to take, on average, around 8-10 seconds per retrieval (all tests have been extracting 100 lines).</p>

<p>I have functions I'd like to run that require me to grab such chunks for hundreds or thousands of symbols, and I would really like to reduce the time.  My code is inefficient, but I am not sure how to make it run faster.</p>

<p>First, I have a function called getData:</p>

<pre><code>def getData(symbol, filename):
  out = ["Symbol","Date","Open","High","Low","Close","Volume","Dividend",
         "Split","Adj_Open","Adj_High","Adj_Low","Adj_Close","Adj_Volume"]
  l = len(symbol)
  beforeMatch = True
  with open(filename, 'r') as f:
    for line in f:
        match = checkMatch(symbol, l, line)
        if beforeMatch and match:
            beforeMatch = False
            out.append(formatLineData(line[:-1].split(",")))
        elif not beforeMatch and match:
            out.append(formatLineData(line[:-1].split(",")))
        elif not beforeMatch and not match:
            break
  return out
</code></pre>

<p>(This code has a couple of helper functions, checkMatch and formatLineData, which I will show below.)  Then, there is another function called getDataColumn that gets the column I want with the correct number of days represented:</p>

<pre><code>def getDataColumn(symbol, col=12, numDays=100, changeRateTransform=False):
  dataset = getData(symbol)
  if not changeRateTransform:
    column = [day[col] for day in dataset[-numDays:]]
  else:
    n = len(dataset)
    column = [(dataset[i][col] - dataset[i-1][col])/dataset[i-1][col] for i in range(n - numDays, n)]
  return column
</code></pre>

<p>(changeRateTransform converts raw numbers into daily change rate numbers if True.)  The helper functions:</p>

<pre><code>def checkMatch(symbol, symbolLength, line):
  out = False
  if line[:symbolLength+1] == symbol + ",":
    out = True
  return out

def formatLineData(lineData):
  out = [lineData[0]]
  out.append(datetime.strptime(lineData[1], '%Y-%m-%d').date())
  out += [float(d) for d in lineData[2:6]]
  out += [int(float(d)) for d in lineData[6:9]]
  out += [float(d) for d in lineData[9:13]]
  out.append(int(float(lineData[13])))
  return out
</code></pre>

<p>Does anyone have any insight on what parts of my code run slow and how I can make this perform better?  I can't do the sort of analysis I want to do without speeding this up.</p>

<hr>

<p>EDIT:
In response to the comments, I made some changes to the code in order to utilize the existing methods in the csv module:</p>

<pre><code>def getData(symbol, database):
  out = ["Symbol","Date","Open","High","Low","Close","Volume","Dividend",
         "Split","Adj_Open","Adj_High","Adj_Low","Adj_Close","Adj_Volume"]
  l = len(symbol)
  beforeMatch = True
  with open(database, 'r') as f:
    databaseReader = csv.reader(f, delimiter=",")
    for row in databaseReader:
        match = (row[0] == symbol)
        if beforeMatch and match:
            beforeMatch = False
            out.append(formatLineData(row))
        elif not beforeMatch and match:
            out.append(formatLineData(row))
        elif not beforeMatch and not match:
            break
  return out

def getDataColumn(dataset, col=12, numDays=100, changeRateTransform=False):
  if not changeRateTransform:
    out = [day[col] for day in dataset[-numDays:]]
  else:
    n = len(dataset)
    out = [(dataset[i][col] - dataset[i-1][col])/dataset[i-1][col] for i in range(n - numDays, n)]
  return out
</code></pre>

<p>Performance was worse using the csv.reader class.  I tested on two stocks, AMZN (near top of file) and ZNGA (near bottom of file).  With the original method, the run times were 0.99 seconds and 18.37 seconds, respectively.  With the new method leveraging the csv module, the run times were 3.04 seconds and 64.94 seconds, respectively.  Both return the correct results.</p>

<p>My thought is that the time is being taken up more from finding the stock than from the parsing.  If I try these methods on the first stock in the file, A, the methods both run in about 0.12 seconds.</p>