Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to print fields that match using conditions and a default value for non-matching in two files
# 1  
Old 03-18-2017
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 the name is BCR/ABL.

If there is a match and $5 of file2 and $7 has full gene sequence in it, then $5 and $4 are printed separated by a tab. If there is no match found then the name that was not matched and 279 are printed separated by a tab. The awk below does execute, but the output is not correct. Also I am not sure how to add in the condition to ensure $7 is full gene sequence.

The names in file2 may be partial matchto file1, but in file1 they will always be complete. Like in the BRCA1 in file1 that matches the BRCA 1, BRCA2 in file2. The full gene sequence in $7 may also be partial in file2 as is the case for BCRA1. The file2 is not a controlled document so the case may be different as in fbn1 from file1 matching FBN1 in file2. The awk seems close but not all conditions are accounted for. Thank you Smilie.


Code:
awk 'BEGIN{FS=OFS="\t"}
  FNR==NR{
      if(NR>1){
          gsub(" ","",$5)       #removing white space
          n=split($5,v,"/")
          d[v[1]] = $4          #from split, first element as key
      }
      next
}{print $1, ($1 in d?d[$1]:279)}' file2 file1

BRCA1	279
BCR	806
SCN1A	279
fbn1	85

Code:
BRCA1	81
BCR	806
SCN1A	279
fbn1	85

file1
Code:
BRCA1
BCR
SCN1A
fbn1

file2
Code:
Tier	explanation	.	List code	gene	gene name	methodology	disease
Tier 1	.	.	811	DMD	dystrophin	deletion analysis and duplication analysis, if performed Publication Date: January 1, 2014	Duchenne/Becker muscular dystrophy
Tier 1	.	Jan-16	81	BRCA 1, BRCA2	breast cancer 1 and 2	full gene sequence and full deletion/duplication analysis	hereditary breast and ovarian cancer
Tier 1	.	Jan-16	70	ABL1	ABL1	gene analysis variants in the kinse domane	acquired imatinib tyrosine kinase inhibitor
Tier 1	.	.	806	BCR/ABL 1 	t(9;22)	major breakpoint, qualitative or quantitative	chronic myelogenous leukemia CML
Tier 1	.	Jan-16	85	FBN1	Fibrillin	full gene sequencing	heart disease
Tier 1	.	Jan-16	95	FBN1	fibrillin	del/dup	heart disease

Last edited by cmccabe; 03-18-2017 at 11:31 AM.. Reason: fixed format
# 2  
Old 03-19-2017
Try these changes:

Code:
awk 'BEGIN{FS=OFS="\t"}
{$0=toupper($0)}
FNR==NR{
   if(NR>1 && ($7 ~ "FULL GENE SEQUENC")) {
          gsub(" ","",$5)       #removing white space
          n=split($5,v,"/")
          d[v[1]] = $4          #from split, first element as key
      }
      next
}{print $1, ($1 in d?d[$1]:279)}' file2 file1

This User Gave Thanks to Chubler_XL For This Post:
cmccabe
# 3  
Old 03-19-2017
I made a typo in on of the file1 lines, BCRA1 should be BCRA2.

file1
Code:
BRCA2
BCR
SCN1A
fbn1

current output:
Code:
awk 'BEGIN{FS=OFS="\t"}
{$0=toupper($0)}
FNR==NR{
   if(NR>1 && ($7 ~ "FULL GENE SEQUENC")) {
          gsub(" ","",$5)       #removing white space
          n=split($5,v,"/")
          d[v[1]] = $4          #from split, first element as key
      }
      next
}{print $1, ($1 in d?d[$1]:279)}' file2 file1
BRCA2    279
BCR    279
SCN1A    279
FBN1    85

FULL GENE SEQUENC could also be case in sensitive so I added a check in for that... why isn't FULL GENE SEQUENCE used, when I try that I get all the names with a value of 279.

Code:
awk 'BEGIN{FS=OFS="\t"}
{$0=toupper($0)} {$7=toupper($7)}
FNR==NR{
   if(NR>1 && ($7 ~ "FULL GENE SEQUENC")) {
          gsub(" ","",$5)       #removing white space
          n=split($5,v,"/")
          d[v[1]] = $4          #from split, first element as key
      }
      next
}{print $1, ($1 in d?d[$1]:279)}' file2 file1
BRCA2    279 
BCR    279
SCN1A    279
FBN1    85

desired output
Code:
BRCA2    81   - match in line 2 of $5 in file 2, BRCA 1, BRCA2
BCR    279     - match in line 2 of $5 in file but $7 is not full gene sequence
SCN1A    279
fbn1    85

Thank you Smilie.
Last edited by cmccabe; 03-19-2017 at 06:09 PM.. Reason: fixed format
# 4  
Old 03-19-2017
Trying to match full gene sequencing and full gene sequence as this is in the data.
This User Gave Thanks to Chubler_XL For This Post:
cmccabe
# 5  
Old 03-19-2017
Makes sense now, thank you.

In order to capture BRCA2 from file1 with BRCA 1, BRCA2 from file2, would:

Code:
 gsub(" ","",$5)       #removing white space

have to be to capture the matching name anywhere in that $5, I believe it is currently only looking in the first position, but BRCA2 is in position 2. Thank you Smilie.

Code:
 gsub(" ","",""$5)       #removing white space

or is the problem that $7 is full gene sequence and full deletion/duplication analysis, that is full gene sequence is a partial match to the full line in $7?
Last edited by cmccabe; 03-19-2017 at 08:55 PM.. Reason: added details
# 6  
Old 03-20-2017
You could go thru all the values in $5 using comma , and slash / as separators.

Code:
awk 'BEGIN{FS=OFS="\t"}
{$0=toupper($0)}
FNR==NR{
   if(NR>1 && ($7 ~ "FULL GENE SEQUENC")) {
          gsub(" ","",$5)       #removing white space
          n=split($5,v,"[,/]")
          for(i=1; i<=n; i++)
             d[v[i]] = $4          # from split, use each element as a key
      }
      next
}{print $1, ($1 in d?d[$1]:279)}' file2 file1

This User Gave Thanks to Chubler_XL For This Post:
cmccabe
# 7  
Old 03-23-2017
Thank you very much for all your help Smilie.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Matching two fields in two csv files, create new file and append match

I am trying to parse two csv files and make a match in one column then print the entire file to a new file and append an additional column that gives description from the match to the new file. If a match is not made, I would like to add "NA" to the end of the file Command that Ive been using... (6 Replies)
Discussion started by: dis0wned
6 Replies

2. UNIX for Beginners Questions & Answers

awk match two fields in two files

Hi, I have two TEST files t.xyz and a.xyz which have three columns each. a.xyz have more rows than t.xyz. I will like to output rows at which $1 and $2 of t.xyz match $1 and $2 of a.xyz. Total number of output rows should be equal to that of t.xyz. It works fine, but when I apply it to large... (6 Replies)
Discussion started by: geomarine
6 Replies

3. UNIX for Beginners Questions & Answers

Match Fields between two files, print portions of each file together when matched in ([g]awk)'

I've written an awk script to compare two fields in two different files and then print portions of each file on the same line when matched. It works reasonably well, but every now and again, I notice some errors and cannot seem to figure out what the issue may be and am turning to you for help. ... (2 Replies)
Discussion started by: jvoot
2 Replies

4. 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

5. UNIX for Beginners Questions & Answers

Awk: matching multiple fields between 2 files

Hi, I have 2 tab-delimited input files as follows. file1.tab: green A apple red B apple file2.tab: apple - A;Z Objective: Return $1 of file1 if, . $1 of file2 matches $3 of file1 and, . any single element (separated by ";") in $3 of file2 is present in $2 of file1 In order to... (3 Replies)
Discussion started by: beca123456
3 Replies

6. Shell Programming and Scripting

Print matching fields (if they exist) from two text files

Hi everyone, Given two files (test1 and test2) with the following contents: test1: 80263760,I71 80267369,M44 80274628,L77 80276793,I32 80277390,K05 80277391,I06 80279206,I43 80279859,K37 80279866,K35 80279867,J16 80280346,I14and test2: 80263760,PT18 80279867,PT01I need to do some... (3 Replies)
Discussion started by: gacanepa
3 Replies

7. Shell Programming and Scripting

awk to combine all matching fields in input but only print line with largest value in specific field

In the below I am trying to use awk to match all the $13 values in input, which is tab-delimited, that are in $1 of gene which is just a single column of text. However only the line with the greatest $9 value in input needs to be printed. So in the example below all the MECP2 and LTBP1... (0 Replies)
Discussion started by: cmccabe
0 Replies

8. Shell Programming and Scripting

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

9. UNIX for Advanced & Expert Users

awk print all fields except matching regex

grep -v will exclude matching lines, but I want something that will print all lines but exclude a matching field. The pattern that I want excluded is '/mnt/svn' If there is a better solution than awk I am happy to hear about it, but I would like to see this done in awk as well. I know I can... (11 Replies)
Discussion started by: glev2005
11 Replies

10. Shell Programming and Scripting

AWK Matching Fields and Combining Files

Hello! I am writing a program to run through two large lists of data (~300,000 rows), find where rows in one file match another, and combine them based on matching fields. Due to the large file sizes, I'm guessing AWK will be the most efficient way to do this. Overall, the input and output I'm... (5 Replies)
Discussion started by: Michelangelo
5 Replies
Login or Register to Ask a Question