0

I have a tab- separated file with many rows like this :

1    ILM-rs199    info1    info2    info3
2    aws-rs2778   info4    info5    info6
3    345-678945   info7    info8    info9
4    aws-rs789    info10   info11   info-rs789

I want to extract 2nd and 4th column and in the second column I want only the string starting with rs and the numbers following it like this :

rs199    info2
rs2778   info5
rs789    info11

I was able to extract the second column alone with the following command:

egrep -o 'rs[0-9]*' filename

gives

rs199 
rs2778
rs789

but I got stuck when I had to also keep the other columns

I was thinking of using egrep (for extracting rs nos in 2nd column) inside awk but could'nt finish it.

9
  • Does the second column always have a - before the rsID? And is your file space-delimited or do you have tabs?
    – terdon
    Jan 25 '18 at 10:56
  • @terdon : tab separated . No, it is not necessary that there is a - always before rsID Jan 25 '18 at 11:00
  • Ah, crap. Please include an example with no - then, my original answer depended on that. By the way, you might be interested in our new sister site, Bioinformatics.
    – terdon
    Jan 25 '18 at 11:03
  • Wait, so you can also have cases with no rs in the second column? What should happen there? Please take a few minutes and think about all the possible cases. Making constant changes that invalidate our answers means we will be wasting your time (and ours) giving you solutions that don't work for your data.
    – terdon
    Jan 25 '18 at 11:19
  • 1
    Then why do you only show two lines of output if three of your input lines have rsIDs in the second column? Why is the 4th line skipped? Once again, I will ask you to take the time to think about your needs and express them clearly. We can't guess them.
    – terdon
    Jan 25 '18 at 11:55
2
$ # assuming `rs[digits]` string will match only in 2nd column
$ # string matched within () will get printed
$ perl -lne 'print /(rs\d+\t)[^\t]+\t([^\t]+)/' ip.txt
rs199   info2
rs2778  info5

$ # to match from 2nd column only
$ perl -lne 'print /^[^\t]+\t[^\t]*(rs\d+\t)[^\t]+\t([^\t]+)/' ip.txt
rs199   info2
rs2778  info5

$ # to get some other column, say 2nd and 5th
$ perl -lne 'print /^[^\t]+\t[^\t]*(rs\d+\t)(?:[^\t]+\t){2}([^\t]+)/' ip.txt
rs199   info3
rs2778  info6

to print only if match is found:

$ perl -lne '/^[^\t]+\t[^\t]*(rs\d+\t)(?:[^\t]+\t){1}([^\t]+)/ && print $1,$2' ip.txt
rs199   info2
rs2778  info5
$ perl -lne '/^[^\t]+\t[^\t]*(rs\d+\t)(?:[^\t]+\t){2}([^\t]+)/ && print $1,$2' ip.txt
rs199   info3
rs2778  info6


Previous solution, where string to be extracted where next to each other

$ # assuming the shell being used supports $'' strings
$ grep -o $'rs[0-9]*\t[^\t]*' ip.txt
rs199   info1
rs2778  info4
4
  • Hi Sandeep , Unfortunately the columns that I wanted to extract are not located next to each other . Jan 25 '18 at 11:09
  • @user3683555 what do you mean? And how were we supposed to guess that? Please edit your question and make sure your example file accurately represents your data.
    – terdon
    Jan 25 '18 at 11:10
  • @terdon : edited the question. Jan 25 '18 at 11:12
  • the same rs number is present in one more column sometimes , which makes it tricky Jan 25 '18 at 11:33
1

Here are a few options:

  1. awk

    $ awk -vOFS="\t" '{sub(/.*-/,"",$2);print $2,$4}' file 
    rs199   info1
    rs2778  info3
    

    This will remove everything until the first - from the second field and then print the resulting 2nd field along with the 4th.

  2. Perl

    $ perl -pe 's/.*?-*(rs\d+\t)\S+\t(\S+).*/$1\t$2/' file 
    rs199   info2
    rs2778  info5
    

    As above, this will fail if you can have rs in the 1st field. A more robust approach would be:

    $ perl -F'\t' -lane '$F[1]=~s/.+-//; print join "\t",@F[1,3]' file
    rs199   info2
    rs2778  info5
    

    This removes any characters before the - in the 2nd field (it will do nothing if the 2nd field doesn't have an -) and then prints the 2nd and 4th fields.

5
  • I was trying with awk as well . The approach with - fails if you have another row with 345-678945 because there is no rsID in that string. Jan 25 '18 at 11:20
  • I will try to use the perl solution and validate it Jan 25 '18 at 11:21
  • @user3683555 no, don't "validate" it. Instead, take the time to look at your data carefully and make sure your example is correct. You seem to want to skip lines where the second field doesn't have an rsID (no rs), but you don't mention that. We can't give you correct answers if you don't give us correct examples to work with. So please edit your question and make sure you are giving valid example data.
    – terdon
    Jan 25 '18 at 11:22
  • Yes, I want to skip lines where there is no rsID. Also, please note that the same rsID is present in other columns sometimes, which makes it tricky Jan 25 '18 at 11:40
  • Instead of sub in the awk command , I was trying to see if I can use grep, but could'nt finish it Jan 25 '18 at 11:41
1

I have done by below method

inputfile

ILM-rs199    info1    info2    info3
aws-rs2778   info4    info5    info6
345-678945   info7    info8    info9
aws-rs789    info10   info11   info-rs789

command

awk -F "-" '{print $1,$2,$3,$4,$5}' inputfile | awk '$2 ~ /^rs[0-9]/{print $2,$4}'

output

rs199 info2
rs2778 info5
rs789 info11
1
  • awesome dude. I was looking for this syntax '$2 ~ /^rs[0-9]/ Jan 29 '18 at 12:03

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.