5

I'm trying to transform some data to a percent of the total of each column, very similar to this thread except that I need to do this for every column: Calculate and divide by total with AWK

Data would like something like this (but more columns and rows):

ID     Sample1     Sample2      Sample3
One      10          0            5
Two      3           6            8
Three    3           4            7

And the desired output would look like this:

ID     Sample1     Sample2     Sample3
One     62.50        0.0        25.0
Two     18.75       60.0        40.0
Three   18.75       40.0        35.0   

The following works for a single column, but I want to do this for every column, except the first one.

gawk -F"\t" '{a[NR]=$1;x+=(b[NR]=$2)}END{while(++i<=NR)print a[i]"\t"100*b[i]/x}' file.txt 

Thanks a lot for any help you might be.

4

Not 100% the same output as you asked, but hopefully close enough:

function percent(value, total) {
    return sprintf("%.2f", 100 * value / total);
}
{
    label[NR] = $1
    for (i = 2; i <= NF; ++i) {
        sum[i] += col[i][NR] = $i
    }
}
END {
    title = label[1]
    for (i = 2; i <= length(col) + 1; ++i) {
        title = title "\t" col[i][1];
    }
    print title
    for (j = 2; j <= NR; ++j) {
        line = label[j]
        for (i = 2; i <= length(col) + 1; ++i) {
            line = line "\t" percent(col[i][j], sum[i]);
        }
        print line
    }
}

Produces output:

ID    Sample1 Sample2 Sample3
One   62.50   0.00    25.00
Two   18.75   60.00   40.00
Three 18.75   40.00   35.00

Run it with gawk -f script.awk file.txt

Of course, you could flatten the script to a single line, but I think it's better to keep it in a script file like this so it's easier to read and maintain.

A simpler and better version that works with BSD AWK too, not only GNU AWK:

function percent(value, total) {
    return sprintf("%.2f", 100 * value / total)
}
BEGIN { OFS = "\t" }
NR == 1 { gsub(/ +/, OFS); print; next }
{
    label[NR] = $1
    for (i = 2; i <= NF; ++i) {
        sum[i] += col[i, NR] = $i
    }
}
END {
    for (j = 2; j <= NR; ++j) {
        $1 = label[j]
        for (i = 2; i <= NF; ++i) {
            $i = percent(col[i, j], sum[i])
        }
        print
    }
}
  • Thanks janos, this indeed does work for this sample data. But I actually have more columns and rows, is it possible that this could be more general, i.e. do the exact same function independent of the number of columns? As I will need to run this on files that have different numbers of columns, sometimes 20, sometimes 200, etc. Again, thanks for your help. – jpalmer Dec 21 '14 at 16:54

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.