0

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

2
  • Why is the second row of the output 9 12 0? Shouldn't it be 9 12 4? Do all files have the same number of rows?
    – muru
    May 3 at 5:08
  • Yes 9 12 4. And yes the dimensions of all the files are equal.
    – algae
    May 3 at 5:09

3 Answers 3

4

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.

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.

8
  • You could probably use rows == "" || FNR <= rows as the first condition, or remove it completely. I'm assuming that the ROWS variable in their code is a byproduct of their way of thinking about the problem rather than a necessary part of the solution.
    – Kusalananda
    May 3 at 5:52
  • @Kusalananda Yes I've removed it. The script should have set ROWS=$(wc -l) or something.
    – algae
    May 3 at 5:53
  • 1
    @algae Since all the fields on each line have to be summed with the corresponding elements in the other files, you can't really print each line as you go, as the sum would not be "done" yet. Also, the amount of memory taken should be no more than the amount needed to store one of the files in RAM.
    – Kusalananda
    May 3 at 6:14
  • 2
    @algae FNR is the line number in the current file. The fields of that line has to be added to the corresponding entries in each of the other files. When FNR increments, that just means you have started working on the next line from the same file.
    – Kusalananda
    May 3 at 6:21
  • 1
    @algae I added an alternative that should use much less memory
    – muru
    May 3 at 8:12
2

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.

3
  • Nice. datamash requires tab delimited files? Why the tab
    – algae
    May 3 at 7:04
  • 1
    @algae It assumes tab-delimited input, but you can use any delimiter and tell it what to use with other options. Tabs are nice because it's also the default delimiter for other tools, such as cut. Tabs are false whitespaces, so any tool, such as sort, would be able to use them with no issue.
    – Kusalananda
    May 3 at 7:08
  • Thanks. I asked because initially, changing '\t' to ' ' didn't work. Good to know about tab default with these tools. Never really seen tabs used for data files.
    – algae
    May 3 at 10:13

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.