Sum values of specific column in multiple files, considering ranges defined in another file

Shell Programming and Scripting
1

I have a file (let say file B) like this:

 File B:  
A1  3  5  
A1  7  9  
A2  2  5  
A3  1  3

The first column defines a filename and the other two define a range in that specific file. In the same directory, I have also three more files (File A1, A2 and A3). Here is 10 sample lines from each file:

File A1:           File A2:     File A3:   
    1   0.6           1  0.2       1  0.1       
    2   0.04          2  0.1       2  0.2 
    3   0.4           3  0.2       3  0.5 
    4   0.5           4  0.4       4  0.3 
    5   0.009         5  0.2       5  0.7
    6   0.2           6  0.3       6  0.3
    7   0.3           7  0.8       7  0.3   
    8   0.2           8  0.1       8  0.2 
    9   0.15          9  0.9       9  0.8 
    10  0.1           10 0.4       10 0.1

I need to add a new column to file B, which in each line gives the sum of values of column two in the defined range and file. For example, file B row 1 means that calculate the sum of values of line 3 to 5 in column two of file A1. The desired output is something like this:

File B:
    A1  3  5  0.909    
    A1  7  9  0.65  
    A2  2  5  0.9  
    A3  1  3  0.8

All files are in tabular text format. How can I perform this task? I have access to bash (ubuntu 14.04) and R but not an expert bash or R programmer. Any help would be greatly appreciated. Thanks in advance

2

For above sample, this should do

awk     '       {printf "%s ", $0
                 FN=$1; ST=$2; EN=$3;
                 while ($1+0 < EN)      {getline < FN
                                         if ($1 >= ST) sum+=$2}
                                         printf "%s\n", sum
                                         sum=0}
        ' file
A1  3  5 0.909
A1  7  9 0.65
A2  2  5 0.9
A3  1  3 0.8

It is not aimed at nor tested for severely different input data.

1 Like
3 
while read f1 f2 f3
do
 awk 'NR==start,NR==end {sum+=$2} END {print FILENAME,start,end,sum}' start="$f2" end="$f3" "$f1"
done < B > B.new

If B.new looks okay, you can rename it to B

mv B.new B
1 Like
4

Thank you for your prompt reply RudiC and MadeInGermany. I will try your code today and post the results.

---------- Post updated at 03:10 PM ---------- Previous update was at 10:30 AM ----------

It solved the problem. Thank you for your clear and effective code and helping me.

---------- Post updated at 09:06 PM ---------- Previous update was at 03:10 PM ----------

sorry MadeInGermany, but I got an error running the code on real data. The code performs well in sample data (f1, f2, f3). but when I run it on multiple files I get this error:

read: f100: bad variable name

I renamed the file to '100' with no success. I also renamed to 'hundred' but the error was repeated for the next file (f101). Unfortunately googling wasn't helpful.
Thank you for your time and consideration.