awk - Remove a line, if a specific column contains as substring an element from another textfile

Question

I have the following tsv-file (extract):

File 1:

NC_002163.1 RefSeq  source  1   1641481 .   +   .   organism=Campylobacter jejuni subsp. jejuni NCTC 11168;mol_type=genomic DNA;strain=NCTC 11168;sub_species=jejuni;db_xref=taxon:192222
NC_002163.1 RefSeq  misc_feature    19386   19445   .   -   .   inference=protein motif:TMHMM:2.0;note=3 probable transmembrane helices predicted for Cj0012c Further possible text
NC_002163.1 RefSeq  misc_feature    19482   19550   .   -   .   inference=protein motif:TMHMM:2.0;note=3 probable transmembrane helices predicted for Cj0014c Sometimes there is more text
NC_002163.1 RefSeq  misc_feature    22853   22921   .   -   .   inference=protein motif:TMHMM:2.0;note=5 probable transmembrane helices predicted for Cj0017c
...

As you can see, the last column contains some identifiers (Cj0014c, Cj0017c, etc). Some of these IDs are saved in another file

File 2:

Cj0012c
Cj0027
CjNC9
Cjp01
SRP_RNA_Cjs03
CjNC11
CjNC1
Cj0113
Cjp03
Cj0197c
Cj0251c

How can I use awk (or any bash-script-tool) to eliminate those lines from File 1, that contain as substring in the last column, any ID that is found in File 2? For example, the second line of File 1 would be deleted, since Cj0012c is found in File 2 and is part of the string in the last column of the line in File 1.

I've been struggling already many hours, so thanks for any help (and, if possible, an explanation of the code!)

Answer 1

Here are a few options you can try - all based around constructing an associative array from the second file, and then testing its elements against the last field of the first file, breaking as soon as a match is found:

1. A true substring match

   awk -F'\t' 'NR==FNR{a[$1]; next} {for(i in a) {if(index($NF,i)>0) next}} 1' File2 File1
   

   Note that (for example) this will identify Cj0012c as a substring of ABCj0012c and/or Cj0012cdef

2. Similar to the above, but padding the elements with a single space character either side to prevent partial word matches

   awk -F'\t' 'NR==FNR{a[$1]; next} {for(i in a) {if(index($NF," "i" ")>0) next}} 1' File2 File1
   

   (you could instead pad the string inside the array assignment call i.e. a[" "$1" "] if you prefer)

3. (probably requires GNU awk) use a regular expression match with patterns consisting of the strings from File2 surrounded by word boundary anchors

   gawk -F'\t' 'NR==FNR{a[$1]; next} {for(i in a) {if($NF ~ "\\<" i "\\>") next}} 1' File2 File1
   

   The potential gotcha with this version is if the elements of File2 may contain regular expression metacharacters - these would need to be escaped somehow.

Answer 2

A bit of perl:

perl -MList::Util=any -Mautodie -F'\t' -ane '
    BEGIN { open $f, "<", shift @ARGV; chomp(@exclusions = <$f>); }
    print unless any {$F[-1] =~ /\b\Q$_\E\b/} @exclusions; 
' file2 file1

The BEGIN line slurps in the file with the list of excluded tokens.
The print line filters out the lines which match any one of them.