Value falls between two values from different files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Value falls between two values from different files
# 1  
Old 02-26-2013
Value falls between two values from different files

Hi, I have two files

cat 1
Code:
100 1
110 2
113 4
230 5
334 7
500 8
900 10


I have another file
Code:
cat 2
100 200
201 300
301 400
401 500
501 600
601 700
701 800
801 900
901 1000

If $1 in file1 falls in the range of a record in file2, then print that record in file2 with $2 in file1. If there are multiple intersections then sum them up. If there is no value, then print 0. My output would be

Code:
100 200 7
201 300 5
301 400 7
401 500 8
501 600 0
601 700 0
701 800 0
801 900 10
901 1000 0

# 2  
Old 02-26-2013
How about this:

Code:
awk 'NR==FNR{
   C[NR]=$1 " " $2
   L[C[NR]]=0
   next
}
{
 for (t in C) {
    split(C[t],v," ")
    if($1>=v[1] && $1<=v[2])
       L[C[t]]+=$2
 }
}
END {
   for(i=1;i in C;i++)
       print C[i] " " L[C[i]]
}' 2 1

These 2 Users Gave Thanks to Chubler_XL For This Post:
# 3  
Old 02-26-2013
Quote:
Originally Posted by Chubler_XL
How about this:

Code:
awk 'NR==FNR{
   C[NR]=$1 " " $2
   L[C[NR]]=0
   next
}
{
 for (t in C) {
    split(C[t],v," ")
    if($1>=v[1] && $1<=v[2])
       L[C[t]]+=$2
 }
}
END {
   for(i=1;i in C;i++)
       print C[i] " " L[C[i]]
}' 2 1

Chubler_XL,

Thanks for your time and a wonderful code.

I have another small issue that is bothering me

If I have the following values in another file

Code:
175 
180
185
199
220
235
246
278
298
311
333
345
399
412

I would like to consider the minimum value and its nearest lesser 100 and the maximum value and its nearest greater 100 and print blocks in a difference of 100.

So, in the above data, the minimum value is 175, so its nearest less value is 100 and the maximum value is 412 and its greatest near 100 is 500.

My output would be

Code:
100 200
201 300
301 400
401 500

# 4  
Old 02-26-2013
This should work:

Code:
awk '{
    min=$1<min||!min?$1:min
    max=$1>max||!max?$1:max
}
END {
  s=int(min/100)*100
  e=int(max/100)*100+100
  print s " " s+100
  for(i=s+101;i<e;i+=100)
     print i " " i+99
}' infile

This User Gave Thanks to Chubler_XL For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Replace Stub Values In One Group Of Files With Actual Values From Another Group Of Files

I have two directories of files (new-config-files and old-config-files): new-config-files/this-db/config.inc.php new-config-files/that-db/config.inc.php new-config-files/old-db/config.inc.php new-config-files/new-db/config.inc.php new-config-files/random-database/config.inc.php etc. ... (4 Replies)
Discussion started by: spacegoose
4 Replies

2. Shell Programming and Scripting

Query the table and return values to shell script and search result values from another files.

Hi, I need a shell script, which would search the result values from another files. 1)execute " select column1 from table_name" query on the table. 2)Based on the result, need to be grep from .wft files. could please explain about this.Below is the way i am using. #!/bin/sh... (4 Replies)
Discussion started by: Rami Reddy
4 Replies

3. Emergency UNIX and Linux Support

How to move files from a directory which falls between Date Range?

Hi All, I am trying to to move files from a directory to another which falls from Current day - 7 days. The files are in zipped format with dates appended on it. Can you pls help me as this came as a immediate change before the production Release planned next week. Pls let me know if... (11 Replies)
Discussion started by: dsfreddie
11 Replies

4. Shell Programming and Scripting

how to get a worrd which falls after a keyword separated by comma

Hi friends, i have a file which contains all words(including comma) in different line. for example: more file.txt select column1 from table1 , table2 join table3 0n condition i just want to get table2 if there is a comma after table1(as shown in the above case) else dont fetch... (4 Replies)
Discussion started by: neelmani
4 Replies

5. Shell Programming and Scripting

Compare values in two files. For matching rows print corresponding values from File 1 in File2.

- I have two files (File 1 and File 2) and the contents of the files are mentioned below. - I am trying to compare the values of Column1 of File1 with Column1 of File2. If a match is found, print the corresponding value from Column2 of File1 in Column5 of File2. - I tried to modify and use... (10 Replies)
Discussion started by: Santoshbn
10 Replies

6. Shell Programming and Scripting

How to get the next word which falls just after a keyword?

Hi friends, i just want to know the command though which i can get the next word which comes just after a particluar keyword. For example: suppose text.out is file which contains a pl/sql procedure . i want to find out the word which falls just after the "table1" keyword. Thank... (7 Replies)
Discussion started by: neelmani
7 Replies

7. Shell Programming and Scripting

Cat Values from Several files if it meets criteria for column values

I have results from some statistical analyses. The format of the results are as given below: I want to select lines that have a p-value (last column) less than 0.05 from all the results files (*.results) and cat to a new results file. It would be very nice if a new column is added that tells... (2 Replies)
Discussion started by: genehunter
2 Replies

8. BSD

Remote connections unable after power falls

Hi everyone: I have a server used for testing running FreeBSD, last weekend we had power cuts in my job and our server was constantly rebooting. since then the network connections are very slow, it's almost impossible establish a remote connection with the server, however running any... (2 Replies)
Discussion started by: edgarvm
2 Replies

9. Shell Programming and Scripting

Awk substring falls between two values

Hi guys, hopefully you can give me a hand with this before my monitor has a nasty accident! :mad: I have the following line in a script: awk 'int(substr($1,2,2))>'06' && int(substr($1,2,2))<'08' ' ANYOLDFILE.log ... which when ran against this data file: ... correctly... (3 Replies)
Discussion started by: dlam
3 Replies
Login or Register to Ask a Question