Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk to match a numeric range specified by two columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to match a numeric range specified by two columns
# 1  
Old 11-30-2010
awk to match a numeric range specified by two columns
Hi Everyone,

Here's a snippet of my data:

File 1 = testRef2:
Code:
A1BG    -    13208    13284
AAA1    -    34758475    34873943
AAAS    -    53701240    53715412

File 2 = 42MLN.3.bedS2:
Code:
13208
13208
13360
13363
13484
13518
13518

My awk script:
Code:
awk 'NR == FNR{a[$1]=$1;next} {$1>=a[$3]}{$1<=a[$4]}{print $0}1' 42MLN.3.bedS2 testRef2

output:
Code:
A1BG    -    13208    13284
A1BG    -    13208    13284
AAA1    -    34758475    34873943
AAA1    -    34758475    34873943
AAAS    -    53701240    53715412
AAAS    -    53701240    53715412

What I was hoping to do is generate the following output:
Code:
A1BG    -    13208    13284
A1BG    -    13208    13284

Basically I want the data in $1 of File 2 to be >= [$3 of File 1] && <= [column $4 of File 1] and when it is then I want to print the lines from File 1.

Make sense?

Alternatively, I suppose simply appending the the string from $1 of File 1 to the end of the row in File 2 would also work.

Thanks for your help.
Last edited by heecha; 11-30-2010 at 02:40 PM.. Reason: Please use code tags
# 2  
Old 11-30-2010
Code:
<42MLN.3.bedS2 xargs -i grep {} testRef2

or
Code:
<42MLN.3.bedS2 xargs -I{} grep {} testRef2

Code:
# cat f3
13208
13208
13360
13363
13484
13518
13518
# cat f2
A1BG    -    13208    13284
AAA1    -    34758475    34873943
AAAS    -    53701240    53715412
# <f3 xargs -i grep {} f2
A1BG    -    13208    13284
A1BG    -    13208    13284
# <f3 xargs -I{} grep {} f2
A1BG    -    13208    13284
A1BG    -    13208    13284
#

Last edited by ctsgnb; 11-30-2010 at 03:11 PM..
This User Gave Thanks to ctsgnb For This Post:
heecha
# 3  
Old 11-30-2010
Code:
awk 'NR==FNR{a[NR]=$1;s=NR;next} 
     {for (i=1;i<=s;i++) if (a[i]>=$3&&a[i]<=$4) {print a[i] "\t" $0}}' 42MLN.3.bedS2 testRef2

13208   A1BG    -    13208    13284
13208   A1BG    -    13208    13284

This User Gave Thanks to rdcwayx For This Post:
heecha
# 4  
Old 12-01-2010
Thank you! I am grateful for your help.
# 5  
Old 12-02-2010
Hi guys, me again, so rdcwayx's suggestion works well, but my server keeps kicking me out when I try it on large data sets; this doesn't happen right away, but after the thing has been running for quite a while. Is this most likely because we are doing something inefficiently? Is there a way around it, or should I be using something besides awk?

Thanks again for your insights.

---------- Post updated at 10:39 AM ---------- Previous update was at 10:32 AM ----------

Never mind, I see what's happening.

Thanks
# 6  
Old 12-02-2010
I guess you login the server by ssh and the timeout setting in ssh is the problem, and kick you out if there is no action for long time.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to print text in field if match and range is met

In the awk below I am trying to match the value in $4 of file1 with the split value from $4 in file2. I store the value of $4 in file1 in A and the split value (using the _ for the split) in array. I then strore the value in $2 as min, the value in $3 as max, and the value in $1 as chr. If A is... (6 Replies)
Discussion started by: cmccabe
6 Replies

2. Shell Programming and Scripting

Get range out using sed or awk, only if given pattern match

Input: START OS:: UNIX Release: xxx Version: xxx END START OS:: LINUX Release: xxx Version: xxx END START OS:: Windows Release: xxx Version: xxx ENDHere i am trying to get all the information between START and END, only if i could match OS Type. I can get all the data between the... (3 Replies)
Discussion started by: Dharmaraja
3 Replies

3. Shell Programming and Scripting

Match files based on either of the two columns awk

Dear Shell experts, I have 2 files with structure: File 1: ID and count head test_GI_count1.txt 1000094 2 10039307 1 10039641 1 10047177 11 10047359 1 1008555 2 10120302 1 10120672 13 10121776 1 10121865 32 And 2nd file: head Protein_gi_GeneID_symbol.txt protein_gi GeneID... (11 Replies)
Discussion started by: smitra
11 Replies

4. UNIX for Dummies Questions & Answers

Awk match on columns and delete line

Hi, I have a file like this a 1 2 b 2 2 c 2 3 d 4 5 f 5 6 output a 1 2 c 2 3 d 4 5 f 5 6 Basically, I want to delete the whole line if $2 and $3 are the same. Thanks (5 Replies)
Discussion started by: jacobs.smith
5 Replies

5. Shell Programming and Scripting

Awk numeric range match only one digit?

Hello, I have a text file with lines that look like this: 1974 12 27 -0.72743 -1.0169 2 1.25029 1974 12 28 -0.4958 -0.72926 2 0.881839 1974 12 29 -0.26331 -0.53426 2 0.595623 1974 12 30 7.71432E-02 -0.71887 3 0.723001 1974 12 31 0.187789 -1.07114 3 1.08748 1975 1 1 0.349933 -1.02217... (2 Replies)
Discussion started by: meridionaljet
2 Replies

6. UNIX for Dummies Questions & Answers

How to match 2 columns where one column has data as a range - extended

Dear all, there is a nice solution for a text merge where the second file has only variables with a numeric range ( sorry, cannot post URL + thread is closed ). The real world is however more complicated than in the earlier example. file1 A 1 A 2 A 3 B 1 B 2 B 3 B 4 C 1 C 2 C 3 C... (4 Replies)
Discussion started by: underscore
4 Replies

7. UNIX for Dummies Questions & Answers

How to match 2 columns where one column has data as a range

Hi, I have a query about joining files using data ranges. Example files below - I want to join file1 to file2 with matches where file1 column 1 is equal to file2 column1, and file1 column 2 is within the range of file2 columns 3 and 4. I would like rows which don't match to be printed too. ... (4 Replies)
Discussion started by: auburn
4 Replies

8. Shell Programming and Scripting

match range of different numbers by AWK

if the column1 and 2 in both files has same key (for example "a" and "a1") compare each first key value(a1 of a) of input2 (for example 1-4 or 65-69 not 70-100 or 44-40 etc) with all the values in input1. if the range of first key value in input2 is outof range in input1 values named it as out... (54 Replies)
Discussion started by: repinementer
54 Replies

9. Shell Programming and Scripting

match columns using awk

Hi All, I need some help in writing a small script using Awk. My input file has following deatils A,B,C,D 8239359,8239359,8388125,8388125 8239359,8239359,8388125,8388125 7165981,7165981,8363138,8363138 8283830,8283830,8382987,8382987 8209964,8209964,8367098,8367098 ... (8 Replies)
Discussion started by: pistachio
8 Replies

10. Shell Programming and Scripting

numeric range comparisons

I have two files.And a sort of matrix analysis. Both files have a string followed by two numbers: File 1: A 2 7 B 3 11 C 5 10 ...... File 2: X 1 10 Y 3 5 Z 5 9 What I'd like to do is for each set of numbers in the second file indicate if the first or second number (or both) in... (7 Replies)
Discussion started by: dcfargo
7 Replies
Login or Register to Ask a Question