Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Finding Maximum value in a column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Finding Maximum value in a column
# 1  
Old 07-25-2010
Question Finding Maximum value in a column
Hello,

I am trying to get a script to work which will find the maximum value of the fourth column and assign that value to all rows where the first three columns match.

For example:
Code:
1111 2222 AAAA 0.3
3333 4444 BBBB 0.7
1111 2222 AAAA 0.9
1111 2222 AAAA 0.5
3333 4444 BBBB 0.4

should give:
Code:
1111 2222 AAAA 0.9
3333 4444 BBBB 0.7
1111 2222 AAAA 0.9
1111 2222 AAAA 0.9
3333 4444 BBBB 0.7

or even better if it returns unique instances:
Code:
1111 2222 AAAA 0.9
3333 4444 BBBB 0.7

Thanks in advance!
# 2  
Old 07-25-2010
Hi,

try:

Code:
awk '{if ($4 > a[$1" "$2" "$3])a[$1" "$2" "$3]=$4}END{for (i in a) print i, a[i]}' file

Output:
Code:
3333 4444 BBBB 0.7
1111 2222 AAAA 0.9

HTH Chris
This User Gave Thanks to Christoph Spohr For This Post:
jaysean
# 3  
Old 07-25-2010
Works with the provided example (only if 3 first fields are fixed width)
Code:
$ cat infile
1111 2222 AAAA 0.3
3333 4444 BBBB 0.7
1111 2222 AAAA 0.9
1111 2222 AAAA 0.5
3333 4444 BBBB 0.4
$ sort -rn infile | uniq -w14
3333 4444 BBBB 0.7
1111 2222 AAAA 0.9

# 4  
Old 07-25-2010
Thanks Christoph, I think that your method will work for me. But there is a slight problem, unlike the sample input that I have given in the above example, the columns are actually tab separated. Also the third column can have spaces in some cases. The script fails in those cases. Can you please suggest a fix?
# 5  
Old 07-25-2010
Please give examples of the cases where the script failes, so i can test the script.
# 6  
Old 07-25-2010
Please see this:
There are 4 tab-delimited columns. 3rd column may have spaces in the name.

Code:
2655	96	IA	0.8179
2655	96	eugene	0.8144
2655	96	CPU	0.4275
2655	96	RMA	0.3407
2655	96	P Proc Tran CPU	0.3377
2655	96	ASUS K	0.2846
2655	96	MSI	0.1921
2655	96	LGVu	0.029
2655	96	IA	0.8373
2655	96	eugene	0.8338
2655	96	CPU	0.4469
2655	96	RMA	0.31
2655	96	P Proc Tran CPU	0.3571

Result should look like this:

Code:
2655	96	IA	0.8373
2655	96	eugene	0.8338
2655	96	CPU	0.4469
2655	96	RMA	0.3407
2655	96	P Proc Tran CPU	0.3571
2655	96	ASUS K	0.2846
2655	96	MSI	0.1921
2655	96	LGVu	0.029

# 7  
Old 07-25-2010
bash
Code:
#!/bin/bash
IFS="	"	# is a Tab
sort -r infile | while read A B C D
do	[ "$L" != "$A$B$C" ] && { L=$A$B$C; echo -e "$A\t$B\t$C\t$D"; }
done

if you need a sorted output
Code:
#!/bin/bash
IFS="	"	# is a Tab
sort -r infile | ( while read A B C D
do	[ "$L" != "$A$B$C" ] && { L=$A$B$C; echo -e "$A\t$B\t$C\t$D"; }
done ) | sort

awk (thanks to Christoph Spohr, i just added -F'\t' and replaced spaces with tabs)
Code:
awk -F'\t' '{if ($4 > a[$1"\t"$2"\t"$3])a[$1"\t"$2"\t"$3]=$4}END{for (i in a) print i"\t"a[i]}' file

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

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to find maximum and minimum from column and store in other column

Need your support for below. Please help to get required output If column 5 is INV then only consider column1 and take out duplicates/identical rows/values from column1 and then put minimum value of column6 in column7 and put maximum value in column 8 and then need to do subtract values of... (7 Replies)
Discussion started by: as7951
7 Replies

2. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2... (6 Replies)
Discussion started by: ejianu
6 Replies

3. Shell Programming and Scripting

Finding the maximum timestamp in a folder

I've the files in a directory in the following format having date +%Y%m%d%H YR_MNTH_2013061205 YR_MNTH_2013060107 and i need the latest file i.e; YR_MNTH_2013061205 to be moved to another folder #!/bin/ksh # Ksh 88 Version for test_time in YR* do --- done How can i achieve that !... (2 Replies)
Discussion started by: smile689
2 Replies

4. UNIX for Dummies Questions & Answers

Finding maximum occurrence value using awk

Hi everyone, I'm a new member at the forum I mistakenly posted this elsewhere too. I have a file like this: field 2 values are either 0 or negative. file test4: 100815 -20 118125 0 143616 0 154488 0 154488 0 154488 -6 196492 -5 196492 -9 196492 -7 27332 0... (5 Replies)
Discussion started by: meet77
5 Replies

5. Answers to Frequently Asked Questions

Finding maximum occurrence value using awk

Hi everyone, I'm a new member at the forum I have a file like this: field 2 values are either 0 or negative. file test4: 100815 -20 118125 0 143616 0 154488 0 154488 0 154488 -6 196492 -5 196492 -9 196492 -7 27332 0 29397 0 I would like to print a... (1 Reply)
Discussion started by: meet77
1 Replies

6. Shell Programming and Scripting

Finding minimum maximum and average

I am trying to find the minimum maximum and average from one file which has values Received message from https://www.demandmatrix.net/app/dm/xml] in milliseconds. Received message from https://www.demandmatrix.net/app/dm/xml] in milliseconds. Received message from... (5 Replies)
Discussion started by: aroragaurav.84
5 Replies

7. Shell Programming and Scripting

Maximum of a column

Hi, I have a file like this a 1 2 a 5 8 a 66 100 b 1 2 b 2 3 b 111 143 d 4 5 d 4 6 e 77 727 g 7 8 How can I extract the maximum of col3 respective to col1. I meant, the output will be a 100 b 143 d 6 (1 Reply)
Discussion started by: jacobs.smith
1 Replies

8. Homework & Coursework Questions

Find the Maximum value and average of a column

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: I am trying to complete a script which will allow me to find: a) reads a value from the keyboard. (ask the... (4 Replies)
Discussion started by: dstewie
4 Replies

9. Shell Programming and Scripting

for each different entry in column 1 extract maximum values from column 2 in unix/awk

Hello, I have 2 columns (1st column has multiple entries but the corresponding values in the column 2 may be the same or different.) however I want to extract unique values for each entry in column 1 by assigning the max value from column 2 SDF4 -0.211654 SDF4 0.978068 ... (1 Reply)
Discussion started by: Diya123
1 Replies

10. UNIX for Dummies Questions & Answers

Unix shell script for finding top ten files of maximum size

I need to write a Unix shell script which will list top 10 files in a directory tree on basis of size. i.e. first file should be the biggest in the whole directory and all its sub directories. Please suggest any ideas (10 Replies)
Discussion started by: abhilashnair
10 Replies
Login or Register to Ask a Question