Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk to match field between two files and use conditions on match

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 field between two files and use conditions on match
# 1  
Old 10-15-2016
awk to match field between two files and use conditions on match
I am trying to look for $2 of file1 (skipping the header) in $2 of file2 (skipping the header) and if they match and the value in $10 is > 30 and $11 is > 49, then print the line from file1 to a output file. If no match is foung the line is not printed. Both the input and output are tab-delimited. I think either the below awks are close, but after it runs no output results and I am not sure what is wrong. Thank you Smilie

awk 1
Code:
awk -F'\t' -v OFS='\t' ' NR==FNR{a[$2]; next} 
($2 in a) && $10>30 && $11>49 ' file1 file2

awk 2
Code:
awk 'BEGIN{FS=OFS="\t"} NR==FNR{a[$2]; next} 
($2 in a) && $10>30 && $11>49  ' file1 file2

file1
Code:
Missing in IDP but found in Reference:
2   166848646   G   A   exonic  SCN1A   68  13  16;20   0;0 17;15   0;0 0;0 0;0     c.[5139C>T]+[=] 52.94

file2
Code:
Chrom    Position    Gene Sym    Target ID    Type    Zygosity    Genotype    Ref    Variant    Var Freq    Qual    Coverage    Ref Cov    Var Cov
chr2    166245425   SCN2A   AMPL5155065355  SNP Het C/T C   T   54  100   50    23  27
chr2    166848646   SCN1A   AMPL1543060606  SNP Het        G/A   G  A   52.9411764706   100 68  32  36

desired output
Code:
2   166848646   G   A   exonic  SCN1A   68  13  16;20   0;0 17;15   0;0 0;0 0;0     c.[5139C>T]+[=] 52.94

Last edited by cmccabe; 10-15-2016 at 11:42 AM.. Reason: added details
# 2  
Old 10-15-2016
Hello cmccabe,

Could you please try following and let me know if this helps you.
Code:
awk 'FNR==NR{A[$2]=$0;next} ($2 in A){if($10>30 && $11>49){print A[$2]}}'  Input_file1  Input_file2

So in above code you could put -F"\t"and OFS="\t" if your Input_files are tab delimited(They doesn't seems to be in your post). Also if you want to take output into a output file then change print A[$2] >> "output_file". Let us know how it goes then.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
cmccabe
# 3  
Old 10-15-2016
Either of the commands in post#1 works for me, given all spaces are removed from $2 in both files.
This User Gave Thanks to RudiC For This Post:
cmccabe
# 4  
Old 10-15-2016
Works great.... thank you very much Smilie.
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 match or non-match and select fields/patterns for non-matches

In the awk below I am trying to output those lines that Match between file1 and file2, those Missing in file1, and those missing in file2. Using each $1,$2,$4,$5 value as a key to match on, that is if those 4 fields are found in both files the match, but if those 4 fields are not found then missing... (0 Replies)
Discussion started by: cmccabe
0 Replies

2. Shell Programming and Scripting

awk to print fields that match using conditions and a default value for non-matching in two files

Trying to use awk to match the contents of each line in file1 with $5 in file2. Both files are tab-delimited and there may be a space or special character in the name being matched in file2, for example in file1 the name is BRCA1 but in file2 the name is BRCA 1 or in file1 name is BCR but in file2... (6 Replies)
Discussion started by: cmccabe
6 Replies

3. Shell Programming and Scripting

awk to match value to a field within +/- value

In the awk below I use $2 of filet to search filea for a match. If the values in $2 are exact match this works great. However, that is not always the case, so I need to perform the search using a range of + or - 2. That is if the value in filea $2 is within + or - 2 of filet $2 then it is matched.... (6 Replies)
Discussion started by: cmccabe
6 Replies

4. Shell Programming and Scripting

awk Match First Field and Replace Second Column

Hi Friends, I have looked around the forums and over online but couldn't figure out how to deal with this problem input.txt gene1,axis1/0/1,axis2/0/1 gene1,axis1/1/2,axis2/1/2 gene1,axis1/2/3,axis2/2/3 gene2,axis1/3/4,axis2/3/4 Match on first column and if first column is... (1 Reply)
Discussion started by: jacobs.smith
1 Replies

5. UNIX for Dummies Questions & Answers

move files that match specific conditions

Hi all, I'm new to this forum and bash scripting. I have the following problem, I need to copy some files (from one dir. to another) whose first 5 numbers (subjects' ID) match the directory names. Here a shortened version of what I'm trying to do: names=(32983_f 35416_f 43579_f) # these are... (6 Replies)
Discussion started by: ada1983
6 Replies

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

7. Shell Programming and Scripting

Match two files and divide a field !

Hello, I have two files that have the date field as a common. I request your help with some script that divide the value of the file1 by the value in the file2 only when the field date are the same between both files and create a new text file. This is a sample of the files file1... (1 Reply)
Discussion started by: csierra
1 Replies

8. UNIX for Dummies Questions & Answers

Awk counting lines with field match

Hi, Im trying to create a script that reads throught every line in a file and then counts how many lines there with a certain field that matches a input, and also ausing another awk it has to do the same as the above but to then use sort anduniq to get rid of all the unique lines with another... (8 Replies)
Discussion started by: fredted40x
8 Replies

9. Shell Programming and Scripting

how do i pattern match a field with awk?

hi, let's say $numbers = "324 350 587" an so on... what i'm trying to do is this: awk -v numbers="$numbers" '{if (numbers ~ /$2/) print $0, "bla bla"}' file # file looks like this: 214 ..... 215 ... 216 .... 250 ... 324 325 ... 350 something ... ... 587 ... (4 Replies)
Discussion started by: someone123
4 Replies

10. Shell Programming and Scripting

match field between 2 files

I would like to do the following in bash shell. file a a:1 b:2 c:3 file b a:work:apple b:baby:banana c:candy:cat d:desk:dog I would like to match field 1 in file a to file b, if there's a match I would like to append field 2 in file a to field 3 in file b. Thank you. (8 Replies)
Discussion started by: phamp008
8 Replies
Login or Register to Ask a Question