Map field of first file based on patten matching in second file

Question

I have two tab-delimited files where I need to match text in the first column of file 1 to any position in lines of file 2. Upon a match I then want to print what's in the second column of the matching line of file 1 to the end of the matching line in file 2 (example below).

I know this can almost certainly be done with awk, but I'm not very competent in using awk or sed and searching related questions on here and trying to adapt their script hasn't worked out for me. Any input would be much appreciated.

File 1

protein_1.p1     note "PJD5F7, match to databaseID=64575, (species X)";
protein_1.p2     note "PJD5F7, match to databaseID=64575, (species X)";
protein_3.p1     note "PA5F9H, match to databaseID=93689, (species W)";
protein_4.p1     note "Q7GT5J, match to databaseID=89045, (species Y)";
protein_4.p3     note "YE6G3L, match to databaseID=44968, (species Z)";

File 2

chromosome_1    programID   transcript_id "protein_1.p1"; parent "protein_1";
chromosome_1    programID   transcript_id "protein_1.p2"; parent "protein_1";
chromosome_1    programID   transcript_id "protein_2.p1"; parent "protein_2";
chromosome_1    programID   transcript_id "protein_2.p2"; parent "protein_2";
chromosome_1    programID   transcript_id "protein_3.p1"; parent "protein_3";
chromosome_1    programID   transcript_id "protein_4.p1"; parent "protein_4";
chromosome_1    programID   transcript_id "protein_4.p2"; parent "protein_4";
chromosome_1    programID   transcript_id "protein_4.p3"; parent "protein_4";

Desired output

chromosome_1    programID   transcript_id "protein_1.p1"; parent "protein_1"; note "PJD5F7, match to databaseID=64575, (species X)";
chromosome_1    programID   transcript_id "protein_1.p2"; parent "protein_1"; note "PJD5F7, match to databaseID=64575, (species X)";
chromosome_1    programID   transcript_id "protein_2.p1"; parent "protein_2";
chromosome_1    programID   transcript_id "protein_2.p2"; parent "protein_2";
chromosome_1    programID   transcript_id "protein_3.p1"; parent "protein_3"; note "PA5F9H, match to databaseID=93689, (species W)";
chromosome_1    programID   transcript_id "protein_4.p1"; parent "protein_4"; note "Q7GT5J, match to databaseID=89045, (species Y)";
chromosome_1    programID   transcript_id "protein_4.p2"; parent "protein_4";
chromosome_1    programID   transcript_id "protein_4.p3"; parent "protein_4"; note "YE6G3L, match to databaseID=44968, (species Z)";

Answer 1

We could parse file1, map values ($2) to keys ($1), then parse file2 and append value to line, when a part of the line ($3) matches any key.

BEGIN {OFS = FS = "\t"}
FNR == NR {arr[$1] = $2; next}
{for (x in arr) if ($3 ~ x) {$0 = $0 " " arr[x]; break}}
{print}

This prints correct results for your example but it is not what you want for many reasons. The first of them is that it could fail for various cases, like protein_1.p1 and protein_1.p11. The second reason is perfomance, the time for every line of file2 is not constant but ~ size of file1.

---

So we have to modify the above script. You probably want to define a regex for the protein string to match. This way, the matching becomes strict enough and also at second parse, time depends on matching a regex on a field, not on the array size.

BEGIN {OFS = FS = "\t"; re = "\\<protein_[[:digit:]]+.p[[:digit:]]+\\>"}
FNR == NR {if ($1 ~ re) arr[$1] = $2; next}
match($3, re) {$0 = $0 " " arr[substr($3,RSTART,RLENGTH)]}
{print}

Notes:

• re: "protein_" followed by one or more digits, ".p" and again one or more digits All this inside word bountaries. The dot is literal. Word characters are [:alnum:] and _ so the rest are bountaries.
• Also there is a sanity check for the 1st field of file1.
• If a match() is found, then built-in variables RSTART, RLENGTH hold the index and the length of the matched string, and this substring is what we use into the hash.

Usage:

> awk -f tst.awk file1 file2
chromosome_1    programID   transcript_id "protein_1.p1"; parent "protein_1"; note "PJD5F7, match to databaseID=64575, (species X)";
chromosome_1    programID   transcript_id "protein_1.p2"; parent "protein_1"; note "PJD5F7, match to databaseID=64575, (species X)";
chromosome_1    programID   transcript_id "protein_2.p1"; parent "protein_2"; 
chromosome_1    programID   transcript_id "protein_2.p2"; parent "protein_2"; 
chromosome_1    programID   transcript_id "protein_3.p1"; parent "protein_3"; note "PA5F9H, match to databaseID=93689, (species W)";
chromosome_1    programID   transcript_id "protein_4.p1"; parent "protein_4"; note "Q7GT5J, match to databaseID=89045, (species Y)";
chromosome_1    programID   transcript_id "protein_4.p2"; parent "protein_4"; 
chromosome_1    programID   transcript_id "protein_4.p3"; parent "protein_4"; note "YE6G3L, match to databaseID=44968, (species Z)";