0

I have a file which looks like:

1   4
2   4
3   5
4   4
5   4
6   1
7   1
8   1
9   4
10  4
12  1
13  1
14  1
15  1
16  2
19  3
20  1
21  1
26  1
28  3
24  4
29  4
30  1

The column 1 is serial number and column2 is the values. I would like to calculate the sum of values between a particular range for Eg: sum the values in column2 which are between 2 and 7 (from column1)

I acheived this by the following awk one liner:

awk '{if ($1 >= 2 && $1 <= 7) x += $2 } END {print x}' file_name #output is 20

The question is I would like to read the ranges from other file2: From 3-9, 2-6, 12-20 etc

3 9
2 6
12 20

How could I pass the range from file2 to the AWK instead of manually typing the range with if statement. How to read multiple files in AWK ?

gthm
  • 1,878
  • 4
  • 26
  • 37
  • This question is possibly duplicated with this: http://stackoverflow.com/questions/14984340/using-awk-to-process-input-from-multiple-files – Raul Luna Jun 13 '14 at 10:02

3 Answers3

3

Another one you could try:

awk '
  NR==FNR{
    A[$1]=$2
    next
  }
  {
    t=0
    for(i in A) if(i>=$1 && i<=$2) t+=A[i]
    print t
  }
' file rangefile

Or in one line:

awk 'NR==FNR{A[$1]=$2; next}{t=0; for(i in A) if(i>=$1 && i<=$2) t+=A[i]; print t}' file rangefile
Scrutinizer
  • 9,608
  • 1
  • 21
  • 22
  • I did not understand the part A[$1]=$2 – gthm Feb 23 '13 at 22:57
  • 2
    The only time that `NR==FNR` is when the first file is being read. During that time the values of that file are put in the array "A" with index $1 and value $2 (`A[$1]=$2`). This array is used in the second part when the rangefile is being read and for every range in the range file, the array A will get enumerated, so that `i` will contain `$1` and `A[i]` will contain `$2` of the lines of the first file.. – Scrutinizer Feb 23 '13 at 23:09
2

You can read multiple files in a few ways. You can specify multiple files on the command line, in which case awk will read through each once, or you can use getline to read a line from a file. In this case, however, it might be simplest to do the computational more expensive thing and just read through file1 once for each range specified in file2, but don't use awk to read the range. Something like:

while read lower upper; do
awk '$1 >= lower && $1 <= upper{ x += $2 } END {print x}' \
    lower=$lower upper=$upper file1
done < file2

If you only want to read file1 once, you can do something more complicated like (untested):

awk 'NR==FNR{ lo[NR]=$1; hi[NR]=$2; next } 
    { for( i in lo ) if( $1 >= lo[i] && $1 <= hi[i] ) sum[i]+=$2 }
    END{ for( i in b ) print "sum of " lo[i] " to " hi[i] ": " sum[i] }' file2 file1
William Pursell
  • 204,365
  • 48
  • 270
  • 300
0

Here's one way using awk:

awk 'NR==FNR { a[$1]=$2; next } { for (i in a) { i+=0; if (i>=$1 && i<=$2) s+=a[i] } print s; s=0 }' file1 file2

Results:

20
18
10
Steve
  • 51,466
  • 13
  • 89
  • 103