[Python] RegEx to differentiate chemical compound names from pattern

I have a RegEx problem with a string pattern that i used to extract data from them by breaking it. I am using Python. The string lines are, simplified, something like this (there are some lines in between but these are the problematic ones):


Compound Name,RT,Score\n2,4,6-Cycloheptatrien-1-one, 3,5-bis-trimethylsilyl-,8.705,626,Area,386902.453,tR (min),8.705

['Compound Name', 'RT', 'Score', '2', '4', '6-Cycloheptatrien-1-one', ' 3', '5-bis-trimethylsilyl-', '8.705', '626', 'Area', '386902.453', 'tR (min)', '8.705', '']

Compound Name,RT,Score\nBenzene, 4-ethenyl-1,2-dimethyl-,11.682,600,Area,16289.239,tR (min),11.682

['Compound Name', 'RT', 'Score', 'Benzene', ' 4-ethenyl-1', '2-dimethyl-', '11.682', '600', 'Area', '16289.239', 'tR (min)', '11.682', '']

Compound Name,RT,Score\n2-(p-Tolyl)propan-2-ol, TMS,11.921,605,Area,17976.558,tR (min),11.921

['Compound Name', 'RT', 'Score', '2-(p-Tolyl)propan-2-ol', ' TMS', '11.921', '605', 'Area', '17976.558', 'tR (min)', '11.921', '']

Compound Name,RT,Score\nTris(tert-butyldimethylsilyloxy)arsane,12.036,685,Area,76702.743,tR (min),12.036

['Compound Name', 'RT', 'Score', 'Tris(tert-butyldimethylsilyloxy)arsane', '12.036', '685', 'Area', '76702.743', 'tR (min)', '12.036', '']

Compound Name,RT,Score\nPhenyl-pentamethyl-disiloxane,13.646,656,Area,84633.281,tR (min),13.646

['Compound Name', 'RT', 'Score', 'Phenyl-pentamethyl-disiloxane', '13.646', '656', 'Area', '84633.281', 'tR (min)', '13.646', '']

Compound Name,RT,Score\n**[1,1'-Bicyclopropyl]-2-octanoic acid, 2'-hexyl-, methyl est**,16.642,605,Area,566.512,tR (min),16.642

['Compound Name', 'RT', 'Score', '[1', "1'-Bicyclopropyl]-2-octanoic acid", " 2'-hexyl-", ' methyl est', '16.642', '605', 'Area', '566.512', 'tR (min)', '16.642', '']

Compound Name,RT,Score\n**(t-Butyl-dimethylsilyl)[2-methyl-2-(4-methyl-pent-3-enyl)-c**,19.853,650,Area,10141.071,tR (min),19.853


The bold area are the chemical compound names which change the most and are the main cause of problems, and straightaway my Python result. These chemical names should be treated as a whole string which may happen in some cases. Everything else between commas should be separated.

The point is first substracting those chemical compounds as characters on their own. I had a first pattern that used to work with simpler compound names:

pattern = r',(?=(?:[^"]*"[^"]*")*[^"]*$)|\n(?=(?:[^"]*"[^"]*")*[^"]*$)'

which put all compound names between single quotes into double quotes to treat them as a single character string. However now it will split if , is found and as you can see in the examples the compounds aren't between single quotes.

I believe it is way easier to tackle from behind: get the ,8.705,626,Area,386902.453,tR (min),8.705 portion first and treat everything before as a single string. Here Area and tR(min) won't change, however their numbers accompanying will.

Any idea how to proceed to separate both sides of the line, the compound and the rest? Shall i use RegEx ^ & $ special characters?


CODE:

import os
import io # for utf8
import sys
import re
from pprint import pprint
import xlsxwriter # for the data, to write them into xlsx.
import tkinter as tk # for GUI
from tkinter import filedialog # for GUI
from collections import Counter # for another thing

file_path=filedialog.askopenfilename()

file=io.open(file_path,'r',encoding="utf-8")
filecsvname=os.path.basename(file.name)
lines=file.readlines()
file.close()

filename=filecsvname.strip('.csv')

liness=[]
seqnames=[]
for i in range(len(lines)-1):
i
lines
if lines.startswith('Compound')==True and lines[i+1].startswith('A')==False:
liness.append(lines+lines[i+1])
elif lines.startswith('Area')==True:
liness.append(lines+',Compound Name, ')
elif lines.startswith('Sample Name')==True:
liness.append(lines)
elif lines.startswith('Sequence Name')==True:
seqnames.append(lines)
liness.append(lines)
# some irrelevant extra code for seqnames.
pattern = r',(?=(?:[^"]*"[^"]*")*[^"]*$)|\n(?=(?:[^"]*"[^"]*")*[^"]*$)'
datalines=[]
for i in range(len(liness)):
lineofdata=liness.rstrip('\t')
splittedlines=re.split(pattern,lineofdata)
datalines.append(splittedlines)


Minimal Example Extract of the input *.csv file. Link to file: https://drive.google.com/file/d/1rXanJdnZP-ey0D8dU7XQ6TrHLZK6HLo2/view?usp=sharing

The main idea here is to make it prettier for Excel automatically via the script.

Sequence Name,20240603_S24031_anonymous
Sample Name,Blank
Area,15906292.428,tR (min),3.696
Area,10164232.248,tR (min),3.783
Area,5541647.716,tR (min),4.091
Area,203969.547,tR (min),6.514
Area,542692.442,tR (min),6.579
Area,1039445.806,tR (min),6.788
Area,4011338.408,tR (min),7.249
Area,504687.529,tR (min),8.177
Area,108363.994,tR (min),8.420
Compound Name,RT,Score
2,4,6-Cycloheptatrien-1-one, 3,5-bis-trimethylsilyl-,8.705,626,Area,386902.453,tR (min),8.705
Area,368393.389,tR (min),8.850
Area,91574.405,tR (min),9.447
Area,899294.259,tR (min),9.651
Area,193389.843,tR (min),10.041
Area,56853.148,tR (min),10.812
Compound Name,RT,Score
Benzene, 4-ethenyl-1,2-dimethyl-,11.682,600,Area,16289.239,tR (min),11.682
Compound Name,RT,Score
2-(p-Tolyl)propan-2-ol, TMS,11.921,605,Area,17976.558,tR (min),11.921
Area,78966.216,tR (min),12.031
Compound Name,RT,Score
Tris(tert-butyldimethylsilyloxy)arsane,12.036,685,Area,76702.743,tR (min),12.036
Area,534.128,tR (min),12.285
Compound Name,RT,Score
Phenyl-pentamethyl-disiloxane,13.646,656,Area,84633.281,tR (min),13.646
Area,11464.379,tR (min),13.696
Compound Name,RT,Score
Cyclopentene, 3,3-dimethyl-4-methylene-1,2-bis(trimethylsil,14.543,645,Area,402308.085,tR (min),14.543

Continue reading...