How to replace characters in a csv file

Question

I'm doing some measurements in the lab and want to transform them into some nice Python plots. The problem is the way the software exports CSV files, as I can't find a way to properly read the numbers. It looks like this:

-10;-0,0000026  
-8;-0,00000139  
-6;-0,000000546   
-4;-0,000000112  
-2;-5,11E-09  
0,0000048;6,21E-09  
2;0,000000318  
4;0,00000304  
6;0,0000129  
8;0,0000724  
10;0,000268

Separation by ; is fine, but I need every , to be .. Ideally I would like Python to be able to read numbers such as 6.21E-09 as well, but I should be able to fix that in excel...

My main issue: Change every , to . so Python can read them as a float.

Maybe this can help: https://stackoverflow.com/a/34315381/812912 — Ivaylo Strandjev, Oct 19 '20 at 10:12
You should be able to set the sepration when you export in excel. — Darina, Oct 19 '20 at 10:12
Does this answer your question? [convert decimal mark](https://stackoverflow.com/questions/7106417/convert-decimal-mark) — Vignesh, Oct 19 '20 at 10:13
`float` already understand the cientific notation, so you only need to replace `,` by `.` and then use the `float` function. — Adirio, Oct 19 '20 at 10:23

score 0 · Answer 1 · answered Oct 19 '20 at 10:18

0

The simplest way would be for you to convert them to string and then use the .replace() method to pretty much do anything. For i.e.

txt = "0,0000048;6,21E-09"
txt = txt.replace(';', '.')

You could also read the CSV file (I don't know how you are reading the file) but depending on the library, you could change the 'delimiter' (to : for example). CSV is Comma-separated values and as the name implies, it separates columns by means of '.

answered Oct 19 '20 at 10:18

Mohammad Chegini

1
2

Thank you so much, this is a simple way to fix my problems. My issue is no more! – user12212883 Oct 21 '20 at 12:30

Adirio · Answer 2 · 2020-10-19T10:24:42.440

0

You can do whatever you want in Python, for example:

import csv

with open('path_to_csv_file', 'r') as csv_file:
    data = list(csv.reader(csv_file, delimiter=';'))

data = [(int(raw_row[0]), float(raw_row[1].replace(',', '.'))) for row in data]

with open('path_to_csv_file', 'w') as csv_file:
    writer = csv.writer(csv_file, delimiter=';')
    writer.writerows(data)

edited Oct 19 '20 at 10:24

answered Oct 19 '20 at 10:19

Adirio

5,040
1
14
26

I edited to make it a one-liner, using int for the first column to prevent the 2.0 notation and float with the replacement of `,` by `.` for the second column. – Adirio Oct 19 '20 at 10:25
Thank you very much, this helped! My problem is solved. – user12212883 Oct 21 '20 at 12:29

score 0 · Answer 3 · answered Oct 19 '20 at 10:25

0

Can you consider a regex to match the ',' all in the text, then loop the match results in a process that takes ',' to '.'.

answered Oct 19 '20 at 10:25

Allen

88
6

How to replace characters in a csv file

3 Answers3