1

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.

2
  • 1
    Show us your best attempt and the problem you faced. – Julie Pelletier May 29 '16 at 6:06
  • i am struggling with arrays. Please advice for the logic. – api1411 May 29 '16 at 6:20
3

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\| '...'
4
  • Thanks Archemar....!! condition 1 is running fine but condition 2. In condition 2: we need to fetch that row which is having maximum $8 value. – api1411 May 29 '16 at 10:05
  • +1. BTW, ending a line with | isn't a bashism - it works in every shell i've tried it in (zsh, dash, ksh, busybox sh, and bash). Also, | doesn't have to be the last character on a line, it only has to be the last non- [[:blank:]] character on a line, any number of whitespace chars can follow it. – cas May 29 '16 at 10:47
  • Great!!!Archemar, Just one more advice!! if i need to print if $2 has only $1 >= 8000 then select only those rows having maximum value of $8.(modified condition 2) – api1411 May 29 '16 at 11:23
  • @api1411 ... then remove first awk line and ref to a[$2]. – Archemar May 29 '16 at 12:37

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.