1

I have several variables (col1) which have 3 values (col 3,4,5) for some levels (col2)

mango123  L1  1 2 3
mango123  L2  4 5 6
squash378  L1  7 8 9
squash378   L5  10 12 13
squash378   L15  0 1 3
orange765   L1  11 1 3
mango123  L11  11 12 23

Given a list of pairwise variables, I would like to find the sum of the values for them, also if some variable is absent, then state that variable in the last column. The sum has to be done for each level available for the pairs.

Given an input of

mango123 squash378
squash378 orange765
pear546 lime436

I would like to have an output , with the last column saying if and which variable has no data for any level.

mango123 squash378 L1 8 10 12 None
mango123 squash378 L2 4 5 6 squash378
mango123 squash378 L11 11 12 23 squash378
mango123 squash378 L5 10 12 13 mango123
mango123 squash378 L15  0 1 3 mango123
squash378 orange765 L15  0 1 3 orange765
squash378 orange765 L1 18 9 12 None
squash378 orange765 L5 10 12 13 orange765
pear546 lime436 Na Na Na Na both

This is my attempt; I can't seem to reach anywhere with this.

awk 'NR=FNR{ a[$1]=$3; b[$1]=$4; c[$1]=$5; next } 
       if (($1 in a) && !($2 in a )) { $3=a[$1] ; $4 = b[$1]; $5=c[$1];$6=$2}
       if (!($1 in a) && ($2 in a )) { $3=a[$1] ; $4 = b[$1]; $5=c[$1];$6=$1}
       if (($1 in a) && ($2 in a )) { $3+=a[$1] ; $4+ = b[$1]; $5+=c[$1];$6="None"}
       if (!($1 in a) && !($2 in a )) { $3="Na" ; $4 = "Na"; $5="Na";$6="Both"}1' file1 file2

1 Answer 1

1
awk '# Process "0pairs_file". Build a "pair" array (keys only). 
     # Include leading an trailing space to unambiguously search of each field value via index().
     NR==FNR{ pair[NR]=" "$1" "$2" "; next }

     # Determine number of records in "pairs_file"0.           
     FNR==1{ pz=NR-1 } # "pz" size of "pair" array

     # Process "main_file"
     #   For each record in "main_file", 
     #   check if "$1"  is found in any of the "pair" elements  
     { for( pi=1; pi<=pz; pi++ ){
           p = pair[pi]                  # Note that "p" has a leading and triling space
           pix = index( p, " "$1" " )    # Get char indes (1-based position) of " "$1" " in "pair" element 

           # When $1 is found, pix > 0
           if( pix--){                   # "pix--" decrements pix after the test  
               pl=p$2" "; plevel[pl]
               # Build a "data" array for 
               #   presence of 1st and 2nd fruit, and for 
               #   the input fields $3 and $4 and $5 
               # Use "pl" plus a corresponding numeric suffixes for "data" keys 
               if( pix ){ data[pl 2]=1 } #  pix != 0 so it must be the 2nd fruit of the pair
               else     { data[pl 1]=1 } #  pix == 0 so it must be the 1st fruit of the pair 
               for( v=3; v<=5; v++ ) data[pl v]+=$v
           } 
       }
     }
     END{# process by pair
         for(pi=1; pi<=pz; pi++){
             p=pair[pi]
             na=1  # flag for when pair is N/A

             # Process by level
             for( plk in plevel ){
                 if( index( plk, p ) == 1 ){
                   na=0
                   printf "%s", substr(plk,2)
                   for( v=3; v<=5; v++ ) printf "%s ", data[plk v]
                   if( data[plk 1] != data[plk 2] ){
                     split(p,u)
                     if ( data[plk 1] ) printf "%s", u[2] 
                     else               printf "%s", u[1] 
                   } 
                   else { printf "None" }  
                   print "" 
                 }
             } 
             if( na ) print substr(p,2) "Na Na Na Na both"
         } 
     }' pairs_file main_file | column -t -s' '

If you want to sort by "pair" groups by "level" then replace the followint 2 lines

             # Process by level
             for( plk in plevel ){

with these lines instead:

             # Sort by level                
             plk=""; for( pl in plevel ) 
                 if( index( pl, p ) == 1 ) plk = plk sprintf( "%s\n", pl )
             cmd = "printf \"%s\" \""plk"\" | sort -V"
             ppli=0; while( ( cmd | getline pplk) > 0 )
                 pplkeys[++ppli] = pplk
             close(cmd)
             pplz=ppli  # "pplz" size of "pplkeys" array

             # Process by level
             for(ppli=1; ppli<=pplz; ppli++){
                 plk = pplkeys[ppli]

If you want tabulated output, pipe as follows:

 awk '...' pairs_file main_file | column -t -s' '

output: Sorted by Level (Pair groups), and Tabulated by column

mango123   squash378  L1   8   10  12  None
mango123   squash378  L2   4   5   6   squash378
mango123   squash378  L5   10  12  13  mango123
mango123   squash378  L11  11  12  23  squash378
mango123   squash378  L15  0   1   3   mango123
squash378  orange765  L1   18  9   12  None
squash378  orange765  L5   10  12  13  orange765
squash378  orange765  L15  0   1   3   orange765
pear546    lime436    Na   Na  Na  Na  both
1
  • perfect, thanks a lot..if you would add a few more comments on the steps, that would be very helpful,, thanks again.
    – Hia Sen
    Commented Aug 15, 2015 at 22:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .