Question

PYTHON
#What you need to do is to transform the file Mkt_data_test.txt
#to the format like in file in the screenshot
#
#Your steps to get there:
#1.Create a header line with following columns: Time;Bid\Ask;Price;Volume. NOTE NO SPACES
#2.Remove all of the timestamp lines, i.e ======== Data: .....
#3.Remove the 1900-01-01 from the timestamp but leave the time itself
#4.Get rid of all spaces and empty lines
#5.Replace 0 or 1 in the second position with Bid or Ask, Bid for 0, Ask for 1
#6.Save the header line and propely formatted lines to the COMMA-SEPARATED csv file named mktDataFormat.csv

The txt file is:

======== Data: 00:05:08.627012 =========          
         
1900-01-01 00:05:08.627012 ; 0 ; 1.16198 ; 10000000.0
1900-01-01 00:05:08.627012 ; 1 ; 1.16232 ; 10000000.0
         
========= Data: 00:05:12.721536 =========        
         
1900-01-01 00:05:08.627012 ; 0 ; 1.16198 ; 10000000.0
1900-01-01 00:05:12.721536 ; 0 ; 1.16209 ; 1000000.0
1900-01-01 00:05:08.627012 ; 1 ; 1.16232 ; 10000000.0
         
========= Data: 00:05:12.729989 =========        
         
1900-01-01 00:05:08.627012 ; 0 ; 1.16198 ; 10000000.0
1900-01-01 00:05:12.721536 ; 0 ; 1.16209 ; 1000000.0
1900-01-01 00:05:12.729989 ; 1 ; 1.16218 ; 1000000.0
1900-01-01 00:05:08.627012 ; 1 ; 1.16232 ; 10000000.0
         
========= Data: 00:05:12.735727 =========        
         
1900-01-01 00:05:08.627012 ; 0 ; 1.16198 ;  10000000.0
1900-01-01 00:05:12.735727 ; 0 ; 1.16208 ; 1000000.0
1900-01-01 00:05:12.729989 ; 1 ; 1.16218 ; 1000000.0
1900-01-01 00:05:08.627012 ; 1 ; 1.16232 ; 10000000.

What I have so far is this:

csvRead = open("Mkt_data_test.txt","r")
lines = csvRead.readlines()

for line in lines[1: ]:
    string =
    string = string.replace("1900-01-01", "")
    string = string.replace("00:","")
    string = string.replace(' ', '')
    line = string.split(';')
    columns = line
    colA = columns[0]
    colB = columns[1]
    colC = columns[2]
    allColumns = colA + "." + colB + "." + colC
    if line[1] == "1":
        line[1] = line[1].replace("1", "Ask")
        print(line)
        if line[1] == "0":
            line[2] = line[2].replace("0", "Bid")
            print(line)
    print(colA,colB,colC)
    #maybe get rid of line below this
    def NewArray(data):
        return line
    print (NewArray(line))
NewArray(line).append(allColumns)

csvRead.close()


csv = open("CSV.csv","w")
header = "Time;Bid\Ask;Price;Volume\n"
csv.write(header)
csv.read(NewArray(line))
csv.close()

I know I need to put the string = to something, but I can't tell what. I know there are some other issues too though and I can't figure out what!

Answer 1

Python code :

import csv

# read the input file 'Mkt_data_test.txt'
with open('Mkt_data_test.txt', 'r') as f, open("edited_file.txt","w") as outfile:
    for line in f:
        #Remove all of the timestamp lines
        if not line.startswith('====='):
            #Remove empty lines
            if not line.isspace():
                #Remove the 1900-01-01
                 l1 = line.replace('1900-01-01 00:', '')
                 #Remove all spaces 
                 l2 =  l1.replace(' ', '')
                 #Replace 0 or 1 in the second position with Bid or Ask, Bid for 0, Ask for 1
                 col = l2.split(";")
                 if(col[1] == '0'):
                     col[1] ='Bid'
                 if(col[1]=='1'):
                     col[1] = 'Ask'
                 l3= ';'.join(col)    
                 #write result to another file 'edited_file.txt'
                 outfile.write(l3)

#convert text file to csv file
with open('edited_file.txt', 'r') as input_file:
    stripped = (line.strip() for line in input_file)
    lines = (line.split(";") for line in stripped if line)
    with open('output.csv', 'w') as output_file:
        writer = csv.writer(output_file)
        #Create a header line and write it to a csv file
        writer.writerow(('Time', 'Bid\Ask', 'Price', 'Volume'))
        writer.writerows(lines)

       
           

SCREENSHOT 1: INPUT FILE

SCREENSHOT 2 : INTERMEDIATE EDITED FILE

SCREENSHOT 3 : OUTPUT CSV FILE

SCREENSHOT 4: OUTPUT CSV FILE