Replacing characters/delimiters (double comma) in CSV file using python

Question

I have a CSV file that produce an empty column every other column due to a ,, delimiter. I"m trying to remove these empty columns, but I can't figure out how to loop thru the rows and replace the ,, with ,.

Any help looping thru the lines and replacing the delimiters would be greatly appreciated.

Thanks!

Example data:

Year,,2005-12,,2006-12,,2007-12,,2008-12,,2009-12,,2010-12,,2011-12,,2012-12,,2013-12,,2014-12,,TTM
Data1,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00
    .... 

Answer 1

Use the csv module:

import csv

input = open('in.csv', 'rb')
output = open('out.csv', 'wb')
writer = csv.writer(output)

for read in csv.reader(input):
    writer.writerow([x for x in read if x]) #Removes empty fields and writes into new file

input.close()
output.close()

Although if the input csv file is pretty large then I don't think this would be an optimal solution.

Answer 2

You can do it from the command line with pysed:

https://pypi.python.org/pypi/pysed

(stackoverflow)macbook:stackoverflow joeyoung$ pip install pysed
(stackoverflow)macbook:stackoverflow joeyoung$ cat file.csv 
Year,,2005-12,,2006-12,,2007-12,,2008-12,,2009-12,,2010-12,,2011-12,,2012-12,,2013-12,,2014-12,,TTM
Data1,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00,,100.00
(stackoverflow)macbook:stackoverflow joeyoung$ pysed -r ",," "," file.csv --write
(stackoverflow)macbook:stackoverflow joeyoung$ cat file.csv 
Year,2005-12,2006-12,2007-12,2008-12,2009-12,2010-12,2011-12,2012-12,2013-12,2014-12,TTM
Data1,100.00,100.00,100.00,100.00,100.00,100.00,100.00,100.00,100.00,100.00,100.00

Answer 3

if you want to do it via python then use the replace method.

>>> s = 'year,,2015-12,,2006-12'
>>> print(s)
year,,2015-12,,2006-1
>>> new_s = s.replace(",,",",")
>>> print(new_s)
year,2015-12,2006-12