title: Python: Read CSV to list is not faster then CSV to dict with key check?
body: <p>While looking for alternative to SAS sort I decided to try Python 2.6 (both on the same Unix server). Sorting a narrow 500mln rows table takes 20 minutes in SAS.
  I exported 20% of the table (100mln rows) to CSV file which looks like this:</p>
  
  <pre><code>X|||465097434|912364420|0.00|0.00|0.00|0.00|1.00|01FEB2016|X|0|0
  X|||465097434|912364420|0.00|0.00|0.00|0.00|0.00|02FEB2016|X|0|0
  X|||465097434|912364420|0.00|0.00|0.00|0.00|2.00|03FEB2016|X|0|0
  X|||465097434|912364421|0.00|0.00|0.00|0.00|3.00|04FEB2016|X|0|0
  X|||465097434|912364421|0.00|0.00|0.00|0.00|6.00|05FEB2016|X|0|0
  X|||965097411|912364455|0.00|0.00|0.00|0.00|4.00|04FEB2016|X|0|0
  X|||965097411|912364455|0.00|0.00|0.00|0.00|1.00|05FEB2016|X|0|0
  </code></pre>
  
  <p>The goal is to sort it by 5th and 11th columns. 
  First I checked how fast python read the file with the code:</p>
  
  <pre><code>from __future__ import print_function
  import csv
  import time
  linesRead=0
  with open ('/path/to/file/CSV_FILE.csv','r') as dailyFile:
      allLines=csv.DictReader(dailyFile, delimiter='|')
      startTime=time.time()
      for row in allLines:
          linesRead += 1
          if (linesRead) % 1000000 == 0:
              print(linesRead, ": ", time.time()-startTime, " sec.")
              startTime=time.time()
  </code></pre>
  
  <p>And the results is that it takes 6 seconds to read each mln of rows.</p>
  
  <pre><code>1000000 :  6.6301009655  sec.
  2000000 :  6.33900094032  sec.
  3000000 :  6.26246404648  sec.
  4000000 :  6.56919789314  sec.
  5000000 :  6.17433309555  sec.
  ...
  98000000 :  6.61627292633  sec.
  99000000 :  7.14683485031  sec.
  100000000 :  7.08069109917  sec.
  </code></pre>
  
  <p>So I extended the code to load it to dictionary (key=column 5 (account identifier)) and value is a list of lists (lines) for this account.
  This is where I realized that loading lists to a dictionary slows down while dictionary is growing (quite logic as there is a growing number of keys to check):</p>
  
  <pre><code>import csv
  import time
  myDictionary = {}
  linesRead=0
  with open ('/path/to/file/CSV_FILE.csv','r') as dailyFile:
      allLines=csv.DictReader(dailyFile, delimiter='|')
      startTime=time.time()
      for row in allLines:
          accountID=row['account_id'].strip('\'')
          linesRead += 1
          if accountID in myDictionary:
              myDictionary[accountID].append([row['date'].strip('\''), row['balance1'], row['balance2'], row['balance3']])
          else:
              myDictionary[accountID]=[]
          if (linesRead) % 1000000 == 0:
              print(linesRead, ": ", time.time()-startTime, " sec.")
              startTime=time.time()
  </code></pre>
  
  <p>And the times are:</p>
  
  <pre><code>1000000, ': ', 8.9685721397399902, ' sec.')
  (2000000, ': ', 10.344831943511963, ' sec.')
  (3000000, ': ', 11.637137889862061, ' sec.')
  (4000000, ': ', 13.024128913879395, ' sec.')
  (5000000, ': ', 13.508150815963745, ' sec.')
  (6000000, ': ', 14.94166088104248, ' sec.')
  (7000000, ': ', 16.307464122772217, ' sec.')
  (8000000, ': ', 17.130259990692139, ' sec.')
  (9000000, ': ', 17.54616379737854, ' sec.')
  (10000000, ': ', 20.254321813583374, ' sec.')
  ...
  (39000000, ': ', 55.350741863250732, ' sec.')
  (40000000, ': ', 56.762171983718872, ' sec.')
  (41000000, ': ', 57.876702070236206, ' sec.')
  (42000000, ': ', 54.548398017883301, ' sec.')
  (43000000, ': ', 60.040227890014648, ' sec.')
  </code></pre>
  
  <p>which means that there is no chance to load 500mln rows in reasonable time (last million of 500 millions would be loading 600 seconds).
  My guess was that the slowest part of each iteration was checking of key existence in dictionary:</p>
  
  <pre><code>if accountID in myDictionary:
  </code></pre>
  
  <p>So I changed dictionary to list hoping that simple appending will be much faster:</p>
  
  <pre><code>with open ('/path/to/file/CSV_FILE.csv','r') as dailyFile:
      allLines=csv.DictReader(dailyFile, delimiter='|')
      startTime=time.time()
      for row in allLines:
          linesRead += 1
          myList.append([row['account_id'].strip('\''), row['date'].strip('\''), row['balance1'], row['balance2'], row['balance3']])
          if (linesRead) % 1000000 == 0:
              print(linesRead, ": ", time.time()-startTime, " sec.")
              startTime=time.time()
  </code></pre>
  
  <p>Unfortunately there was no increase in performance at all:</p>
  
  <pre><code>1000000 :  9.15476489067  sec.
  2000000 :  10.3512279987  sec.
  3000000 :  12.2600080967  sec.
  4000000 :  13.5473120213  sec.
  5000000 :  14.8431830406  sec.
  6000000 :  16.5556428432  sec.
  7000000 :  17.6754620075  sec.
  8000000 :  19.1299819946  sec.
  9000000 :  19.7615978718  sec.
  10000000 :  22.5903761387  sec.
  </code></pre>
  
  <p><strong>Should not loading list be much faster than loading dictionary with key-check at entry?</strong></p>
  
  <p>Am I misusing python to process this kind of data?
  For comparison I sorted the file in with unix sort command:</p>
  
  <pre><code>$ date ; sort  -t'|' -k5,9 CSV_FILE.csv &gt; delete.txt; date;
  Sun Jul 23 18:46:16 CEST 2017
  Sun Jul 23 19:06:53 CEST 2017
  </code></pre>
  
  <p>And it took 20 minutes to do the job. While in python I was unable to load into memory the data.</p>