Tab-delimited file using csv.reader not delimiting where I expect it to

Question

I am trying to loop through a tab-delimited file of election results using Python. The following code does not work, but when I use a local file with the same results (the commented out line), it does work as expected.

The only thing I can think of is some headers or content type I need to pass the url, but I cannot figure it out.

Why is this happening?

import csv
import requests

r = requests.get('http://vote.wa.gov/results/current/export/MediaResults.txt') 
data = r.text
#data = open('data/MediaResults.txt', 'r')
reader = csv.reader(data, delimiter='\t')
for row in reader:
    print row

Results in:

...
['', '']
['', '']
['2']
['3']
['1']
['1']
['8']
['', '']
['D']
['a']
['v']
['i']
['d']
[' ']
['F']
['r']
['a']
['z']
['i']
['e']
['', '']
...

Solution 1

so whats happening, well, a call to help may shed some light.

>>> help(csv.reader)
 reader(...)
    csv_reader = reader(iterable [, dialect='excel']
                            [optional keyword args])
        for row in csv_reader:
            process(row)

    The "iterable" argument can be any object that returns a line
    of input for each iteration, such as a file object or a list.  The
    optional "dialect" parameter is discussed below.  The function
    also accepts optional keyword arguments which override settings
    provided by the dialect.

so it appears that csv.reader expects an iterator of some kind which will return a line, but we are passing a string which iterates on a char bases which is why its parsing character by character, one way to fix this would be to generate a temp file, but we don't need to, we just need to pass any iterable object.

note the following, which simply splits the string to a list of lines, before its fed to the reader.

import csv
import requests

r = requests.get('http://vote.wa.gov/results/current/export/MediaResults.txt') 
data = r.text
reader = csv.reader(data.splitlines(), delimiter='\t')
for row in reader:
    print row

this seems to work.

I also recommend using csv.DictReader its quite useful.

>>> reader = csv.DictReader(data.splitlines(), delimiter='\t')
>>> for row in reader:
...      print row
{'Votes': '417141', 'BallotName': 'Michael Baumgartner', 'RaceID': '2', 'RaceName': 'U.S. Senator', 'PartyName': '(Prefers Republican Party)', 'TotalBallotsCastByRace': '1387059', 'RaceJurisdictionTypeName': 'Federal', 'BallotID': '23036'}
{'Votes': '15005', 'BallotName': 'Will Baker', 'RaceID': '2', 'RaceName': 'U.S. Senator', 'PartyName': '(Prefers Reform Party)', 'TotalBallotsCastByRace': '1387059', 'RaceJurisdictionTypeName': 'Federal', 'BallotID': '27435'}

basically it returns a dictionary for every row, using the header as the key, this way we don't need to keep track of the order but instead just the name making a bit easier for us ie row['Votes'] seems more readable then row[4]...

Solution 2

This works perfectly:

import csv

reader = csv.reader(open('./MediaResults.txt'),
                    delimiter='\t')
for row in reader:
    print row

The first parameter to csv.readershould be:

any object which supports the iterator protocol and returns a string each time its next() method is called

as per the docs, and you are passing a string, not a file object. A string behaves as a list of single characters hence the behavior you are observing.

Solution 3

Simple problem: The csv.reader didn't expect a string for its input.

Simple solution: Change the input to: data.splitlines().

The csv reader expects an iterable that returns lines one at a time. A string, unfortunately, iterates a character at a time. To solve the problem, use splitlines() to turn the string into a list of lines:

reader = csv.reader(data.splitlines(), delimiter='\t')
for row in reader:
    print row

Solution 4

Perhaps you want to sniff the dialect through the csv API:

csvfile = open("example.csv", "rb")
dialect = csv.Sniffer().sniff(csvfile.read(1024))
csvfile.seek(0)
reader = csv.reader(csvfile, dialect)

This will produce the correct output.

See also

http://docs.python.org/library/csv.html#csv.Sniffer

View more solutions

Comments

[Andreas Jung]

The original is actually that you are passing the data directly to the reader() constructor instead of file handle.