Convert comma to dot in notepad (Not every comma)

March 21, 2023

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 ?

>Solution :

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