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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK script to create max value of 3rd column, grouping by first column
# 1  
Old 06-12-2012
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.

Code:
clientname,day-of-month,max-users
-----------------------------------
client1,20120610,5
client2,20120610,2
client3,20120610,7
client4,20120610,9
client5,20120610,2
client1,20120611,8
client3,20120611,4
client4,20120611,4
client6,20120611,9

and get results like:
Code:
clientname,max-monthly-users
-----------------------------
client1,8
client2,2
client3,7
client4,9
client5,2
client6,9


The column-header lines are not required, but I thought would make example more concrete.

Thanks-In-Advance For any help!

Last edited by Scrutinizer; 06-12-2012 at 05:37 PM.. Reason: code tags
# 2  
Old 06-12-2012
awk

Hi,
Try this one,
Code:
awk 'BEGIN{FS=",";}{if(a[$1]<$3){a[$1]=$3;}}END{for(i in a){print i","a[i];}}' file|sort -t',' -k1,1

if you are using gawk, make you of asort function to sort the array a and ignore the sort cmd..
Cheers,
Ranga:-)

Last edited by rangarasan; 06-12-2012 at 06:02 PM.. Reason: typo
This User Gave Thanks to rangarasan For This Post:
# 3  
Old 06-13-2012
Try this ,
Code:
sort -t"," -k1,1 -k3,3nr Filename | awk -F"," '!a[$1]++'

This User Gave Thanks to pravin27 For This Post:
# 4  
Old 06-13-2012
Another try..
Code:
awk -F, 'a[$1]<$3{a[$1]=$3} END {for (i in a){print i FS a[i]}}' file | sort

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

UNIX script to compare 3rd column value with first column and display

Hello Team, My source data (INput) is like below EPIC1 router EPIC2 Targetdefinition Exp1 Expres rtr1 Router SQL SrcQual Exp1 Expres rtr1 Router EPIC1 Targetdefinition My output like SQL SrcQual Exp1 Expres Exp1 Expres rtr1 Router rtr1 Router EPIC1 Targetdefinition... (5 Replies)
Discussion started by: sekhar.lsb
5 Replies

2. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

3. Shell Programming and Scripting

awk script to append suffix to column when column has duplicated values

Please help me to get required output for both scenario 1 and scenario 2 and need separate code for both scenario 1 and scenario 2 Scenario 1 i need to do below changes only when column1 is CR and column3 has duplicates rows/values. This inputfile can contain 100 of this duplicated rows of... (1 Reply)
Discussion started by: as7951
1 Replies

4. Shell Programming and Scripting

Solution for replacement of 4th column with 3rd column in a file using awk/sed preserving delimters

input "A","B","C,D","E","F" "S","T","U,V","W","X" "AA","BB","CC,DD","EEEE","FFF" required output: "A","B","C,D","C,D","F" "S", T","U,V","U,V","X" "AA","BB","CC,DD","CC,DD","FFF" tried using awk but double quotes not preserving for every field. any help to solve this is much... (5 Replies)
Discussion started by: khblts
5 Replies

5. Shell Programming and Scripting

Finding max of a column grouping by the time

Hi, I have the below text: 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 16:00 0.50 16:01 0.30 I have tried (3 Replies)
Discussion started by: satishrao
3 Replies

6. UNIX for Dummies Questions & Answers

Search word in 3rd column and move it to next column (4th)

Hi, I have a file with +/- 13000 lines and 4 column. I need to search the 3rd column for a word that begins with "SAP-" and move/skip it to the next column (4th). Because the 3rd column need to stay empty. Thanks in advance.:) 89653 36891 OTR-60 SAP-2 89653 36892 OTR-10 SAP-2... (2 Replies)
Discussion started by: AK47
2 Replies

7. Shell Programming and Scripting

Join and awk max column

Hi Friends, I have a file1 with 3400 records that are tab separated and I have a file2 with 6220 records. I want to merge both these files. I tried using join file1 and file2 after sorting. But, the records should be (3400*6220 = 21148000). Instead, I get only around 11133567. Is there anything... (13 Replies)
Discussion started by: jacobs.smith
13 Replies

8. Shell Programming and Scripting

extracting row with max column value using awk or unix

Hello, BC106081_abc_128240811_128241377 7.96301 BC106081_abc_128240811_128241377 39.322 BC106081_cde_128240811_128241377 1.98628 BC106081_def_128240811_128241377 -2.44492 BC106081_abc_128240811_128241377 69.5504 FLJ00075_xyz_14406_16765 -0.173417 ... (3 Replies)
Discussion started by: Diya123
3 Replies

9. 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

10. Shell Programming and Scripting

sed/awk to retrieve max year in column

I am trying to retrieve that max 'year' in a text file that is delimited by tilde (~). It is the second column and the values may be in Char format (double quoted) and have duplicate values. Please help. (4 Replies)
Discussion started by: CKT_newbie88
4 Replies
Login or Register to Ask a Question

Featured Tech Videos