ValueError: could not convert string to float: '" "'

Question

I have some values is csv file and In csv file some values are numeric and some are string number. Example of csv file:

1,"1151226468812.22",100,1,467,999.00,999.95,15,1,999.00,999.95,998.50,999.95,15,999.01,1396,34,06092016091501.444,1394627.25
2,"1151226468812.11",100,1,467,999.00,1000.00,1605,3,999.00,1000.00,998.50,1000.00,5,999.03,1426,37,06092016091502.111,1424626.50

So I wnated to convert string to float. So here is my code:

datareader = csv.reader(datafile, delimiter=",", quoting= csv.QUOTE_NONE)

    names =  []
    names.append("local_timestamp")
    names.append("nse_timestamp")
for row in datareader:
        data = dict()
        data.update(local_timestamp = row[0])
        data.update(nse_timestamp = float(row[1]))

But it return value error.

ValueError: could not convert string to float: '"1151226468812.22"'

Solution 1

The problem is that your string is not just '1151226468812.22', but it is '"1151226468812.22"'. It also contains speech marks ("). This means that before you convert this to a float, you need to remove the leading and trailing speech marks. Luckily, Python has a very handy string method .strip() to do this for you.

string.strip(s) will return a string that has the leading and ending 's' characters removed

For example:

myString = "#hello#".strip("#")

In this code, myString would be just 'hello'

In this case, you want to strip row[1] of the leading and trailing " characters. You can do this very easily:

row[1].strip("\"")

Solution 2

The second field in your csv is quoted with ". In csv, having quoted fields does not mean those are strings, but that the field could contain a delimiter, like "123,45".

The right way to read such data is to tell the reader some fields can be quoted:

datareader = csv.reader(datafile, delimiter=',', quotechar='"')

This will return the second field without the quotes and solve your problem.

Removing the quotes afterwards not only adds extra work, but can also lead to errors if the field contains a delimiter. For example "123,45" would return "123 and 45" as two different fields.