awk to search field2 in file2 using range of fields file1 and using match to another field in file1 Post: 302988171

Sponsored Content

Top Forums Shell Programming and Scripting awk to search field2 in file2 using range of fields file1 and using match to another field in file1 Post 302988171 by cmccabe on Wednesday 21st of December 2016 09:28:35 AM

12-21-2016

Registered User

awk to search field2 in file2 using range of fields file1 and using match to another field in file1

I am trying to use awk to find all the $2 values in file2 which is ~30MB and tab-delimited, that are between $2 and $3 in file1 which is ~2GB and tab-delimited.

I have just found out that I need to use $1 and $2 and $3 from file1 and $1 and $2of file2 must match $1 of file1 and be in the range of $2 and $3 of file1.

If the line in file2 is not found then it is skipped and the next line is processed.

I have removed the chr from $1 file1 so it now looks like:

file1 ~2GB

Code:

1  948953  948956  chr1:948953-948956  .   ISG15
1  949363  949858  chr1:949363-949858  .   ISG15

file 2 ~30MB

Code:

1  12214   rs202068986 C   G
1  949800  rs201725126 T   G

so in order for the line to be printed in the output it must match $1 and be in the range of $2 and $3 of file2

So, since the line from file2 matches $1 in file1 and is in the $2 and $3 range it is printed in the output.

desired output tab-delimited

Code:

1   949800  rs201725126 T   G     ISG15

awk

Code:

awk -F'\t' -v OFS='\t' 'FNR==1 { next }
      FNR == NR { file1[$1,$2,$3] = $1 FS $2 FS $3 }
      FNR != NR { file2[$1,$2,] = $1 FS $2 FS $3 }
            END { print "Match:"; for (k in file1) if (k in file2) print file1[k] # Or file2[k]
      }' file1 file2 > out

awk 2 (very slow) and doesn't use $1 as a match

Code:

awk -F'\t' -v OFS='\t' '                   
NR == FNR {min[NR]=$2; max[NR]=$3; Gene[NR]=$NF; next}
 {                
     for (id in min) 
         if (min[id] < $2 && $2 < max[id]) {
             print $0, id, Gene[id]
             break              
         }
}                                     
' file1 file2

Also tried:

Code:

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

Thank you

Last edited by cmccabe; 12-21-2016 at 12:24 PM.. Reason: Changed a CODE tag pair to be ICODE

cmccabe

View Public Profile for cmccabe

Find all posts by cmccabe

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

match value from file1 in file2

Hi, i've two files (file1, file2) i want to take value (in column1) and search in file2 if the they match print the value from file2. this is what i have so far. awk 'FILENAME=="file1"{ arr=$1 } FILENAME=="file2" {print $0} ' file1 file2

2. Shell Programming and Scripting

awk/sed search lines in file1 matching columns in file2

Hi All, as you can see I'm pretty new to this board. :D I'm struggling around with small script to search a few fields in another file. Basically I have file1 looking like this: 15:38:28 sz:10001 pr:14.16 15:38:28 sz:10002 pr:18.41 15:38:29 sz:10003 pr:19.28 15:38:30 sz:10004...

3. Shell Programming and Scripting

Search & replace fields from file1 to file2

hi, I have two xml files with the name source.xml and tobe_replaced.xml. Sample data: source.xml contains: <?xml version="1.0"?> <product description="prod1" product_info="some/info"> <product description="prod2" product_info="xyz/allinfo"> <product description="abc/partialinfo"...

4. Shell Programming and Scripting

AWK: Pattern match between 2 files, then compare a field in file1 as > or < field in file2

First, thanks for the help in previous posts... couldn't have gotten where I am now without it! So here is what I have, I use AWK to match $1 and $2 as 1 string in file1 to $1 and $2 as 1 string in file2. Now I'm wondering if I can extend this AWK command to incorporate the following: If $1...

5. Shell Programming and Scripting

Match one column of file1 with that of file2

Hi, I have file1 like this aaa ggg ddd vvv eeeand file2 aaa 2 aaa 443 xxx 76 aaa 34 ggg 33 wee 99 ggg 33 ddd 1 ddd 10 ddd 98 sds 23

6. UNIX for Dummies Questions & Answers

if matching strings in file1 and file2, add column from file1 to file2

I have very limited coding skills but I'm wondering if someone could help me with this. There are many threads about matching strings in two files, but I have no idea how to add a column from one file to another based on a matching string. I'm looking to match column1 in file1 to the number...

7. Shell Programming and Scripting

Retreive the records from file2 by using the first field in file1

Hi Freinds, i have a file1 as below file1 1|ndmf|fdd|d3484|34874 2|jdehf|wru7|478|w489 3|dfkj|wej|484|49894 file2 contains lakhs of records and not in sorted order i want to retrive only the records from file2 by searcing the first field of file 1 i used grep ^1 file2...

8. UNIX for Dummies Questions & Answers

Compare file1 and file2, print matching lines in same order as file1

I want to print only the lines in file2 that match file1, in the same order as they appear in file 1 file1 file2 desired output: I'm getting the lines to match awk 'FNR==NR {a++}; FNR!=NR && a' file1 file2 but they are in sorted order, which is not what I want: Can anyone...

9. Shell Programming and Scripting

awk to update field in file2 if not the same as file1

Trying to use awk to: update $2 in file2 with the $2 value in file1, if $1 in file1 matches $13 in file2, which is tab-delimeted. The $2values may already be the same so in that case nothing happens and the next line is processed. There are exactly 4,605 unique $13 values. Thank you :). ...

10. Shell Programming and Scripting

awk to update field using matching value in file1 and substring in field in file2

In the awk below I am trying to set/update the value of $14 in file2 in bold, using the matching NM_ in $12 or $9 in file2 with the NM_ in $2 of file1. The lengths of $9 and $12 can be variable but what is consistent is the start pattern will always be NM_ and the end pattern is always ;...

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

match value from file1 in file2

Discussion started by: myguess21

2. Shell Programming and Scripting

awk/sed search lines in file1 matching columns in file2

Discussion started by: floripoint

3. Shell Programming and Scripting

Search & replace fields from file1 to file2

Discussion started by: dragon.1431

4. Shell Programming and Scripting

AWK: Pattern match between 2 files, then compare a field in file1 as > or < field in file2

Discussion started by: right_coaster

5. Shell Programming and Scripting

Match one column of file1 with that of file2

Discussion started by: polsum

6. UNIX for Dummies Questions & Answers

if matching strings in file1 and file2, add column from file1 to file2

Discussion started by: pathunkathunk

7. Shell Programming and Scripting

Retreive the records from file2 by using the first field in file1

Discussion started by: i150371485

8. UNIX for Dummies Questions & Answers

Compare file1 and file2, print matching lines in same order as file1

Discussion started by: pathunkathunk

9. Shell Programming and Scripting

awk to update field in file2 if not the same as file1

Discussion started by: cmccabe

10. Shell Programming and Scripting

awk to update field using matching value in file1 and substring in field in file2

Discussion started by: cmccabe