Generating Max value from column which depends upon the range of another column

Question

I have a large dataset as given below:

 35.7337  408  0.5 
 35.732  407  0.5 
 35.7301  406  0.5 
 35.7281  405  0.5 
 35.7259  404  0.5 
 35.7236  403  0.5 
 35.7212  402  0.5 
 35.7187  401  0.5 
 35.7162  400  0.5 
 35.7136  399  0.5 
 35.711  398  0.5 
 35.7085  397  0.5 
 35.706  396  0.5 
 35.7036  395  0.5 
 35.7013  394  0.5 
 35.6992  393  0.5

Now, I would like to get max value of column1; only among the values whose column2 is less than 400 and also Max value of column1 whose column2 is greater than 400. There are no negative values in column 2 and column1. column 2==400 is not required as the expected outcome shall be far away from $2==400.

So my desired output

35.7136 (second column value <400)

35.7337 (second column value > 400)

Answer 1

User csvsql from csvkit:

csvsql -HS -d' ' --query 'select max(a) from file where b<400' file

For tab separated content, use -t instead of -d' '

---

or awk:

awk '
    $2<400 && $1>max1{max1=$1}
    $2>400 && $1>max2{max2=$1}
    END {printf "%s (second column value < 400)\n%s (second column value > 400)\n",max1,max2}
' file

If column 1 can be negative, you have to initialize max1 and max2, because if it unset, max1 equals zero for $1>max1.

Answer 2

Tested and worked fine

command

awk 'BEGIN{sum=0}($2 < 400 && $1 > sum){sum=$1}END{print sum}' filename;awk 'BEGIN{sum=0}($2 > 400 && $1 > sum){sum=$1}END{print sum}' filename

output

35.7136
35.7337

Answer 3

Using Miller

$ mlr --nidx --repifs filter '$2 < 400' then stats1 -a max -f 1 data
35.713600

$ mlr --nidx --repifs filter '$2 > 400' then stats1 -a max -f 1 data
35.733700