0

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
    }
  • 2
    Welcome. If "not always the columns to be summed are consecutive", it is a good idea to provide input and expected output that illustrate that. You have used awk tag, so give it a try. Users are supposed to make their efforts before posting a question. – Quasímodo May 28 '20 at 12:12
  • 1
    What are the headers of the output file supposed to be? Do you literally want the string header followed by an increasing number or should we take the header names from the second file? – terdon May 28 '20 at 12:13
  • Unclear about the relation between e.g. header4 versus header_4, and any significance in e.g. key3 versus just associating cols 4 and 5. Also header_n has no key reference: should un-keyed columns be listed without totalling, or omitted? Can a column be totalled into more than one key? – Paul_Pedant May 28 '20 at 13:03
  • 1
    What do key1, key2, etc. have to do with it? – Ed Morton May 28 '20 at 13:36
  • I edit the post to make it more clear. – aec May 28 '20 at 13:45
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
  • I tested with the mapping header8,header4,header5 plus header6,header3,header1,header2, and this reorders the output columns as rowname header8 header6 and omits header7. Seems to me that any solution should at least preserve the total count in every row. – Paul_Pedant May 28 '20 at 19:15
  • I don't get your point. Then add a single mapping for header7 to preserve the total count per row, just like in the given example where header3 and header7 are left as they are. – Freddy May 28 '20 at 20:20
  • Somewhere back around edit 3 of the OP, the keys list only mentioned the columns to be summed, and the accompanying example showed columns that appeared by default. Given the real file has 60+ columns, it seems a burden that the user has to list every column name that he wants in the output, rather than just the ones he needs to be summated. Granted, the spec is not clear, and the opportunities for strange keys are numerous. – Paul_Pedant May 28 '20 at 22:10
  • @Freddy, it works as expected, amazing! thanks! – aec May 29 '20 at 11:17
1

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""}
  • Thanks for the answer, but when I run it appears this message: "column: line too long" – aec May 29 '20 at 11:07
  • Did you try it without invoking column -t? That is only to tidy up the spacing. – bu5hman May 29 '20 at 14:40
  • See here – bu5hman May 29 '20 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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