How to sum many numbers inside 2D array using awk

Question

I need to sum numbers located in a file like this one:

       column1  column2 column3
 row1   a(1,1)   a(1,2)  a(1,3)
 row2   a(2,1)   a(2,2)  a(2,3)
 row3   a(3,1)   a(3,2)  a(3,3)
 row4   a(4,1)   a(4,2)  a(4,3)
 row5   a(5,1)   a(5,2)  a(5,3)
 row6   a(6,1)   a(6,2)  a(6,3)
 row7   a(7,1)   a(7,2)  a(7,3)
 row8   a(8,1)   a(8,2)  a(8,3)
 row9   a(9,1)   a(9,2)  a(9,3)
 row10  a(10,1)  a(10,2) a(10,3)
 row11  a(11,1)  a(11,2) a(11,3)
 row12  a(12,1)  a(12,2) a(12,3)


       column4  column5 column6
 row1  b(1,1)   b(1,2)  b(1,3)
 row2  b(2,1)   b(2,2)  b(2,3)
 row3  b(3,1)   b(3,2)  b(3,3)
 row4  b(4,1)   b(4,2)  b(4,3)
 row5  b(5,1)   b(5,2)  b(5,3)
 row6  b(6,1)   b(6,2)  b(6,3)
 row7  b(7,1)   b(7,2)  b(7,3)
 row8  b(8,1)   b(8,2)  b(8,3)
 row9  b(9,1)   b(9,2)  b(9,3)
 row10 b(10,1)  b(10,2) b(10,3)
 row11 b(11,1)  b(11,2) b(11,3)
 row12 b(12,1)  b(12,2) b(12,3)

the output should be like this:

  column1    a(1,1)+a(2,1)+a(5,1)+a(6,1)+a(7,1)+a(8,1)+a(11,1)      a(3,1)+a(4,1)+a(9,1)+a(10,1)+a(12,1)  
  column2    a(1,2)+a(2,2)+a(5,2)+a(6,2)+a(7,2)+a(8,2)+a(11,2)      a(3,2)+a(4,2)+a(9,2)+a(10,2)+a(12,2) 
  column3    a(1,3)+a(2,3)+a(5,3)+a(6,3)+a(7,3)+a(8,3)+a(11,3)      a(3,3)+a(4,3)+a(9,3)+a(10,3)+a(12,3)
  column4    b(1,1)+b(2,1)+b(5,1)+b(6,1)+b(7,1)+b(8,1)+b(11,1)      b(3,1)+b(4,1)+b(9,1)+b(10,1)+b(12,1)
  column5    b(1,2)+b(2,2)+b(5,2)+b(6,2)+b(7,2)+b(8,2)+b(11,2)      b(3,2)+b(4,2)+b(9,2)+b(10,2)+b(12,2)
  column6    b(1,3)+b(2,3)+b(5,3)+b(6,3)+b(7,3)+b(8,3)+b(11,3)      b(3,3)+b(4,3)+b(9,3)+b(10,3)+b(12,3)

I have a way to do something similar but which only its useful for 4 rows. I need o modify this script :

sed 's/row[1-9]//;/^$/d' file |    #elimina os rows
pr -2t -w 1000| 
awk 'NR==1{$1=$1; print; next} 
 !(NR%2){split($0,a); next}          
        {for(i=1;i<=NF;i++) $i+=a[i]}1' | 
 tr ' ' '\n' | 
 pr -3t 

Note to compute sum use

   $ tr -d 'ab(,)' < file > filenums

I think that it is necessary to do a modification in the awk section, but I do not know how to do that.

Answer 1

Here is a literal answer to your question, using just awk:

awk '
  /column4/ { c = 3 }   # add three for the second set of columns
  /row/ {
    row = substr($1,4)  # extract the row number
    col[1+c,row] = $2   # extract column 1 or 4, store in hash
    col[2+c,row] = $3   # extract column 2 or 5, store in hash
    col[3+c,row] = $4   # extract column 3 or 6, store in hash
  }
  END {
    split("1 2 5 6 7 8 11", out1) # create an array of first set of rows
    split("3 4 9 10 12", out2)    # create an array of second set of rows

    for (c = 1; c <= 6; c++) {
      out = sprintf("  column%d    %s", c, col[c,1]) # title and first row
      for (r = 2; r <= 7; r++) {
        out = out "+" col[c,out1[r]]                 # the rest of the first set
      }
      out = out "      " col[c,3]                    # spaces, 1st row of 2nd set
      for (r = 2; r <= 5; r++) {
        out = out "+" col[c,out2[r]]                 # the rest of the 2nd set
      }
      print out
    }
  }
' file

This outputs:

  column1    a(1,1)+a(2,1)+a(5,1)+a(6,1)+a(7,1)+a(8,1)+a(11,1)      a(3,1)+a(4,1)+a(9,1)+a(10,1)+a(12,1)
  column2    a(1,2)+a(2,2)+a(5,2)+a(6,2)+a(7,2)+a(8,2)+a(11,2)      a(3,2)+a(4,2)+a(9,2)+a(10,2)+a(12,2)
  column3    a(1,3)+a(2,3)+a(5,3)+a(6,3)+a(7,3)+a(8,3)+a(11,3)      a(3,3)+a(4,3)+a(9,3)+a(10,3)+a(12,3)
  column4    b(1,1)+b(2,1)+b(5,1)+b(6,1)+b(7,1)+b(8,1)+b(11,1)      b(3,1)+b(4,1)+b(9,1)+b(10,1)+b(12,1)
  column5    b(1,2)+b(2,2)+b(5,2)+b(6,2)+b(7,2)+b(8,2)+b(11,2)      b(3,2)+b(4,2)+b(9,2)+b(10,2)+b(12,2)
  column6    b(1,3)+b(2,3)+b(5,3)+b(6,3)+b(7,3)+b(8,3)+b(11,3)      b(3,3)+b(4,3)+b(9,3)+b(10,3)+b(12,3)

However, you also mentioned using tr -d 'ab(,)' to "compute sum" and I don't know what you mean there; this command will merely remove the characters you gave, so the first set of column1's output would become 11+21+51+61+71+81+111. Is that what you want? If so, wouldn't you also want to add those numbers together and print 407 in that case?

I just assumed a() and b() were functions and you'd be evaling them somewhere. If they're not, you might as well do that all in awk.

Answer 2

Given the irregular choices of which rows to accumulate, it's hard to device a more generic solution, and it ends up as cherry picking, like the following:

sed 's/row[0-9]*//;/^$/d' file | pr -2t -w 1000 | awk '
NR==1 {split($0,h);w=NF;c=".aabbaaaabbab";next;}
substr(c,NR,1)=="a" {for(i=1;i<=NF;i++)a[i]+=$i;next;}
{for(i=1;i<=NF;i++) b[i]+=$i;}
END {for(i=1;i<=w;i++)printf"%s %d %d\n",h[i],a[i],b[i];}'

The final layout is then also generated directly from awk. Obviously the cherry picking can be done in many different ways; here I've used a string of "a" and "b" to represent which rows add into which result column.

Note that the initial sed expression also needed a slight revision to cater for row numbers above 9.