<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>

CC BY-SA 3.0
2018-01-02T16:39:37.110Z
2018-01-02T16:39:37.110Z
glenn jackman
<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>

CC BY-SA 3.0
2018-01-02T14:42:47.593Z
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.
Devon Ryan
CC BY-SA 3.0
2018-01-02T14:51:44.577Z
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)?
heathobrien
CC BY-SA 3.0
2018-01-02T16:34:11.533Z
@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.
terdon
<vcf><bioawk><text-processing>
Intersecting two different files with one "master" file based on different columns
# 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**: @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.  

---

## 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.


---

