Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to find maximum and minimum from column and store in other column
# 1  
Old 05-07-2018
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 column 7 from column 8 and then plus one to result and store final result in column 9 and column 11. Also need to ommit/delete column(column5 and column 6) in output file

For example in input file 27AAACH1458C1ZZ is 8 times, but need to consider only 7 rows as one row in column 5 has value REVERSED which we need to be discard(need to consider rows where column 5 has values "INV").

So among 7 rows, in column 6 minimum value will be "IN27201800023182" and maxmimum value will be "IN27201800024289", so need to put minimum in column7 and maximum in column8.

Thereafter need to consider last 8 digits from both column(7 and 8) and subtract column 7 from column 8. Then need to add/plus 1 to subtraction result and store in column 9 and column 11. Need to skip/delete column 5 and column 6 in output file.

Code:
    a|b|c|d|e|f|g|h|i|j|k
    27AAAC8C1ZZ|042018|||INV|IN27201800023521|||||
    27AAAC8C1ZZ|042018|||INV|IN27201800024289|||||
    27AAAC8C1ZZ|042018|||INV|IN27201800023356|||||
    27AAAC8C1ZZ|032018|||REVERSED|IN27201800022431|||||
    27AAAC8C1ZZ|042018|||INV|IN27201800023400|||||
    27AAAC8C1ZZ|042018|||INV|IN27201800023182|||||

output should be

Code:
a|b|c|d|g|h|i|j|k
27AAAC8C1ZZ|042018|||N27201800023182|IN27201800024289|1108||1108

Code im trying to find maximum and minimum value, but getting wrong output.
Code:
 awk 'BEGIN{OFS=FS="|"} {if ($5=="INV"){ getline; min=$6;max=$6}}
          {(min>$6)?min=$7:"";(max>$6)?"":max=$8}
     END{print min, max}' input.txt

Wrong output
Code:
IN546474DGDGD00|

Last edited by as7951; 05-07-2018 at 07:52 AM..
# 2  
Old 05-07-2018
Did you read and consider the comments in your other recent thread(s)? A specification that doesn't need reading thrice or even more often helps people help you.
Why the leading white space in the input, and why is that removed in the output?
Are the key values ($1) in contiguous order, or are they scattered through the file? Is that order to be retained?
Which $2 value to retain; should they differ?
How would you define a minimum and / or maximum of the last 8 chars of IN546474DGDGD00, or their difference?
What to do with the values that have just one record (the last two in the sample)?
Why assign values to fields 7 and 8, and then remove fields 5 and 6 resulting in the new fields being 5 and 6?
Last edited by RudiC; 05-07-2018 at 07:00 AM..
# 3  
Old 05-07-2018
Hi Rudic,

I posted my response long back, but now i found out it did not went.
so posting again..
please help and let me know in case have any query


There are no leading spaces, i think it came in post when ..sorry
Values in ($1) are scattered through file.
Consider the last eight digit in column 6 to find max and min.
No need to consider last 2 records as one dnt have INV in column 5 in input and also both are unique and no duplicate, we dnt need to print those in output file.
we required, max and min in seperate column, so want them in separate cloumn.
# 4  
Old 05-07-2018
Did you read my questions, and relate them to your sample data?
WHAT are the last 8 digits in IN546474DGDGD00, and their min, max, and difference?
HOW to handle $2?
# 5  
Old 05-07-2018
Hi Rudic,

Have edited my post and removed last two rows as they were creating confusion.
also there nothing to be done with $2, i will hard code $2 value..later..

We need to check column 5 and need to consider value of $1 only when we have values as "INV" in column 5 and this will take care $2 as well.
# 6  
Old 05-07-2018
Try
Code:
awk -F\| -vOFS=\| '
NR==1           {sub ("\\"FS $5 "\\"FS $6 "\\"FS, FS)
                 print
                 next
                }
$5 == "INV"     {if (MIN[$1] > $6 || MIN[$1] == "") MIN[$1] = $6
                 if (MAX[$1] < $6 || MAX[$1] == "") MAX[$1] = $6
                 F2[$1] = $2
                }
END             {for (m in MIN)         {MN = substr (MIN[m], length(MIN[m])-7)
                                         MX = substr (MAX[m], length(MAX[m])-7)
                                         TMP = MX - MN + 1
                                         if (TMP != 1) print m, F2[m], _, _, MIN[m], MAX[m], TMP, _, TMP
                                        }
                }
' file
    a|b|c|d|g|h|i|j|k
    27AAAC8C1ZZ|042018|||IN27201800023182|IN27201800024289|1108||1108

This User Gave Thanks to RudiC For This Post:
jiam912
# 7  
Old 05-07-2018
Hi Rudic,

Just one more help,
need to handle one more thing
if value in $1 is unique then need to show output as like below:
In $5 and $6 in ouput file have to make min and max value same as in input file in $6 in input and then do subtraction and then add/plus 1 to result to make it 1

a|b|c|d|e|f|g|h|i|j|k
Code:
27AAAC8C1ZZ|042018|||INV|IN27201800023521|||||
    27AAAC8C1ZZ|042018|||INV|IN27201800024289|||||
    27AAAC8C1ZZ|042018|||INV|IN27201800023356|||||
    27AAAC8C1ZZ|032018|||REVERSED|IN27201800022431|||||
    27AAAC8C1ZZ|042018|||INV|IN27201800023400|||||
    27AAAC8C1ZZ|042018|||INV|IN27201800023182|||||
    27PPPC5C1PP|042018|||INV|IN27201565757575|||||

output

a|b|c|d|g|h|i|j|k
Code:
27AAAC8C1ZZ|042018|||N27201800023182|IN27201800024289|1108||1108
27PPPC5C1PP|042018|||IN27201565757575|IN27201565757575|1||1|

---------- Post updated at 11:30 AM ---------- Previous update was at 06:39 AM ----------

Hi Rudic,

Sorry for bothering you.
Can you please send me some web link to study and learn these kind of awk scenarios.
It would be really great help.
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

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

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

4. Shell Programming and Scripting

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. 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... (2 Replies)
Discussion started by: yifangt
2 Replies

5. UNIX for Dummies Questions & Answers

Using awk to find and use the maximum value in column of data

Dear Unix Gurus, I have a text file with multiple columns, for example, see sample.txt below 0 1 301 1 4 250 2 6 140 3 2 610 7 1 180I want to find the maximum in, say, column 3, normalise all the values to this maximum value (to 4 decimal places) and spit everything into a new... (2 Replies)
Discussion started by: tintin72
2 Replies

6. Shell Programming and Scripting

Extract minimum/maximum using awk

From the below table I want to print highest value and lowest value using awk script. aaa 55 66 96 77 ggg 22 96 77 23 ddd 74 58 18 3 kkk 45 89 47 92 zzz 34 58 89 92 Thanks, Green edit by bakunin: it sure is not news to you that you should use CODE-tags, no? And that we do not want such... (3 Replies)
Discussion started by: gwgreen1
3 Replies

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

8. UNIX for Dummies Questions & Answers

[Solved] Using awk to obtain minimum of each column (ignoring zeros)

Hi, I have a wide and long dataset which looks as follows: 0 3 4 2 3 0 2 2 ... 3 2 4 0 2 2 2 3 ... 0 3 4 2 0 4 4 4 ... 3 0 4 2 2 4 2 4 ... .... I would like to obtain the minimum of each column (ignoring zero values) so the output would look like: 3 2 4 2 2 2 2 2 I have the... (3 Replies)
Discussion started by: kasan0
3 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

find expression with awk in only one column, and if it fits, print whole column

Hi. How do I find an expression with awk in only one column, and if it fits, then print that whole column. 1 apple oranges 2 bannanas pears 3 cats dogs 4 hesaid shesaid echo "which number:" read NUMBER (user inputs number 2 for this example) awk " /$NUMBER/ {field to search is field... (2 Replies)
Discussion started by: glev2005
2 Replies
Login or Register to Ask a Question