1

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)";
7
  • How do you define a match? You say anywhere in file two, but doesn't it need to at least be in a quoted string? Can protein_3.p1 appear outside quotes in file2 and, if it does, is that a match?
    – terdon
    Nov 24, 2020 at 19:26
  • Yes, an ideal solution would include protein_3.p1 appearing outside of quotes as a positive match.
    – cjwwfd
    Nov 24, 2020 at 19:36
  • And would thisisnotprotein_3.p1 be considered a match? Or do we need to find it as a "word", so surrounded by spaces or other non-word characters?
    – terdon
    Nov 24, 2020 at 20:02
  • Either you have always only a unique "protein.*' for every row of file2, in a well-defined (fixed or by regexp) position of the last part, but you say "at any position", or your description is not determinative for the output. I mean probably you should slightly modify the description, and then the matching would be faster also.
    – thanasisp
    Nov 24, 2020 at 20:04
  • I mean file2 cannot have: chromosome_1 programID transcript_id "protein_1.p1"; parent "protein_2"; right? Because it would have two matches "for any position". So it has a unique match into file1/filed1. Is this always in a specific field of file2? for example the 4th or the last field.
    – thanasisp
    Nov 24, 2020 at 20:26

1 Answer 1

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)";
2
  • Thank you! This worked perfectly and is flexible enough for all the files I need to modify.
    – cjwwfd
    Nov 29, 2020 at 15:28
  • Very well, also without knowing much details on your domain, I think that matching a regexp here makes it customizable for small changes, similar searches.
    – thanasisp
    Nov 29, 2020 at 15:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .