awk sum column based on matching header pattern

user_3377241 0
Viewed 160

I am having a file with ~10,000 columns and ~20,000 rows in the following format

Id  A_1 A_2 A_3 B_2 B_5 C_1
T1  0   1   1   6   1   0
T2  1   1   1   0   0   1
T3  2   0   3   1   1   5
T4  1   1   1   2   3   1

In the header row, 1st column is the id. From 2nd column onward are the sample name in the following format sampleName_batch#. Now, I want to add all the values for the id's based on the sampleName and have the sampleName and the sum up value in the output. My expected output is

Id  A   B   C
T1  2   7   0
T2  3   0   1
T3  5   2   5
T4  3   5   1

I have came across this answer https://unix.stackexchange.com/questions/569615/combine-columns-in-one-file-by-matching-header but i dont know how to modify the whole header row.

Thanks

awk
1 Answers

I am trying to edit solution mentioned in OP's post from cross site, its a bit tweak in solution and all lines are same as it is from it. I am NO where near in knowledge like "THE Ed Morton" in awk, so humbly taking his permission(I hope he is ok with it) trying to edit his great solution from cross site, could you please try following.

awk '
NR==1 {
    for (inFldNr=2; inFldNr<=NF; inFldNr++) {
        sub(/_.*/,"",$inFldNr)
        fldName = $inFldNr
        if ( !(fldName in fldName2outFldNr) ) {
            outFldNr2name[++numOutFlds] = fldName
            fldName2outFldNr[fldName] = numOutFlds
        }
        outFldNr = fldName2outFldNr[fldName]
        out2inFldNrs[outFldNr,++numInFlds[outFldNr]] = inFldNr
    }

    printf "%s%s", $1, OFS
    for (outFldNr=1; outFldNr<=numOutFlds; outFldNr++) {
        outFldName = outFldNr2name[outFldNr]
        printf "%s%s", outFldName, (outFldNr<numOutFlds ? OFS : ORS)
    }
    next
}
{
    printf "%s%s", $1, OFS
    for (outFldNr=1; outFldNr<=numOutFlds; outFldNr++) {
        sum = 0
        for (inFldIdx=1; inFldIdx<=numInFlds[outFldNr]; inFldIdx++) {
            inFldNr = out2inFldNrs[outFldNr,inFldIdx]
            sum += $inFldNr
        }
        printf "%s%s", sum, (outFldNr<numOutFlds ? OFS : ORS)
    }
}
'  Input_file

What is added in already existing Ed's code:

  1. Add a sub function to substitute everything after _ in first line(header).
  2. Removed \t as delimiter since OP's samples are NOT tab delimited.
user_58665800
Related