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.

  • sounds like a simple awk 'seen[$1]++' filename or the answer is too complex – Valentin Bajrami Aug 16 '16 at 19:42
  • A little bit of additional explanation added and the file/desired output fixed. – XYZ1007 Aug 16 '16 at 20:06
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]

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.