Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

extracting row with max column value using awk or unix

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting extracting row with max column value using awk or unix
# 1  
Old 07-19-2011
extracting row with max column value using awk or unix
Hello,

Code:
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
 FLJ00075_abc_14406_16765       -0.173417
 FLJ00075_def_14406_16765       0.46909
 FLJ00075_abc_14406_16765       0.46909

Now based on 1st column 1st value before the 1st underscore (BC106081,FLJ00075) extract the max value from column 2

o/p
Code:
BC106081       69.5504
FLJ00075       0.46909

Thanks,
# 2  
Old 07-19-2011
Code:
awk ' (arr[substr($1,1,7)] < $2) {arr[substr($1,1,7)] =$2}
        END { for (i in arr {print i, arr[i]  }} '   inputfile

# 3  
Old 07-19-2011
error message
Thank you for the reply, but I have an error message


awk: (arr[substr($1,1,7)] < $2) {arr[substr($1,1,7)] =$2} END { for (i in arr {print i, arr[i]}}
# 4  
Old 07-20-2011
Quote:
Originally Posted by Diya123
Thank you for the reply, but I have an error message


awk: (arr[substr($1,1,7)] < $2) {arr[substr($1,1,7)] =$2} END { for (i in arr ){print i, arr[i]}}
There was a missing bracket in the for loop condition.
Another version would be..
Code:
 awk -F'[_ ]' '{a[$1]<$NF?a[$1]=$NF:""} END {for (i in a){print i,a[i]}}' inputfile

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Print a row with the max number in a column

Hello, I have this table: chr1_16857_17742 - chr1 17369 17436 "ENST00000619216.1"; "MIR6859-1"; - 67 chr1_16857_17742 - chr1 14404 29570 "ENST00000488147.1"; "WASH7P"; - 885 chr1_16857_18061 - chr1 ... (5 Replies)
Discussion started by: coppuca
5 Replies

2. Shell Programming and Scripting

Filter Row Based On Max Column Value After Group BY

Hello Team, Need your expertise on following: Here is the set of data: C1|4|C1SP1|A1|C1BP1|T1 C1|4|C1SP2|A1|C1BP2|T2 C2|3|C2SP1|A2|C2BP1|T2 C3|3|C3SP1|A3|C3BP1|T2 C2|2|C2SP2|A2|C2BP2|T1 I need to filter above date base on following two steps: 1. Group them by column 1 and 4 2.... (12 Replies)
Discussion started by: angshuman
12 Replies

3. UNIX for Dummies Questions & Answers

awk to print first row with forth column and last row with fifth column in each file

file with this content awk 'NR==1 {print $4} && NR==2 {print $5}' file The error is shown with syntax error; what can be done (4 Replies)
Discussion started by: cdfd123
4 Replies

4. Shell Programming and Scripting

awk, max value, array, row

Hello: I want to print out the entire row with max value in column 3 based on column 2. Input file is millions rows. test.dat: Contig1 lcl|1DL 111 155 265 27 Contig2 lcl|1DS 100 73 172 100 Contig3 lcl|1DL 140 698 837 140 Contig3 lcl|6DS 107 1488 1594... (1 Reply)
Discussion started by: yifangt
1 Replies

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

6. Shell Programming and Scripting

Subtracting each row from the first row in a single column file using awk

Hi Friends, I have a single column data like below. 1 2 3 4 5 I need the output like below. 0 1 2 3 4 where each row (including first row) subtracting from first row and the result should print below like the way shown in output file. Thanks Sid (11 Replies)
Discussion started by: ks_reddy
11 Replies

7. Shell Programming and Scripting

AWK script - extracting min and max values from selected lines

Hi guys! I'm new to scripting and I need to write a script in awk. Here is example of file on which I'm working ATOM 4688 HG1 PRO A 322 18.080 59.680 137.020 1.00 0.00 ATOM 4689 HG2 PRO A 322 18.850 61.220 137.010 1.00 0.00 ATOM 4690 CD ... (18 Replies)
Discussion started by: grincz
18 Replies

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

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