title: Why is numpy/pandas parsing of a csv file with long lines so slow?
body: <p>I'm trying to efficiently parse a csv file with around 20,000 entries per line (and a few thousand lines) to a numpy array (or list of arrays, or anything similar really). I found a number of other questions, along with <a href="http://wesmckinney.com/blog/a-new-high-performance-memory-efficient-file-parser-engine-for-pandas/" rel="noreferrer">this</a> blog post, which suggest that pandas's csv parser is extremely fast. However I've benchmarked pandas, numpy and some pure-python approaches and it appears that the trivial pure-python string splitting + list comprehension beats everything else by quite a large margin.</p>
  
  <ul>
  <li><p>What's going on here?</p></li>
  <li><p>Are there any csv parsers that that would be more efficient?</p></li>
  <li><p>If I change the format of the input data will it help?</p></li>
  </ul>
  
  <p>Here's the source code I'm benchmarking with (the <code>sum()</code> is just to make sure any lazy iterators are forced to evaluate everything):</p>
  
  <pre><code>#! /usr/bin/env python3
  
  import sys
  
  import time
  import gc
  
  import numpy as np
  from pandas.io.parsers import read_csv
  import csv
  
  def python_iterator_csv():
      with open("../data/temp_fixed_l_no_initial", "r") as f:
          for line in f.readlines():
              all_data = line.strip().split(",")
              print(sum(float(x) for x in all_data))
  
  
  def python_list_csv():
      with open("../data/temp_fixed_l_no_initial", "r") as f:
          for line in f.readlines():
              all_data = line.strip().split(",")
              print(sum([float(x) for x in all_data]))
  
  
  def python_array_csv():
      with open("../data/temp_fixed_l_no_initial", "r") as f:
          for line in f.readlines():
              all_data = line.strip().split(",")
              print(sum(np.array([float(x) for x in all_data])))
  
  
  def numpy_fromstring():
      with open("../data/temp_fixed_l_no_initial", "r") as f:
          for line in f.readlines():
              print(sum(np.fromstring(line, sep = ",")))
  
  
  def numpy_csv():
      with open("../data/temp_fixed_l_no_initial", "r") as f:
          for row in np.loadtxt(f, delimiter = ",", dtype = np.float, ndmin = 2):
              print(sum(row))
  
  
  def csv_loader(csvfile):
      return read_csv(csvfile,
                        header = None,
                        engine = "c",
                        na_filter = False,
                        quoting = csv.QUOTE_NONE,
                        index_col = False,
                        sep = ",")
  
  def pandas_csv():
      with open("../data/temp_fixed_l_no_initial", "r") as f:
          for row in np.asarray(csv_loader(f).values, dtype = np.float64):
              print(sum(row))
  
  
  def pandas_csv_2():
      with open("../data/temp_fixed_l_no_initial", "r") as f:
          print(csv_loader(f).sum(axis=1))
  
  
  def simple_time(func, repeats = 3):
      gc.disable()
  
      for i in range(0, repeats):
          start = time.perf_counter()
          func()
          end = time.perf_counter()
          print(func, end - start, file = sys.stderr)
          gc.collect()
  
      gc.enable()
      return
  
  
  if __name__ == "__main__":
  
      simple_time(python_iterator_csv)
      simple_time(python_list_csv)
      simple_time(python_array_csv)
      simple_time(numpy_csv)
      simple_time(pandas_csv)
      simple_time(numpy_fromstring)
  
      simple_time(pandas_csv_2)
  </code></pre>
  
  <p>The output (to stderr) is:</p>
  
  <pre><code>&lt;function python_iterator_csv at 0x7f22302b1378&gt; 19.754893831999652
  &lt;function python_iterator_csv at 0x7f22302b1378&gt; 19.62786615600271
  &lt;function python_iterator_csv at 0x7f22302b1378&gt; 19.66641107099713
  
  &lt;function python_list_csv at 0x7f22302b1ae8&gt; 18.761991592000413
  &lt;function python_list_csv at 0x7f22302b1ae8&gt; 18.722911622000538
  &lt;function python_list_csv at 0x7f22302b1ae8&gt; 19.00348913199923
  
  &lt;function python_array_csv at 0x7f222baffa60&gt; 41.8681991630001
  &lt;function python_array_csv at 0x7f222baffa60&gt; 42.141840383999806
  &lt;function python_array_csv at 0x7f222baffa60&gt; 41.86879085799956
  
  &lt;function numpy_csv at 0x7f222ba5cc80&gt; 47.957625758001086
  &lt;function numpy_csv at 0x7f222ba5cc80&gt; 47.245571732000826
  &lt;function numpy_csv at 0x7f222ba5cc80&gt; 47.25457685799847
  
  &lt;function pandas_csv at 0x7f2228572620&gt; 43.39656048499819
  &lt;function pandas_csv at 0x7f2228572620&gt; 43.5016079220004
  &lt;function pandas_csv at 0x7f2228572620&gt; 43.567352316000324
  
  &lt;function numpy_fromstring at 0x7f593ed3cc80&gt; 32.490607361
  &lt;function numpy_fromstring at 0x7f593ed3cc80&gt; 32.421125410997774
  &lt;function numpy_fromstring at 0x7f593ed3cc80&gt; 32.37903898300283
  
  &lt;function pandas_csv_2 at 0x7f846d1aa730&gt; 24.903284349999012
  &lt;function pandas_csv_2 at 0x7f846d1aa730&gt; 25.498485038999206
  &lt;function pandas_csv_2 at 0x7f846d1aa730&gt; 25.03262125800029
  </code></pre>
  
  <p>From the blog post linked above it seems that pandas can import a csv matrix of random doubles at a data rate of <code>145/1.279502</code> = 113 MB/s. My file is 814 MB, so pandas is only manages ~19 MB/s for me!</p>
  
  <p>edit: As pointed out by @ASGM, this wasn't really fair to pandas because it is not designed for rowise iteration. I've included the suggested improvement in the benchmark but it's still slower than pure python approaches. (Also: I've played around with profiling similar code, before simplifying it to this benchmark, and the parsing always dominated the time taken.)</p>
  
  <p>edit2: Best of three times without the <code>sum</code>:</p>
  
  <pre><code>python_list_csv    17.8
  python_array_csv   23.0
  numpy_csv          28.6
  numpy_fromstring   13.3
  pandas_csv_2       24.2
  </code></pre>
  
  <p>so without the summation <code>numpy.fromstring</code> beats pure python by a small margin (I think fromstring is written in <a href="https://github.com/numpy/numpy/blob/master/numpy/core/src/multiarray/ctors.c#L3528" rel="noreferrer">C</a> so this makes sense).</p>
  
  <p>edit3:</p>
  
  <p>I've done some experimentation with the C/C++ float parsing code <a href="http://tinodidriksen.com/2011/05/28/cpp-convert-string-to-double-speed/" rel="noreferrer">here</a> and it looks like I'm probably expecting too much from pandas/numpy. Most of the robust parsers listed there give times of 10+ seconds just to parse this number of floats. The only parser which resoundingly beats <code>numpy.fromstring</code> is boost's <a href="http://www.boost.org/doc/libs/1_57_0/libs/spirit/doc/html/index.html" rel="noreferrer"><code>spirit::qi</code></a> which is C++ and so not likely to make it into any python libraries.</p>
  
  <p>[ More precise results: <code>spirit::qi</code> ~ 3s, <code>lexical_cast</code> ~ 7s, <code>atof</code> and <code>strtod</code> ~ 10s,  <code>sscanf</code> ~ 18s, <code>stringstream</code> and <code>stringstream reused</code> are incredibly slow at 50s and 28s. ]</p>