1

I have two files:

file1

ST4.03ch10 56000001 56500000
ST4.03ch11 34500001 35000000
ST4.03ch04 54500001 55000000
ST4.03ch12 500001 1000000

file2

ST4.03ch12 56014301 56019800 0.163 gene 5.5
ST4.03ch12 56022401 56025300 0.419 gene 2.9
ST4.03ch12 671201 803500 1 gene 5.5
ST4.03ch12 671201 803500 1 gene 5.3
ST4.03ch12 671201 803500 1 gene 5.0
ST4.03ch12 447401 449500 0.038 gene 8.5
ST4.03ch12 671201 803500 1 gene 9.5

edited:added part of my real data here since codes created based on the previous sample data did not work well with real data.

And I would like to select and print rows in file2 that match one of the rows in file1 under the following three criteria:

1. col1 of file2 = col1 of file1;  
2. col2 of file2 >/= col2 of file1, and  
3. col3 of file2 </= col3 of file1

What I am trying to do is that: file 1 contains identifiers (ST4.03chXX) in col1 and corresponding range of coordinations in col2 (the starting point) and col3 (the ending point), and I am looking for entries in file 2 that:
1) have identifiers in colA that match the identifier in file 1 and
2) have the starting and ending coordinations (colB and colC) that fall inside the range in file 1 of the respective identifier

expected output:

ST4.03ch12 671201 803500 1 gene 5.5
ST4.03ch12 671201 803500 1 gene 5.3
ST4.03ch12 671201 803500 1 gene 5.0
ST4.03ch12 671201 803500 1 gene 9.5

The actual files (especially file 2) are pretty big so I am hoping to get the job done using awk, but looping if necessary will also be okay.

2
  • sounds like a simple awk 'seen[$1]++' filename or the answer is too complex Commented Aug 16, 2016 at 19:42
  • A little bit of additional explanation added and the file/desired output fixed.
    – XYZ1007
    Commented Aug 16, 2016 at 20:06

1 Answer 1

1

You could try something like:

awk 'NR==FNR{z[$1]?z[$1]=z[$1]"|"$2"|"$3:z[$1]=$2"|"$3;next}
{if ($1 in z){l=split(z[$1], k, "|");
{for (i=1;i<l;i+=2){if ($2>=k[i] && $3<=k[i+1]){print}}}}}' file1 file2

This reads file1, joins 2nd and 3rd fields with a | and saves that into an array z (indexed by 1st field) then reads file2 and checks if 1st field is in z - if so it splits z[1st field] on | into k[i] and for each odd value of i it prints the line if the 2nd field >=k[i] and 3rd field <=k[i+1]

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .