2

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

  • can there be more than 3 numbers in a line in second file? – Sundeep Oct 6 '16 at 5:39
  • also, seems like 1st/2nd column numbers are sorted both row wise and column wise, is that always true? – Sundeep Oct 6 '16 at 5:46
  • If the real data is huge, I'd consider using at least a small perl script instead of awk/cut (because traversing file 2 each time is slow and makes it quadratic), and if it's really huge, I'd write a C program. It's also important to have an idea how large these numbers can get, especially those in file 2. – dirkt Oct 6 '16 at 6:58
  • @dirkt I don't think that perl can be faster than awk, see benchmarks in my answer. – rudimeier Oct 6 '16 at 11:26
  • @rudimeier, agree awk is usually faster than perl, but I feel like someone with better experience would be able to write a perl solution much faster than mine :) – Sundeep Oct 6 '16 at 11:37
3

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
  • 1
    This code does nothing on my system (perl 5.18.1). But my perl knowledge is too limited to debug that. Looks like the code is only executed once, not for each line. – rudimeier Oct 6 '16 at 9:53
  • 1
    @rudimeier, mystery solved I think.. no wonder I've others complain in some form or other before too.. need to explicitly add -n option for that version.. can you try with perl -nale ? – Sundeep Oct 6 '16 at 9:59
3

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
  • to avoid tmp file, awk 'NF>1{for(i=1;i<=NF;i++)for(j=i+1;j<=NF;j++)print "^ *"$i" *"$j" ";}' file2 | grep -f- file1 – Sundeep Oct 6 '16 at 10:39
  • my grep -f is useless. It needs 100's of times more time and also memory! I'll remove that from my answer. – rudimeier Oct 6 '16 at 11:32
  • might interest you in knowing why grep is slower: stackoverflow.com/questions/39893789/… – Sundeep Oct 6 '16 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.