CODEWITHSUNDEEP
pythonregexcsv
TheEmperor
TheEmperor

Reputation: 355

Remove rows from a csv file where some column matches a specific regex

I have the following csv file:

ID,PDBID,FirstResidue,FirstChain,SecondResidue,SecondChain,ThirdResidue,ThirdChain,FourthResidue,FourthChain,Pattern
RZ_AUTO_505,1hmh,A22L,C,A22L,A,G21L,A,A23L,A,AA/GA Naked ribose
RZ_AUTO_506,1hmh,A22L,C,A22L,A,G114,A,A23L,A,AA/GA Naked ribose
RZ_AUTO_507,1hmh,A130,E,A90,A,G80,A,A130,A,AA/GA Naked ribose
RZ_AUTO_508,1hmh,A140,E,A90,E,G120,A,A90,A,AA/GA Naked ribose
RZ_AUTO_509,1hmh,G102,A,C103,A,G102,E,A90,E,GC/GA Single ribose
RZ_AUTO_510,1hmh,G102,A,C103,A,G120,E,A90,E,GC/GA Single ribose
RZ_AUTO_511,1hmh,G113,C,C112,C,G21L,A,A23L,A,GC/GA Single ribose
RZ_AUTO_512,1hmh,G113,C,C112,C,G114,A,A23L,A,GC/GA Single ribose
RZ_AUTO_513,1hnw,C1496,A,G1497,A,A1518,A,A1519,A,CG/AA Canonical ribose
RZ_AUTO_514,1hnw,C1496,A,G1497,A,A1519,A,A1518,A,CG/AA Canonical ribose
RZ_AUTO_515,1hnw,C221,A,U222,A,A195,A,A196,A,CU/AA Canonical ribose
RZ_AUTO_516,1hnw,C221,A,U222,A,A196,A,A195,A,CU/AA Canonical ribose

I need to remove the csv rows if the value of FirstResidue or SecondResidue or ThirdResidue or FourthResidue matches the regex: '[A-Za-z]$'. The output should look something like below.

RZ_AUTO_507,1hmh,A130,E,A90,A,G80,A,A130,A,AA/GA Naked ribose
RZ_AUTO_508,1hmh,A140,E,A90,E,G120,A,A90,A,AA/GA Naked ribose
RZ_AUTO_509,1hmh,G102,A,C103,A,G102,E,A90,E,GC/GA Single ribose
RZ_AUTO_510,1hmh,G102,A,C103,A,G120,E,A90,E,GC/GA Single ribose
RZ_AUTO_513,1hnw,C1496,A,G1497,A,A1518,A,A1519,A,CG/AA Canonical ribose
RZ_AUTO_514,1hnw,C1496,A,G1497,A,A1519,A,A1518,A,CG/AA Canonical ribose
RZ_AUTO_515,1hnw,C221,A,U222,A,A195,A,A196,A,CU/AA Canonical ribose
RZ_AUTO_516,1hnw,C221,A,U222,A,A196,A,A195,A,CU/AA Canonical ribose

So far I've saved each column as a list but I'm not sure how to proceed next. Here is my code:

import csv
import re

rzid = []
pdbid = []
first_residue = []
first_chain = []
second_residue = []
second_chain = []
third_residue = []
third_chain = []
fourth_residue = []
fourth_chain = []
rz_pattern = []

#open csv file rz45.csv
f = open( 'rz45.csv', 'rU' ) #open the file in read universal mode
for line in f:
    cells = line.split( "," )
    rzid.append( (cells[0]) )
    pdbid.append( (cells[1]) )
    first_residue.append( (cells[2]) )
    first_chain.append( (cells[3]) )
    second_residue.append( (cells[4]) )
    second_chain.append( (cells[5]) )
    third_residue.append( (cells[6]) )
    third_chain.append( (cells[7]) )
    fourth_residue.append( (cells[8]) )
    fourth_chain.append( (cells[9]) )  
    rz_pattern.append( (cells[10]) )

f.close()

Can someone please help? Thanks

UPDATE 1

import re
import csv

output = []
regex = '[AUGC]\d{1,4}'

#open csv file test_regex.csv
f = open( 'test_regex.csv', 'rU' ) #open the file in read universal mode
    for line in f:
    cells = line.split( "," )
    output.append( [ cells[ 2 ], cells[ 4 ], cells[ 6 ], cells[ 8 ] ] )
    match = re.search(regex, str(output))
    if match:
        print line 
f.close()

I've made some changes to my code but I'm still not sure how to check that all values in cells [2,4,6,8] fulfill the given regex. Can someone advise on how to proceed next?

Upvotes: 1

Views: 1158

Answers (1)

dawg
dawg

Reputation: 103834

Something like this works (at least on your example):

import csv
import re

tgt=['FirstResidue', 'SecondResidue', 'ThirdResidue']

with open(file) as f:
    reader=csv.reader(f)
    header=next(reader)
    for row in reader:
        di={k:v for k, v in zip(header, row)}
        if any(re.search(r'[A-Za-z]$', s) for s in [di[x] for x in tgt]):
            continue
        print row

Prints:

['RZ_AUTO_507', '1hmh', 'A130', 'E', 'A90', 'A', 'G80', 'A', 'A130', 'A', 'AA/GA Naked ribose']
['RZ_AUTO_508', '1hmh', 'A140', 'E', 'A90', 'E', 'G120', 'A', 'A90', 'A', 'AA/GA Naked ribose']
['RZ_AUTO_509', '1hmh', 'G102', 'A', 'C103', 'A', 'G102', 'E', 'A90', 'E', 'GC/GA Single ribose']
['RZ_AUTO_510', '1hmh', 'G102', 'A', 'C103', 'A', 'G120', 'E', 'A90', 'E', 'GC/GA Single ribose']
['RZ_AUTO_512', '1hmh', 'G113', 'C', 'C112', 'C', 'G114', 'A', 'A23L', 'A', 'GC/GA Single ribose']
['RZ_AUTO_513', '1hnw', 'C1496', 'A', 'G1497', 'A', 'A1518', 'A', 'A1519', 'A', 'CG/AA Canonical ribose']
['RZ_AUTO_514', '1hnw', 'C1496', 'A', 'G1497', 'A', 'A1519', 'A', 'A1518', 'A', 'CG/AA Canonical ribose']
['RZ_AUTO_515', '1hnw', 'C221', 'A', 'U222', 'A', 'A195', 'A', 'A196', 'A', 'CU/AA Canonical ribose']
['RZ_AUTO_516', '1hnw', 'C221', 'A', 'U222', 'A', 'A196', 'A', 'A195', 'A', 'CU/AA Canonical ribose']

Once you have filtered the data based on your regex, you have row that is as you want it. Either write that to a new csv or whatever you wish.

Upvotes: 1

Related Questions