Replace Commas Except Those In Quotation Marks

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/

Solution 1:

Have you considered saving this as a csv file.

after saving it as csv file:

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

output:

DateTimeRef     Sen      ATN      Flow  PCB temp    Status  Battery BC
02015/04/2312:30:00779581908043-15,25449310100   NaN
12015/04/2312:35:00778715907084-15,25949310100-127.022015/04/2312:40:00778299906419-15,23949320100461.0

You can read this as a dataframe.

Solution 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;'

Solution 3:

A solution using the csv Python standard library:

import csv

withopen('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;-1272015/04/23;12:40:00;778299;906419;-15,239;49;32;0;100;461