Why is numpy/pandas parsing of a csv file with long lines so slow?

Question

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 this 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.

• What's going on here?

• Are there any csv parsers that that would be more efficient?

• If I change the format of the input data will it help?

Here's the source code I'm benchmarking with (the sum() is just to make sure any lazy iterators are forced to evaluate everything):

#! /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)

The output (to stderr) is:

<function python_iterator_csv at 0x7f22302b1378> 19.754893831999652
<function python_iterator_csv at 0x7f22302b1378> 19.62786615600271
<function python_iterator_csv at 0x7f22302b1378> 19.66641107099713

<function python_list_csv at 0x7f22302b1ae8> 18.761991592000413
<function python_list_csv at 0x7f22302b1ae8> 18.722911622000538
<function python_list_csv at 0x7f22302b1ae8> 19.00348913199923

<function python_array_csv at 0x7f222baffa60> 41.8681991630001
<function python_array_csv at 0x7f222baffa60> 42.141840383999806
<function python_array_csv at 0x7f222baffa60> 41.86879085799956

<function numpy_csv at 0x7f222ba5cc80> 47.957625758001086
<function numpy_csv at 0x7f222ba5cc80> 47.245571732000826
<function numpy_csv at 0x7f222ba5cc80> 47.25457685799847

<function pandas_csv at 0x7f2228572620> 43.39656048499819
<function pandas_csv at 0x7f2228572620> 43.5016079220004
<function pandas_csv at 0x7f2228572620> 43.567352316000324

<function numpy_fromstring at 0x7f593ed3cc80> 32.490607361
<function numpy_fromstring at 0x7f593ed3cc80> 32.421125410997774
<function numpy_fromstring at 0x7f593ed3cc80> 32.37903898300283

<function pandas_csv_2 at 0x7f846d1aa730> 24.903284349999012
<function pandas_csv_2 at 0x7f846d1aa730> 25.498485038999206
<function pandas_csv_2 at 0x7f846d1aa730> 25.03262125800029

From the blog post linked above it seems that pandas can import a csv matrix of random doubles at a data rate of 145/1.279502 = 113 MB/s. My file is 814 MB, so pandas is only manages ~19 MB/s for me!

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.)

edit2: Best of three times without the sum:

python_list_csv    17.8
python_array_csv   23.0
numpy_csv          28.6
numpy_fromstring   13.3
pandas_csv_2       24.2

so without the summation numpy.fromstring beats pure python by a small margin (I think fromstring is written in C so this makes sense).

edit3:

I've done some experimentation with the C/C++ float parsing code here 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 numpy.fromstring is boost's spirit::qi which is C++ and so not likely to make it into any python libraries.

[ More precise results: spirit::qi ~ 3s, lexical_cast ~ 7s, atof and strtod ~ 10s, sscanf ~ 18s, stringstream and stringstream reused are incredibly slow at 50s and 28s. ]

Answer 1

Does your CSV file contain column headers? If not, then explicitly passing header=None to pandas.read_csv can give a slight performance improvement for the Python parsing engine (but not for the C engine):

In [1]: np.savetxt('test.csv', np.random.randn(1000, 20000), delimiter=',')

In [2]: %timeit pd.read_csv('test.csv', delimiter=',', engine='python')
1 loops, best of 3: 9.19 s per loop

In [3]: %timeit pd.read_csv('test.csv', delimiter=',', engine='c')
1 loops, best of 3: 6.47 s per loop

In [4]: %timeit pd.read_csv('test.csv', delimiter=',', engine='python', header=None)
1 loops, best of 3: 6.26 s per loop

In [5]: %timeit pd.read_csv('test.csv', delimiter=',', engine='c', header=None)
1 loops, best of 3: 6.46 s per loop

Update

If there are no missing or invalid values then you can do a little better by passing na_filter=False (only valid for the C engine):

In [6]: %timeit pd.read_csv('test.csv', sep=',', engine='c', header=None)
1 loops, best of 3: 6.42 s per loop

In [7]: %timeit pd.read_csv('test.csv', sep=',', engine='c', header=None, na_filter=False)
1 loops, best of 3: 4.72 s per loop

There may also be small gains to be had by specifying the dtype explicitly:

In [8]: %timeit pd.read_csv('test.csv', sep=',', engine='c', header=None, na_filter=False, dtype=np.float64)
1 loops, best of 3: 4.36 s per loop

Update 2

Following up on @morningsun's comment, setting low_memory=False squeezes out a bit more speed:

In [9]: %timeit pd.read_csv('test.csv', sep=',', engine='c', header=None, na_filter=False, dtype=np.float64, low_memory=True)
1 loops, best of 3: 4.3 s per loop

In [10]: %timeit pd.read_csv('test.csv', sep=',', engine='c', header=None, na_filter=False, dtype=np.float64, low_memory=False)
1 loops, best of 3: 3.27 s per loop

For what it's worth, these benchmarks were all done using the current dev version of pandas (0.16.0-19-g8d2818e).