Awk, highest and lowest value of a column

Hi again!
I am still impressed how fast I get a solution for my topic "average specific column value awk" yesterday.
The associative arrays in awk work fine for me!
But now I have another question for the same project.

Now I have a list like this

1 -0.1
1 0
1 0.1
2 0
2 0.2
2 -0.2

How can I get the highest(H) and the lowest(h) value of the $2 column addicted to the values of
the $1 columns? My goal is to compute the difference between both and also to summarize all differences,
to get a scalar for an evaluation:

a,i = (H,i-h,i)/(sum(1-n)[(H,j -h,j)])

e.g.

a,1= (0.1-(-0.1))/( 0.1-(-0.1) + 0.2-(-0.2)) = 0.2/0.6 = 0.33333..

(In the end I want to evaluate the averages from my last post with these scalars.)

Thanks in advance!

Bj�rn

Just create two variables and load them with the first column value (min and max so far). On lines 2-$ check to see if you have a new min, and if not, do you have a new max (can't be both!)?

Try:

awk     '       {if (!L[$1] && $2 < L[$1]) L[$1]=$2
                 if (!H[$1] && $2 > H[$1]) H[$1]=$2}
         END    {for (x in L) print x, L[x], H[x]}
        ' file
1 -0.1 0.1
2 -0.2 0.2

@DGPickett:
Do you mean something like this:

awk' {min[$1]=$2 , max[$1]=$2
         if  ($2 >= max) max=$2
         else if ($2 <= min) min = $2}
END {for (x in min) print x, min[x], max[x]}
' list

It doesnt work, but I dont know how to do it better right now..

@RudiC:
I tried your code, but when I try lists like this I fail.

3 -1
3 1
4 -1
4 5
4 7
4 -7

Thanks!

Here is another awk approach:

awk '
        {
                if ( $1 in L )
                {
                        L[$1] = L[$1] > $2 ? $2 : L[$1]
                        H[$1] = H[$1] < $2 ? $2 : H[$1]
                }
                else
                {
                        L[$1] = $2
                        H[$1] = $2
                }
        }
        END {
                for ( k in L )
                        print k, L[k], H[k]
        }
' file

Thanks a lot to all of you!
Especially Yodas post helps me out.
My script now looks like that:

awk '
{
s[$1]+=$3;n[$1]++
if ( $1 in L )
{
L[$1] = L[$1] > $2 ? $2 : L[$1]
H[$1] = H[$1] < $2 ? $2 : H[$1]
}
else
{
L[$1] = $2
H[$1] = $2
}
}
END {
for ( k in L )
print k, (H[k] - L[k]), (SUM OF ALL (H[k] - L[k]), s[k]/n[k]
}
' list

My last problem is to summarize all differences.
==> (SUM OF ALL (H[k] - L[k])

I tried it before the END{} but
of course it summarized also intermediate steps.. so the value went to high..

A list like this:

-1 -1 10
-1 0 20
-1 1 30
0 -2 25
0 0 20
0 2 15
1 -3 21
1 0 20
1 3 19

should become this:

-1 2 12 20
0 4  12 20
1 6  12 20

This awk code might help:

awk '
        {
                if ( $1 in L )
                {
                        L[$1] = L[$1] > $2 ? $2 : L[$1]
                        H[$1] = H[$1] < $2 ? $2 : H[$1]
                }
                else
                {
                        L[$1] = $2
                        H[$1] = $2
                }
                S[$1] += $3
                N[$1]++
        }
        END {
                for ( k in L )
                {
                        SL += L[k]
                        SH += H[k]
                }
                for ( k in L )
                        print k, H[k] - L[k], SH - SL, S[k] / N[k]
        }
' file

That works very good, thank you Yoda!
To give you an impression, the list is a sample of a cfd simulation.
So I get the x , y -components of the profile and the velocity.
Now I can compute the mean value of the profile!!

The idea is that the line 1 value is both in and max, and the comparison is only on lines 2-$, but your code does both on all lines. Others used the null variable load as a signal of the first line. For best speed, you want the edge activities like that to be efficiently avoided on other lines, and awk is very fast at line number checks. Now, if the data is not on every line, then you need some way to deal with startup. One solution is to set max to -2gig (int min) and min to int max. The first couple lines should load real values. Just remember that while we want to avoid checking for both min and max on later lines, the first line is both min and max, so if it was assigned to max but not min, and was actually the min, or vice versa, it would be lost. I suppose the clean and clear way to treat the first different is to have a flag variable initially 0 but set to 1 after the first value is min and max.

Hi Yoda;

I wonder if I could use this script for a similar task.
Because, I need also to extract name present in a third column for the selected highest or lowest value.

a  1  aa
a  3  ab
a  5  ac
b  4  ba
b  2  bc
b  6  bb
c  1  ca
c  4  cb
c  3  cc

I wonder if I could create a file like this for example for highest values:

a  5  ac
b  6  bb
c  4  cb

Thanks

a) Please create a new thread for your new problem
b) Why don't you give it a shot yourself and then post it for problem analysis (should problems arise)? Hint: if max($1) found, keep $3 in an array as well.