Looking to compare the first column of two input files that have an identical format. The format looks like the following:

0000abc5abc3 GR096
0000def5ae87 GR001
0000cab5aea3 GR001
0000bac5aeeb GR001
0000fed5af13 GR001
0000efd5b16f GR001
0000cba5b187 GR001
0000bca5b2a3 GR001

0000abc5abc3 GR097
0000def5ae87 GR001
0000cab5aea3 GR001
0000bac5aeeb GR001
0000fed5af13 GR123
0000cba5b187 GR169

Column 1 contains MAC addresses in both FILE1 and FILE2. I want the value of column 1 in FILE1 to check against column 1 in FILE2 and if there is a match to output the value of column 1 and column 2 of FILE1 and the value of column 2 in FILE2 as a third column in this fashion.

0000abc5abc3 GR096 GR097
0000def5ae87 GR001 GR001
0000cba5b187 GR001 GR169

Each file contains several million entries. Running the input in bash is eternally slow and inefficient using while loops as it loops through each entry:

while read -r mac1 code1; do
    while read -r mac2 code2 ; do
        if [ "$mac1" == "$mac2" ]; then
            printf "%s %s %s\n" "$mac1" "$code1" "$code2"
    done < "$FILE1"
done < "$FILE2" > OUTPUTFILE

Awk is significantly faster for me using arrays but I am unable to print that 2nd column of FILE2 into the third column of the output using syntax like the following. This syntax just prints column 2 a second time:

awk 'NR==FNR { n[$1] = $1; n[$2] = $2; next } ($1 in n) { print n[$1],n[$2],$2 }'

My preference is AWK, but if it can be run in bash just as fast, I am okay with that as well.

Summary: If the value in Column 1 in file1 is found in file2, print the value of column 1, column 2 (File1) and column2 (File2).

  • 1
    Don't you just want awk 'NR==FNR { n[$1] = $2; next } ($1 in n) { print $1,n[$1],$2 }'? – steeldriver Mar 22 '17 at 20:13

if the output can be sorted:

join <(sort file1.txt) <(sort file2.txt)
  • +1 You beat me by 14 sec on this ;-) Add some explanation and I delete my answer – xhienne Mar 22 '17 at 20:20

Just to correct your awk:

awk 'NR==FNR { n[$1]=$0;next } ($1 in n) { print n[$1],$2 }' file1 file2

0000abc5abc3 GR096 GR097 
0000def5ae87 GR001 GR001 
0000cab5aea3 GR001 GR001 
0000bac5aeeb GR001 GR001 
0000fed5af13 GR001 GR123 
0000cba5b187 GR001 GR169 
  • @drfunkenstein Welcome. You can mark only one answer as solution, not all of them – George Vasiliou Mar 23 '17 at 22:02

join is the proper tool for this:

join <(sort file1) <(sort file2)

Since it works on sorted files, I am using bash's process substitution (<(...)) to sort each file before feeding them to join.

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.