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
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.

3
  • Perfect! Thank you Aug 26 '20 at 16:24
  • @NickBailey Glad to help. Consider accepting an answer that solves your problem best.
    – Quasímodo
    Aug 27 '20 at 13:59
  • 1
    @EdMorton Very true, thank you for catching that!
    – Quasímodo
    Aug 27 '20 at 23:16
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
    Aug 27 '20 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.