<p>Maybe <a href="https://www.gnu.org/software/coreutils/manual/html_node/comm-invocation.html" rel="nofollow noreferrer">comm</a>, which is part of GNU coreutils, is more efficient.</p>
<blockquote>
<p>comm writes to standard output lines that are common, and lines that are unique, to two input files; a file name of ‘-’ means standard input. Synopsis:</p>
<p>comm [option]… file1 file2</p>
</blockquote>
<p>In your case you could sort both files based on the first 3 columns:</p>
<pre><code>file1=file1.vcf
file2=file2.vcf

comm -12 &lt;(awk '{print <span class="math-container">$1,$</span>2,<span class="math-container">$3}' $</span>file1 | sort) \
         &lt;(awk '{print <span class="math-container">$1,$</span>2,<span class="math-container">$3}' $</span>file2 | sort) &gt; matches


# result:
cat matches
1 10366 rs58108140
1 51954 rs185832753
#CHROM POS ID
</code></pre>
<p>This could be of course polished by removing the header.
Also if you want the original lines as output, you can grep the matches to the original files:</p>
<pre><code>grep -f matches file1
#CHROM POS ID REF ALT QUAL 
1 10366 rs58108140 G A
1 51954 rs185832753 G C
</code></pre>
<p>Edit: In the first version, intermediate temp files were used, but as suggested by terdon in the comments, this can be avoided using <a href="http://tldp.org/LDP/abs/html/process-sub.html" rel="nofollow noreferrer">Process Substitution</a> allowing the <code>&lt;(..)</code> contstruct.</p>

CC BY-SA 4.0
2019-09-26T10:37:48.493Z
You can also avoid creating temp files with `comm -12 <(awk '{print $1,$2,$3}' file1 | sort) <(awk '{print $1,$2,$3}' file2 | sort)`.
terdon
CC BY-SA 4.0
2019-09-26T10:43:19.030Z
That's true, Process Substitution avoids temp files very nicely, but since OP was talking about large files this might not fit in the memory, so writing to disk might be more appropriate for that particular scenario.
Sebastian Müller
CC BY-SA 4.0
2019-09-26T11:12:30.533Z
I doubt that would be an issue. `sort` will create temp files when sorting anyway, this won't be a memory intensive procedure.
terdon
CC BY-SA 4.0
2019-09-26T12:28:44.203Z
If that's the case, I'd add it to the answer. Do you know what the official procedure is for that? Is it ok to include this as on top as the quickest solution, or should we just leave it as your comment here?
Sebastian Müller
CC BY-SA 4.0
2019-09-26T12:30:19.463Z
Oh, it's absolutely OK to include anything given in the comments! More than OK, it's actively encouraged. The only reason I didn't edit it in myself was that I wanted to let you choose whether or not to include it.
terdon
CC BY-SA 4.0
2019-09-26T12:46:13.793Z
Good to know! Hope this is ok this way now! Thanks
Sebastian Müller
CC BY-SA 4.0
2019-09-26T08:30:35.060Z
2019-09-26T12:45:22.430Z
2020-06-18T08:30:41.743Z
Sebastian Müller
<p>Use bedtools intersect. You can select the various combinations of outputs:</p>

<p><a href="https://bedtools.readthedocs.io/en/latest/content/tools/intersect.html" rel="nofollow noreferrer">https://bedtools.readthedocs.io/en/latest/content/tools/intersect.html</a></p>

CC BY-SA 4.0
2019-10-06T21:53:08.607Z
2019-10-06T21:53:08.607Z
darked89
<p>Sort both files by chromosome and position and read each file one line by line. </p>

<p>Either you iterate to the next line in one file, or go the next in the other, or there's a match that you print out and then iterate both lines. </p>

<p>This is not necessarily an awk approach, but is doable with Perl, Python, or any other scripting language.</p>

<p>In pseudocode:</p>

<pre><code>Sub getCommonLines()
  Sort FileA
  Sort FileB
  Get lineA from FileA
  Get lineB from FileB
  While not EOF in FileA or EOF in FileB:
    If lineA &gt; lineB:
      Get lineB from FileB
    Elseif lineB &gt; lineA:
      Get lineA from FileA
    Else:
      Print lineA or lineB
      Get lineA from FileA
      Get lineB from FileB
End Sub
</code></pre>

<p>The use of the <code>&gt;</code> operator is based on lexicographical and numerical ordering provided from sorting files A and B, and comparison should be done only on the fields of interest. If you compare other fields (esp. from VCF files) you may get other fields that give you the wrong or unexpected comparison result.</p>

<p>The term <code>EOF</code> means end-of-file, which means just what it says — you're at the end of the file and there are no more lines to compare between files, so your subroutine is done.</p>

<p>Sorting has a time cost, but memory overhead for modern sorting algorithms is very minimal. You can sort via process substitution, but sorting to an intermediate file can be useful if you have other things you want to do with them, where sorted input is valuable.</p>

<p>This would be a good approach for very large files that you do not edit or update often (or at all), for which a hash table (or "dictionary", or "associative array", all the same terms for the same thing) is impractical because of its large memory overhead.</p>

<p>You can also use a tool like <code>comm</code> with sorted files, as described in another answer/comment. This does basically what my answer describes without you having to understand or implement things. You must remember to provide sorted input, however, as with other Unix tools that do line comparison (<code>uniq</code>, etc.). </p>

<p>Despite this answer being a really long "comment", it is useful to have a basic understanding of how things work under the hood, so that when you use these tools, you know what's going on and why you need to provide inputs in a certain way. It's easy to get a million one-liners off of Stack Exchange sites, but understanding is valuable beyond gold.</p>

CC BY-SA 4.0
2019-09-26T08:37:40.333Z
Can you expand this to show how it could be done? As it stands, this is really more of a comment than an answer.
terdon
CC BY-SA 4.0
2019-09-26T03:15:16.010Z
2019-09-27T23:51:57.433Z
2019-09-27T23:51:57.433Z
Alex Reynolds
<p>I have two very large vcf files 2GB and 6GB I want to look for unique combinations of CHROM and POS and output the row that matches. However, because the files a so large my machine always hang and stop processing. Is there are a way to work around the problem of this large file? </p>

<p>I am using this command, taken from an answer to <a href="https://unix.stackexchange.com/q/543633/22222">another question</a>:</p>

<pre><code>awk  '{ if(NR==FNR){a[<span class="math-container">$1$</span>2]=<span class="math-container">$0}else{if($1$</span>2 in a){print}}}' file1.vcf file2.vcf
</code></pre>

<p>file 1</p>

<pre><code>#CHROM  POS ID  REF ALT QUAL
1   10366   rs58108140  G   A   
1   10611   rs189107123 C   G   
1   51954   rs185832753 G   C   
1   13327   rs144762171 G   C
</code></pre>

<p>file 2</p>

<pre><code>#CHROM  POS ID  REF ALT QUAL    
1   10366   rs58108140  G   A   
1   51935   rs181754315 C   T   
1   51954   rs185832753 G   C   
1   52058   rs62637813  G   C   
1   52144   rs190291950 T   A
</code></pre>

<p>output</p>

<pre><code>1 10366 rs58108140  G   A
1 51954  rs185832753 G   C 
</code></pre>

CC BY-SA 4.0
2019-09-26T08:36:16.053Z
Do you have actual vcf files? The lines you show are not valid vcf, is that really what you have or do you have proper vcf files?
terdon
<vcf><awk>
awk working with large files
# awk working with large files

(Asked by: username_0 on 2019-09-26)

I have two very large vcf files 2GB and 6GB I want to look for unique combinations of CHROM and POS and output the row that matches. However, because the files a so large my machine always hang and stop processing. Is there are a way to work around the problem of this large file? 
I am using this command, taken from an answer to another question:
awk  '{ if(NR==FNR){a[$1$2]=$0}else{if($1$2 in a){print}}}' file1.vcf file2.vcf

file 1
#CHROM  POS ID  REF ALT QUAL
1   10366   rs58108140  G   A   
1   10611   rs189107123 C   G   
1   51954   rs185832753 G   C   
1   13327   rs144762171 G   C

file 2
#CHROM  POS ID  REF ALT QUAL    
1   10366   rs58108140  G   A   
1   51935   rs181754315 C   T   
1   51954   rs185832753 G   C   
1   52058   rs62637813  G   C   
1   52144   rs190291950 T   A

output
1 10366 rs58108140  G   A
1 51954  rs185832753 G   C

**Tags:** vcf, awk
> **Comments:**
> **username_1**: Do you have actual vcf files? The lines you show are not valid vcf, is that really what you have or do you have proper vcf files?  

---

## 3 answers

### Answer by username_2 on 2019-09-26. Score: 3 | Accepted answer

Maybe comm, which is part of GNU coreutils, is more efficient.

comm writes to standard output lines that are common, and lines that are unique, to two input files; a file name of ‘-’ means standard input. Synopsis:
comm [option]… file1 file2

In your case you could sort both files based on the first 3 columns:
file1=file1.vcf
file2=file2.vcf

comm -12 <(awk '{print $1,$2,$3}' $file1 | sort) \
         <(awk '{print $1,$2,$3}' $file2 | sort) > matches


# result:
cat matches
1 10366 rs58108140
1 51954 rs185832753
#CHROM POS ID

This could be of course polished by removing the header.
Also if you want the original lines as output, you can grep the matches to the original files:
grep -f matches file1
#CHROM POS ID REF ALT QUAL 
1 10366 rs58108140 G A
1 51954 rs185832753 G C

Edit: In the first version, intermediate temp files were used, but as suggested by terdon in the comments, this can be avoided using Process Substitution allowing the <(..) contstruct.

> **Comments:**
> **username_1**: You can also avoid creating temp files with `comm -12 <(awk '{print $1,$2,$3}' file1 | sort) <(awk '{print $1,$2,$3}' file2 | sort)`.  
> **username_2**: That's true, Process Substitution avoids temp files very nicely, but since OP was talking about large files this might not fit in the memory, so writing to disk might be more appropriate for that particular scenario.  
> **username_1**: I doubt that would be an issue. `sort` will create temp files when sorting anyway, this won't be a memory intensive procedure.  
> **username_2**: If that's the case, I'd add it to the answer. Do you know what the official procedure is for that? Is it ok to include this as on top as the quickest solution, or should we just leave it as your comment here?  
> **username_1**: Oh, it's absolutely OK to include anything given in the comments! More than OK, it's actively encouraged. The only reason I didn't edit it in myself was that I wanted to let you choose whether or not to include it.  
> **username_2**: Good to know! Hope this is ok this way now! Thanks  

---

### Answer by username_3 on 2019-10-06. Score: 0

Use bedtools intersect. You can select the various combinations of outputs:

<https://bedtools.readthedocs.io/en/latest/content/tools/intersect.html>


---

### Answer by username_4 on 2019-09-26. Score: 0

Sort both files by chromosome and position and read each file one line by line. 
Either you iterate to the next line in one file, or go the next in the other, or there's a match that you print out and then iterate both lines. 
This is not necessarily an awk approach, but is doable with Perl, Python, or any other scripting language.
In pseudocode:
Sub getCommonLines()
  Sort FileA
  Sort FileB
  Get lineA from FileA
  Get lineB from FileB
  While not EOF in FileA or EOF in FileB:
    If lineA > lineB:
      Get lineB from FileB
    Elseif lineB > lineA:
      Get lineA from FileA
    Else:
      Print lineA or lineB
      Get lineA from FileA
      Get lineB from FileB
End Sub

The use of the > operator is based on lexicographical and numerical ordering provided from sorting files A and B, and comparison should be done only on the fields of interest. If you compare other fields (esp. from VCF files) you may get other fields that give you the wrong or unexpected comparison result.
The term EOF means end-of-file, which means just what it says — you're at the end of the file and there are no more lines to compare between files, so your subroutine is done.
Sorting has a time cost, but memory overhead for modern sorting algorithms is very minimal. You can sort via process substitution, but sorting to an intermediate file can be useful if you have other things you want to do with them, where sorted input is valuable.
This would be a good approach for very large files that you do not edit or update often (or at all), for which a hash table (or "dictionary", or "associative array", all the same terms for the same thing) is impractical because of its large memory overhead.
You can also use a tool like comm with sorted files, as described in another answer/comment. This does basically what my answer describes without you having to understand or implement things. You must remember to provide sorted input, however, as with other Unix tools that do line comparison (uniq, etc.). 
Despite this answer being a really long "comment", it is useful to have a basic understanding of how things work under the hood, so that when you use these tools, you know what's going on and why you need to provide inputs in a certain way. It's easy to get a million one-liners off of Stack Exchange sites, but understanding is valuable beyond gold.

> **Comments:**
> **username_1**: Can you expand this to show how it could be done? As it stands, this is really more of a comment than an answer.  

---

