This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Extract rows if 2 column values has 21 in between

Hi,

I have a space separated file like this:

start   end
23  36
15  34
7   15
6   15
6   25
21  29
34  41
23  39
22  28
21  29

and I want only those lines if two columns have a value of 21 in between. The desired output would be:

start end
15    34
6      15
6      25
21    29

I searched, there are instances like if column value greater or less than, but not a scenrio like this,

Any help appreciated.

Waqas.

bed sequence
• 2,556 views • link
updated by Ram Ram • written by waqasnayab waqasnayab

What do you mean by "21 in between"? Since you list the interval "6 15" in your desired output I don't understand it.

• 0 views • link
written by stianlagstad stianlagstad

Changed the formatting, guess op wants to return the intervals that contain a given position. however, [6,15] doesn't contain 21 and therefore the example is wrong. Otherwise this looks like simple case of interval arithmetics. I would like to ask for the biological application of the case, it determines which method is best.

• 0 views • link
written by Michael Dondrup Michael Dondrup

2 answers

If your (tab-delimited) text file does not have a header row:

$ awk '($1 < 21) && ($2 > 21)' data.txt > answer.txt

If your text file has a header row ("start" and "end"):

$ tail -n +2 data.txt | awk '($1 < 21) && ($2 > 21)' > answer.txt

Since you tagged your question with the BED tag, if you're working with a BED file, there is a faster way to do this.

The BEDOPS bedextract tool can do a binary or O(log n) search over a sorted BED file, for instance, whereas a simplistic use of awk (such as the ones I wrote above) will read through the entire file, which is a linear or O(n) search. For large BED files, if sorted, a linear scan is a waste of time.

For example, a search for position 21 along a hypothetical chromosome chrN is much faster this way:

$ echo -e "chrN\t21\t22" | bedextract query.bed - > answer.bed

The file answer.bed contains elements from query.bed that overlap ("contain") position 21 — the half-open genomic region [21, 22) — along chromosome chrN.

• 0 views • link
written by Alex Reynolds Alex Reynolds

You're looking for the AND function, and a basic primer on programming concepts.

In R, the code would look like

   y <- x[ x[,1]<21 & x[,2]>21 ,]
• 0 views • link
written by karl.stamm karl.stamm

Log in to answer this question.

More posts like this