1

I have the following csv file:

ID,PDBID,FirstResidue,SecondResidue,ThirdResidue,FourthResidue,Pattern
RZ_AUTO_1,4tov,1404,1405,1518,1519,CG/AA Canonical ribose-zipper
RZ_AUTO_2,4tov,1405,1406,1517,1518,GU/AA Naked ribose-zipper
RZ_AUTO_3,4tov,1043,1044,1047,1048,CC/GA Naked ribose-zipper
RZ_AUTO_4,4tov,1556,1557,1514,1515,CC/GA Naked ribose-zipper
RZ_AUTO_5,4tow,130,131,99,100,AU/CA Canonical ribose-zipper
RZ_AUTO_6,4tow,766,767,1524,1525,AA/CG Canonical ribose-zipper
RZ_AUTO_7,4tow,131,132,98,99,UC/AC Canonical ribose-zipper

I need to go through each row and print rows where the value of FirstResidue and SecondResidue can be extended (meaning the SecondResidue becomes the FirstResidue in a different row having the same PDBID). For example, (line RZ_AUTO_1 & line line RZ_AUTO_2) AND (line RZ_AUTO_5 & line line RZ_AUTO_7). The output should look something like this:

RZ_AUTO_1,4tov,1404,1405,1518,1519,CG/AA Canonical ribose-zipper
RZ_AUTO_2,4tov,1405,1406,1517,1518,GU/AA Naked ribose-zipper
RZ_AUTO_5,4tow,130,131,99,100,AU/CA Canonical ribose-zipper
RZ_AUTO_7,4tow,131,132,98,99,UC/AC Canonical ribose-zipper

Is it possible to do this using awk or other unix methods? I'm using OSX.

4

You could try this:

$ sort -rt"," -k2,3 file.csv | 
   awk -F, '{a[$2][$3]=$0; if(a[$2][$4]){print a[$2][$4]; print;}}'
RZ_AUTO_7,4tow,131,132,98,99,UC/AC Canonical ribose-zipper
RZ_AUTO_5,4tow,130,131,99,100,AU/CA Canonical ribose-zipper
RZ_AUTO_2,4tov,1405,1406,1517,1518,GU/AA Naked ribose-zipper
RZ_AUTO_1,4tov,1404,1405,1518,1519,CG/AA Canonical ribose-zipper

Explanation

  • The sort will sort the file based on the 2nd and 3rd fields so that lines with the same PDBID are sorted together and according to the position of the feature's 1st residue. -r reverses the sort (so that larger numbers are printed first), -t, sets the field delimiter to , and -k defines the fields to sort on.

As for the awk script:

  • -F, : set the field delimiter to ,.
  • a[$2][$3]=$0; : this is a list of lists, a two-dimensional array. For the first line, for example, it would be a[4tov][1404]=$0. a is just the name of the array. The value is set to the current line, $0.
  • if(a[$2][$4]) : if the 4th field (the second residue) has been seen associated with the same PDBID as a 1st residue.
  • print a[$2][$4]; print; : print the line it was seen on (since that was saved as the value of the a array) and the current line.

I don't have access to OSX to check but based on your comment, it looks like the OSX awk, unlike GNU awk, cannot deal with multidimensional arrays. So, here's the same thing in Perl:

sort -rt"," -k2,3 file.csv | 
 perl -F"," -ane '$k{$F[1]}{$F[2]}=$_; 
                  print "$k{$F[1]}{$F[3]}$_" if $k{$F[1]}{$F[3]}'
  • Doesn't seem to work @terdon. I'm getting the following error: awk: syntax error at source line 1 context is >>> {a[$2][ <<< awk: illegal statement at source line 1 – Sri Feb 11 '15 at 9:50
  • @Sri what operating system are you using? – terdon Feb 11 '15 at 10:00
  • @tendon I'm using Mac OSX V10.9.5 – Sri Feb 11 '15 at 12:31
  • @Sri see updated answer. The awk on OSX is different from that on Linux. Next time, please remember to include your OS in your question. – terdon Feb 11 '15 at 21:10
  • @tendon I will keep that in mind. The Perl solution worked for my case. Thank you very much for the help! – Sri Feb 12 '15 at 2:40
0

You can use textql to query the spreadsheet data like a SQL Database.

  • Could you expand on this a little? What SQL command would give the OP the answer they need? How would textql be installed? As it stands, this is more of a comment than an answer. – terdon Feb 11 '15 at 0:59
0

Through python3 using re module.

#!/usr/bin/python3
import re
import sys
j = sys.argv[1]
with open(j) as f:
    fil = f.read()
m = re.findall(r'(?s)(?:^|\n)([^,]*,([^,]*),[^,]*,([^,]*)[^\n]*).*?\n([^,]*,\2,\3,[^\n]*)', fil)
for i in m:
    print(i[0]+'\n'+i[-1])

Save the above code in a file named script.py and execute this file by running the below command on terminal.

python3 script.py inputfile

Regex Demo

$ python3 script.py file.csv
RZ_AUTO_1,4tov,1404,1405,1518,1519,CG/AA Canonical ribose-zipper
RZ_AUTO_2,4tov,1405,1406,1517,1518,GU/AA Naked ribose-zipper
RZ_AUTO_5,4tow,130,131,99,100,AU/CA Canonical ribose-zipper
RZ_AUTO_7,4tow,131,132,98,99,UC/AC Canonical ribose-zipper

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.