Match columns based on partial match between file1 and file2 & print non-matching rows

Question

file1

HIB12  
VH6|KB4  
KB4  
KB4|LKM98|HIB12  

file2

c1 c1 c3 c4 HIB12|LKM98 c6  
c1 c1 c3 c4 KB4|LKM98 c6  
c1 c1 c3 c4 LL15|VH6  
c1 c1 c3 c4 ZZ16|YY15 c6 
c1 c1 c3 c4 ZZ16 c6 
c1 c1 c3 c4 AB1 c6  

Both files are tab-separated. Column1 of file1 has partial match with column5 of file2. Values in both columns are separated by '|' If any value of column1 of file1 is matching with any value of column5 of file5, that row should not be printed in output, other non-matching should be there in output. I tried but not getting expected output

awk 'BEGIN{FS=OFS="\t"} NR==FNR {a[$1]=$5; next} {for (i in a) if (index(i, $5)) print $0, a[i]}' file2 file1

expected output

c1 c1 c3 c4 ZZ16|YY15 c6 
c1 c1 c3 c4 ZZ16 c6  

Answer 1

Using GNU awk for mult-char RS:

$ awk '
    NR==FNR { a[$0]; next }
    { split($5,v,"|"); for (i in v) if (v[i] in a) next; print }
' FS='\t' RS='[[:space:]|]+' file1 RS='\n' file2
c1 c1 c3 c4 ZZ16|YY15 c6
c1 c1 c3 c4 ZZ16 c6
c1 c1 c3 c4 AB1 c6

or using any awk:

$ awk '
    NR==FNR { for (i=1; i<=NF; i++) a[$i]; next }
    { split($5,v,"|"); for (i in v) if (v[i] in a) next; print }
' FS='|' file1 FS='\t' file2
c1 c1 c3 c4 ZZ16|YY15 c6
c1 c1 c3 c4 ZZ16 c6
c1 c1 c3 c4 AB1 c6

Answer 2

Compatible with any awk.

awk 'BEGIN{FS=OFS="\t"}NR==FNR{split($0,vals,"|");for(i in vals){v[vals[i]]}}NR!=FNR{hide=0;for(j in v){if($5~j){hide=1}};if(!hide){print}}' ./file1 ./file2

My result was:

c1 c1 c3 c4 ZZ16|YY15 c6
c1 c1 c3 c4 ZZ16 c6
c1 c1 c3 c4 AB1 c6

Description:

NR==FNR: in the first file NR and FNR are equal.

{v[vals[i]]}: creating associative array of unallowed values.

if($5~j){hide=1}: set row to hide if there is unallowed value in 5fth field

hide=0: reset hiding status for new row.

Answer 3

$ perl -lane '
    # is this the first file? ($fc is file counter)
    if ($fc == 0) {
      # split first field on pipe chars                         
      my @p = split /\|/, $F[0];
      # use as keys for %patterns hash
      foreach my $p (@p) { $patterns{$p} = 1 };
    } else {
     print unless $F[4] =~ /$regex/;
    };

    if (eof) { # end of file
      if ($fc == 0) { # is this still the first (zeroth) file?
        # use keys of %patterns to build a regular expression
        $regex = join "|", keys %patterns;
      };
      $fc++;
    }' file1 file2
c1      c1      c3      c4      ZZ16|YY15       c6
c1      c1      c3      c4      ZZ16    c6
c1      c1      c3      c4      AB1     c6

BTW, here's a shorter version with fewer intermediate variables and no comments:

perl -lane '
  if ($fc == 0) {
    foreach (split /\|/, $F[0]) { $patterns{$_} = 1 };
  } else {
   print unless $F[4] =~ /$regex/;
  };

  if (eof) {
    $regex = join "|", keys %patterns if ($fc == 0);
    $fc++;
  }' file1 file2

If you wanted to make it unreadable, you could shorten the variable names, replace the ($c==0) test with the shorter but equivalent (and harder for novices to understand, so bonus!) (!$c), squash it all into one line and get rid of excess whitespace and semi-colons without changing how it runs. Some people prefer that - masochistic cargo-culting FTW!

perl -lane 'if(!$c){foreach(split/\|/,$F[0]){$p{$_}=1}}else{print unless $F[4]=~/$regex/};if(eof){$regex=join"|",keys %p if(!$c);$c++}' file1 file2

Answer 4

t=$(printf '\t')
T="[^$t]" F="($T*$t)"
d='[|]'   D='[^|]'

sed -E "G
  /$t/!{
    y/\n/|/;:a
    s/((^|$d)($D+)$d(.*$d)?)\3$d/\1/
    ta
  }
  h;/\n/!d
  s/$F{4}/|/
  s/($t.*)?(\n)/|\2|/
  /($d$D+$d).*\n.*\1/d
  g;P;s/.*\n//;h;d
" file1 file2

c1  c1  c3  c4  ZZ16|YY15   c6
c1  c1  c3  c4  ZZ16    c6
c1  c1  c3  c4  AB1 c6

---

perl -F'\t' -lane '
  if ( @ARGV ) {
    $h{$_}++ for split /\|/;
  } else {
    print if ! grep { $h{$_} } split /\|/, $F[4];
  }
' file1 file2

---

sets in python are a data structure that can be used for this problem. File1 to be used to construct the superset s1 which has pipe delimited fields. Then the fifth column of file2 is split on pipes and a set s2 is formed, whose intersection with the superset s1 yields a null set => we print the current record.

python3 -c 'import sys
ifile1,ifile2 = sys.argv[1:]

with open(ifile1) as f1, open(ifile2) as f2:
  s1 = {e for l in f1 for e in l.rstrip("\n").split("|")}

  for _ in f2:
    l = _.rstrip("\n")
    F = l.split("\t")
    s2 = set(F[4].split("|"))
    if not(s1 & s2): print(l)
' file1 file2

Answer 5

In the second file, the fifth field already contains a logical element OR:

awk '
NR==FNR {A[$1]; next}
        {for(i in A)
                if(i ~ "^("$5")$") next
        print}
' RS='[\n|]' file1 RS='\n' file2

It remains only to substitute the anchors of the beginning and end of the line and parentheses in the conditional expression.