awk based solution for summing the rows of multiple files

Question

I have several files which look like

file1.dat:

1 1
1 3 4
5 9 10 11

file2.dat:

3 0
8 9 0
3 9 2 4

typically with many more rows (with each row containing one less column than the previous row). I devised a hybrid bash/awk script to sum the rows of each file, e.g. using the above as an example:

out.dat:

4 1
9 12 4
8 18 12 15

The script works as expected but is quite slow. Well over 30 minutes for 100 files each with 10000 rows on my machine. The script seems to spend most of the time collecting the nth row from all the files. Is there a way to perform my operation simply by passing file*.dat to the awk command (see below)?

#!/bin/bash
ROWS=$1; shift
OUT_FILE=$1; shift
IN_FILE=("$@")

for i in `seq 1 1 ${ROWS}`; do
    # Get ith row from all input files
    for j in "${IN_FILE[@]}"; do
        tail -n+${i} ${j} | head -1 >> "temp.dat"
    done
    # Sum the rows 
    awk '{for (j=1;j<=NF;j++) a[j]+=$j} END {for (j in a) printf a[j] " "}' temp.dat >> ${OUT_FILE}
    echo >> ${OUT_FILE}
    rm temp.dat
done

Script usage based on the above example: ./RowSums.sh 3 out.dat file*.dat

Answer 1

Using any paste and any awk:

$ cat tst.sh
#!/usr/bin/env bash

paste "${@}" |
awk -v numFiles="$#" '{
    numFldsPerFile = NF / numFiles
    for ( outFldNr=1; outFldNr<=numFldsPerFile; outFldNr++ ) {
        sum = 0
        for ( fileNr=1; fileNr<=numFiles; fileNr++ ) {
            inFldNr = outFldNr + (fileNr - 1) * numFldsPerFile
            sum += $inFldNr
        }
        printf "%d%s", sum, (outFldNr<numFldsPerFile ? OFS : ORS)
    }
}'

$ ./tst.sh file1.dat file2.dat
4 1
9 12 4
8 18 12 15

Hopefully the descriptive variable names and explicit inFldNr calculation make it clear what that's doing.

Answer 2

The following awk script can pretty much replace the whole shell script:

# cat rowsum.awk
FNR <= rows {
    for (i = 1; i <= NF; i++)
        sum[FNR,i] += $i
}
END {
    for (i = 1; i <= rows; i++) {
        for (j = 1; j <= rows + 1; j++) {
            printf "%s ", sum[i, j]
        }
        printf "\n";
    }
}

Example:

% awk -f rowsum.awk -v rows=2 file*.dat
4 1
9 12 4
% awk -f rowsum.awk -v rows=3 file*.dat
4 1
9 12 4
8 18 12 15

This should be faster than going through all files again and again for each row.

Note: I'm assuming the nth row has n+1 columns. If not, save the number of columns for each row (e.g., cols[FNR]=NF) and use that in the final loop.

---

Another, more memory-efficient option could be to use paste to get all the relevant lines from each file together:

% paste -d '\n' file*.dat                                                                                                                                                
1 1
3 0
1 3 4
8 9 0
5 9 10 11
3 9 2 4

And then use awk on them:

# cat rowsum-paste.awk
NR > 1 && NF != prevNF {
    for (i = 1; i <= prevNF; i++) {
        printf "%s ", sum[i];
        sum[i] = 0
    };
    printf "\n"
}
{
    for (i = 1; i <= NF; i++)
        sum[i] += $i;
    prevNF = NF
}

% (paste -d '\n' file*.dat; echo) | awk -f rowsum-paste.awk
4 1 
9 12 4 
8 18 12 15 

This awk code sums lines until the number of fields changes, and then prints and resets the current sums. The extra echo is to change the number of fields at the end and trigger the final print, which can also be done with the printing code duplicated in an END block.

Answer 3

Using awk to output a tab-delimited data set consisting of the row and column indexes in the first two fields, and the data value at that position as the third field, for all files:

awk -v OFS='\t' '{ for (i = 1; i <= NF; ++i) print FNR, i, $i }' file*.dat

Sorting the data and using GNU datamash to do a crosstab operation on the above-generated data, summing the elements that occur at the same (row,column) index (the --filler '' option makes datamash output nothing in place of N/A for missing fields):

sort -n | datamash --filler '' crosstab 1,2 sum 3

Trimming off the added header on each column, and the initial column with the line numbers that datamash outputs:

tail -n +2 | cut -f 2-

All together with the output, given the two files in the question:

$ awk -v OFS='\t' '{ for (i = 1; i <= NF; ++i) print FNR, i, $i }' file*.dat | sort -n | datamash --filler '' crosstab 1,2 sum 3 | tail -n +2 | cut -f 2-
4       1
9       12      4
8       18      12      15

Benchmarking this and comparing it to the solution by muru, it is not quite four times slower (3.7) on the two tiny data files.