awk to print field from lookup file in output


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to print field from lookup file in output
# 1  
Old 08-24-2016
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 [ICODE]lookup/ICODE]. What I can't seem to figure out is how to print the matching $5 from search on that line. Thank you Smilie.

awk
Code:
awk '                   
    NR == FNR {min[$1]=$3; max[$1]=$4; next}
    {                
        for (id in min) 
            if (min[id] < $2 && $2 < max[id]) {
                print $0, id
                break              
            }
    }                                     
' search lookup > out

lookup
Code:
##INFO=<ID=MA,Number=1,Type=String,Description="Minor Allele">
##INFO=<ID=MAF,Number=1,Type=Float,Description="Minor Allele Frequency">
##INFO=<ID=MAC,Number=1,Type=Integer,Description="Minor Alelele Count">
##INFO=<ID=AA,Number=1,Type=String,Description="Ancestral Allele">
#CHROM    POS    ID    REF    ALT    QUAL    FILTER    INFO
1    1014143    rs786201005    C    T    .    .    dbSNP_147;TSA=SNV;E_Phenotype_or_Disease;CLIN_pathogenic;AA=C
7    48311388    rs672601345    G    GG    .    .    dbSNP_147;TSA=insertion;E_Phenotype_or_Disease;CLIN_pathogenic
1    1232600    rs786200943    GCCCTGGAGCGGGAGCAGGCGCG    G    .    .    dbSNP_147;TSA=deletion;E_Phenotype_or_Disease;CLIN_pathogenic
1    1232630    rs750088530    GA    G    .    .    dbSNP_147;TSA=deletion;E_Phenotype_or_Disease;E_ExAC;CLIN_pathogenic;AA=A
1    1232692    rs786200942    CATGCTGGCC    C    .    .    dbSNP_147;TSA=deletion;E_Phenotype_or_Disease;CLIN_pathogenic
22    94487198    rs672601312    G    T    .    .    dbSNP_147;TSA=SNV;E_Phenotype_or_Disease;E_ExAC;CLIN_pathogenic;AA=G

search
Code:
R_Index    Chr    Start    End    Gene
20    chr7    48684202    48684205    ABCA13
21    chr7    48311373    48311704    ABCA13;AX746840
22    chr22    94487185    94487248    ABCA4

desired output
Code:
7    48311388    rs672601345    G    GG    .    .    dbSNP_147;TSA=insertion;E_Phenotype_or_Disease;CLIN_pathogenic 21     ABCA13;AX746840
22    94487198    rs672601312    G    T    .    .    dbSNP_147;TSA=SNV;E_Phenotype_or_Disease;E_ExAC;CLIN_pathogenic;AA=G 22     ABCA4

current output
Code:
7    48311388    rs672601345    G    GG    .    .    dbSNP_147;TSA=insertion;E_Phenotype_or_Disease;CLIN_pathogenic 21
22    94487198    rs672601312    G    T    .    .    dbSNP_147;TSA=SNV;E_Phenotype_or_Disease;E_ExAC;CLIN_pathogenic;AA=G 22

# 2  
Old 08-24-2016
Code:
[akshay@localhost tmp]$ awk '                   
    NR == FNR {min[$1]=$3; max[$1]=$4; o[$1]=$5; next}
    {                
        for (id in min) 
            if (min[id] < $2 && $2 < max[id]) {
                print $0, id, o[id]
                break              
            }
    }                                     
'  search lookup
7    48311388    rs672601345    G    GG    .    .    dbSNP_147;TSA=insertion;E_Phenotype_or_Disease;CLIN_pathogenic 21 ABCA13;AX746840
22    94487198    rs672601312    G    T    .    .    dbSNP_147;TSA=SNV;E_Phenotype_or_Disease;E_ExAC;CLIN_pathogenic;AA=G 22 ABCA4

OR

Code:
[akshay@localhost tmp]$ awk '                   
    NR == FNR {i=$1 OFS $5; min[i]=$3; max[i]=$4; next}
    {                
        for (id in min) 
            if (min[id] < $2 && $2 < max[id]) {
                print $0, id       
                break              
            }
    }                                     
'  search lookup
7    48311388    rs672601345    G    GG    .    .    dbSNP_147;TSA=insertion;E_Phenotype_or_Disease;CLIN_pathogenic 21 ABCA13;AX746840
22    94487198    rs672601312    G    T    .    .    dbSNP_147;TSA=SNV;E_Phenotype_or_Disease;E_ExAC;CLIN_pathogenic;AA=G 22 ABCA4

This User Gave Thanks to Akshay Hegde For This Post:
# 3  
Old 08-24-2016
You are almost done with it.

Code:
awk '                   
    NR == FNR {min[$1]=$3; max[$1]=$4; Gene[$1]=$NF; next}
    {                
        for (id in min) 
            if (min[id] < $2 && $2 < max[id]) {
                print $0, id, Gene[id]
                break              
            }
    }                                     
' search lookup > out

Cheers,
Ranga
This User Gave Thanks to rangarasan For This Post:
# 4  
Old 08-24-2016
Hello cmccabe,

Following may help you in same.
Code:
awk 'FNR==NR && $1 ~ /[[:digit:]]/{;A[$2]=$0;B[++i]=$2;next} {for(j=1;j<=i;j++){if(B[j]>$3 && B[j]<$4){print A[B[j]] FS $NF;next}}}' lookup search

Output will be as follows.
Code:
7    48311388    rs672601345    G    GG    .    .    dbSNP_147;TSA=insertion;E_Phenotype_or_Disease;CLIN_pathogenic ABCA13;AX746840
22    94487198    rs672601312    G    T    .    .    dbSNP_147;TSA=SNV;E_Phenotype_or_Disease;E_ExAC;CLIN_pathogenic;AA=G ABCA4

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 5  
Old 08-24-2016
Thank you all Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Using awk to print output based on first field.

Hi Folks, I have one requirement, There is one file, which contains two fields. Based on first field, I need to print an output. Example will be more suitable. Input file like this. abc 5 abc 10 xyz 6 xyz 9 xyz 10 mnp 10 mnp 12 mnp 6 (2 Replies)
Discussion started by: Raza Ali
2 Replies

2. Shell Programming and Scripting

awk to lookup stored variable in file and print matching line

The bash bash below extracts the oldest folder from a directory and stores it in filename That result will match a line in bold in input. In the matching line there is an_xxx digit in italics that (once the leading zero is removed) will match a line in link. That is the lint to print in output.... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

Lookup field values in two fixed format file in UNIX - not working

I have 2 fixed length files input#1 & input#2. I want to match the rows based on the value in position 37-50 in both files (pos 37-50 will have same value in both files). If any matching record is found then cut the value against company code & Invoice number from input file #1 (position 99 until... (3 Replies)
Discussion started by: Lingaraju
3 Replies

4. UNIX for Dummies Questions & Answers

Lookup field in map file

Hi, I have two questions which I would massively appreciate help with. 1. I am trying to insert a field into a file similar to the vlookup function in excel. In column 2 is a gene id for which i would like to insert the full name in the adjacent column. I have a map file (map.file) which... (1 Reply)
Discussion started by: genehersh
1 Replies

5. Shell Programming and Scripting

File field to replace lookup from another file

Hi All, I don't know how to fast do this field replace that need lookup from another file to form the update result:confused: I want to do it by general shell script Can anyone help to solve it ? Thanks for your kindly reply in advance. CK (0 Replies)
Discussion started by: ckwong99
0 Replies

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:... (4 Replies)
Discussion started by: venalla_shine
4 Replies

7. Shell Programming and Scripting

Awk script to run a sql and print the output to an output file

Hi All, I have around 900 Select Sql's which I would like to run in an awk script and print the output of those sql's in an txt file. Can you anyone pls let me know how do I do it and execute the awk script? Thanks. (4 Replies)
Discussion started by: adept
4 Replies

8. UNIX for Dummies Questions & Answers

Multiple Column print after lookup using NR==FNR (awk)

foo.txt FAMID IID AFF SEX Group AgeCat Dis1 Dis2 Dis3 Dis4 Dis5 Dis6 Dis6 AMD0001 Mayo_49542 1 2 AMD 8 1 1 1 1 1 1 1 AMD0002 Mayo_49606 1 1 AMD 3 1 1 1 1 ... (7 Replies)
Discussion started by: genehunter
7 Replies

9. Shell Programming and Scripting

awk command : To print the output to a file

half of the problem is already solved with the help of bartus11 suggestion I have a txt file having rows and coulmns, i want to perform some operation on a specific coulmn starting from a specific line. 50.000000 1 1 1 1000.00000 1000.00000 ... (5 Replies)
Discussion started by: shashi792
5 Replies

10. UNIX for Dummies Questions & Answers

using awk or sed to print output from one file

dear i have one file regarding >abshabja>sdksjbs>sknakna>snajxcls so i want to be output like >abshabja >sjkabjb >sknakna >snajxcls Any using awk or sed will help thanks (2 Replies)
Discussion started by: cdfd123
2 Replies
Login or Register to Ask a Question