Need to filter the data on the basis of two column having many to one relationship using awk

Question

I have a big file having 50s columns and 100K of rows delimited by |. Now $2(col 2) has multiple type of $1(col 1) value which means col 2 will be repeated. So I have sorted the file. I need now to extract/filter the result file on the basis of below condition: $1 is column 1 $2 is column 2

there is one to many relation ship between $2 and $1

condition 1 : when $2 has both type of $1 (value of $1 for $2 is more than 8000 as well as less than 8000) then select the complete row where $1 < 8000 for the given $2(column 2)

condition 2: if $2 has only $1 >= 8000 then select the complete row where $1 is the smallest for the given $2(column 2) E.g: source file In the below example we have 3 types of $2 (1234,123 & 456) Now 1234 has 3 types of value in column 1 ($1) means greater as well as less than 8000. So we have selected the complete row for those having $1<8000.

For 123 and 465 we have value of column 1 only greater than 8000($1>80000) so we have selected the row which is latest (on the basis of higher the value of column 8) .

Sample File

  4000|1234||||||23
    5000|1234||||||40
    9000|1234||||||25
    10000|123|||||||21
    9000|123|||||||22
    22000|456|||||||27
    15000|456|||||||29

result file would have:

4000|1234||||||23
5000|1234||||||40

9000|123|||||||22

15000|456|||||||29

Please advice. Thanks in advance.

Answer 1

try (u being your file)

sort -n -t\| -k2 -k1 < u |
awk -F\| '$1 < 8000 { a[$2]++ ; print } 
          $1 >= 8000 { if ( !a[$2] && ( !e[$2] || e[$2]<$8 ))  {u[$2]=$0;e[$2]=$8;} ; } 
          END { for ( i in u ) print u[i] ;}'

gives

4000|1234||||||23
5000|1234||||||40
15000|456||||||29
9000|123||||||22

where

• -t\| and -F\| instruct sort and awk to use | as separator
• -k2 -k1 : sort by second, then fist field
• | in sort line should be last char in line
• $1 < 8000 { a[$2]++ ; print } if under 8000, print lines and remember $2 value
• $1 >= 8000 { ... } if above, store highest value

• END { for ( i in u ) print u[i] ;} upon exiting, dump all value

• you may need to re-sort.

• line 2 condition could be simplified (by having if's condition outside {} )

• some line in your test have 9 field.

note that command can be one lined

... | sort -n -t\| -k2 -k1  | awk -F\| '...'