Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I need to read a file, find the maximum value in each row, and print the ID of the row, the column the maximum value came from (sno#), and the value from the associated lc# column. If the maximum value occurs more than once, I need to include all matches. For example:

input.txt (tab-delimited)

Id  sno1  lc1  sno2  lc2    sno3  lc3  sno4 lc4
RM1  98   ss1   88   ms1    78    gs1   45  rs1
RM2  23   ss2   44   ms2    98    gs2   15  rs2
RM3  45   ss3   100  ms3    33    gs3   10  rs3
RM4  45   ss4   45   ms4    12    gs4   11  rs4

output.txt

RM1 ss1 sno1
RM2 gs2 sno3
RM3 ms3 sno2
RM4 ss4,ms4 sno1,sno2   
share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

input

$ cat input.txt
Id  sno1  lc1  sno2  lc2    sno3  lc3  sno4 lc4
RM1  98   ss1   88   ms1    78    gs1   45  rs1
RM2  23   ss2   44   ms2    98    gs2   15  rs2
RM3  45   ss3   100  ms3    33    gs3   10  rs3
RM4  45   ss4   45   ms4    12    gs4   11  rs4

awk script

$ cat row_max.awk
NR == 1 {
        for (i = 1; i <= NF; i++) headers[i] = $i;
        next
}

{
        # find maximum value
        max = $2
        for (i = 4; i <= NF; i += 2) if ($i > max) max = $i;
        # print row id
        printf "%s", $1
        # print all lc# column values (assuming the column 
        # after the max value sno# column)
        sep = OFS
        for (i = 2; i <= NF; i += 2) {
                if ($i == max) {
                        printf "%s%s", sep, $(i + 1);
                        sep = ","
                }
        }
        # print all column headers of the max value columns
        sep = OFS
        for (i = 2; i <= NF; i += 2) {
                if ($i == max) {
                        printf "%s%s", sep, headers[i];
                        sep = ","
                }
        }
        printf "\n"
}

output

$ awk -f row_max.awk input.txt
RM1 ss1 sno1
RM2 gs2 sno3
RM3 ms3 sno2
RM4 ss4,ms4 sno1,sno2
share|improve this answer
add comment
awk 'BEGIN{ getline; for(i=1;i<=NF;i++) hdr[i]=$i; max=-1 }
   { for(i=2; i<=NF; i+=2) 
        if($i==max) { lc=lc","$(i+1); sn=sn","hdr[i] }
        else if($i>max) { max=$i; lc=$(i+1); sn=hdr[i] }
     print $1" "lc" "sn; max=-1 }'  

Here it is with comments

awk 'BEGIN{ 
       # read line 1 column-headers to an array 
       getline; for(i=1;i<=NF;i++) hdr[i]=$i
       # set max to less than zero 
       max=-1
     }
     { # processes lines 2 -> last
       # check each "sn.." field 
       for(i=2; i<=NF; i+=2) 
           if($i==max) { # this cannot occurr for 1st itteration per line  
                # append "lc" field value and "sn" header 
                #  to previous "lc" and "sn", using ","   
                lc=lc","$(i+1); sn=sn","hdr[i] 
            }
            else if($i>max) { # this must occurr on 1st itteration per line 
                # save "lc" field value and "sn" header  
                max=$i; lc=$(i+1); sn=hdr[i]  
            }
       # print output for the current line 
       print $1" "lc" "sn; 
       # set max to less than zero; ready for next line
       # max=-1 
     }

Output

RM1 ss1 sno1
RM2 gs2 sno3
RM3 ms3 sno2
RM4 ss4,ms4 sno1,sno2
share|improve this answer
    
This approach is probably more efficient than mine and works given the current assumptions. I'd just throw in some comments for posterity. I hadn't considered the string-building approach. –  jw013 Aug 7 '12 at 20:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.