Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk - Print column number that return value comes from

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk - Print column number that return value comes from
# 1  
Old 02-23-2016
awk - Print column number that return value comes from
I have the following awk script that I am using to find the max value in the file and print results.

Code:
awk 'BEGIN  {MAX=-1E100} {for (x=2; x<=NF; x++) if ($x>MAX) {MAX = $x;  C1 = $1}} END {print substr(C1,1,11), substr(C1,13,4), substr(C1,18,2), MAX}' ABC*


Input (ABC*)
Code:
ABC00053862Y2001M08    3    4    5    6    7    0    1    2    3    4    0   -9   -9   -9   -9   -9    1    2    3    4   -9   -9   -9   -9   -9   -9   -9   -9   -9   -9   -9


Output
Code:
ABC00053862 2001 08 7


I need to also return the column (-1) that the "7" (MAX) came from. In this case it would be Column 5 (Column 6-1). Columns 2-32 are days so that is why I need to subtract 1 from the actual column number.

Is there a way to incorporate that function in with the existing script?

Expected output
Code:
ABC00053862 2001 08 5 7

Last edited by ncwxpanther; 02-23-2016 at 09:24 AM..
# 2  
Old 02-23-2016
Hello ncwxpanther,

Could you please try following and let me know if this helps you.
Code:
awk '{split($1, array,"Y|M");A=array[1] OFS array[2] OFS array[3];for(i=2;i<=NF;i++){MAX=MAX>$i?MAX:$i};print A OFS MAX;MAX=""}'  Input_file

Output will be as follows.
Code:
ABC00053862 2001 08 7

NOTE: Assuming your all data(specially first field) is of same pattern like sample Input_file which you have shown to us into your post above.

Thanks,
R. Singh
Last edited by RavinderSingh13; 02-23-2016 at 09:46 AM.. Reason: Added a note for user now.
# 3  
Old 02-23-2016
The output is not what I expected.

Your Output

Code:
USW00096407 1998 04PRCPEQ00 7
USW00096407 1998 05PRCPEQ00 6
USW00096407 1998 06PRCPEQ00 9
USW00096407 1998 07PRCPEQ00 12
USW00096407 1998 08PRCPEQ00 3
USW00096407 1998 09PRCPEQ00 11

Expected Output
Code:
USW00096407 2015 09 8 11


Keep in mind that there are thousands of input files (ABC*). Inside each input file there are thousands of lines.

Example of input file names
Code:
USW00096407
USW00096406
USW00096404

Example of contents of one of the input files.
Code:
USW00096407Y2015M08PRCPEQ00   -9   -9   -9   -9   -9   -9   -9   -9   -9   -9   -9   -9   -9    1    2   0    0    0    0    0    0    1    2    0    0    0    1    0    1    2    3
USW00096407Y2015M09PRCPEQ00    4    5    6    7    8    9   10   11    0    0    0    0    0    1    2    0    1    2    0    1    2    3    4    0    0    0    0    1    2    0   -9
USW00096407Y2015M10PRCPEQ00    0    0    0    0    0    1    2    3    4    5    0    1    0    1    0    1    2    0    0    1    2    3    4    0    0   -9   -9   -9    1    2    0

# 4  
Old 02-23-2016
Hello ncwxpanther,

Request you to please show us complete Input_file(sample file) as in your very first post you haven't showed us the complete data which you have done in 3rd post, following may helpful for you.
Code:
awk '{if($0 !~ /^$/){split($1, array,"Y|M|PRCPEQ");A=array[1] OFS array[2] OFS array[3] OFS array[3]-1;for(i=2;i<=NF;i++){MAX=MAX>$i?MAX:$i};print A OFS MAX;MAX=""}}'  Input_file

Here as you have mentioned there are so may files you could use following then.
Code:
for file in ABC*
do
     awk '{if($0 !~ /^$/){split($1, array,"Y|M|PRCPEQ");A=array[1] OFS array[2] OFS array[3] OFS array[3]-1;for(i=2;i<=NF;i++){MAX=MAX>$i?MAX:$i};print A OFS MAX;MAX=""}}'  $file
done

If you wanted to add file's name to output then you could use following too.
Code:
for file in ABC*
do
     awk 'BEGIN{print "File which is getting executed is: " FILENAME} {if($0 !~ /^$/){split($1, array,"Y|M|PRCPEQ");A=array[1] OFS array[2] OFS array[3] OFS array[3]-1;for(i=2;i<=NF;i++){MAX=MAX>$i?MAX:$i};print A OFS MAX;MAX=""}}'  $file
done

If you have any queries or above solutions doesn't meet your requirement then request you to please show us more sample inputs with more specifically sample outputs with complete terms and conditions etc in detailed manner.

Thanks,
R. Singh
# 5  
Old 02-23-2016
My original code prints the max in each unique file.

Code:
USW00003013 2009 03 43
USW00003016 2003 07 21
USW00003017 2006 01 27
USW00003024 2000 09 42


Your code appears to print the max of each line within each file. It is also not printing the correct column numbers.


Code:
USW00003048 2015 12 11 25
USW00003048 2016 01 0 13
USW00003048 2016 02 1 19
File which is getting executed is:
USW00003054 2004 02 1 0
USW00003054 2004 03 2 10
USW00003054 2004 04 3 5

# 6  
Old 02-23-2016
How about
Code:
awk '
BEGIN   {MAX=-1E100
        }
        {for (x=2; x<=NF; x++) if ($x>MAX)      {MAX = $x
                                                 C1  = $1
                                                 COL = x
                                                }
        }
END     {print substr(C1,1,11), substr(C1,13,4), substr(C1,18,2), MAX, COL-1
        }
' file

This User Gave Thanks to RudiC For This Post:
ncwxpanther
# 7  
Old 02-23-2016
Thanks Rudi. I was able to get that to work as expected.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

2. Shell Programming and Scripting

awk command to return only field with a number in it

What is an awk command to print only fields with a number in it?? Input file....... S,S,S,S,S,S,S,S,S 001S,S,S,S,S,S,S,S,S 00219S,23S,24S,43S,47S,S,S,S,S 00319S,10S,23S,41S,43S,47S,S,S,S 00423S,41S,43S,46S,47S,S,S,S,S 00510S,23S,24S,43S,46S,S,S,S,S 00610S,23S,43S,46S,47S,S,S,S,S... (2 Replies)
Discussion started by: garethsays
2 Replies

3. Shell Programming and Scripting

awk to print column number while ignoring alpha characters

I have the following script that will print column 4 ("25") when column 1 contains "123". However, I need to ignore the alpha characters that are contained in the input file. If I were to ignore the characters my output would be column 3. What is the best way to print my column of interest... (3 Replies)
Discussion started by: ncwxpanther
3 Replies

4. Shell Programming and Scripting

awk to find number in a field then print the line and the number

Hi I want to use awk to match where field 3 contains a number within string - then print the line and just the number as a new field. The source file is pipe delimited and looks something like 1|net|ABC Letr1|1530||| 1|net|EXP_1040 ABC|1121||| 1|net|EXP_TG1224|1122||| 1|net|R_North|1123|||... (5 Replies)
Discussion started by: Mudshark
5 Replies

5. Shell Programming and Scripting

Help with compare two column and print out column with smallest number

Input file : 5 20 500 2 20 41 41 0 23 1 Desired output : 5 2 20 0 1 By comparing column 1 and 2 in each line, I hope can print out the column with smallest number. I did try the following code, but it don't look good :( (2 Replies)
Discussion started by: perl_beginner
2 Replies

6. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

7. Shell Programming and Scripting

awk return number of entries

I have a string with the following information and want to return the number of entries enclosed by <> in awk <stdin>: N = 441 <0.369000018/0.569000006> <0.369000018/0.569000006> <0/1> (7 Replies)
Discussion started by: kristinu
7 Replies

8. Shell Programming and Scripting

AWK print number of records, divide this number

I would like to print the number of records of 2 files, and divide the two numbers awk '{print NR}' file1 > output1 awk '{print NR}' file2 > output2 paste output1 output2 > output awl '{print $1/$2}' output > output_2 is there a faster way? (8 Replies)
Discussion started by: programmerc
8 Replies

9. Solaris

awk - Print variable number of colums from a starting column

Hi guys, I usualy am able to google awk stuff but I can't find it so far and there are so many awking gurus here that I will give it a shot. I want to print $1;$3;"$5 up to the $NF". In other words, I can have 1000 colums, but need to have $5 up to the end. I started with the idea of... (2 Replies)
Discussion started by: plmachiavel
2 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