Removing certain separators from csv file with pandas or csv

user_14175545 0
Viewed 913

I've got multiple csv files, which I received in the following line format:

-8,000E-04,2,8E+1,

The first and the third comma are meant to be decimal separators, the second comma is a column delimiter and I think the last one is supposed to indicate a new line. So the csv should only consist of two columns and I have to prepare the data in order to plot it. Therefore I need to specify the two columns as x and y to plot the data.I tried removing or replacing the separators in every line but by doing that I'm no longer able to specify the two columns. Is there a way to remove certain separators from every line of the csv?

pandas data-manipulation dataframe csv python
3 Answers

I think you should replace the second comma using regex. Well, I'm definitely not an expert at it, but I've managed to come up with this:

import re

s = "-8,000E-04,2,8E+1,"
pattern = "^([^,]*,[^,]*),(.*),$"

grps = re.search(pattern, s).groups()

res = [float(s.replace(",", ".")) for s in grps]
print(res)
# [-0.0008, 28.0]

Sample csv file:

-8,000E-04,2,8E+1,
6,0E-6,-45E+2,
-5,550E-6,-6,2E+1,

And you can do something like this:

x = []
y = []

regex = re.compile("^([^,]*,[^,]*),(.*),$")

with open("a.csv") as f:
    for line in f:
        result = regex.search(line).groups()
        x.append(float(result[0].replace(",", ".")))
        y.append(float(result[1].replace(",", ".")))

The result is:

print(x, y)
# [-0.0008, 6e-06, -5.55e-06] [28.0, -4500.0, -62.0]

I'm not sure this is the most efficient way, but it works.

user_117512940

You can use the string returned by reading line as follow

line="-8,000E-04,2,8E+1,"
list_string = line.split(',')
x= float(list_string[0]+"."+list_string[1])
y= float(list_string[2]+"."+list_string[3])

print(x,y)

Result is

-0.0008 28.0

you can arrange x and y in columns also or whatever you want

user_109436050

Here a short program in python to convert your csv-files

import csv

f1 = "in_test.csv"
f2 = "out_test.csv"
with open(f1, newline='') as csv_reader:
    reader = csv.reader(csv_reader, delimiter=',')
    with open(f2, mode='w', newline='') as csv_writer:
        writer = csv.writer(csv_writer, delimiter=";")
        for row in reader:
            out_row = [row[0] + '.' + row[1], row[2] + '.' + row[3]]
            writer.writerow(out_row)

Sample input:

-8,000E-04,2,8E+1,
-2,000E-03,2,7E+2,

Sample output:

-8.000E-04;2.8E+1
-2.000E-03;2.7E+2
user_131471840
Related