compare and print 2 columns from 2 files in awk ou perl

user_19028059 0
Viewed 67

I have 2 files with 2 million lines. I need to compare 2 columns in 2 different files and I want to print the lines of the 2 files where there are equal items. this awk code works, but it does not print lines from the 2 files:

awk 'NR == FNR {a[$3]; next}$3 in a' file1.txt file2.txt

file1.txt

0001 00000001 084010800001080
0001 00000010 041140000100004

file2.txt

2451 00000009 401208008004000
2451 00000010 084010800001080

desired output:

file1[$1]-file2[$1] file1[$2]-file2[$2] $3 ( same on both files )

0001-2451 00000001-00000010 084010800001080

how to do this in awk or perl?

awk
5 Answers

With your shown samples, please try following awk code. Fair warning I haven't tested it yet with millions of lines.

awk '
FNR == NR{
  arr1[$3]=$0
  next
}
($3 in arr1){
  split(arr1[$3],arr2)
  print (arr2[1]"-"$1,arr2[2]"-"$2,$3)
  delete arr2
}
' file1.txt file2.txt

Explanation: Adding detailed explanation for above.

awk '                                  ##Starting awk program from here.
FNR == NR{                             ##checking condition which will be TRUE when first Input_file is being read.
  arr1[$3]=$0                          ##Creating arr1 array with value of $1 OFS $2 and $3
  next                                 ##next will skip all further statements from here.
}
($3 in arr1){                          ##checking if $3 is present in arr1 then do following.
  split(arr1[$3],arr2)             ##Splitting value of arr1 into arr2.
  print (arr2[1]"-"$1,arr2[2]"-"$2,$3) ##printing values as per requirement of OP.
  delete arr2                          ##Deleting arr2 array here.
}
' file1.txt file2.txt                  ##Mentioning Input_file names here.
user_58665800

Assuming your $3 values are unique within each input file as shown in your sample input/output:

$ cat tst.awk
NR==FNR {
    foos[$3] = $1
    bars[$3] = $2
    next
}
$3 in foos {
    print foos[$3] "-" $1, bars[$3] "-" $2, $3
}


$ awk -f tst.awk file1.txt file2.txt
0001-2451 00000001-00000010 084010800001080

I named the arrays foos[] and bars[] as I don't know what the first 2 columns of your input actually represent - choose a more meaningful name.

user_17450010

If you have two massive files, you may want to use sort, join and awk to produce your output without having to have the first file mostly in memory.

Based on your example, this pipe would do that:

join -1 3 -2 3 <(sort -k3 -n file1) <(sort -k3 -n file2) | awk '{printf("%s-%s %s-%s %s\n",$2,$4,$3,$5,$1)}'

Prints:

0001-2451 00000001-00000010 084010800001080
user_2986070

If your files are that big, you might want to avoid storing the data in memory. It's a whole lot of comparisons, 2 million lines times 2 million lines = 4 * 1012 comparisons.

use strict;
use warnings;
use feature 'say';

my $file1 = shift;
my $file2 = shift;

open my $fh1, "<", $file1 or die "Cannot open '$file1': $!";

while (<$fh1>) {
    my @F = split;
    open my $fh2, "<", $file2 or die "Cannot open '$file2': $!";
    # for each line of file1 file2 is reopened and read again
    while (my $cmp = <$fh2>) {
        my @C = split ' ', $cmp;
        if ($F[2] eq $C[2]) {       # check string equality
            say "$F[0]-$C[0] $F[1]-$C[1] $F[2]";
        }
    }
}

With your rather limited test set, I get the following output:

0001-2451 00000001-00000010 084010800001080
user_7254180

Python: tested with 2.000.000 rows each file

d = {}
with open('1.txt', 'r') as f1, open('2.txt', 'r') as f2:
  for line in f1:
    if not line: break
    c0,c1,c2 = line.split()
    d[(c2)] = (c0,c1)

  for line in f2:
    if not line: break
    c0,c1,c2 = line.split()
    if (c2) in d: print("{}-{} {}-{} {}".format(d[(c2)][0], c0, d[(c2)][1], c1, c2))

$ time python3 comapre.py
1001-2001 10000001-20000001 224010800001084
1042-2013 10000042-20000013 224010800001096

real    0m3.555s
user    0m3.234s
sys     0m0.321s
user_41778280
Related