Filter a smaller file using another huge file
<p>I have a huge csv file with about 10^9 lines where each line has a pair of ids such as:</p>

<pre><code>IDa,IDb
IDb,IDa
IDc,IDd
</code></pre>

<p>Call this file1.  I have another much smaller csv file with about 10^6 lines in the same format. Call this file2. </p>

<p>I want to simply find the lines in file2 which contain at least one ID that exists somewhere in file1.</p>

<p>Is there a fast way to do this?  I don't mind if it is in awk, python or perl.</p>

<p>Using these input files for testing:</p>

<pre><code>$ cat file1
IDa,IDb
IDb,IDa
IDc,IDd

$ cat file2
IDd,IDw
IDx,IDc
IDy,IDz
</code></pre>

<p>If file1 can fit in memory:</p>

<pre><code>$ awk -F, 'NR==FNR{a[$1];a[$2];next} ($1 in a) || ($2 in a)' file1 file2
IDd,IDw
IDx,IDc
</code></pre>

<p>If not but file2 can fit in memory:</p>

<pre><code>$ awk -F, '
    ARGIND==2 {
        if ($1 in inBothFiles) {
            inBothFiles[$1] = 1
        }
        if ($2 in inBothFiles) {
            inBothFiles[$2] = 1
        }
        next
    }
    ARGIND==1 {
        inBothFiles[$1] = 0
        inBothFiles[$2] = 0
        next
    }
    ARGIND==3 {
        if (inBothFiles[$1] || inBothFiles[$2]) {
            print
        }
    }
' file2 file1 file2
IDd,IDw
IDx,IDc
</code></pre>

<p>The above uses GNU awk for ARGIND - with other awks just add a <code>FNR==1{ARGIND++}</code> block at the start.</p>

<p>I have the <code>ARGIND==2</code> block (i.e. the part that processes the 2nd argument which in this case is the 10^9 <code>file1</code>) listed first for efficiency so we don't unnecessarily test <code>ARGIND==1</code> for every line in the much larger file.</p>

python perl awk