Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Getting max value of specific fields with awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Getting max value of specific fields with awk
# 1  
Old 02-23-2017
Getting max value of specific fields with awk
Hello All,

Here is am trying to get maximum value of third field depending on
first,second and fourth fields with awk command . delimeter is pipe(|) .
Code:
input

0221|09|14.25|aaa
0221|09|44.27|aaa
0221|09|44.33|aaa
0221|09|44.53|bbb
0221|09|34.32|bbb
0221|09|37.13|bbb
0221|09|33.68|ccc
0221|09|32.38|ccc
0221|09|33.48|ccc

Code:
output

0221|09|44.33|aaa
0221|09|44.53|bbb
0221|09|33.68|ccc

# 2  
Old 02-23-2017
What have you tried to solve this problem?

Are all of the records that have common values for fields 1, 2, & 4 always adjacent as in your sample input file?

If the records with common values for fields 1, 2, & 4 are always adjacent, does the order of lines in the output matter?
# 3  
Old 02-23-2017
Dear Don,
Please find my answer :
1) I have tried like this
Code:
egrep "0221|09|ccc" test.unl  | awk  -F "|"  'maxvalue=="" || $3 > maxvalue { maxvalue=$3 } END { print "ccc|"maxvalue }'

With this I am providing unique value for fields 1,2 and 4 .

2 ) Yes , all the fields have common value .

3 ) Output order does not matter . In output , line having max third field have to be displayed .
Code:
0221|09|aaa| ,44.33

# 4  
Old 02-23-2017
Try this Third field move to last, if required in order can be done

Code:
awk -F"|" '(!a[$1"|"$2"|"$4]){a[$1"|"$2"|"$4]=$3;next}{if(a[$1"|"$2"|"$4] < $3){a[$1"|"$2"|"$4]=$3}}END{for( i in a) { print i"|"a[i]}}' file_name

Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!
Last edited by RudiC; 02-23-2017 at 06:05 AM.. Reason: Added CODE tags.
This User Gave Thanks to raj_saini20 For This Post:
sayami00
# 5  
Old 02-23-2017
Try also
Code:
awk -F"|" '{if ($3 > MX[$1 FS $2 FS $4]) MX[$1 FS $2 FS $4] = $3} END {for (m in MX) print m FS MX[m]}' file
0221|09|ccc|33.68
0221|09|aaa|44.33
0221|09|bbb|44.53

OR, if you want to keep the field order in the output:
Code:
awk -F"|" '{if ($3 > MX[$1 FS $2 FS FS $4]) MX[$1 FS $2 FS FS $4] = $3} END {for (m in MX) {sub ("\|\|", FS MX[m] FS, m); print m}}' file
0221|09|33.68|ccc
0221|09|44.33|aaa
0221|09|44.53|bbb

Last edited by RudiC; 02-23-2017 at 06:14 AM..
This User Gave Thanks to RudiC For This Post:
sayami00
# 6  
Old 02-23-2017
Dear RudiC and Raj ,

Thanks a lot . Both code are working for me .
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How do you preserve the max length of a line after replacing a specific value?

I'm new with scripting and I can't seem to figure out what I should do to get the output that I want. My file content would be below. ID2|ID3 |ID4|ID5 | I'm trying to replace the field of ID3 which has a fixed length of 10 characters, for each entry I have placed on a .txt file... (5 Replies)
Discussion started by: asdfghjkl
5 Replies

2. UNIX for Beginners Questions & Answers

Grep or awk a unique and specific word across many fields

Hi there, I have data with similar structure as this: CHR START-SNP END-SNP REF ALT PATIENT1 PATIENT2 PATIENT3 PATIENT4 chr1 69511 69511 A G homo hetero homo hetero chr2 69513 69513 T C . hetero homo hetero chr3 69814 69814 G C . . homo homo chr4 69815 69815 C A hetero . . hetero is... (10 Replies)
Discussion started by: daashti
10 Replies

3. Shell Programming and Scripting

awk to combine all matching fields in input but only print line with largest value in specific field

In the below I am trying to use awk to match all the $13 values in input, which is tab-delimited, that are in $1 of gene which is just a single column of text. However only the line with the greatest $9 value in input needs to be printed. So in the example below all the MECP2 and LTBP1... (0 Replies)
Discussion started by: cmccabe
0 Replies

4. Shell Programming and Scripting

awk to output match and mismatch with count using specific fields

In the below awk I am trying output to one file those lines that match between $2,$3,$4 of file1 and file2 with the count in (). I am also trying to output those lines that are missing between $2,$3,$4 of file1 and file2 with the count of in () each. Both input files are tab-delimited, but the... (7 Replies)
Discussion started by: cmccabe
7 Replies

5. Shell Programming and Scripting

awk partial string match and add specific fields

Trying to combine strings that are a partial match to another in $1 (usually below it). If a match is found than the $2 value is added to the $2 value of the match and the $3 value is added to the $3 value of the match. I am not sure how to do this and need some expert help. Thank you :). file ... (2 Replies)
Discussion started by: cmccabe
2 Replies

6. UNIX for Dummies Questions & Answers

Read the file and generate specific fields by awk

Hi I need to generate these output file from the below input file. Output : customer_id as customer, zip as zip_cd, catg_cd as catg, Input: out.customer::in.customer_id; out.zip_cd::in.zip; out.catg::in.catg_cd; Could you please help me on this. Please use code tags next... (1 Reply)
Discussion started by: Murugesh
1 Replies

7. Shell Programming and Scripting

awk summing specific lines and fields

Hi I would like to know if it is possible to sum some specific fields. I have this x;x;x;x;x;x;x;x;467,390,611 Bytes;0.435291 GB;0.062247 GB;0.373045 GB;11,225;157 a;a;a;a;a;a;a;a;13,805,156,846 Bytes;12.857054 GB;1.838559 GB;11.018495 GB;151,063;18,933 b;b;b;b;b;b;b;b;232,797,478,723... (5 Replies)
Discussion started by: nakaedu
5 Replies

8. UNIX for Dummies Questions & Answers

Updating specific fields with awk using conditions

Can someone help me again, I think with awk? I have a file with 4 columns (pipe-delimited): I just want to convert the last field so that e1 is now 'message 1', e2 is 'message 2', e0 is 'message 3', etc. I don't want to change any other columns because the e0-e10 code may appear as part of a... (4 Replies)
Discussion started by: giannicello
4 Replies

9. Shell Programming and Scripting

awk sed cut? to rearrange random number of fields into 3 fields

I'm working on formatting some attendance data to meet a vendors requirements to upload to their system. With some help on the forums here, I have the data close. But they've since changed what they want. The vendor wants me to submit three fields to them. Field 1 is the studentid field,... (4 Replies)
Discussion started by: axo959
4 Replies
Login or Register to Ask a Question