sum values in a file if keys match another file and based on conditional

Question

I have two files.

file1

NC_000001.11_NM_001005484.2 69270   234 69037
NC_000001.11_NM_001005484.2 69511   475 69037
NC_000001.11_NM_001005484.2 69761   725 69037
NC_000001.11_NM_001385640.1 942155  20  942136

file2

NC_000001.11_NM_001005484.2 65565   9
NC_000001.11_NM_001005484.2 69037   969
NC_000001.11_NM_001385640.1 924432  517
NC_000001.11_NM_001385640.1 925922  92
NC_000001.11_NM_001385640.1 930155  182
NC_000001.11_NM_001385640.1 931039  51
NC_000001.11_NM_001385640.1 935772  125
NC_000001.11_NM_001385640.1 939040  90
NC_000001.11_NM_001385640.1 939272  141
NC_000001.11_NM_001385640.1 941144  163
NC_000001.11_NM_001385640.1 942136  116
NC_000001.11_NM_001385640.1 942410  79
NC_000001.11_NM_001385640.1 942559  500
NC_000001.11_NM_001385640.1 943253  125
NC_000001.11_NM_001385640.1 943698  111
NC_000001.11_NM_001385640.1 943908  243

If column 1 in file 1 matches column 1 in file 2 AND the value in column 2 of file 2 is less than the value of column 4 in file 1, I would like to sum column 3 of file 2 over the matching keys. Then I would like to print each line from file1 with its corresponding sum from file2.

Expected output

NC_000001.11_NM_001005484.2 69270   234 69037  9
NC_000001.11_NM_001005484.2 69511   475 69037  9
NC_000001.11_NM_001005484.2 69761   725 69037  9
NC_000001.11_NM_001385640.1 942155  20  942136 1361

I'm not experienced enough with awk or python to get this right but have tinkered around for a couple of days now. I would appreciate any help with this.

Answer 1

For this task, you need to store the value of $4 for each key ($1) of line 1 (in the script below, I'll use an array called keys for this, with $1 as the key and $4 as the value).

You also need to store each actual line in another array (I'll use lines for this, with the line-number as the key and the entire line as the value). Note that this can consume large amounts of memory if file1 is huge....but, unless it's enormous, is probably not a problem on any modern system with many gigabytes of RAM. If does happen to be too large to fit into RAM, the script would have to be modified to iterate through the first file a second time rather than store it in the lines array.

Finally, you also need to store the key ($1) that corresponds to each line-number (I'll use an array called linekeys for this, with the line-number as index and the key, $1, as the value). BTW, if the first file was so huge you have to process it a second time, then this array wouldn't be needed, as you can just get it from $1 as you process each line again. Technically, this array isn't really needed at all as you could split() it from lines[l] in the END{} block when you need it, but it's easier to do it this way - trading a bit more memory usage for simpler code and possibly faster run-time.

awk '# process the first file
     NR==FNR {
       keys[$1] = $4;      # remember the value of $4 for the key ($1)
       lines[FNR] = $0;    # store the entire line
       linekeys[FNR] = $1; # remember the key for that line
       next
     };

     # process any remaining file(s)
     $1 in keys {
       if ($2 < keys[$1]) {
         sum[$1]+=$3
       };
     };

     # All files have been processed, so print the output
     END {
       for (l in lines) {
         print lines[l], sum[linekeys[l]]
       }
     }' file1 file2
NC_000001.11_NM_001005484.2 69270   234 69037 9
NC_000001.11_NM_001005484.2 69511   475 69037 9
NC_000001.11_NM_001005484.2 69761   725 69037 9
NC_000001.11_NM_001385640.1 942155  20  942136 1361

BTW, I'd recommend saving this in either a sh script as is (except using "$@" as the argument to awk instead of file1 file2 so you can specify the input lines on the command-line when you run it (e.g. as bash scriptname.sh file1 file2, OR saving it as an awk script (remove the awk command and the single-quotes and the filenames) so you can run it as awk -f scriptname.awk file1 file2. With an appropriate #! line as the first line of the script, you can also make it executable so you can run it directly without having to type the interpreter name on the commmand-line when you run it.

Or, if you really insist, you could squeeze the entire script onto one line - semi-colons have been left in place where needed between statements to allow for that. I wouldn't recommend it, though, as the shell command line is a terrible place to be editing scripts, even ones as short as this, and even with convenience features (like Ctrl-XCtrl-E in bash) to edit the current line in vi or your preferred editor.

Answer 2

With GNU awk for arrays of arrays:

$ cat tst.awk
NR==FNR {
    addends[$1][$2][$3]
    next
}
$1 in addends {
    sum = 0
    for ( val in addends[$1] ) {
        if ( val < $4 ) {
            for ( addend in addends[$1][val] ) {
                sum += addend
            }
        }
    }
    print $0, sum
}

$ awk -f tst.awk file2 file1
NC_000001.11_NM_001005484.2 69270   234 69037 9
NC_000001.11_NM_001005484.2 69511   475 69037 9
NC_000001.11_NM_001005484.2 69761   725 69037 9
NC_000001.11_NM_001385640.1 942155  20  942136 1361

Note that the above will simply output the lines from file1 in the same order they occur in file1, other solutions that read file1 instead of file2 into memory may not do so, e.g. if they use for (i in array) to print them out afterwards that will shuffle them into a "random" order as determined by the internals of the awk version you use, see https://www.gnu.org/software/gawk/manual/gawk.html#Scanning-an-Array, so even if you happen to get the expected output for some specific sample input do not rely on that always happening for all input.