Sum of alternate values in a column using either sed or nawk

awksedtext processing

foo.txt:

1  10     11
2   20     22
3   30     32
4   40     42
5   50     52
6   60     62
7   70     72
8   80     82
9   90     92
10  100   110

Desired Out.txt :

1  10     11
2   20     22
3   30     32
4   40     42
5   50     52
6   60     62
7   70     72
8   80     82
9   90     92
10  100   110
25  250   275   #Line 11
30  300   330   #Line 12
45  550   595  #Line 13

Line 11 is a sum of alternate lines starting from line 1 in 1st,2nd and 3rd columns, Line 12 is sum of alternate lines starting from line 2 in 1st, 2nd and 3rd columns. Line 13 is sum of columns in line 11 and line 12. I am using KSH and Solaris 5.10, The values in the input file may not be sequential and will not be more than 3 digit integers. My input file will only have 10 lines. How to achieve this?

Best Answer

$ awk -v OFS='\t' '{for (i=1;i<=NF;i++) {s[2-NR%2,i]+=$i;s[3,i]+=$i;};$1=$1;print} END{for (n=1;n<=3;n++) print s[n,1],s[n,2],s[n,3]}' foo.txt 
1       10      11
2       20      22
3       30      32
4       40      42
5       50      52
6       60      62
7       70      72
8       80      82
9       90      92
10      100     110
25      250     259
30      300     318
55      550     577

The above was tested on GNU awk and linux.

How it works

  • -v OFS='\t'

    Optional: this sets the output to tab-separated.

  • {for (i=1;i<=NF;i++) {s[2-NR%2,i]+=$i;s[3,i]+=$i;}; $1=$1; print}

    This loops through each column, adding its values to the array s. For each column i, even numbered rows are added to s[2,i] while odd-numbered rows are added to s[1,i]. Column i on all rows is added to s[3,i].

    This row is then printed.

  • END{for (n=1;n<=3;n++) print s[n,1],s[n,2],s[n,3]}

    After we have reached the end of the file, the results are printed, first for the odd-numbered lines (n=1), then the even-numbered lines (n=2), then the total (n=3).

Sun/Solaris

I have had multiple reports that the default awk on Sun/Solaris has issues. Please try:

nawk -v OFS='\t' '{for (i=1;i<=NF;i++) {s[2-NR%2,i]+=$i;s[3,i]+=$i;};$1=$1;print} END{for (n=1;n<=3;n++) print s[n,1],s[n,2],s[n,3]}' foo.txt

Or:

/usr/xpg4/bin/awk -v OFS='\t' '{for (i=1;i<=NF;i++) {s[2-NR%2,i]+=$i;s[3,i]+=$i;};$1=$1;print} END{for (n=1;n<=3;n++) print s[n,1],s[n,2],s[n,3]}' foo.txt

Or:

/usr/xpg6/bin/awk -v OFS='\t' '{for (i=1;i<=NF;i++) {s[2-NR%2,i]+=$i;s[3,i]+=$i;};$1=$1;print} END{for (n=1;n<=3;n++) print s[n,1],s[n,2],s[n,3]}' foo.txt
Related Question