I need to split these data in excel File

Mejbri Elias 0
Viewed 35

I tried using below code python to split this data But I still can't split the other part.

Data: 0000-13:56:16.952610|6375|11111|6375|10211|1001||||0|002200002000FOR07,0=1000,1=B,4=15.97,5=500,6=90006.953331,8=0.0000001,9=500,10=15.97,11=15.97,12=15.97,13= TH,14=+,16=15.97,17=1,18=15.97,22=17.2476,24=14.6924,29=1,33=2,34=FOR07,36=22,37=AC,41=4,44=7985,61=0,65=115517.796781,67=B4txhs6nSkInog8v,70=EUR,73=B,74=AT,75=H,88=FOR000927807,98=15.97,104=3,140= ,141=I,153=15.97,154=P,157=200000,160=15.97,180=33726,189=1,234=115459.78062,235=115517.80251|

Python Code:

import pandas as pd
from openpyxl import workbook

df = pd.read_table("TEST1.dat", sep='[|,=]', usecols=[
    0, 5, 10], names=['Time', 'Request', 'ISIN Code'], index_col=False, header=None, engine='python')
df.to_excel('output5.xlsx', 'sheet1')

Result :

Time Request ISIN Code
0000-13:56:16.952610 1001 002200002000FOR07

What I should have is as below:

Time Request ISIN Code 0 1 4 5 ... 235
0000-13:56:16.952610 1001 002200002000FOR07 1000 B 15.97 500 ... 11.

Any Help Please ??

pandas python
1 Answers

Please, consider this code:

import numpy as np
import pandas as pd

with open("TEST1.dat", "r") as f:
    text = f.readlines()
    
text = list(map(lambda x: np.array(x.split('|'))[[0,5,10]], text))
result = []
for row in text:
    row2 = row[2].split(',')
    result.append({'Time': row[0], 'Request': row[1], 'ISIN Code': row2[0]} | {k.split('=')[0]: k.split('=')[1] for k in row2[1:]})

df = pd.DataFrame(result)
df.to_excel('output5.xlsx', index=False)
userYE4D0
Related