awk to lookup section of file in a range of another file Post: 302981338

Sponsored Content

Top Forums Shell Programming and Scripting awk to lookup section of file in a range of another file Post 302981338 by RavinderSingh13 on Saturday 10th of September 2016 11:25:36 AM

09-10-2016

Moderator

Hello cmccabe,

I think some more information would have given added by you into your post, like as follows:
i- Is value of field2 is going to be always lesser than field3's value?(though I have considered both the cases into my code following)
ii- You have mentioned about last section of Input_file1 as Missing in Reference but found in IDP:, so do you mean by last column? Sorry I couldn't get it, so I didn't keep this point into my code.
iii- Could you please let us know if we need to update the Input_file1 itself here?

As a start we could try following code, if you could specify more clearly about your requirements, I would like to help more on this please.

Code:

awk 'FNR==NR{if($0 ~ /^[[:digit:]]/ || $0 ~ /^chr/){gsub(/[[:alpha:]]/,X,$1)};A[$1]=$2;B[$1]=$0;next} {gsub(/[[:alpha:]]/,X,$1)} ($1 in A){if((A[$1]>=$2 && A[$1]<=$3) || (A[$1]<=$2 && A[$1]>=$2)){print $0 FS "low";next}} END{for(i in A){print B[i]}} '  Input_file1  Input_file2

Output will be as follows.

Code:

7 148106400 148106550 CNTNAP2 low
Missing in IDP but found in Reference:
7    148106478    -    GT    intronic    CNTNAP2    c.3716-5_3716-4insGT    4168    28.6    51.01
Match:
2    166210776    C    T    exonic    SCN2A    c.[2994C>T]+[=]    3095    23.1    24.56
15 68521889 C T exonic CLN6 GOOD 50 het 4

Thanks,
R. Singh

Last edited by RavinderSingh13; 09-10-2016 at 12:30 PM..

This User Gave Thanks to RavinderSingh13 For This Post:

RavinderSingh13

View Public Profile for RavinderSingh13

Find all posts by RavinderSingh13

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

sed & awk--get section of file based 2 params

I need to get a section of a file based on 2 params. I want the part of the file between param 1 & 2. I have tried a bunch of ways and just can't seem to get it right. Can someone please help me out.....its much appreciated. Here is what I have found that looks like what I want....but doesn't...

2. Shell Programming and Scripting

file Lookup using awk

Hi All, I have two files file1 and file2(lookup file).I need to map more than one keyfields of file1 with file2.how can we achieve it using awk. file1(max 2.2 million records) -------------------------- 680720|680721|077 680720|680721|978 680721|680722|090 file2(no idea about the...

3. UNIX for Advanced & Expert Users

Clueless about how to lookup and reverse lookup IP addresses under a file!!.pls help

Write a quick shell snippet to find all of the IPV4 IP addresses in any and all of the files under /var/lib/output/*, ignoring whatever else may be in those files. Perform a reverse lookup on each, and format the output neatly, like "IP=192.168.0.1, ...

4. Shell Programming and Scripting

Multiple file lookup using awk

I want to lookup filea with fileb,filec and filed. If entry in filea exist in fileb and filec mark Y and then if entry in filea exist in filed mark as Y. Final output should have all the entries from filea. This prints only matching entries from file a in fileb i want all entries from...

5. Shell Programming and Scripting

Extract section of file based on word in section

I have a list of Servers in no particular order as follows: virtualMachines="IIBSBS IIBVICDMS01 IIBVICMA01"And I am generating some output from a pre-existing script that gives me the following (this is a sample output selection). 9/17/2010 8:00:05 PM: Normal backup using VDRBACKUPS...

6. UNIX for Dummies Questions & Answers

Help with AWK - Compare a field in a file to lookup file and substitute if only a match

I have the below 2 files: 1) Third field from file1.txt should be compared to the first field of lookup.txt. 2) If match found then third field, file1.txt should be substituted with the second field from lookup.txt. 3)Else just print the line from file1.txt. File1.txt:...

7. Shell Programming and Scripting

Combined sed+awk for lookup csv file

have written a combined sed+awk to perform a lookup operation which works but looking to enhance it. looking to match a record using any of the comma separated values + return selected fields from the record - including the field header. so: cat foo make,model,engine,trim,value...

8. Shell Programming and Scripting

awk to print field from lookup file in output

The below awk uses $3 and $4 in search as the min and max, then takes each $2 value in lookup and compares it. If the value in lookupfalls within the range in searchthen it prints the entire line in lookup/ICODE]. What I can't seem to figure out is how to print the matching $5 from search on that...

9. Shell Programming and Scripting

awk to lookup value in one file in another range

I am trying to update the below awk, kindly provided by @RavinderSingh13, to update each line of file1 with either Low or No Low based on matching $2 of file1 to a range in $2 and $3 of file2. If the $2 value in file1 matches the range in file2 then that line is Low, otherwise it is No Low in the...

10. Shell Programming and Scripting

awk conditional operators- lookup value in 2nd file

I use conditional operators alot in AWK to print rows from large text files based on values in a certain column. For example: awk -F '\t' '{ if ($1 == "A" || $1 == "C" ) print $0}' OFS="\t" file1.txt > file2.txt In this case every row is printed from file1 to file2 for which the column 1...

LEARN ABOUT V7

diff3

DIFF3(1)						      General Commands Manual							  DIFF3(1)

NAME

       diff3 - 3-way differential file comparison

SYNOPSIS

       diff3 [ -ex3 ] file1 file2 file3

DESCRIPTION

       Diff3 compares three versions of a file, and publishes disagreeing ranges of text flagged with these codes:

       ====	       all three files differ

       ====1	       file1 is different

       ====2	       file2 is different

       ====3	       file3 is different

       The type of change suffered in converting a given range of a given file to some other is indicated in one of these ways:

       f : n1 a        Text is to be appended after line number n1 in file f, where f = 1, 2, or 3.

       f : n1 , n2 c   Text is to be changed in the range line n1 to line n2.  If n1 = n2, the range may be abbreviated to n1.

       The original contents of the range follows immediately after a c indication.  When the contents of two files are identical, the contents of
       the lower-numbered file is suppressed.

       Under the -e option, diff3 publishes a script for the editor ed that will incorporate into file1 all changes between file2 and file3,  i.e.
       the  changes  that  normally  would  be	flagged ==== and ====3.  Option -x (-3) produces a script to incorporate only changes flagged ====
       (====3).  The following command will apply the resulting script to `file1'.

		(cat script; echo '1,$p') | ed - file1

FILES

       /tmp/d3?????
       /usr/lib/diff3

SEE ALSO

       diff(1)

BUGS

       Text lines that consist of a single `.' will defeat -e.
       Files longer than 64K bytes won't work.

																	  DIFF3(1)