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.

Improve this question
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, 2018 at 10:56
  • @terdon : tab separated . No, it is not necessary that there is a - always before rsID
    – user3683555
    Jan 25, 2018 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, 2018 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, 2018 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, 2018 at 11:55

3 Answers 3

Reset to default
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
Improve this answer
4
  • Hi Sandeep , Unfortunately the columns that I wanted to extract are not located next to each other .
    – user3683555
    Jan 25, 2018 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, 2018 at 11:10
  • @terdon : edited the question.
    – user3683555
    Jan 25, 2018 at 11:12
  • the same rs number is present in one more column sometimes , which makes it tricky
    – user3683555
    Jan 25, 2018 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.

Improve this answer
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.
    – user3683555
    Jan 25, 2018 at 11:20
  • I will try to use the perl solution and validate it
    – user3683555
    Jan 25, 2018 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, 2018 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
    – user3683555
    Jan 25, 2018 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
    – user3683555
    Jan 25, 2018 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
Improve this answer
1
  • awesome dude. I was looking for this syntax '$2 ~ /^rs[0-9]/
    – user3683555
    Jan 29, 2018 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.