2

I have a text file (with header line) called file.txt. I am trying to extract the lines equal to the maximum value of a specific column (I do not know what the max value is):

ID t1 q1 t2 q2 q3 
1 f 45 ex 1 45
2 r 47 tr 1 33
3 r 33 ex 2 44
4 f 44 s 0 55
5 e 32 ex 0 54
6 f 34 tr 2 46

I need to find the maximum value of column $5 and then print only the lines with column 5 equal to this number:

3 r 33 ex 2 44
6 f 34 tr 2 46

I think the following code works, but my file is huge and it takes a vary long time, so I am looking for a faster solution (maybe using sort?):

This is what I have for now:

Fist find the max value:

max=`awk '{print $5}' file.txt | sort -nr | sed -n 2p`

Then select the lines where column 5 is equal to this value:

awk 'NR>1' file.txt|while read LINE; do value=`echo $LINE|awk '{print $5}'`; if [ $value -eq $max ]; then echo $LINE >> test.txt; fi; done

4 Answers 4

2

One way of doing it would be to read the file once to get max and then again to print the relevant lines:

max=$(awk 'NR>1 && $5>max {max=$5}; END{print max}' file.txt) && 
 awk -v max="$max" '$5==max' file.txt 

Or, more concisely:

awk -v m="$(awk '(NR>1 && $5>m){m=$5};END{print m}' file.txt)"  '$5==m' file.txt 

The trick here is awk's -v flag which allows you to pass a variable to awk. In this case, I am first calculating the max value and then giving it to awk as variable max.

1
  • Thank you! All these work, I just accepted the first code since it was more in line with what I had tried so I understand it better. Thank a lot to all!!
    – user971102
    Sep 10, 2013 at 14:29
1

This is a fairly typical problem for which an idiomatic awk solution involving two passes over the file exists. In the first pass, identify the max value for $5 and in the second, extract records that contain that max value in $5. Here's a quick example.

awk 'NR == FNR && NR > 1{max = max < $5? $5: max; next}; $5 == max{print}' file.txt file.txt
2
  • Thank you! Both these methods work, I find this a bit hard to understand because I am a beginner…I am having a hard time making sense mostly of the piece inside brackets (and why do you repeat the input file twice?). But it does the job perfectly...
    – user971102
    Sep 10, 2013 at 14:32
  • @user971102, sure. Since terdon has done a great job of providing a clear explanation, you should accept his answer
    – iruvar
    Sep 10, 2013 at 14:43
1

If memory usage is not really an issue, a one-pass version in perl would probably be:

perl -ane 'END { $"=""; print "@res"; } if($F[4] =~ /^\d+/ and $F[4] > $max) { 
    $max = $F[4]; @res = (); } push @res, $_ if($F[4] =~ /^\d+$/ and $max == $F[4]);' infile

-n tells perl to process the infile one line at a time passing each line to the commands specified in -e. The -a tells perl to expand each line around the field separator (this defaults to space), and assign this to an array called @F. The upshot is for every line we can process it and use $F[n] to refer to the nth element of that line.

The perl itself:

END { $"=""; print "@res"; } # at the end of execution set the field separator to
                             # empty and print the contents of @res, which includes
                             # newlines when the matching rows were stored

if($F[4] =~ /^\d+/ and $F[4] > $max) {  # if the 5th element of the line is solely a
    $max = $F[4]; @res = ();            # number and it's greater than $max (which
}                                       # starts as undefined), set $max to this number
                                        # and empty the @res results array.

push @res, $_                                  # push this line to @res ...
     if($F[4] =~ /^\d+$/ and $max == $F[4]);   # IF the 5th element is solely a 
                                               # number and equal to $max

The logic is that $max starts as undefined and the @res array empty. The first time we find a number in column 5, we store this in $max and empty @res. This also has the side effect of emptying @res if we find a new maximum value in column 5 on following rows. As a separate check, if column 5 is equal to $max add this line to @res (store lines with the present maximum value). Repeat for all lines, and then the END { } block is executed which prints the contents of the @res results array without the leading space field separator normally set in $".

This could also probably be achieved in awk, but my awk-fu is not that great!

1

Sort the file with values in descending order, then print the lines from the top until the value changes.

sort -k 5n | awk 'NR==1 {max=$5} $5!=max {quit} {print}'

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .