Finding max of a column grouping by the time


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Finding max of a column grouping by the time
# 1  
Old 03-17-2014
Finding max of a column grouping by the time

Hi,
I have the below text:
Code:
16:00 0.50
16:00 0.30
16:00 0.00
16:00 0.00
16:00 0.30
16:01 0.00
16:01 0.30

I want to find the max of the 2nd column grouping by the values in the 1st column using awk. So
Code:
16:00 0.50
16:01 0.30

I have tried
Code:
 awk -F" " '{ if(!($1 in max) || (max[$1]<$3)) max[$1]=$3 ; count[$1]++ } END { for(element in max) printf("%s %.2f\n", element, max[element] ) } ' file.txt

but all i get is
Code:
16:00 0.00
15:59 0.00
16:01 0.00

even
Code:
 cat file.txt | awk -F " " 'BEGIN{if(a[$1]<$2) { a[$1]=$3;}}END {for(i in a){print i " " a[i];}}'

does not work...

Any help much appreciated.
# 2  
Old 03-17-2014
A BEGIN rule is invoked even before the first record in a file is read. So never put any code over there which deals with input file records:
Code:
awk '
        {
                if ( A[$1] < $2 )
                        A[$1] = $2
        }
        END {
                for ( k in A )
                        print k, A[k]
        }
' file

This User Gave Thanks to Yoda For This Post:
# 3  
Old 03-17-2014
Hello,

Following is one more approach for same.

Code:
awk 'NR==FNR{a[$1]=$2;} ($1 in a){if(a[$1]<$2) {a[$1]=$2}} END{for(j in a){print j OFS a[j]}}' check_2nd_column_max_value12111 check_2nd_column_max_value12111

Output will be as follows.

Code:
16:00 0.50
16:01 0.30

NOTE: Where check_2nd_column_max_value12111 is the input file name.


Thanks,
R. Singh
# 4  
Old 03-18-2014
Code:
awk '{if($2 > a[$1]) {a[$1] = $2}} END {for(x in a) {print x, a[x]}}' file

sorry, I didn't see Yoda's solution...I have provided the same solution
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to grouping time and based on value with multiple pattern?

Hi All, need help... I have some log below : ### {"request_id":"e8395eb0-a8bd-11e9-b77b-d507ea5312aa","message":"when inquiry paybill 628524871 prevalidation cause : Invalid Transaction"} ### {"request_id":"043f2310-a8be-11e9-b57b-f9c7344998d7","message":"when inquiry paybill 62821615... (2 Replies)
Discussion started by: fajar_3t3
2 Replies

2. Shell Programming and Scripting

Get the MAX value out of a column

I've the following data set. I would like to look at the column 3 and only use the rows which has the max value for column 3 Can we use the awk or sed to achieve it. 10 2 10 100 11 2 20 100 12 2 30 100 13 2 30 100 14 ... (7 Replies)
Discussion started by: rudoraj
7 Replies

3. Shell Programming and Scripting

AWK script to create max value of 3rd column, grouping by first column

Hi, I need an awk script (or whatever shell-construct) that would take data like below and get the max value of 3 column, when grouping by the 1st column. clientname,day-of-month,max-users ----------------------------------- client1,20120610,5 client2,20120610,2 client3,20120610,7... (3 Replies)
Discussion started by: ckmehta
3 Replies

4. Shell Programming and Scripting

grouping based on first column

I do have a tab delimited file of the following format a_1 rt a_1 st_2 a_1 st_3 a_2 bt_2 a_2 st_er b_2 st_2 b_2 st_32 S_1 rt_8 S_1 rt_64 I want to cut short the above file and group the file based on the first column like below. a_1 rt st_2 st_3 a_2 bt_2 st_er b_2 st_2... (1 Reply)
Discussion started by: Lucky Ali
1 Replies

5. Shell Programming and Scripting

Finding max number in filename and opening it

Hi, I have files named as energy.dat.1 energy.dat.2 energy.dat.3 ... energy.dat.2342 I would like to find the file with maximum number in the filename (ex. energy.dat.2342) and open it. Would you please share your expertize in writing the script? Thanks in advance. (8 Replies)
Discussion started by: rpd25
8 Replies

6. Shell Programming and Scripting

finding max size

Hi I have a list of 2000 records with multiple entries and I want to get the max size for each entry ABC 1 ABC 2 ABC 3 ABC 4 DEF 1 DEF 2 DEF 2 DEF 2 DEF 2 ... (9 Replies)
Discussion started by: Diya123
9 Replies

7. Shell Programming and Scripting

loop in awk - column max for each column

Hello all, this should really be easy for you... I need AWK to print column maxima for each column of such input: Input: 1 2 3 1 2 1 1 3 2 1 1 2 Output should be: 2 2 3 3 This does the sum, but i need max instead: { for(i=1; i<=NF; i++) sum +=$i } END {for(i=1; i in sum;... (3 Replies)
Discussion started by: irrevocabile
3 Replies

8. Shell Programming and Scripting

Finding Max value from an array

Hi, I need to find max and second max element from an array. array contains 0338,0337,0339,0340,0401,0402,0403 (10 Replies)
Discussion started by: vjasai
10 Replies

9. Shell Programming and Scripting

Help in finding the max and min position

Hi, I have this input file called ttbitnres (which is catenated and sorted):- 8 0.4444 213 10 0.5555 342 11 0.5555 321 12 0.5555 231 13 0.4444 400 My code is at :- #!/bin/bash echo -e Version "\t" Number of Pass "\t" Number of Fail "\t" Rank Position "\t"Min "\t" Max... (1 Reply)
Discussion started by: ahjiefreak
1 Replies

10. Shell Programming and Scripting

Finding max value

My code below is supposed to find which company had the most business and then print the appropriate fields from another file which are the companies ID number and name. I can loop through awk and display all the total amount of business for each company but I need help in only printing out the... (1 Reply)
Discussion started by: Enigma23
1 Replies
Login or Register to Ask a Question