2

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  
1
  • 2
    Shouldn't c1 c1 c3 c4 AB1 c6 be present in the expected output since AB1 doesn't appear in file1?
    – Ed Morton
    Dec 15, 2021 at 14:52

5 Answers 5

3

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

0
1
$ 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
1
  • BTW, if your file2 is enormous, you can speed this up by precompiling the regular expression. e.g. after the $regex = join ... line, add the line $regex = qr/\b(?:$regex)\b/; . Also BTW, I should have used \b word-boundary markers in a non-capturing group when I first wrote this, even without the qr to precompile the regex, for exact matches only (e.g. a line with HIBI123 would match HIBI12 without \b). Depends on whether you want only exact matches or if partial matches are acceptable too.
    – cas
    Dec 17, 2021 at 10:09
0
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
0
0

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.

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.