<p>Despite an <code>awk</code> solution is favored by the OP, here is an <code>R</code> solution. (The task, especially dealing with exon coordinates in file2, is rather complex for a straightforward <code>awk</code> solution, at least for me).</p>

<pre><code>res &lt;- data.frame() # empty df for storing results

for (i in 1:nrow(file1)) {

  chr &lt;- file1[i,1] # get chr
  pos &lt;- file1[i,2] # get position of interest
  print("pos")
  print(pos)

  if(chr %in% file2$chrom) {

    file2_subset &lt;- file2[file2$chrom == chr,] # subset file2

    for(j in 1:nrow(file2_subset)) {

      # create exon start - exon end pairs

      exon_start &lt;- file2_subset[j,2]
      exon_start &lt;- as.numeric(unlist(strsplit(exon_start,",")))
      print(exon_start)

      exon_end &lt;- file2_subset[j,3]
      exon_end &lt;- as.numeric(unlist(strsplit(exon_end,",")))
      print(exon_end)

      # if position of interest lie within exons
      # need to be careful if start/end position are counted as exon

      if(any(pos &gt;= exon_start &amp; pos &lt;= exon_end)) {
        print(file1[i,])
        res &lt;- rbind(res, file1[i,]) # write line i from file1 if satisfies above conds
      }
    }
  }
}

&gt; res
  CHROM    POS REF ALT FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2          INFO
6  chr1 790590   C   T    0/1    0/1       0|0       0/1       0/1  0|0  1|1 AN=2184;AC=84
</code></pre>

CC BY-SA 4.0
2019-10-04T09:59:09.707Z
File 1 looks like a vcf file? Maybe you can read it in using VariantAnnotation package from Bioconductor, and do overlap with the second file. To make it easier, one can convert the second file to a GRanges object in R.
StupidWolf
CC BY-SA 4.0
2019-10-04T15:34:07.080Z
OP seems to add `awk` tags to all their posts even though multiple people have mentioned that R would be a better way to merge/lookup files.
Ram RS
CC BY-SA 4.0
2019-10-04T17:26:17.637Z
It's a good answer by @haci nevertheless
M__
CC BY-SA 4.0
2019-10-04T06:44:30.987Z
2019-10-04T12:39:49.010Z
2019-10-04T12:39:49.010Z
haci
<p>Make a sorted BED file called <code>exons.bed</code> and run BEDOPS <code>bedmap</code> to map variants to exons:</p>

<pre><code>$ bedmap --echo --echo-map --delim '\t' --skip-unmapped exons.bed &lt;(vcf2bed &lt; variants.vcf) &gt; answer.bed
</code></pre>

<p>To make a sorted BED file from your exons, I'd suggest using <code>sort-bed</code>. </p>

<p>I would also suggest using <code>awk</code> to make sure that the chromosome naming scheme is consistent between exons and variants, e.g.:</p>

<pre><code><span class="math-container">$ vcf2bed &lt; variants.vcf | awk '{ print 'chr$</span>0' }' &gt; variants.bed
</code></pre>

<p>Then use that result, instead, for mapping:</p>

<pre><code>$ bedmap --echo --echo-map --delim '\t' --skip-unmapped exons.bed variants.bed &gt; answer.bed
</code></pre>

<p>If chromosome names are not consistent, mapping or other set operations with BEDOPS or other toolkits will not work.</p>

CC BY-SA 4.0
2019-10-04T20:27:10.517Z
2019-10-04T20:27:10.517Z
Alex Reynolds
<p>BEDTools already has a function to take a vcf and bedfile as input and look for intersections.  Most people would get the exon coordinates into bed format, and use that.</p>

CC BY-SA 4.0
2019-10-04T16:04:36.343Z
2019-10-04T16:04:36.343Z
swbarnes2
<p>I have two files, <code>file1</code> and <code>file2</code>. I want to match <code>file1</code> chrom with <code>file2</code> chrom, and if there is a match then I want to check <code>file2</code> if the position of the match chrom1 is within the exon region that is in <code>file1</code>. i want to do this with the whole file and print out rows that match and the position is within the exonic regions.</p>

<p>For instance, <code>file1</code> has a chrom 1 and POS 736689 which match chrom 1 in <code>file2</code>, now I take <code>POS 736689</code> from <code>file1</code> and check if it is within the exon region <code>29553,30563,30975,   30039,30667,31097,...</code> in <code>file2</code>.</p>

<p>If it is within the region it will print out the file1 row that matches the pattern.</p>

<p>The exon regions can be read as:</p>

<pre><code>29553 to 30039
30563 to 30667
30975 to 31097
</code></pre>

<p>file1 look like this:</p>

<pre><code>CHROM   POS REF ALT FATHER  MOTHER  DAUGHTER1   DAUGHTER2   DAUGHTER3   SON1    SON2    INFO
1   736689  T   A   0/0 1/0 0|0 0|1 0|1 0|0 0|1 AN=2184;AC=6    
1   758663  CGCC    TTAG    0/0 0/1 0|0 0|1 0|1 0|0 0|1 AC=2;AN=2184    
1   762005  G   C   1/0 0/0 1|0 1|0 1|0 1|0 0|0 AN=2184;AC=13   
1   771950  C   T   0/0 0/1 0|1 0|0 0|0 0|1 0|0 AC=110;AN=2184  
1   772172  C   T   0/1 0/0 0|0 0|0 0|0 0|0 1|0 AN=2184;AC=8    
1   790590  C   T   0/1 0/1 0|0 0/1 0/1 0|0 1|1 AN=2184;AC=84   
</code></pre>

<p>file2 look like this</p>

<pre><code>chrom   Start   End
chr1    29553,30563,30975,  30039,30667,31097,
chr1    53048,54829,    53067,54936,
chr1    69090,  70008,
chr1    160445,161313,  160690,161525,
chr1    324755,327551,  326514,328453,
chr1    367639, 368634,
chr1    459655,461153,461750,   459992,461288,461954,
chr1    523008,529614,  523081,530148,
</code></pre>

CC BY-SA 4.0
2019-10-04T06:48:33.473Z
Please edit your question so that your data is properly formatted (like you did withh file headers) and provide some easily parsable example data. I have posted an answer but not all people would (understandably) spend time cleaning data before suggesting a solution.
haci
<awk>
match two files and check if position of chrom is within exon region
# match two files and check if position of chrom is within exon region

(Asked by: username_0 on 2019-10-04)

I have two files, file1 and file2. I want to match file1 chrom with file2 chrom, and if there is a match then I want to check file2 if the position of the match chrom1 is within the exon region that is in file1. i want to do this with the whole file and print out rows that match and the position is within the exonic regions.
For instance, file1 has a chrom 1 and POS 736689 which match chrom 1 in file2, now I take POS 736689 from file1 and check if it is within the exon region 29553,30563,30975,   30039,30667,31097,... in file2.
If it is within the region it will print out the file1 row that matches the pattern.
The exon regions can be read as:
29553 to 30039
30563 to 30667
30975 to 31097

file1 look like this:
CHROM   POS REF ALT FATHER  MOTHER  DAUGHTER1   DAUGHTER2   DAUGHTER3   SON1    SON2    INFO
1   736689  T   A   0/0 1/0 0|0 0|1 0|1 0|0 0|1 AN=2184;AC=6    
1   758663  CGCC    TTAG    0/0 0/1 0|0 0|1 0|1 0|0 0|1 AC=2;AN=2184    
1   762005  G   C   1/0 0/0 1|0 1|0 1|0 1|0 0|0 AN=2184;AC=13   
1   771950  C   T   0/0 0/1 0|1 0|0 0|0 0|1 0|0 AC=110;AN=2184  
1   772172  C   T   0/1 0/0 0|0 0|0 0|0 0|0 1|0 AN=2184;AC=8    
1   790590  C   T   0/1 0/1 0|0 0/1 0/1 0|0 1|1 AN=2184;AC=84   

file2 look like this
chrom   Start   End
chr1    29553,30563,30975,  30039,30667,31097,
chr1    53048,54829,    53067,54936,
chr1    69090,  70008,
chr1    160445,161313,  160690,161525,
chr1    324755,327551,  326514,328453,
chr1    367639, 368634,
chr1    459655,461153,461750,   459992,461288,461954,
chr1    523008,529614,  523081,530148,

**Tags:** awk
> **Comments:**
> **username_1**: Please edit your question so that your data is properly formatted (like you did withh file headers) and provide some easily parsable example data. I have posted an answer but not all people would (understandably) spend time cleaning data before suggesting a solution.  

---

## 3 answers

### Answer by username_1 on 2019-10-04. Score: 2

Despite an awk solution is favored by the OP, here is an R solution. (The task, especially dealing with exon coordinates in file2, is rather complex for a straightforward awk solution, at least for me).
res <- data.frame() # empty df for storing results

for (i in 1:nrow(file1)) {

  chr <- file1[i,1] # get chr
  pos <- file1[i,2] # get position of interest
  print("pos")
  print(pos)

  if(chr %in% file2$chrom) {

    file2_subset <- file2[file2$chrom == chr,] # subset file2

    for(j in 1:nrow(file2_subset)) {

      # create exon start - exon end pairs

      exon_start <- file2_subset[j,2]
      exon_start <- as.numeric(unlist(strsplit(exon_start,",")))
      print(exon_start)

      exon_end <- file2_subset[j,3]
      exon_end <- as.numeric(unlist(strsplit(exon_end,",")))
      print(exon_end)

      # if position of interest lie within exons
      # need to be careful if start/end position are counted as exon

      if(any(pos >= exon_start & pos <= exon_end)) {
        print(file1[i,])
        res <- rbind(res, file1[i,]) # write line i from file1 if satisfies above conds
      }
    }
  }
}

> res
  CHROM    POS REF ALT FATHER MOTHER DAUGHTER1 DAUGHTER2 DAUGHTER3 SON1 SON2          INFO
6  chr1 790590   C   T    0/1    0/1       0|0       0/1       0/1  0|0  1|1 AN=2184;AC=84

> **Comments:**
> **username_2**: File 1 looks like a vcf file? Maybe you can read it in using VariantAnnotation package from Bioconductor, and do overlap with the second file. To make it easier, one can convert the second file to a GRanges object in R.  
> **username_3**: OP seems to add `awk` tags to all their posts even though multiple people have mentioned that R would be a better way to merge/lookup files.  
> **username_4**: It's a good answer by @haci nevertheless  

---

### Answer by username_5 on 2019-10-04. Score: 1

Make a sorted BED file called exons.bed and run BEDOPS bedmap to map variants to exons:
$ bedmap --echo --echo-map --delim '\t' --skip-unmapped exons.bed <(vcf2bed < variants.vcf) > answer.bed

To make a sorted BED file from your exons, I'd suggest using sort-bed. 
I would also suggest using awk to make sure that the chromosome naming scheme is consistent between exons and variants, e.g.:
$ vcf2bed < variants.vcf | awk '{ print 'chr$0' }' > variants.bed

Then use that result, instead, for mapping:
$ bedmap --echo --echo-map --delim '\t' --skip-unmapped exons.bed variants.bed > answer.bed

If chromosome names are not consistent, mapping or other set operations with BEDOPS or other toolkits will not work.


---

### Answer by username_6 on 2019-10-04. Score: 1

BEDTools already has a function to take a vcf and bedfile as input and look for intersections. Most people would get the exon coordinates into bed format, and use that.


---

