1

I am trying to sum a column in a csv file. As the amount column has comma in it the following error is getting thrown:

Traceback (most recent call last):
  File "completeness.py", line 10, in <module>
    debit_gl=debit_gl+float(line['Accounted_Debit'])
ValueError: could not convert string to float: ' 1,028.540000'

As I am new to python im not able to figure out which code i can use to sum the whole row.

I have already wrote the code:

import csv
import sys
import os

debit_gl=0
credit_gl=0
reader_gl=csv.DictReader(open('ABC 2011 GL Extract.txt',encoding='utf-8',errors='ignore'), delimiter='|',quotechar='"')
next(reader_gl)
for line in reader_gl:
    debit_gl=debit_gl+float(line['Accounted_Debit'])
print(debit_gl)

It would be great of someone can resolve this issue. Also please note as I work with huge data sets I wont be able to create another column and remove the commas.

Siddhartha
  • 53
  • 6
  • 1
    Remove thousand separators then try parsing: `float(line['Accounted_Debit'].replace(',', ''))` – abdusco Jul 01 '19 at 10:28
  • 2
    Related to: https://stackoverflow.com/questions/1779288/how-to-convert-a-string-to-a-number-if-it-has-commas-in-it-as-thousands-separato – abdusco Jul 01 '19 at 10:29
  • oh ok. thanks for the help. I was using the wrong syntax for replace. – Siddhartha Jul 02 '19 at 06:46

1 Answers1

1

Just remove the commas:

import csv
import sys
import os

debit_gl=0
credit_gl=0
reader_gl=csv.DictReader(open('ABC 2011 GL Extract.txt',encoding='utf-8',errors='ignore'), delimiter='|',quotechar='"')
next(reader_gl)
for line in reader_gl:
    debit_gl=debit_gl+float(line['Accounted_Debit'].replace(',', ''))
print(debit_gl)
ooa
  • 463
  • 2
  • 8