Python reading in integers from a csv file into a list

Question

I am having some trouble trying to read a particular column in a csv file into a list in Python. Below is an example of my csv file:

Col 1       Col 2
1,000,000   1
  500,000   2
  250,000   3

Basically I am wanting to add column 1 into a list as integer values and am having a lot of trouble doing so. I have tried:

for row in csv.reader(csvfile):
    list = [int(row.split(',')[0]) for row in csvfile]

However, I get a ValueError that says "invalid literal for int() with base 10: '"1'

I then tried:

for row in csv.reader(csvfile):
    list = [(row.split(',')[0]) for row in csvfile]

This time I don't get an error however, I get the list:

['"1', '"500', '"250']

I have also tried changing the delimiter:

for row in csv.reader(csvfile):
    list = [(row.split(' ')[0]) for row in csvfile]

This almost gives me the desired list however, the list includes the second column as well as, "\n" after each value:

['"1,000,000", 1\n', etc...]

If anyone could help me fix this it would be greatly appreciated!

Cheers

Solution

You should choose your delimiter wisely : If you have floating numbers using ., use , delimiter, or if you use , for floating numbers, use ; as delimiter.

Moreover, as referred by the doc for csv.reader you can use the delimiter= argument to define your delimiter, like so:

with open('myfile.csv', 'r') as csvfile:
    mylist = []
    for row in csv.reader(csvfile, delimiter=';'):
        mylist.append(row[0]) # careful here with [0]

or short version:

with open('myfile.csv', 'r') as csvfile:
    mylist = [row[0] for row in csv.reader(csvfile, delimiter=';')]

To parse your number to a float, you will have to do

 float(row[0].replace(',', ''))

Comments

[Adeel Ahmad]

Please post your CSV file (or just link to it), so we can produce the same results.

[Sean]

The separator is commas, could that be the problem?

[gogaz]

If your data contains commas, you can't use comma as separator

[timgeb]

Just noticed that this is a well asked question, so +1!

[Ajax1234]

What is your desired output?

[Sean]

The desired output would be ['1,000,000' , '500,000', '250,000'] P.S. these values would also need to be integers

[OneCricketeer]

What if you replace row.split(',')[0] with just row[0]? The commas are already split by the csv.reader

[Sean]

I have just given that a go however, it only adds the first number into the list so it looks like: [1, 5, 2] as oppose to [1,000,000, 500,000, 250,000]

[Serge Ballesta]

Normally, a csv file can use quoting is a delimiter (or a new line) is embedded in a field, so just rely on the scv module to give you fields and just forget split. And please show the actual content of the csv file (in a text editor, cat on linux, type on Windows) and NOT in a spreadsheet!

[gogaz]

he has spaces in the csv header

[Ivan Anishchuk]

It's not really a float though and float won't really parse things like 1,000,000 without extra steps.

[Ivan Anishchuk]

Everything is actually wrong with spaces, tab is the most sane option if you don't use a comma or a semicolon. You can theoretically use any whitespace but handling whitespace-containing strings becomes complicated if you do. You should quote your strings anyway though.

[Ivan Anishchuk]

Yep. No reason to do weird regexp stuff when you have a whole special library built specifically to read csv.