Compare second column of two text files and print first columns of both files if match

Question

I am trying to compare second columns of two text files and print the first columns of both files if match.

I have tried the below awk codes but it was no use

1) awk 'NR==FNR {a[$2]=$2; next} {print $1,a[$1]}' nid8.txt nid9.txt

2) awk 'NR==FNR {a[$2]=$2; next} {print $1, $1 in a}' nid8.txt nid9.txt

Example files:

nid8.txt:

1000 500
1001 501
1002 502
1003 503
1004 504
1005 505

nid9.txt:

2000 504
2001 502
2002 508
2003 505
2004 500
2005 501

Output:

1000 2004
1001 2005
1002 2001
1004 2000
1005 2003

Answer 1

You can use join here:

join -j 2 -o 1.1 2.1 <(sort -nk2,2 nid8.txt) <(sort -nk2,2 nid9.txt)

Use the second field -j 2 on both files as the keys.

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

join requires input files to be sorted, so we sort them on the second field that will be used as the key numerically sort -nk2,2 input.

---

Using awk which not require to have sorted inputs but load first input file into memory:

awk '!second_file{ my_array[$2]=$1; next }
     ($2 in my_array) { print $1, my_array[$2] }' nid8.txt second_file=1 nid9.txt

with my_array[$2]=$1 we are saving first column of first input file nid8.txt only where the keys are the second column of the same first file until second_file variable value is not set to 1 which cause !second_file expression value evaluate to false and won't execute that block for next input(s).

With ($2 in my_array) condition we checks if the second field exist in our array my_array or not, if that was exist then we print first field $1 (which is from the second file) and the value for that same key with my_array[$2] which contains the first field from the first file with the same key.