Print Arrays Without Duplicates in AWK

Question

I am looking to compare two files, printing only records with a matching ID number and without duplicate records.

I have two files:

file1.txt contains:

Simons 0987768798980
West 09809867678
Vickers 768774564650
Simons 76867790987
Peterson 24346576865
Simons 76867790987
Holister 87879655456
Peterson 87686765766

And, file2.txt contains:

768774564650 Harry
76867790987 Steve
0987768798980 Mary
0987768798980 Mary
76856009097 Ali
87879655456 Rick
87686765766 Martin

The desired outcome is:

Harry Vickers 768774564650
Steve Simons 76867790987
Mary Simons 0987768798980   
Rick Holister 87879655456
Martin Peterson 87686765766

This is what I have tried:

ARGV[1]==FILENAME{id2lastname[$2]=$1;id2id[$2]=$2}
ARGV[2]==FILENAME{id2firstname[$1]=$2}

$1 in id2id{print id2firstname[$1],id2lastname[$1],id2id[$1],id2firstname[$1]="",id2id[$1]="",id2lastname[$1]=""}

Which produces the following output:

Harry Vickers 768774564650   
Steve Simons 76867790987   
Mary Simons 0987768798980   
Mary     
Rick Holister 87879655456   
Martin Peterson 87686765766 

I would be pleased to know why this removed the last name and ID number of the duplicate record, but left the first name.

Apologies if the technique is odd or unconventional. I have not been learning for long.

If my attempt cannot be fixed or you feel there is a better way, I am happy for you to produce the desired result in a different way, but please:

• use GAWK (as I want to progress with it),
• try to keep it as simple as possible,
• and, explain how it works so I can learn something.

Answer 1

The reason the partial line is printed, is that in your code you are not deleting the values you want to remove from the array, but replacing their values with empty strings.

This causes the check $1 in id2id{ ... } to be evaluated to true for the values that are empty strings.

The solution is to replace the code id2id[$1]="" with delete id2id[$1], and then it should work as expected.

Here is a slightly simplified version of the code:

awk 'NR == FNR { a[$2] = $1; next }
     $1 in a { print a[$1], $2, $1; delete a[$1] }' file1.txt file2.txt

In a one-liner:

awk 'NR==FNR{a[$2]=$1;next} $1 in a{print a[$1],$2,$1; delete a[$1]}' file1.txt file2.txt

Using awk instead of join has the advantage of being simple and easily customizable.

The disadvantage is that is stores the first file in RAM before merging, so it will not handle huge files effectively.

Answer 2

Use join as you are looking to keep it as simple as possible;

join -1 2 -2 1 -o 2.2 1.1 2.1 <(sort -unk2,2 file1) <(sort -unk1,1 file2) 2>/dev/null

join on second field of first file -1 2 with first field of second file -2 1 as the keys.

and -output these fields:
second field from the second file 2.2
first field from the first file 1.1
and first field from the second file 2.1

this sorts the first file on the second field as the key numerically sort -unk2,2 file1
and this sorts the second file on the first field as the key numerically sort -unk1,1 file2 and remove the duplicates -u from both files.

---

awk solution:

awk '!second_file{ Ids[$2]=$1; next }
     ($1 in Ids) { print $2, Ids[$1], $1 }' file1 second_file=1 <(sort -u file2)