Replacing ',' with '.' and then ';' with ',' in a csv file

Question

I'm trying to replace the commas with decimal points and then replace the semi-colons with commas in a CSV file.

The CSV file:

# 2018-03-16: ECarbix Prices/Volumes for Emission Spot Market
#  
# Data type(ST);Trading Date;Creation Time
# Data type(IL);Index;Unit;Price;Volume
# Data type(AL);Number of Lines
#  
ST;2018-03-16;2018-03-19T08:39:48+01:00
IL;Day;EUR/tCO2;10,97;4533000
AL;9

The code I tried:

import pandas as pd
from os import walk
import csv

import xml.dom.minidom
from xml.etree import ElementTree
with open('some.csv', 'w', newline='') as fw:
 writer = csv.writer(fw)

 for filenames in walk("D:\EEX_EMS\CSV"):
    (filenames)
    fname= list(filenames)
    for f in fname[2]:
     if "Auction"  not in f:
      #print(f)
      with open('D:/EEX_EMS/CSV/'+f, 'r') as csvfile:
       spamreader = csv.reader(csvfile,  quotechar='|')
       for fg in spamreader:
        fi =str(fg)
        print(fi)
        fi1 = str(fi.replace(',','.'))
        fi2 = str(fi1.replace(';',','))
        fi_list =str(fi2.split(','))
        print (str(fi_list))

        writer.writerow(fi_list)
fw.close()

This is the output:

['# 2018-03-16: ECarbix Prices/Volumes for Emission Spot Market']
['#  ']
['# Data type(ST),Trading Date,Creation Time']
['# Data type(IL),Index,Unit,Price,Volume']
['# Data type(AL),Number of Lines']
['#  ']
['ST,2018-03-16,2018-03-19T08:39:48+01:00']
['IL,Day,EUR/tCO2,10'. '97,4533000']
['AL,9']

How can I get the correct output?

Possible duplicate of [convert decimal mark](https://stackoverflow.com/questions/7106417/convert-decimal-mark) — donkopotamus, Mar 20 '18 at 05:01
Don't replace the `;`, just provide a correct delimiter to `csv.reader` — donkopotamus, Mar 20 '18 at 05:01

score 0 · Accepted Answer · answered Mar 20 '18 at 04:52

One way to do the desired conversion would be:

Code:

def convert_comma_number(maybe_number):
    try:
        return float(maybe_number.replace(',', '.'))
    except ValueError:
        return maybe_number

import csv
with open('csvfile.csv', 'rU') as f:
    reader = csv.reader(f, delimiter=';')
    for row in reader:
        row = [convert_comma_number(col) for col in row]
        print(row)

Results:

['# 2018-03-16: ECarbix Prices/Volumes for Emission Spot Market']
['#  ']
['# Data type(ST)', 'Trading Date', 'Creation Time']
['# Data type(IL)', 'Index', 'Unit', 'Price', 'Volume']
['# Data type(AL)', 'Number of Lines']
['#  ']
['ST', '2018-03-16', '2018-03-19T08:39:48+01:00']
['IL', 'Day', 'EUR/tCO2', 10.97, 4533000.0]
['AL', 9.0]

Replacing ',' with '.' and then ';' with ',' in a csv file

1 Answers1

Code:

Results: