Compare two files with awk

Question

I'm trying to compare two files and print the output if they match on some fields.

File1.txt:

bart:29482164591748
 apu:29482164591748
smithers:68468468468464
lisa:68468468468464
maggie:2348578903247548

File2.txt:

68468468468464:keyboard
68463894578424:user
29482164591748:computer

I would like this output:

bart:29482164591748:computer

Answer 1

A classic with join:

join -t: -1 2 -2 1 -o 2.1,1.1,1.2 <(sort -t: -k1,1 file1) <(sort -t: -k2,2 file2)

• -t: specifies the colon as separator.
• -1 2 file1's join field is the second one
• -2 1 file2's join field is the first one
• -o 2.1,1.1,1.2 the output format.
• <(...): both files must be sorted on the join field (-k1,1 and -k2,2), -t: specifies the colon as separator for sort.

Answer 2

With awk:

awk -F: 'NR==FNR{a[$1]=$2;next}a[$2]{print $1":"$2":"a[$2]}' file1 file2

Output:

bart:29482164591748:computer
smithers:68468468468464:keyboard
lisa:68468468468464:keyboard

Explanation:

• awk -F: start awk treating colon as a fields delimiter
• NR==FNR{} process only the first file
• a[$1]=$2;next build an array a indexed by the first field with values of the second field then skip to the next row
• a[$2]{} process only if value of previously build array with the index of the current second field is not empty (this is done only for the file2, because of the next word in the previous expression)
• print $1":"$2":"a[$2] print everything as desired

---

After question edit:

awk -F: 'NR==FNR{a[$1]=$2;next}a[$2]{print $1":"$2":"a[$2]}' file2 file1

Output:

bart:29482164591748:computer
 apu:29482164591748:computer
smithers:68468468468464:keyboard
lisa:68468468468464:keyboard

Answer 3

Wouldn't use awk, but would use perl instead.

#!/usr/bin/env perl
use strict;
use warnings;

#open both files for reading
open( my $input1, '<', "file1.txt" ) or die $!;
open( my $input2, '<', "file2.txt" ) or die $!;

#read the key-values into a hash called lookup. 
my %lookup = do { local $/; <$input1> =~ m/(\d+):(\w+)/g; };

#iterate by line of second file
while ( <$input2> ) { 
    #trim trailing linefeeds
    chomp;
    #split current line on :
    my ( $user, $key ) = split /:/;
    #if exists in original lookup, display record 
    if ( $lookup{$key} ) {
        print join ( ":", $user, $key, $lookup{$key}),"\n";
    }
}

I get a slightly different output though - specifically:

bart:29482164591748:computer
smithers:68468468468464:keyboard
lisa:68468468468464:keyboard

I'm not sure why the second 2 shouldn't be printed based on matching key-values.

If you want a one liner that's basically the same:

perl -F: -lane "print $k{$F[0]}.':'.$_ if $k{$F[0]}; $k{$F[1]}//=$F[0];" file2.txt file1.txt