1

This issue is covered as separate topics, but I can't get everything to work together.

I have a tsv file, here is an example line: (file1)

NODE_1_length_45927_cov_22.029055_g0_i0 WP_055195622.1  93.1    729     50      0       291     2477    1       729     0.0e+00 1275.4  166486;1897048

The final field contains a list of numbers, separated by ;.

I have a file with a list of search patterns (file2):

33090
3041
1897
97100
97101
166486

I want to output lines which contain the search pattern in:

  1. only the final field
  2. any of the numbers in the final field, separated by ; can be matched, but they must be an exact word match for the pattern

So far my best attempt is

awk 'NR==FNR{a[$1]; next} {for (i in a) if ($NF ~ i) print $0}' file2 file1 

However this includes partial matches e.g. 1897 will match the second number when it shouldn't

I have tried many variations using gawk regex to match whole words:

awk 'NR==FNR{a[$1]; next} {for (i in a) if ($NF ~ '/\yi\y/') print $0}' file2 file1
awk 'NR==FNR{a[$1]; next} {for (i in a) if ($NF ~ '/[^[:alpha:]]i[^[:alpha:]]/') print $0}' file2 file1

but all give no output.

Any bash solution would be great, no matter how ugly - however I can't solve this issue with loops because I have 12 files with 20 million lines each, so it needs to be fast.

Any help is much appreciated.

1

3 Answers 3

3
awk 'NR==FNR{a[$1];next}{n=split($NF,b,";");for(i=1;i<=n;i++){if(b[i] in a){print;break}}}' file2 file1

Unwinded version:

awk '
  NR==FNR{a[$1];next}
  {
    n=split($NF,b,";")
    for(i=1;i<=n;i++){
      if(b[i] in a){print;break}
    }
  }   
' file2 file1

split($NF,b,";") splits the last field on the semicolon and puts the results in b array. It returns the number of elements of b, which is assigned to n. In your example line, n=2, b[1]=166486 and b[2]=1897048.

if(b[i] in a){print;break} if there is an exact match, the line is printed and the loop is broken, so as to not print the same line twice.

0
0

python data structure set gives us a way to compare and find whether one of the semicolon-separated numbers is found in another set which is formed by the file2 numbers. Set intersection when yields in nonempty, we go ahead and print the current line.

python3 - file1 file2 <<\eof
import sys

data, pat = sys.argv[1:]
rs, fs = '\n', '\t'

with open(pat) as fp, open(data) as fd:
  pats = set(fp.read().splitlines())
  for l in fd:
    s = l.rstrip(rs).split(fs)[-1].split(';')
    if set(s) & pats: print(l, end='')
eof
0

command

 awk 'NR == FNR {a[$1];next}FS=";"($NF in  a){print $0}' f2 f1

output

NODE_1_length_45927_cov_22.029055_g0_i0 WP_055195622.1  93.1    729     50      0       291     2477    1       729     0.0e+00 1275.4  166486;1897048
1
  • $NF is always the last number in this awk, not true? I'm afraid that's not what OP wants.
    – Quasímodo
    Commented Aug 27, 2020 at 14:01

You must log in to answer this question.

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