Convert comma to dot in notepad (Not every comma)

Question

I have a data which looks like

5,960464,6,65430376927058E-10,-91,7689743041992,-89,5235061645508,6,71407200919707E-05,0,00869479635485696
11,92093,5,23110578457515E-10,-92,8140640258789,-90,5685958862305,8,97218165598724E-05,0,011619072933792
17,88139,4,52214777469635E-10,-93,4465560913086,-91,2010803222656,0,000104674258706842,0,0135554304720727
23,84186,4,29985026518504E-10,-93,6654663085938,-91,4199981689453,0,00011655840052398,0,0150944397768688
29,80232,5,20411183436712E-10,-92,8365325927734,-90,591064453125,0,000128133766655091,0,0165934622941289

This data has actually 6 columns. Comma used to separate the data as well as used for floating numbers.

I need to convert above data to the following for further processing.

x,           y1,                    y2,                y3,               y4,               y5
5.960464,6.65430376927058E-10,-91.7689743041992,-89.5235061645508,6.71407200919707E-05,0.00869479635485696
11.92093,5.23110578457515E-10,-92.8140640258789,-90.5685958862305,8.97218165598724E-05,0.011619072933792
17.88139,4.52214777469635E-10,-93.4465560913086,-91.2010803222656,0.000104674258706842,0.0135554304720727
23.84186,4.29985026518504E-10,-93.6654663085938,-91.4199981689453,0.00011655840052398,0.0150944397768688
29.80232,5.20411183436712E-10,-92.8365325927734,-90.591064453125,0.000128133766655091,0.0165934622941289

I've tried to read the file in python and read data from each column which separated by comma. This case is not working, at some rows has more columns.

Can anyone helps me to find a solution ?

The code works, Thank you. But there is an error occurred on lines 432 and other lines of output.

16433.7,08114511022965E-12.-111,498962402344.-109,253494262695.0,000461181910941812.016438.96,5.80785796046257E-12,-112.359840393066,-110.114372253418,0.000461223557779535,0.0597289529135575

16886.6,00392270522813E-12.-112,21565246582.-109,970184326172.0,000463834922107529.016891.96,4.66341618448496E-12,-113.312957763672,-111.067489624023,0.00046386919345512,0.0600715656141191


17488.3,70004369566838E-12.-114,317932128906.-112,072463989258.0,00046732773455058.017493.96,5.22050812530021E-12,-112.822875976563,-110.57740020752,0.000467356179916467,0.0605231341575011

The data before and after the error lines are perfectly ok.

17476.08,6.52454250181715E-12,-111.854499816895,-109.609031677246,0.00046727035202924,0.0605120193526557
17482.04,3.88578861020505E-12,-114.105209350586,-111.859741210938,0.000467303551829094,0.0605163187628847


17499.92,5.28003710011641E-12,-112.773628234863,-110.528160095215,0.000467389661260297,0.0605274700279817
17505.88,4.37629603159924E-12,-113.588935852051,-111.343467712402,0.000467420448688495,0.0605314570334547

Please help me to correct it! Thanks in advance

Your data is messed up. There are some cases where there's no way of knowing what comma is a decimal separator and which comma is a number separator — user20396240
– user20396240, Commented Mar 21, 2023 at 9:21
Yes, this was an output from a device. This is the only way to save the data. Is there an option to change alternative comma to decimal? This helps in my case — Sunny
– Sunny, Commented Mar 21, 2023 at 9:24
Can you be sure every number will have decimal part? Can we assume there are no integers? — Jorge Luis
– Jorge Luis, Commented Mar 21, 2023 at 9:28

Corralien · Accepted Answer · 2023-03-21 09:34:56Z

3

Since it looks like you only have real numbers, you can replace every other comma:

with (open('data.txt', 'r') as fr,
      open('out.csv', 'w') as fw):
    fw.write('x,y1,y2,y3,y4,y5\n')
    for row in fr:
        l = row.split(',')
        l = ['.'.join(i) for i in zip(l[::2], l[1::2])]
        fw.write(','.join(l))

out.csv:

x,y1,y2,y3,y4,y5
5.960464,6.65430376927058E-10,-91.7689743041992,-89.5235061645508,6.71407200919707E-05,0.00869479635485696
11.92093,5.23110578457515E-10,-92.8140640258789,-90.5685958862305,8.97218165598724E-05,0.011619072933792
17.88139,4.52214777469635E-10,-93.4465560913086,-91.2010803222656,0.000104674258706842,0.0135554304720727
23.84186,4.29985026518504E-10,-93.6654663085938,-91.4199981689453,0.00011655840052398,0.0150944397768688
29.80232,5.20411183436712E-10,-92.8365325927734,-90.591064453125,0.000128133766655091,0.0165934622941289

Read it with Pandas and check:

import pandas as pd

df = pd.read_csv('out.csv')

>>> df
           x            y1         y2         y3        y4        y5
0   5.960464  6.654304e-10 -91.768974 -89.523506  0.000067  0.008695
1  11.920930  5.231106e-10 -92.814064 -90.568596  0.000090  0.011619
2  17.881390  4.522148e-10 -93.446556 -91.201080  0.000105  0.013555
3  23.841860  4.299850e-10 -93.665466 -91.419998  0.000117  0.015094
4  29.802320  5.204112e-10 -92.836533 -90.591064  0.000128  0.016593

>>> df.dtypes
x     float64
y1    float64
y2    float64
y3    float64
y4    float64
y5    float64
dtype: object

edited Mar 21, 2023 at 9:34

answered Mar 21, 2023 at 9:28

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sunny Over a year ago

Thank you @Corralien . The code works. The file has more than 10k 'x' values. At line 432, an error occurred. This error again rises at some other values as well. Do you know why? Thanks in advance!

Corralien Over a year ago

Please update your post with line 432 (and another to verify similarity). Maybe a try/except can solve the problem if all the problematic lines have the same format.

Jorge Luis Over a year ago

This code works as long as every number has decimal positions. If you find an integer value, you will substitute the comma you don't want to substitute.

Corralien Over a year ago

@Sunny. What is the error? Does Python raise an exception?

Corralien Over a year ago

How 16433.7,08114511022965E-12 should be interpreted?

|

Collectives™ on Stack Overflow

Convert comma to dot in notepad (Not every comma)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related