ansaurus

Question

Reading CSV files in numpy where delimiter is ","

Answer 1

+4 A:

The basic problem is that NumPy doesn't understand the concept of stripping quotes (whereas the csv module does). When you say delimiter='","', you're telling NumPy that the column delimiter is literally a quoted comma, i.e. the quotes are around the comma, not the value, so the extra quotes you get on he first and last columns are expected.

Looking at the function docs, I think you'll need to set the converters parameter to strip quotes for you (the default does not):

import re
import numpy as np

fieldFilter = re.compile(r'^"?([^"]*)"?$')
def filterTheField(s):
    m = fieldFilter.match(s.strip())
    if m:
        return float(m.group(1))
    else:
        return 0.0 # or whatever default

#...

# Yes, sorry, you have to know the number of columns, since the NumPy docs
# don't say you can specify a default converter for all columns.
convs = dict((col, filterTheField) for col in range(numColumns))
data = np.genfromtxt(csvfile, dtype=None, delimiter=',', names=True, 
    converters=convs)

Or abandon np.genfromtxt() and let csv.csvreader give you the file's contents a row at a time, as lists of strings, then you just iterate through the elements and build the matrix:

reader = csv.csvreader(csvfile)
result = np.array([[float(col) for col in row] for row in reader])
# BTW, column headings are in reader.fieldnames at this point.

EDIT: Okay, so it looks like your file isn't all floats. In that case, you can set convs as needed in the genfromtxt case, or create a vector of conversion functions in the csv.csvreader case:

reader = csv.csvreader(csvfile)
converters = [datetime, float, int, float]
result = np.array([[conv(col) for col, conv in zip(row, converters)] 
    for row in reader])
# BTW, column headings are in reader.fieldnames at this point.

EDIT 2: Okay, variable column count... Your data source just wants to make life difficult. Luckily, we can just use magic...

reader = csv.csvreader(csvfile)
result = np.array([[magic(col) for col in row] for row in reader])

... where magic() is just a name I got off the top of my head for a function. (Psyche!)

At worst, it could be something like:

def magic(s):
    if '/' in s:
        return datetime(s)
    elif '.' in s:
        return float(s)
    else:
        return int(s)

Maybe NumPy has a function that takes a string and returns a single element with the right type. numpy.fromstring() looks close, but it might interpret the space in your timestamps as a column separator.

P.S. One downside with csvreader I see is that it doesn't discard comments; real csv files don't have comments.

Mike DeSimone 2010-04-19 02:17:43

The `str.replace('"', '')` method should perform noticeably faster than the regular expression if the input file is large (many MBs or GBs), and will be correct if you can assume the `"` character will not appear in the middle of a field, only at the ends.

gotgenes 2010-04-19 02:29:28

Thanks Mike and gotgenes, but I should've also mentioned that the CSV file has a variable number of columns. I could probably use the approach you've described by adding an initial step to read in the 1st record of the file to determine the number of columns, then using that as input for later steps, but it seems pretty clunky.Is there a better way?

monch1962 2010-04-19 02:46:57

Tiny note: you don't need to use `re.compile()` because just using `re.match()` directly will cache the compiled regular expression anyway.

blokeley 2010-04-19 07:42:18

Works like a charm - thanks guys

monch1962 2010-04-19 12:09:07

@blokeley: Really? I've never heard of that. Thanks... but I'll still use `re.compile` anyway since it lets me name my regular expressions.

Mike DeSimone 2010-04-19 13:54:08

@Mike DeSimone. Yes indeed. I hope this helps. See the note at http://docs.python.org/library/re.html#re.compile

blokeley 2010-04-19 16:53:25

ansaurus

tags:

views:

answers:

Reading CSV files in numpy where delimiter is ","

related questions