0

I have a tsv file that contains some values. I want the sum of each column and total number of values and percentage values. Eg:

file.tsv contains

x     1     1     0     1     x     x     1     x

1     1     x     0     0     x     1     x     0

0     0     x     1     1     x     1     1     x

0     x     x     x     1     x     x     x     1

(tsv file contain more than 4 rows)

result:

        x     1     1     0     1     x     x     1     x

        1     1     x     0     0     x     1     x     0

        0     0     x     1     1     x     1     1     x

        0     x     x     x     1     x     x     x     1

sum     1     2     1     1     3     0     2     2     1

total   3     3     1     3     4     0     2     2     2

percent 33    66    100   33    75    0     100   100   50

I have used a sed script to calculate number of one and zero but that did not append to the end of file. And in the result sum represents the addition of '1' present in the column, total is the number of zero and one in the column ignoring the value of x(non-numeric character).

  • Are the blank lines actually present in your file? What are the percentages? Do you want to just ignore the x values? – terdon Apr 15 '16 at 8:45
  • @terdon percentage is the number of 1 in that column and total is number of 0 and 1 in the column,yes i want to ignore value of x. – Pradyumna Sagar Apr 15 '16 at 9:12
  • Please edit your question to add extra information. It is hard to read and easy to miss in the comments. Also, comments can be deleted with no warning. When you edit, also clarify if the empty lines are really part of your file. – terdon Apr 15 '16 at 9:20
2

You can do this with awk, keeping track of numeric versus non-numeric columns and summarizing at the end:

#!/usr/bin/awk -f
BEGIN {
    width = 0;
}
{   
    if (width < NF)
        width = NF;
    for (n = 1; n <= NF; ++n) {
        if ( $n ~ /^[0-9]+$/ ) {
            number[n] += $n;
            total[n] += 1;
        } else {
            others[n] += $n;
        }
    }
    print;
    next;
}
END {
    printf "sum";
    for (n = 1; n <= width; ++n) {
        printf "%5d", number[n];
    }
    printf "\n";
    printf "total";
    for (n = 1; n <= width; ++n) {
        printf "%5d", total[n];
    }
    printf "\n";
    printf "percent";
    for (n = 1; n <= width; ++n) {
        if ( total[n] != 0) {
            printf "%5d", 100 * number[n] / total[n];
        } else {
            printf "%5d", 0;
        }
    }
    printf "\n";
}
  • Thanks for the reply. The script just gave the result what I was in need of, Thanks again. – Pradyumna Sagar Apr 15 '16 at 9:25

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.