Answers.Body: <p>An awk solution</p>
  
  <pre><code>awk '
      FILENAME == ARGV[1] {f2[$2] = $2 OFS $3 OFS $4 OFS $5 OFS $6 OFS $7; next}
      FILENAME == ARGV[2] {f3[$2] = $2 OFS $3 OFS $4 OFS $5 OFS $6 OFS $7; next}
      {
          for (key2 in f2) {
              for (key3 in f3) {
                  if (($2 &lt;= key2 &amp;&amp; key2 &lt;= $3) &amp;&amp; ($5 &lt;= key3 &amp;&amp; key3 &lt;= $6)) {
                      print $1,$2,$3,$4, f2[key2], $5,$6,$7, f3[key3]
                  }
              }
          }
      }
  ' file2 file3 file1
  </code></pre>
  
  <p>outputs</p>
  
  <pre><code>1 15776220 15776240 GTGACCAGCAGGTGTCTCTG 15776231 2 5008 G:5002 A:6  16855676 16855696 CTGTCCAGCAGAGGGCGGTG 16855677 2 5008 A:5003 C:5
  1 15776220 15776240 GTGACCAGCAGGTGTCTCTG 15776231 2 5008 G:5002 A:6  16855676 16855696 CTGTCCAGCAGAGGGCGGTG 16855689 3 5008 G:4957 A:41 T:10
  1 15776220 15776240 GTGACCAGCAGGTGTCTCTG 15776239 3 5008 C:3358 A:14 G:1636 16855676 16855696 CTGTCCAGCAGAGGGCGGTG 16855677 2 5008 A:5003 C:5
  1 15776220 15776240 GTGACCAGCAGGTGTCTCTG 15776239 3 5008 C:3358 A:14 G:1636 16855676 16855696 CTGTCCAGCAGAGGGCGGTG 16855689 3 5008 G:4957 A:41 T:10
  </code></pre>
  
  <p>For your sample data, all combinations of records in file2 and file3 meet the criteria for the file1 record.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-01-02T16:39:37.110Z
Answers.LastActivityDate: 2018-01-02T16:39:37.110Z
Answers.UserDisplayName: glenn jackman
Body: <p>I have the following sets of data:</p>
  
  <h2>file1:</h2>
  
  <pre><code>1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    16855676    16855696    CTGTCCAGCAGAGGGCGGTG 
  </code></pre>
  
  <h2>file2:</h2>
  
  <pre><code>1   15776231    2   5008    G:5002  A:6 
  1   15776239    3   5008    C:3358  A:14    G:1636
  </code></pre>
  
  <h2>file3:</h2>
  
  <pre><code>1   16855677    2   5008    A:5003  C:5
  1   16855689    3   5008    G:4957  A:41    T:10
  </code></pre>
  
  <p>Most lines have 6 columns but a few lines also have 7 columns which have data that I want to preserve. </p>
  
  <p>Can a script be written which does the following:</p>
  
  <p>if <code>$2</code> of file2 comes in between <code>$2</code> and <code>$3</code> of file1 (between the interval of <code>$2</code> and <code>$3</code>) and <code>$2</code> of file3 comes in between <code>$5</code> and <code>$6</code> of file1 (between the interval of <code>$5</code> and <code>$6</code>), then the output would be <code>$1,$2,$3,$4</code> from file1 and <code>$2,$3,$4,$5,$6,$7</code> from file2 and <code>$5,$6,$7</code> from file1 and <code>$2,$3,$4,$5,$6,$7</code> from file 3, all in one line. So something like this:</p>
  
  <pre><code>1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    15776231    2   5008    G:5002  A:6 16855676    16855696    CTGTCCAGCAGAGGGCGGTG    16855677    2   5008    A:5003  C:5
  
  1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    15776239    3   5008    C:3358  A:14    G:1636  16855676    16855696    CTGTCCAGCAGAGGGCGGTG    1   16855689    3   5008    G:4957  A:41    T:10
  </code></pre>
  
  <p>Something like bedtools does but I am not able to use bedtools one this since I don't have two columns in file2 and file3. The intersection is based on $2  in both the files, and all the other columns after it are just copied </p>
  
  <p>(There could be many lines from file2 and file3 intersecting in the same line in file1,so that line could be repeated based on the number of intersections)</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-01-02T14:42:47.593Z
Comments.Text: Can't you just munge file2 to add an end coordinate and then use `bedtools intersect`? For example `awk '{$3 = $2+1 FS $3}' file2 > file2.modified`, assuming the coordinates are 0-based.
Comments.UserDisplayName: Devon Ryan
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-01-02T14:51:44.577Z
Comments.Text: Can you please clarify how this is related to your [previous question](https://bioinformatics.stackexchange.com/questions/3153/using-column-2-of-one-file-to-match-with-two-columns-of-another-file-and-append)?
Comments.UserDisplayName: heathobrien
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-01-02T16:34:11.533Z
Comments.Text: @heathobrien it's an extension. When the OP asked the previous question, he hadn't realized there were certain issues in the input files. So I suggested he ask a new question showing the files he currently has after fixing these issue. This is now about getting information from three separate files and two formats (number of fields) per file. So quite a different issue.
Comments.UserDisplayName: terdon
Tags: <vcf><bioawk><text-processing>
Title: Intersecting two different files with one "master" file based on different columns
ThreadText: # Intersecting two different files with one "master" file based on different columns
  
  (Asked by: username_0 on 2018-01-02)
  
  I have the following sets of data:
  
  ## file1:
  
  ```
  1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    16855676    16855696    CTGTCCAGCAGAGGGCGGTG 
  
  ```
  
  ## file2:
  
  ```
  1   15776231    2   5008    G:5002  A:6 
  1   15776239    3   5008    C:3358  A:14    G:1636
  
  ```
  
  ## file3:
  
  ```
  1   16855677    2   5008    A:5003  C:5
  1   16855689    3   5008    G:4957  A:41    T:10
  
  ```
  
  Most lines have 6 columns but a few lines also have 7 columns which have data that I want to preserve.
  
  Can a script be written which does the following:
  
  if `$2` of file2 comes in between `$2` and `$3` of file1 (between the interval of `$2` and `$3`) and `$2` of file3 comes in between `$5` and `$6` of file1 (between the interval of `$5` and `$6`), then the output would be `$1,$2,$3,$4` from file1 and `$2,$3,$4,$5,$6,$7` from file2 and `$5,$6,$7` from file1 and `$2,$3,$4,$5,$6,$7` from file 3, all in one line. So something like this:
  
  ```
  1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    15776231    2   5008    G:5002  A:6 16855676    16855696    CTGTCCAGCAGAGGGCGGTG    16855677    2   5008    A:5003  C:5
  
  1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    15776239    3   5008    C:3358  A:14    G:1636  16855676    16855696    CTGTCCAGCAGAGGGCGGTG    1   16855689    3   5008    G:4957  A:41    T:10
  
  ```
  
  Something like bedtools does but I am not able to use bedtools one this since I don't have two columns in file2 and file3. The intersection is based on $2 in both the files, and all the other columns after it are just copied
  
  (There could be many lines from file2 and file3 intersecting in the same line in file1,so that line could be repeated based on the number of intersections)
  
  **Tags:** vcf, bioawk, text-processing
  
  > **Comments:**
  > **username_1**: Can't you just munge file2 to add an end coordinate and then use `bedtools intersect`? For example `awk '{$3 = $2+1 FS $3}' file2 > file2.modified`, assuming the coordinates are 0-based.  
  > **username_2**: Can you please clarify how this is related to your [previous question](https://bioinformatics.stackexchange.com/questions/3153/using-column-2-of-one-file-to-match-with-two-columns-of-another-file-and-append)?  
  > **username_3**: @username_2 it's an extension. When the OP asked the previous question, he hadn't realized there were certain issues in the input files. So I suggested he ask a new question showing the files he currently has after fixing these issue. This is now about getting information from three separate files and two formats (number of fields) per file. So quite a different issue.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_4 on 2018-01-02. Score: 4 | Accepted answer
  
  An awk solution
  
  ```
  awk '
      FILENAME == ARGV[1] {f2[$2] = $2 OFS $3 OFS $4 OFS $5 OFS $6 OFS $7; next}
      FILENAME == ARGV[2] {f3[$2] = $2 OFS $3 OFS $4 OFS $5 OFS $6 OFS $7; next}
      {
          for (key2 in f2) {
              for (key3 in f3) {
                  if (($2 <= key2 && key2 <= $3) && ($5 <= key3 && key3 <= $6)) {
                      print $1,$2,$3,$4, f2[key2], $5,$6,$7, f3[key3]
                  }
              }
          }
      }
  ' file2 file3 file1
  
  ```
  
  outputs
  
  ```
  1 15776220 15776240 GTGACCAGCAGGTGTCTCTG 15776231 2 5008 G:5002 A:6  16855676 16855696 CTGTCCAGCAGAGGGCGGTG 16855677 2 5008 A:5003 C:5
  1 15776220 15776240 GTGACCAGCAGGTGTCTCTG 15776231 2 5008 G:5002 A:6  16855676 16855696 CTGTCCAGCAGAGGGCGGTG 16855689 3 5008 G:4957 A:41 T:10
  1 15776220 15776240 GTGACCAGCAGGTGTCTCTG 15776239 3 5008 C:3358 A:14 G:1636 16855676 16855696 CTGTCCAGCAGAGGGCGGTG 16855677 2 5008 A:5003 C:5
  1 15776220 15776240 GTGACCAGCAGGTGTCTCTG 15776239 3 5008 C:3358 A:14 G:1636 16855676 16855696 CTGTCCAGCAGAGGGCGGTG 16855689 3 5008 G:4957 A:41 T:10
  
  ```
  
  For your sample data, all combinations of records in file2 and file3 meet the criteria for the file1 record.
  
  
  ---
  
  