Awk, highest and lowest value of a column


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Awk, highest and lowest value of a column
# 1  
Old 07-10-2013
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
Code:
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:

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

e.g.
Code:
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
# 2  
Old 07-10-2013
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!)?
This User Gave Thanks to DGPickett For This Post:
# 3  
Old 07-10-2013
Try:
Code:
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

This User Gave Thanks to RudiC For This Post:
# 4  
Old 07-10-2013
@DGPickett:
Do you mean something like this:
Code:
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.
Code:
3 -1
3 1
4 -1
4 5
4 7
4 -7

Thanks!
# 5  
Old 07-10-2013
Here is another awk approach:
Code:
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

These 3 Users Gave Thanks to Yoda For This Post:
# 6  
Old 07-11-2013
Thanks a lot to all of you!
Especially Yodas post helps me out.
My script now looks like that:

Code:
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:
Code:
-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:

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

# 7  
Old 07-11-2013
This awk code might help:
Code:
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

This User Gave Thanks to Yoda For This Post:
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Print whole line with highest value from one column

Hi, I have a little issue right now. I have a file with 4 columns test0000002,10030010330,c_,218 test0000002,10030010330,d_,202 test0000002,10030010330,b_,193 test0000002,10030010020,c_,178 test0000002,10030010020,b_,170 test0000002,10030010330,a_,166 test0000002,10030010020,a_,151... (3 Replies)
Discussion started by: Ebk
3 Replies

2. AIX

Print whole line with highest value from one column

Hi, I have a little issue right now. I have a file with 4 columns test0000002,10030010330,c_,218 test0000002,10030010330,d_,202 test0000002,10030010330,b_,193 test0000002,10030010020,c_,178 test0000002,10030010020,b_,170 test0000002,10030010330,a_,166 test0000002,10030010020,a_,151... (2 Replies)
Discussion started by: Ebk
2 Replies

3. Shell Programming and Scripting

Sort from highest to lowest number

Hi Guys, I am looking for a way to sort the output below from the "Inuse" count from Highest to Lowest. Is it possible? Thanks in advance. user1 0.12 0.06 0 0.12 User Inuse Pin Pgsp Virtual Unit:... (4 Replies)
Discussion started by: jaapar
4 Replies

4. Shell Programming and Scripting

Find lines with matching column 1 value, retain only the one with highest value in column 2

I have a file like: I would like to find lines lines with duplicate values in column 1, and retain only one based on two conditions: 1) keep line with highest value in column 3, 2) if column 3 values are equal, retain the line with the highest value in column 4. Desired output: I was able to... (3 Replies)
Discussion started by: pathunkathunk
3 Replies

5. Shell Programming and Scripting

top 10 highest and lowest percentile from a column

Hi, I want to extract the the top 10 and lowest 10 percentile for a column of values. For example in column 2 for this file: JOE 1 JAY 5 JAM 6 JIL 8 JIB 4 JIH 3 JIG 2 JIT 7 JAM 9 MAR 10 The top 10 lowest will be: JOE 1 and the top 10 highest will be: (2 Replies)
Discussion started by: kylle345
2 Replies

6. Shell Programming and Scripting

Selecting lowest and highest values in columns 1 and 2, based on subsets in column 3

Hi, I have a file with the following columns: 361459 447394 CHL1 290282 290282 CHL1 361459 447394 CHL1 361459 447394 CHL1 178352861 178363529 AGA 178352861 178363529 AGA 178363657 178363657 AGA Essentially, using CHL1 as an example. For any line that has CHL1 in... (2 Replies)
Discussion started by: hubleo
2 Replies

7. UNIX for Dummies Questions & Answers

Print line with highest value from one column

Hi everyone, This is my first post, but I have already received a lot of help from the forums in the past. Thanks! I've searched the forums and my question is very similar to an earlier post entitled "Printing highest value from one column", which I am apparently not yet allowed to post a... (1 Reply)
Discussion started by: dliving3
1 Replies

8. Shell Programming and Scripting

trying to make an AWK code for ordering numbers in a column from least to highest

Hi all, I have a large column of numbers like 5.6789 2.4578 9.4678 13.5673 1.6589 ..... I am trying to make an awk code so that awk can easily go through the column and arrange the numbers from least to highest like 1.6589 2.4578 5.6789 ....... can anybody suggest, how can I do... (5 Replies)
Discussion started by: ananyob
5 Replies

9. UNIX for Dummies Questions & Answers

Printing highest value from one column

Hi, I have a file that looks like this: s6 98 s6 91 s6 56 s5 32 s5 10 s5 4 So what I want to do is print only the highest value for each value in the column: So the file will look like this: s6 98 s5 32 Thanks (4 Replies)
Discussion started by: phil_heath
4 Replies

10. Shell Programming and Scripting

Perl ? - How to find and print the lowest and highest numbers punched in by the user?

. . . . . . (3 Replies)
Discussion started by: some124one
3 Replies
Login or Register to Ask a Question