grep, awk, sed, print column 2 matching in file1 and file2 column3

Question

edit: I would like to match column 1,2 of file1.txt with column of 1,3 of file2.txt and print the matching lines of file2.txt

file1.txt:

scaffold1   57482
scaffold1   63114
scaffold1   63118
scaffold1   63129
scaffold1   63139
scaffold1   63279
scaffold1   63294
scaffold2   65015
scaffold2   77268
scaffold2   77335

file2.txt:

scaffold1   381 382 T/A +
scaffold1   384 385 T/A,G   +
scaffold1   385 386 G/C +
scaffold1   445 446 C/T +
scaffold1   57481   57482   T/A +
scaffold1   63113   63114   T/A,G   +
scaffold1   63128   63129   G/C +
scaffold2   65014   65015   G/A +
scaffold2   77267   77268   G/A +
scaffold2   77334   77335   C/T +

output.txt:

scaffold1   57481   57482   T/A +
scaffold1   63113   63114   T/A,G   +
scaffold1   63128   63129   G/C +
scaffold2   65014   65015   G/A +
scaffold2   77267   77268   G/A +
scaffold2   77334   77335   C/T +

Answer 1

An awk solution:

$ awk 'NR==FNR{a[$1$2]++;next}{if($1$3 in a){print}}' file1 file2 
scaffold1   57481   57482   T/A +
scaffold1   63113   63114   T/A,G   +
scaffold1   63128   63129   G/C +
scaffold2   65014   65015   G/A +
scaffold2   77267   77268   G/A +
scaffold2   77334   77335   C/T +

NR is the current line number and FNR the current line number of the current file. The two will be equal only while the first file is being read. So the first block will only be executed while the 1st file is being read and therefore the 1st and 2nd fields of the first file are saved in the array a. Then, when the second file is being processed, we print its lines only if the 1st and 3rd field is present in a, so only if it were present in the first file.

Answer 2

If your example data can be generalised, there are a couple of assumptions that can be made:

• You only need to match column 2 of file 1 with column 3 of file 2
• The input files are already sorted by the above columns

If these are fair assumptions, then the following join command works:

join -1 2 -2 3 -o "1.1,2.2,2.3,2.4,2.5" file1.txt file2.txt > output.txt

The output is:

scaffold1 57481 57482 T/A +
scaffold1 63113 63114 T/A,G +
scaffold1 63128 63129 G/C +
scaffold2 65014 65015 G/A +
scaffold2 77267 77268 G/A +
scaffold2 77334 77335 C/T +

---

Alternately, if those are not valid assumptions, then we can use awk to rearrange the columns a bit (specifically combine columns 1,2 of file 1 and columns 1,3 of file 2) and then sort (as required by join). Then join will match the combined columns.

join -o "1.2,2.2,2.3,2.4,2.5" <( awk '$1=$1"_"$2" "$1' file1.txt | sort ) <( awk '$1=$1"_"$3' file2.txt | sort )

Answer 3

Try using "cut" to capture the required fields, something like:

cut -d$"\t" -f2 file1.txt | sort | > file1_col2.txt

User cuonglm has a good overview of using awk to compare the files and print the matches:

Compare two files for matching lines and store positive results