how to delete some of the rows in first file based on information from a second file?

Question

I have a file like this:

file 1:

    1   2       0.000
    1   3       0.213
    1   4       0.014
    1   5       0.001
    1   6       0.555
    1   7       0.509
    1   8       0.509
    3   4       0.995
    3   5       0.323
    3   6       0.555
    3   7       0.225
    3   8       0.000
    4   5       0.095
    4   6       0.058
    4   7       0.335
    4   8       0.000
    5   6       0.995
    5   7       0.658
    5   8       0.000
    6   7       0.431
    6   8       0.000
    7   8       0.000

and file 2 :

1
3 
4
5 6 7

I want keep those rows in file 1 in which pair numbers are observed together in a row at file2. here only 5, 6 and 7 are observed together in file 2. therefore each pair of those numbers should be remained in file1. so out put is:

5   6       0.995
5   7       0.658
6   7       0.431

any suggestion(by considering that the real data is huge and numbers may not begin from 1) ?

Answer 1

If perl solution is okay. Assumes that column1 and column2 data in file1 are ordered such that value in column1 is always less than column2

$ perl -lane '
    if(!$nf)
    {
        if($#F > 0)
        {
            foreach $i (0..$#F)
            {
                $h{"-$F[$i]-$F[$_]-"}++ foreach ($i+1..$#F)
            }
        }
        $nf++ if eof;
    }
    else
    {
        print if $h{"-$F[0]-$F[1]-"}
    }
' file2 file1
    5   6       0.995
    5   7       0.658
    6   7       0.431

• First a hash of keys is built on file2 in pairs (again assuming numbers are arranged in increasing order)
  • the two columns values are surrounded by - to prevent possible mismatch, like 11 and 20 vs 1 and 120
• Then for file1 lines, print the line if key exists

If file2 is changed as

$ cat file2
1
3 4
5 6 7 8

$ perl -lane '
    if(!$nf)
    {
        if($#F > 0)
        {
            foreach $i (0..$#F)
            {
                $h{"-$F[$i]-$F[$_]-"}++ foreach ($i+1..$#F)
            }
        }
        $nf++ if eof;
    }
    else
    {
        print if $h{"-$F[0]-$F[1]-"}
    }
' file2 file1
    3   4       0.995
    5   6       0.995
    5   7       0.658
    5   8       0.000
    6   7       0.431
    6   8       0.000
    7   8       0.000

Answer 2

Using awk.

First awk command creates a file with all pairs. Second awk command reads the pairs file once into an array and then prints all matching lines.

awk 'NF>1{for(i=1;i<=NF;i++) for(j=i+1;j<=NF;j++) print $i,$j;}' file2 > /tmp/pairs
awk 'BEGIN{while((getline <"/tmp/pairs") > 0) pair[$1,$2]=1; close("/tmp/pairs")} ($1,$2) in pair' file1

The second command may need much memory! If the files are ordered then we could avoid the array somehow, reading both files simultaneously. Note I'm using two commands to be able to review the pairs file before the final run.

Here the same code as a single command and readable formatted:

awk '
  BEGIN {
    f="file2"
    while((getline <f) > 0)
      for(i=1;i<=NF;i++)
        for(j=i+1;j<=NF;j++)
          pair[$i,$j]=1;
    close(f);
  }
  ($1,$2) in pair
' file1

FYI I've run some benchmarks on file1 (22 mill. lines), file2 (4 mill lines) which produces 2 mill. lines output.

• gawk: 9.6 secs, 275M memory
• mawk: 20.7 secs, 134M memory
• Sundeep's perl answer: 31.9 sec, 231M memory