Compare 2nd column of the 1st file and 1st column of the 2nd file and print the matched records of the 1st file

Question

I have two files large files:

f1.txt:

5020118359      |13ZJ24001218                  |20141224|R
5020120475      |13ZJ38000813                  |20141204|R
5020127431      |13ZJ38001569                  |20141201|R
5020127689      |12ZJ44000606                  |20141203|R
5020127728      |13ZJ38001356                  |20141203|R
5020127956      |13ZJ62002544                  |20141205|R
5020127972      |13ZJ49000082                  |20141205|R
5020128325      |13ZJ57000785                  |20141210|R
5020128706      |13ZJ38002805                  |20141211|R
5020129084      |10XJ70107764                  |20141217|R
5020129102      |12ZJ54000041                  |20141217|R

f2.txt:

09Y903010552
12ZJ54000041
11XJ62118385
08Y909018946
09Y902011954
11XJ57120346
10XJ70107764
11XJ40165329
09XJ42008336
08Y912021435
11XJ51040272
07Y910027235

Output:

5020129084      |10XJ70107764                  |20141217|R
5020129102      |12ZJ54000041                  |20141217|R

it will compare 2nd column of the first file and 1st column of the second file and then print the matched records of the 1st file.

Answer 1

Did you try with awk? That could easily be implemented in awk. Here's a different solution:

egrep $(tr '\n' '|' < f2.txt | sed 's/|$//') f1.txt

tr is used to replace all newline characters with |, sed is used to remove the trailing | (I bet there is a more elegant way to do that, without invoking another subprocess), and then this is used as argument for egrep = grep -e.

Answer 2

In the simple example you show, you can use grep, giving it f2 as the list of patterns to search for (-f):

$ grep -wf f2.txt f1.txt 
5020129084      |10XJ70107764                  |20141217|R
5020129102      |12ZJ54000041                  |20141217|R

The -w makes grep only look for "whole word" matches, so that foobar is not considered a match for foo.

For more complex cases where, for example, f2.txt can have multiple columns or where the data in f2.txt could appear in any column of f1.txt but you only want those cases where it appears on column 1, you can use awk:

$ awk -F'[ |]+' 'NR==FNR{a[$1]++; next}($2 in a )' f2.txt f1.txt 
5020129084      |10XJ70107764                  |20141217|R
5020129102      |12ZJ54000041                  |20141217|R

The -F sets the field separator to one or more | or space. NR is the current line number and FNR is the current file's line number. When multiple files are given as input, the two will be equal only while the first file is being read. Therefore, the a[$1]++; next will be run for each line of the first file. Since the first file is f2.txt, that will save each of the 1st fields of f2.txt in the array a. The next skips to the next line.

Then, when we reach the next file, if its 2nd field is in the array, then the assertion ($2 in a) will be true and awk will print the line. This is the default action for when an assertion evaluates to true. It is just a shortened version of:

awk -F'[ |]+' '{
                 if(NR==FNR){ 
                    a[$1]++; 
                 }
                 else if($2 in a ){
                    print
                 }
                }' f2.txt f1.txt