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

user_8407582 0
Viewed 130

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.

awk
3 Answers

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
user_40820520

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

Since the requirement was If the column in Test2 found in line in Test1 I'm not limiting the search to character positions 5 & 6:

$ awk '
NR==FNR {                                       # process file2
    r=r (r==""?"":"|") $0                       # build a regex of data (23|19|15)
    next
}
{                                               # process file1
    print substr($0,1,8) ($0~r) substr($0,10)   # output: 1-8 condition 9-
    # print $1,($0~r),$2                        # coincidently this would work also 
}' file2 file1

Output:

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

If the contents of file2 are only found in positions 5 & 6, this solution is ineffective compared to the two other solutions (presented this point).

user_41623560
Related