How to assign variables associated with list of ranges to another table with integers

Question

I have a list of integers from 1 to 6k, and I have a list of ranges (start/stop) with a third value I'd like to assign to each integer within those ranges. The list of ranges might look like this, although in reality I will have hundreds of unique ranges per file to check against each integer.

5277 5305 72

5306 5331 50

5332 5361 57

5368 5389 55

What I'd like to do is print the integer and the value from the third column of the range table, or print a "0" for integers not falling in any range.

1 0

2 0

3 0

...

5277 72

5278 72

5279 72

...

5305 72

5306 50

5307 50

...

6000 0

Any thoughts on how to get the intended results? I was thinking of writing 'for' loops for integers 1-6000 with embedded 'if' files to check each integer in each range, but don't want to individually write hundreds of 'if' statements, just to have to change them for the next dataset. Maybe this could be done by creating a table of ranges in MySQL and checking each of 6000 values against the table and printing the result, but I'm not that familiar with MySQL so it would take me much longer to figure out.

Answer 1

This is a little convoluted, but at least it avoids the use of SQL. Using your sample range in the file named 'range':

seq 6000 > integers
awk '{print $1","$2"{s/\\(.*\\)/\\1 "$3"/; t\n}" } END { print "s/\\(.*\\)/\\1 0/" }' range > range.sed
sed -f range.sed integers > integers.output

This creates the integers file with the numbers 1..6000, then uses awk to translate the range file into a series of sed commands, which are all then redirected into a sed script file (range.sed). We then call sed with that sed script file on the integers input and redirect it to an output file.

The awk script is doing two basic things:

1. Printing a "start,stop" range (using the first and second fields from the range file) with a block of two commands:

   a. print out a sed search & replace command of "anything on the line" with "that previous anything" followed by the 3rd column of the range file.

   b. if the replacement was successful, branch to the end of the sed script (and so bypassing the last piece of the sed script)

2. as the last command in the sed script (after ENDing all the range input), print out a sed search & replace command to replace any value on the line with that value followed by a zero.

This leaves you fairly flexible in being able to update the range and/or integer files as needed, without having to update lots of hard-coded logic.