Replace commas except those in quotation marks

Question

Date,Time,Ref,Sen,ATN,Flow,PCB temp,Status,Battery,BC
2015/04/23,12:30:00,779581,908043,"-15,254",49,31,0,100,
2015/04/23,12:35:00,778715,907084,"-15,259",49,31,0,100,-127
2015/04/23,12:40:00,778299,906419,"-15,239",49,32,0,100,461
(...)

Hi, I have an ascii file like the one above where I am trying to replace the commas for semicolon. This is the code I am using:

filein = open('Prueba1.txt')
fileout = open('Fin.txt', 'wt')
for line in filein:
    if line.startswith('20'):
        fileout.write( line.replace(',', ';') )
filein.close()
fileout.close()

The problem is that now I want to maintain the commas for the 5th column and also take of the quotation marks. Any ideas?

Answer 1

Have you considered saving this as a csv file.

after saving it as csv file:

import pandas as pd
v = pd.read_csv("check_delete.csv")

output:

      Date            Time  Ref     Sen      ATN      Flow  PCB temp    Status  Battery BC
0   2015/04/23  12:30:00    779581  908043  -15,254 49  31  0   100   NaN
1   2015/04/23  12:35:00    778715  907084  -15,259 49  31  0   100   -127.0
2   2015/04/23  12:40:00    778299  906419  -15,239 49  32  0   100    461.0

You can read this as a dataframe.

Answer 2

This is an answer making use of the split() method if you do not want to use .csv files.

Let's consider the line to be:

line = '2015/04/23,12:35:00,778715,907084,"-15,259",49,31,0,100,-127'

First, split the line into three parts. One before the part in the quotes, the quoted part, and the part after the part in quotes. This can be done by line.split('"').

This will give us a list consisting of these three parts:

alist = ['2015/04/23,12:35:00,778715,907084,', '-15,259', ',49,31,0,100,-127']

Now, split the first and last elements of the list by the comma. And add all the elements to an empty string. Write this string to your new file.

Like this:

left_part = alist[0].split(',')
right_part = alist[2].split(',')
middle_part = alist[1]
final_list = left_part + [middle_part] + right_part
new_line = ''
for part in final_list:
    # to prevent the empty strings to be added add the if condition.
    if part:
        new_line += part + ';' 

The final result should look like this:

'2015/04/23;12:35:00;778715;907084;-15,259;49;31;0;100;-127;'

Answer 3

A solution using the csv Python standard library:

import csv

with open('example.csv', newline='') as inputfile, \
     open('parsedcsv.csv', 'w',  newline='') as outpufile:

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

    csvwriter = csv.writer(outpufile, delimiter=';',
            quotechar="'", quoting=csv.QUOTE_MINIMAL)

    csvwriter.writerows(datareader)

Quote from the documentation about the QUOTE_MINIMAL option:

csv.QUOTE_MINIMAL: Instructs writer objects to only quote those fields which contain special characters such as delimiter, quotechar or any of the characters in lineterminator.

The output file is:

Date;Time;Ref;Sen;ATN;Flow;PCB temp;Status;Battery;BC
2015/04/23;12:30:00;779581;908043;-15,254;49;31;0;130;
2015/04/23;12:35:00;778715;907084;-15,259;49;31;0;100;-127
2015/04/23;12:40:00;778299;906419;-15,239;49;32;0;100;461