sum columns based on header

Question

I have a tab-separated file1 like this (subset shown, real matrix 60x60000):

rowname   header1 header2 header3 header4 header5 header6 header7 header8
rowname1    1       1        10       2     3       1       10       2 
rowname2    0       7        200      3     37      1        2       1 

And another file2 like this:

header1,header2
header3
header4,header5
header6,header8
header7

I would like to sum columns specified in each line of file2:

rowname    header1 header3 header4 header6 header7 
rowname1    2        10        5     3       10
rowname2    7        200       40    2       2

So column1+column2, column3 as it is, column4+column5, column6+column8, column7 as it is...

Some columns must be summed, others not, and not always the columns to be summed are consecutive.

If the columns are summed, the header of the first column needs to remain in the output file.

I am wondering if you have a solution with awk. So far I just know how to store header entries:

awk '
NR==1 {
    for (i=1; i<=NF; i++) {
        f[$i] = i
    }

Answer 1

awk '
  FNR==NR{
    newhdr[FNR]=$1       # new header name
    newhdrcnt++          # number of new header names
    for (i=1;i<=NF;i++)
      hdrnames[FNR]=$0   # save new header names comma-separated
    next
  }
  FNR==1{
    # save column numbers for new header names in array hdrcols
    for (i=1;i<=newhdrcnt;i++){
      n=split(hdrnames[i], oldhdr, ",")
      for(j=1;j<=n;j++){
        for(k=2;k<=NF;k++){
          if ($(k) == oldhdr[j]){
            hdrcols[i]=(j==1 ? "" : hdrcols[i] ",") k;
            if (j==n) break
          }
        }
      }
    }
    # print header
    printf $1
    for (i=1;i<=newhdrcnt;i++)
      printf FS newhdr[i]
    printf ORS
    next
  }
  { # print data
    printf $1
    for (i=1;i<=newhdrcnt;i++){
      n=split(hdrcols[i], cols, ",")
      res=0
      for(j=1;j<=n;j++)
        res=res+$(cols[j])
      printf FS res
    }
    printf ORS
  }
' FS="," file2 FS="\t" file1

Output:

rowname header1 header3 header4 header6 header7
rowname1        2       10      5       3       10
rowname2        7       200     40      2       2

Answer 2

I think I hurt my brain, but using gawk

#!/usr/bin/awk -f

BEGIN{FS="\t"} 
  NR==FNR{H[NR]=","$0","; next}
  FNR==1{for (i=2;i<=NF; i++) T[i]=","$i","; printf $1; 
    for (i=1; i<=length(H); i++){split(H[i],sp,","); printf "\t"sp[2]}; print ""}
  FNR>1{delete S; for (i=2; i<=NF; i++) 
    {for (h in H) {if (H[h] ~ T[i]) S[h]+=$i}} printf $1; 
    for (i=1; i<=length(H); i++) printf "\t"S[i]; print""}

invoked as

./script file2 file1 | column -t

Output

rowname   header1  header3  header4  header6  header7
rowname1  2        10       5        3        10
rowname2  7        200      40       2        2

As per @paul_pedant comment to @freddy unspecified columns are dropped

walkthrough

Set up the FS

BEGIN{FS="\t"} 

Load the list of columns to be summed into the arrayH[] adding "," to each end (to avoid mismatches in the regex to follow)

  NR==FNR{H[NR]=","$0","; next}

If it's the first row of the data file then load the column headers in the array T[] adding "," to each end of the header name $i

  FNR==1{for (i=2;i<=NF; i++) T[i]=","$i","; 

...and print the titles as extracted as the first part of the sum specifying string

    printf $1; for (i=1; i<=length(H); i++){split(H[i],sp,","); printf "\t"sp[2]}; print ""}

For each data row, clear the summing array S[] and then iterate across the fields

  FNR>1{delete S; for (i=2; i<=NF; i++)

Checking each H[] for the field name T[] and if it exists then add the field value to the sum array indexed to the match S[h]

    {for (h in H) {if (H[h] ~ T[i]) S[h]+=$i}}

And at the end of each line, print

    printf $1; for (i=1; i<=length(H); i++) printf "\t"S[i]; print""}