Find minimum and maximum values based on column with associative array


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Find minimum and maximum values based on column with associative array
# 1  
Old 10-18-2016
Find minimum and maximum values based on column with associative array

Hello,
I need to find out the minimum and maximum values based on specific column, and then print out the entire row with the max value.
Code:
Infile.txt:
scf6 290173 290416 . + X_047241 T_00113118-1
scf6 290491 290957 . + X_047241 T_00113118-2
scf6 290898 290957 . + X_047241 T_00113119-3
scf6 291091 291903 . + X_047241 T_00113119-4
scf9 1281205 1281978 . + X_056323 T_00138418-1
scf9 1282596 1283255 . + X_056323 T_00138418-2
scf25 1360501 1360647 . - X_025043 T_00053809-1
scf25 1360774 1360836 . - X_025043 T_00053809-2
scf25 1361113 1361233 . - X_025043 T_00053809-3
scf29 943891 944500 . - X_029078 T_00064858-1
scf29 944629 944708 . - X_029078 T_00064858-2
scf29 944814 944859 . - X_029078 T_00064858-3
scf34 319914 320609 . - X_034278 T_00078927-1
scf34 320892 322061 . - X_034278 T_00078927-2
scf34 322151 322374 . - X_034278 T_00078927-3
scf34 319914 322061 . - X_034278 T_00078928-1
scf34 322151 322529 . - X_034278 T_00078928-2

Code:
Expected output:
290173 291903 scf6 291091 291903 . + X_047241 T_00113119-4
1281205 1283255 scf9 1282596 1283255 . + X_056323 T_00138418-2
1360501 1361233 scf25 1361113 1361233 . - X_025043 T_00053809-3
943891 944859 scf29 944814 944859 . - X_029078 T_00064858-3
319914 322529 scf34 322151 322529 . - X_034278 T_00078928-2

I have tried this one:
Code:
 awk '{if ($2 < min[$6]) min[$6]=$2; if ($3 > max[$6]) {max[$6]=$3; entry[$6]=$0;} next }END{for (i in max) {print max[i], entry[i]}}'  infile.txt

and I only get
Code:
291903 scf6 291091 291903 . + X_047241 T_00113119-4
322529 scf34 322151 322529 . - X_034278 T_00078928-2
1283255 scf9 1282596 1283255 . + X_056323 T_00138418-2
1361233 scf25 1361113 1361233 . - X_025043 T_00053809-3
944859 scf29 944814 944859 . - X_029078 T_00064858-3

Note column 6 is the common field for keys of the associative array I was trying. The problem is with the min[$6], which is always set to 0 by default. Any help is greatly appreciated!

Last edited by joeyg; 10-18-2016 at 02:54 PM..
# 2  
Old 10-18-2016
How about
Code:
awk '{if ($2 < min[$6] || !min[$6]) min[$6]=$2; if ($3 > max[$6]) {max[$6]=$3; entry[$6]=$0;} next }END{for (i in max) {print min[i], max[i], entry[i]}}'  file
290173 291903 scf6 291091 291903 . + X_047241 T_00113119-4
319914 322529 scf34 322151 322529 . - X_034278 T_00078928-2
1281205 1283255 scf9 1282596 1283255 . + X_056323 T_00138418-2
1360501 1361233 scf25 1361113 1361233 . - X_025043 T_00053809-3
943891 944859 scf29 944814 944859 . - X_029078 T_00064858-3

This User Gave Thanks to RudiC For This Post:
# 3  
Old 10-18-2016
Thanks!
That's a quick fix and what I need!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Find the minimum value of the column with respect to other column

Hi All, I would like get the minimum value in the certain column with respect to other column. For example, I have a text file like this. ATOM 1 QSS SPH S 0 -2.790 -1.180 -2.282 2.28 2.28 ATOM 1 QSS SPH S 1 -2.915 -1.024 -2.032 2.31 2.31 ATOM 1 ... (4 Replies)
Discussion started by: bala06
4 Replies

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

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

4. Shell Programming and Scripting

awk to select lines with maximum value of each record based on column value

Hello, I want to get the maximum value of each record separated by empty line based on the 3rd column of each row within each record? Input: A1 chr5D 634 7 82 707 A2 chr5D 637 6 82 713 A3 chr5D 637 5 82 713 A4 chr5D 626 1 82 704... (4 Replies)
Discussion started by: yifangt
4 Replies

5. Shell Programming and Scripting

Output minimum and maximum values for replicates ID

Hi All I hope that someone could help me! I have an input file like this, with 4 colum(ID, feature1, start, end): a x 1 5 b x 3 10 b x 4 9 b x 5 16 c x 5 9 c x 4 8 And my output file should be like this: a x 1 5 b x 3 16 c x 4 9 What I would like to do is to output for each ID... (2 Replies)
Discussion started by: giuliangiuseppe
2 Replies

6. Shell Programming and Scripting

Print minimum and maximum values using awk

Can I print the minimum and maximum values of values in first 4 columns ? input 3038669 3038743 3037800 3038400 m101c 3218627 3218709 3217600 3219800 m290 ............. output 3037800 3038743 m101c 3217600 3219800 m290 (2 Replies)
Discussion started by: quincyjones
2 Replies

7. Shell Programming and Scripting

AWK, Perl or Shell? Unique strings and their maximum values from 3 column data file

I have a file containing data like so: 2012-01-02 GREEN 4 2012-01-02 GREEN 6 2012-01-02 GREEN 7 2012-01-02 BLUE 4 2012-01-02 BLUE 3 2012-01-02 GREEN 4 2012-01-02 RED 4 2012-01-02 RED 8 2012-01-02 GREEN 4 2012-01-02 YELLOW 5 2012-01-02 YELLOW 2 I can't always predict what the... (4 Replies)
Discussion started by: rich@ardz
4 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. Shell Programming and Scripting

Problem with lookup values on AWK associative array

I'm at wits end with this issue and my troubleshooting leads me to believe it is a problem with the file formatting of the array referenced by my script: awk -F, '{if (NR==FNR) {a=$4","$3","$2}\ else {print a "," $0}}' WBTSassignments1.txt RNCalarms.tmp On the WBTSassignments1.txt file... (2 Replies)
Discussion started by: JasonHamm
2 Replies
Login or Register to Ask a Question

Featured Tech Videos