AWK - If column in Test2 found in line in Test1 insert 1 else 0 in Test1

Question

I have two files Test1 and Test2.

Test1:

10AP23Q   ERTY
10AP20J   FDGC
978J15K   BGTD
98KT23M   ERTY
76VU14P   FDGC

Test2:

23
19
15

Test1 is a fixed width file. If the column in Test2 found in line in Test1, insert ‘1’ , else insert ‘0’ at position 9 in Test1.

Expected output:

10AP23Q 1 ERTY
10AP20J 0 FDGC
978J15K 1 BGTD
98KT23M 1 ERTY
76VU14P 0 FDGC

I tried the below code. Getting parse error near '}'.

awk 'BEGIN {if('NR==FNR {a[$1];next} (substr($0,5,2) in a)' test2 test1) (substr($0,9,1)="1") else(substr($0,9,1)="0")}'

Appreciate the solution.

Answer 1

Here's one way to do it:

$ awk 'BEGIN{FS=OFS=""} NR==FNR{a[$0]; next}
       {$9 = ($5$6 in a) ? 1 : 0} 1' Test2 Test1
10AP23Q 1 ERTY
10AP20J 0 FDGC
978J15K 1 BGTD
98KT23M 1 ERTY
76VU14P 0 FDGC

• FS=OFS="" clear FS and OFS so that each field is individual characters, makes it easier to write a solution compared to using substr
• NR==FNR{a[$0]; next} create array keys based on entire line contents of Test2
• $9 = ($5$6 in a) ? 1 : 0 check if 5th/6th character sequence from Test1 is present as a key in the array and change 9th character accordingly
  • Instead of ternary operator, you can also use $9 = ($5$6 in a) since in returns 1 for true case and 0 for false case (courtesy: anubhava)
• 1 idiomatic way to print contents of $0

Answer 2

With your shown samples, could you please try following.

awk '
FNR==NR{
  arr[$0]
  next
}
{
  $1=$1 OFS ((substr($1,5,2) in arr)?"1":"0")
}
1
' Test2 Test1

OR as per anubhava sir's nice suggestion, above could be shorten to:

awk '
FNR==NR{
  arr[$0]
  next
}
{
  $1=$1 OFS (substr($1,5,2) in arr)
}
1
' Test2 Test1

Explanation: Adding detailed explanation for above.

awk '               ##Starting awk program from here.
FNR==NR{            ##Checking condition which will be TRUE when Test2 is being read.
  arr[$0]           ##Creating array arr with index of current line.
  next              ##next will skip all further statements from here.
}
{
  $1=$1 OFS ((substr($1,5,2) in arr)?"1":"0") ##Checking condition if sub string of 1st field is present in array then add 1 else add 0 here into $1 itself.
}
1                   ##1 will print current line here.
' Test2 Test1       ##mentioning Input_file names here.