Comparing two lists and printing select columns from each list

Question

I want to compare two lists and print some columns from one, and some from the other if two match. I suspect I'm close but I suppose it's better to check..

1st file: Data.txt

101    0.123
145    0.119
242    0.4
500    0.88

2nd File: Map.txt

red    1    99     
blue    3    101
rob    3    240
ted    7    500

So, if I want to compare the 3rd column of file2 against the 1st of file1 and print the 1st column of file2 and all of file1, I tried awk 'NR==FNR {a[$3];next}$1 in a{print$0}' file2 file1

but that only prints matches in file1. I tried adding x=$1 in the awk. i.e. awk 'NR==FNR {x=$1;a[$3];next}$1 in a{print x $0} file2 file1 but that saves only one value of $1 and outputs that value every line. I also tried adding $1 into a[$3], which is obviously wrong thus giving zero output.

Ideally I'd like to get this output:

blue    145    0.119
ted    500    0.88

which is the 1st column of file2 and the 3rd column of file2 matched to 1st column of file1, and the rest of file1.

jas jas · Accepted Answer · 2015-10-05T10:31:13

You had it almost exactly in your second attempt. Just instead of assigning the value of $1 to a scalar you can stash it in the array for later use.

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

$ cat file1.txt
101    0.123
145    0.119
242    0.4
500    0.88

$ cat file2.txt
red   1    99
blue  3    101
rob   3    240
ted   7    500

$ awk 'NR==FNR {a[$3]=$1; next} $1 in a {print a[$1], $0}' file2.txt file1.txt
blue 101    0.123
ted 500    0.88

Comparing two lists and printing select columns from each list

1 Answers