# How to calculate percent of every column?

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
``````

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
for (i = 2; i <= length(col) + 1; ++i) {
title = title "\t" col[i];
}
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. Dec 21 '14 at 16:54