3

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.
3
  • Please make your questions easier to read. (1) You have eight distinct ID numbers. When you have fewer than 100 values, you should consider using two-digit numbers instead of 11-, 12- and 13-digit numbers. (And, of course, if you have 100 or more values, that’s too many to post in a question.) The data in this question are particularly hard to read because (a) the first three digits of 09809867678 and 0987768798980 are the same,  … (Cont’d) – Scott Nov 18 '20 at 21:51
  • (Cont’d) …  (b) the first three digits of 76856009097, 76867790987 and 768774564650 are the same, (c) the first two digits of 87686765766 and 87879655456 are the same, and the third digits (6 and 8) are visually similar, (d) 768 and 876 are the same three digits, in different order, and (e) some of the last digits are the same.  (My favorite five two-digit numbers are 17, 42, 60, 83 and 95 — note that each digit is used exactly once.)  … (Cont’d) – Scott Nov 18 '20 at 21:51
  • (Cont’d) …  (2) I believe that it’s easier for the reader if you use names like Adam, Beth, Carl, Denise, Edgar, Nichols, Olsen, Palmer, Quincy and Ramos.  (3) Seriously?  You put the first names in the second file and the last names in the first file?  (4) When you post code, make it readable by putting spaces in places like before and after {, before }, after , and ;, etc.  And don’t write lines that are longer that the terminal is wide; break them into multiple lines. – Scott Nov 18 '20 at 21:51
7

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.

0
7

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)
2
  • I think -o is for "output," not "only," but join is definitely the right tool for the job here. – Kevin Nov 17 '20 at 20:14
  • @Kevin man page said obey but I think outout also would better than only, and I have updated with that, thanks – αғsнιη Nov 18 '20 at 17: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.