Answers.Body: <p>Pipe a modified form of the second file to BEDOPS <code>bedmap</code> and the first file, then pipe <em>that</em> result to cut out desired columns:</p>
  
  <pre><code>$ awk -v OFS='\t' '{ print $1, $2, ($2+1), $3, $4, $5, $6, $7, $8, $9, $10; }' second.txt | bedmap --echo --echo-map first.bed - | cut -f1-4,8,9,11- - &gt; answer.bed
  </code></pre>
  
  <p>This should run pretty quickly and use very little memory, as it uses Unix streams on (presumably) sorted BED data.</p>
  
  <p>You'll want to test the column indices in my answer by running this on your test inputs, but that's an easy adjustment for the larger dataset, in case I missed a column.</p>
  
  <p>If you're not sure about sort order, pipe to <code>sort-bed</code>:</p>
  
  <pre><code>$ sort-bed first.unsorted.bed &gt; first.bed
  $ awk -v OFS='\t' '{ print $1, $2, ($2+1), $3, $4, $5, $6, $7, $8, $9, $10; }' second.txt | sort-bed - | bedmap --echo --echo-map first.bed - | cut -f1-4,8,9,11- - &gt; answer.bed
  </code></pre>
  
  <p>Use streams where you can. It's a different way of thinking but can pay huge dividends.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-24T11:05:10.517Z
Answers.Text: Nice! What is the `($2+1)` doing in your last awk command? I mean, I know what it does, it prints the value of the second field plus one, but why?
Answers.UserDisplayName: terdon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-24T14:06:51.833Z
Answers.Text: This creates a half-open interval that can be used for set operations. I'm assuming that both files are on the same coordinate system. If not, that can be adjusted with awk.
Answers.UserDisplayName: Alex Reynolds
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-27T13:37:14.720Z
Answers.Text: Hey thanks for the script, but it doesn't give me any intersection. It only repeats `$2` `$3` and `$5` `$6`  with the sequence in some lines, and I get no values from my second file in my answer
Answers.UserDisplayName: rishi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-24T03:32:28.753Z
Answers.LastActivityDate: 2017-12-24T03:43:11.073Z
Answers.LastEditDate: 2017-12-24T03:43:11.073Z
Answers.UserDisplayName: Alex Reynolds
Answers.Body: <p>Here's one approach in Perl. Note that I have not tested this extensively, and I am not confident it works. Please test first and let me know if there are problems. </p>
  
   <pre class="lang-perl prettyprint-override"><code>#!/usr/bin/env perl
  use strict;
  use warnings;
  use 5.10.0;
  my (@file1,%file2);
  open(my $f1, '-|',"sort -nk2 $ARGV[0]");
  while (&lt;$f1&gt;) {
    chomp;
    my @fields = split(/\s+/);
    push @file1, [ @fields ];
  }
  close($f1);
  
  open(my $f2, "sort -nk2 $ARGV[1] |");
  line:while (&lt;$f2&gt;) {
    chomp;
    my @fields = split(/\s+/);
    foreach my $file1Line (@file1) {
      my $file1Start1 = $file1Line-&gt;[1];
      my $file1End1 = $file1Line-&gt;[2];
      my $file1Start2 = $file1Line-&gt;[4];
      my $file1End2 = $file1Line-&gt;[5];
      my $file2Start1 = $fields[1];
      my $file2End1 = $fields[2];
      my $file2Start2 = $fields[7];
      my $file2End2 = $fields[8];
      my @file1Fields = @$file1Line;
      if ($file1Start1 &lt; $file2Start1 &amp;&amp;
          $file1End1 &gt; $file2End1 &amp;&amp;
          $file1Start2 &lt; $file2Start2 &amp;&amp;
          $file1End2 &gt; $file2End2) {
        say join "\t", @{$file1Line}[0..3], @fields[1..5], @{$file1Line}[4..6], @fields[7..11];
      }
      elsif ($file1Start1 &gt; $file2Start1) {
        last;
      }
    }
  }
  close($f2);    
  </code></pre>
  
  <p>Save the script as <code>foo.pl</code> somewhere in your <code>$PATH</code>, make it executable and run as:</p>
  
  <pre><code>$ foo.pl file1.bed file2.bed 
  1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    15776231    2   5008    G:5002  A:6 16855676    16855696    CTGTCCAGCAGAGGGCGGTG    16855677    2   5008    A:5003  C:5
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-27T13:24:58.423Z
Answers.Text: Hey thank you for the script. I think it almost works but there are problems. Shell gives a few Argument errors ("Argument "end" isn't numeric in numeric gt") for a lot of lines and "Use of uninitialized value $file2Start2 in numeric lt" for a lot of lines as well.
  
  However it does output a file, but with incorrect intersections and the number of lines in the file is 30 times more than the first file, which shouldn't be.
  
  The files are only 90kb and 30kb in size. Would you mind taking a closer look if I e-mailed them to you or something?
Answers.UserDisplayName: rishi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-27T13:32:06.027Z
Answers.Text: @MaharshiChakravortee this isn't a shell script. It's a perl script. Run it as I say in my answer, but making it executable and placing it in your PATH, or run it like this: `perl foo.pl file1.bed file2.bed `. If this still fails, ping me (`@terdon`) in [chat](https://chat.stackexchange.com/rooms/58858/bioinformatics).
Answers.UserDisplayName: terdon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-23T12:31:51.630Z
Answers.LastActivityDate: 2017-12-23T12:52:03.510Z
Answers.LastEditDate: 2017-12-23T12:52:03.510Z
Answers.UserDisplayName: terdon
Body: <p>I have file 1 like following:</p>
  
  <hr>
  
  <pre><code>1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    16855676    16855696    CTGTCCAGCAGAGGGCGGTG    
  </code></pre>
  
  <p>And file 2 as following</p>
  
  <hr>
  
  <pre><code>1   15776231    2   5008    G:5002  A:6 1   16855677    2   5008    A:5003  C:5
  </code></pre>
  
  <p>I am trying to write a code(but failing!) to do 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 if <code>$8</code> of file2 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</code> from file2 and <code>$5,$6,$7</code> from file1 and <code>$8,$9,$10,$11,$12</code> from file 2, 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
  </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. Is there any way to make bedtools work on this?</p>
  
  <p>(There could be many lines from file2 intersecting between the same line in file1,so that line could be repeated)</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-12-23T11:54:00.233Z
Comments.Text: I see you took my advice, welcome to [bioinformatics.se]! How big are the files? Are they small enough to fit in RAM? Can we load one of the files in memory and then process the other?
Comments.UserDisplayName: terdon
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-12-23T13:24:24.110Z
Comments.Text: Yes they can be worked on in the RAM itself, big but not huge.
Comments.UserDisplayName: rishi
Tags: <vcf><bedtools><bioawk><text-processing>
Title: Using column 2 of one file to match with two columns of another file, and append
ThreadText: # Using column 2 of one file to match with two columns of another file, and append
  
  (Asked by: username_0 on 2017-12-23)
  
  I have file 1 like following:
  
  ---
  
  ```
  1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    16855676    16855696    CTGTCCAGCAGAGGGCGGTG    
  
  ```
  
  And file 2 as following
  
  ---
  
  ```
  1   15776231    2   5008    G:5002  A:6 1   16855677    2   5008    A:5003  C:5
  
  ```
  
  I am trying to write a code(but failing!) to do the following:
  
  if `$2` of file2 comes in between `$2` and `$3` of file1 (between the interval of `$2` and `$3`) and if `$8` of file2 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` from file2 and `$5,$6,$7` from file1 and `$8,$9,$10,$11,$12` from file 2, 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
  
  ```
  
  Something like bedtools does but I am not able to use bedtools one this since I don't have two columns in file2. Is there any way to make bedtools work on this?
  
  (There could be many lines from file2 intersecting between the same line in file1,so that line could be repeated)
  
  **Tags:** vcf, bedtools, bioawk, text-processing
  
  > **Comments:**
  > **username_1**: I see you took my advice, welcome to [bioinformatics.se]! How big are the files? Are they small enough to fit in RAM? Can we load one of the files in memory and then process the other?  
  > **username_0**: Yes they can be worked on in the RAM itself, big but not huge.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2017-12-24. Score: 3
  
  Pipe a modified form of the second file to BEDOPS `bedmap` and the first file, then pipe *that* result to cut out desired columns:
  
  ```
  $ awk -v OFS='\t' '{ print $1, $2, ($2+1), $3, $4, $5, $6, $7, $8, $9, $10; }' second.txt | bedmap --echo --echo-map first.bed - | cut -f1-4,8,9,11- - > answer.bed
  
  ```
  
  This should run pretty quickly and use very little memory, as it uses Unix streams on (presumably) sorted BED data.
  
  You'll want to test the column indices in my answer by running this on your test inputs, but that's an easy adjustment for the larger dataset, in case I missed a column.
  
  If you're not sure about sort order, pipe to `sort-bed`:
  
  ```
  $ sort-bed first.unsorted.bed > first.bed
  $ awk -v OFS='\t' '{ print $1, $2, ($2+1), $3, $4, $5, $6, $7, $8, $9, $10; }' second.txt | sort-bed - | bedmap --echo --echo-map first.bed - | cut -f1-4,8,9,11- - > answer.bed
  
  ```
  
  Use streams where you can. It's a different way of thinking but can pay huge dividends.
  
  > **Comments:**
  > **username_1**: Nice! What is the `($2+1)` doing in your last awk command? I mean, I know what it does, it prints the value of the second field plus one, but why?  
  > **username_2**: This creates a half-open interval that can be used for set operations. I'm assuming that both files are on the same coordinate system. If not, that can be adjusted with awk.  
  > **username_0**: Hey thanks for the script, but it doesn't give me any intersection. It only repeats `$2` `$3` and `$5` `$6`  with the sequence in some lines, and I get no values from my second file in my answer  
  
  ---
  
  ### Answer by username_1 on 2017-12-23. Score: 1
  
  Here's one approach in Perl. Note that I have not tested this extensively, and I am not confident it works. Please test first and let me know if there are problems.
  
  ```perl
  #!/usr/bin/env perl
  use strict;
  use warnings;
  use 5.10.0;
  my (@file1,%file2);
  open(my $f1, '-|',"sort -nk2 $ARGV[0]");
  while (<$f1>) {
    chomp;
    my @fields = split(/\s+/);
    push @file1, [ @fields ];
  }
  close($f1);
  
  open(my $f2, "sort -nk2 $ARGV[1] |");
  line:while (<$f2>) {
    chomp;
    my @fields = split(/\s+/);
    foreach my $file1Line (@file1) {
      my $file1Start1 = $file1Line->[1];
      my $file1End1 = $file1Line->[2];
      my $file1Start2 = $file1Line->[4];
      my $file1End2 = $file1Line->[5];
      my $file2Start1 = $fields[1];
      my $file2End1 = $fields[2];
      my $file2Start2 = $fields[7];
      my $file2End2 = $fields[8];
      my @file1Fields = @$file1Line;
      if ($file1Start1 < $file2Start1 &&
          $file1End1 > $file2End1 &&
          $file1Start2 < $file2Start2 &&
          $file1End2 > $file2End2) {
        say join "\t", @{$file1Line}[0..3], @fields[1..5], @{$file1Line}[4..6], @fields[7..11];
      }
      elsif ($file1Start1 > $file2Start1) {
        last;
      }
    }
  }
  close($f2);    
  
  ```
  
  Save the script as `foo.pl` somewhere in your `$PATH`, make it executable and run as:
  
  ```
  $ foo.pl file1.bed file2.bed 
  1   15776220    15776240    GTGACCAGCAGGTGTCTCTG    15776231    2   5008    G:5002  A:6 16855676    16855696    CTGTCCAGCAGAGGGCGGTG    16855677    2   5008    A:5003  C:5
  
  ```
  
  > **Comments:**
  > **username_0**: Hey thank you for the script. I think it almost works but there are problems. Shell gives a few Argument errors ("Argument "end" isn't numeric in numeric gt") for a lot of lines and "Use of uninitialized value $file2Start2 in numeric lt" for a lot of lines as well.  However it does output a file, but with incorrect intersections and the number of lines in the file is 30 times more than the first file, which shouldn't be.  The files are only 90kb and 30kb in size. Would you mind taking a closer look if I e-mailed them to you or something?  
  > **username_1**: @MaharshiChakravortee this isn't a shell script. It's a perl script. Run it as I say in my answer, but making it executable and placing it in your PATH, or run it like this: `perl foo.pl file1.bed file2.bed `. If this still fails, ping me (`@username_1`) in [chat](https://chat.stackexchange.com/rooms/58858/bioinformatics).  
  
  ---
  
  