awk filter columns with calculated values and number range | updated

user_13527455 0
Viewed 79

Hi i dont know how to achieve expected output from given input file , current code giving output but need to have to expected one

input file                     awk code output
--------

3700 TEXT_0A 34 NA NA        | 3700  TEXT_0A  34    NA   NA    850
3721 TEXT_0A 55 ete 851      | 3721  TEXT_0A  55    ete  851   850
3701 TEXT_0A 100 NA NA       | 3701  TEXT_0A  100   NA   NA    850
3724 TEXT_0A 150 ete 1275    | 3724  TEXT_0A  150   ete  1275  1275
3713 TEXT_0A 170 ete 1445    | 3713  TEXT_0A  170   ete  1445  1445
25 TEXT_XA 200 ete 822       | 3686  TEXT_0A  200   ete  822   1700
3686 TEXT_0A 200 ete 822     | 3692  TEXT_0A  200   ete  1700  1700
3692 TEXT_0A 200 ete 1700    | 3716  TEXT_0A  200   ete  3000  1700
3716 TEXT_0A 200 ete 3000    | 3688  TEXT_0A  250   ete  822   1875
88 TEXT_AF 250 ete 822       | 3708  TEXT_0A  300   ete  2250  2250
69 DNTT_AF 250 ete 822       | 3704  TEXT_0A  304   ete  2280  2280
3688 TEXT_0A 250 ete 822     | 3705  TEXT_0A  304   ete  2350  2280
3708 TEXT_0A 300 ete 2250    | 3689  TEXT_0A  400   ete  3000  3000
3704 TEXT_0A 304 ete 2280    | 3717  TEXT_0A  400   ete  3002  3000
3705 TEXT_0A 304 ete 2350    | 3706  TEXT_0A  404   ete  3030  3030
3689 TEXT_0A 400 ete 3000    | 3696  TEXT_0A  500   ete  3750  3750
3717 TEXT_0A 400 ete 3002    | 3707  TEXT_0A  650   ete  4500  4875
3706 TEXT_0A 404 ete 3030    | 3720  TEXT_0A  800   ete  3000  6000
3696 TEXT_0A 500 ete 3750    | 3694  TEXT_0A  1000  ete  7500  7500
3707 TEXT_0A 649.5 ete 4500  | 
3720 TEXT_0A 800 ete 3000    | 
3694 TEXT_0A 1000 ete 7500   |

filter rules:

  1. filter lines containging strings _0A and exclude lines containing DNTT _AF _XA based on column $3 <=100 assign to output column $6 fixed value 850
  2. based on column $3 > 100 && $3 <=200 within this number range assign to output column $6 calculated value $3*8.5
  3. based on column $3 > 200 && $3 <=1000 within this number range assign to output column $6 calculated value $3*7.5
  4. and filter it to expected output once compared if not equeal column $5 and $6 below

expected output:

3700  TEXT_0A  34    NA   NA    850
3721  TEXT_0A  55    ete  851   850
3701  TEXT_0A  100   NA   NA    850
3686  TEXT_0A  200   ete  822   1700
3716  TEXT_0A  200   ete  3000  1700
3688  TEXT_0A  250   ete  822   1875
3705  TEXT_0A  304   ete  2350  2280
3717  TEXT_0A  400   ete  3002  3000
3707  TEXT_0A  650   ete  4500  4875
3720  TEXT_0A  800   ete  3000  6000

partially working code:

awk '{if (( $2 ~ /_0A/ && !/DNTT|_AF|_XA/ && $3 <=100 )) \
printf ("%s %s %.0f %s %s %.0f\n",$1,$2,$3,$4,$5,850); \
else if (( $2 ~ /_0A/ && !/DNTT|_AF|_XA/ && $3 > 100 && $3 <=200 )) \
printf ("%s %s %.0f %s %s %.0f\n",$1,$2,$3,$4,$5,$3*8.5); \
else if (( $2 ~ /_0A/ && !/DNTT|_AF|_XA/ && $3 > 200 && $3 <=1000 )) \
printf ("%s %s %.0f %s %s %.0f\n",$1,$2,$3,$4,$5,$3*7.5)}' file2 | column -t
multiple-columns numbers compare awk
2 Answers

Reading the lines from 1 to 4, and converting this to awk:

gawk '$0~"_0A" && $0!~"DNTT"{ $6=(($3>100 && $3<=200)?($3*8.5):($3>200 && $3<=1000?($3*7.5):850)); if($5!=$6){ print }}' input.txt

output:

3700 TEXT_0A 34 NA NA 850
3721 TEXT_0A 55 ete 851 850
3701 TEXT_0A 100 NA NA 850
3686 TEXT_0A 200 ete 822 1700
3716 TEXT_0A 200 ete 3000 1700
3688 TEXT_0A 250 ete 822 1875
3705 TEXT_0A 304 ete 2350 2280
3717 TEXT_0A 400 ete 3002 3000
3707 TEXT_0A 649.5 ete 4500 4871.25
3720 TEXT_0A 800 ete 3000 6000

An if statement (if a=b then c else d;) can also be written as (a=b?c:d)

NOTE: You have to do the exclusion of _AF _XA, it was unclear to me, because of no seeing _XA. That's why i excluded DNTT.

user_7240390

You may use this awk:

cat calc.awk

$2 ~ /_0A/ && !/DNTT|_AF|_XA/ {
   if ( $3 <=100  )
      n = 850
   else if ( $3 > 100 && $3 <=200 )
      n = $3*8.5
   else if ( $3 > 200 && $3 <=1000 )
      n = $3*7.5
   if ($5 != n)   
      printf ("%s %s %.0f %s %s %.0f\n",$1,$2,$3,$4,$5,n)
}

Then use it as:

awk -f calc.awk file | column -t

3700  TEXT_0A  34   NA   NA    850
3721  TEXT_0A  55   ete  851   850
3701  TEXT_0A  100  NA   NA    850
3686  TEXT_0A  200  ete  822   1700
3716  TEXT_0A  200  ete  3000  1700
3688  TEXT_0A  250  ete  822   1875
3705  TEXT_0A  304  ete  2350  2280
3717  TEXT_0A  400  ete  3002  3000
3707  TEXT_0A  650  ete  4500  4871
3720  TEXT_0A  800  ete  3000  6000
user_5482250
Related