Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk to match file1 and extract specific tag values

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 file1 and extract specific tag values
# 1  
Old 01-02-2019
awk to match file1 and extract specific tag values
File2 is tab-delimeted and I am trying to use $2 in file1 (space delimeted) as a search term in file2. If it is found then the AF= in and the FDP= values from file2 are extracted and printed next to the file1 line. I commented the awk before I added the lines in bold the current output resulted. I also can not figure out how to change the FDP= to READS=. Thank you Smilie.

Code:
script.awk

BEGIN { OFS="\t" }
    # Read $2 search terms from file1 into 's'
      FNR==NR {
          s[$2]
          next
}
    { match($8,/'"'"'normalizedPos'"'"':'"'"'/, pos)  # extract and read into pos value after :
    }
     { match($8,/(AF=[^;]+).*(FDP=[^;]+)/, info) # extract and read into info value after AF= and FDP=
      print $1":"pos[1],gene[1],info[2],info[1] ($1 in s ? s[$1] : "NOT DETECTED")  # print desired output
     }
	 
awk -f script.awk file FS='\t' file2

Code:
file1  space delimited

AKT1 c.49G>A p.E17K
AKT1 c.155T>G p.L52R
APC c.4033G>T p.E1345*
EGFR c.2237_2255delAATTAAGAGAAGCAACATCinsT p.E746_S752delinsV

Code:
file2  tab-delimited

chr5	112175324	COSM18759	G	T	5141.14	PASS	AF=0.248124;AO=1844;DP=7607;FAO=496;FDP=1999;FDVR=10;FR=.;FRO=1503;FSAF=248;FSAR=248;FSRF=764;FSRR=739;FWDB=0.00324498;FXX=4.99998E-4;HRUN=1;HS_ONLY=0;LEN=1;MLLD=196.753;OALT=T;OID=COSM18759;OMAPALT=T;OPOS=112175324;OREF=G;PB=.;PBP=.;QD=10.2874;RBI=0.0620718;REFB=-6.9359E-4;REVB=-0.0619869;RO=5728;SAF=921;SAR=923;SRF=2898;SRR=2830;SSEN=0;SSEP=0;SSSB=-0.0082562;STB=0.506253;STBP=0.772;TYPE=snp;VARB=0.00209058;HS;FUNC=[{'origPos':'112175324','origRef':'G','normalizedRef':'G','gene':'APC','normalizedPos':'112175324','normalizedAlt':'T','gt':'pos','codon':'TAA','coding':'c.4033G>T','transcript':'NM_000038.5','function':'nonsense','protein':'p.Glu1345Ter','location':'exonic','origAlt':'T','exon':'16'}]	GT:GQ:DP:FDP:RO:FRO:AO:FAO:AF:SAR:SAF:SRF:SRR:FSAR:FSAF:FSRF:FSRR	0/1:5141:7607:1999:5728:1503:1844:496:0.248124:923:921:2898:2830:248:248:764:739
chr7	55242467	COSM23571;COSM6254;COSM12369;COSM12386;COSM6220;COSM12384	AATTAAGAGAAGCAACATC	AATC,TATC,A,T	2127.65	PASS	AF=0.0,0.0,0.0,0.139582;AO=0,0,0,0;DP=6091;FAO=0,0,0,274;FDP=1963;FDVR=10,10,10,10;FR=.,.,.,.;FRO=1689;FSAF=0,0,0,173;FSAR=0,0,0,101;FSRF=1017;FSRR=672;FWDB=-0.00907011,-0.0112182,-0.0151195,-0.0172792;FXX=0.0184999;HRUN=2,2,2,2;HS_ONLY=0;LEN=4,4,18,18;MLLD=539.227,630.185,613.658,627.795;OALT=-,T,-,T,-,-;OID=COSM23571,COSM12386,COSM6220,COSM12384,COSM6254,COSM12369;OMAPALT=AATC,TATC,A,T,AATC,AATC;OPOS=55242468,55242467,55242468,55242467,55242469,55242470;OREF=ATTAAGAGAAGCAAC,AATTAAGAGAAGCAAC,ATTAAGAGAAGCAACATC,AATTAAGAGAAGCAACATC,TTAAGAGAAGCAACA,TAAGAGAAGCAACAT;PB=.,.,.,.;PBP=.,.,.,.;QD=4.33551;RBI=0.00953953,0.0170328,0.0229596,0.0226107;REFB=0.00154032,-0.0127049,0.00689381,0.0103482;REVB=0.00295562,-0.0128166,-0.0172784,-0.0145834;RO=5942;SAF=0,0,0,0;SAR=0,0,0,0;SRF=3671;SRR=2271;SSEN=0,0,0,0;SSEP=0,0,0,0;SSSB=0,0,0,0;STB=0.5,0.5,0.5,0.526658;STBP=1.0,1.0,1.0,0.373;SUBSET=.,.,4,.;TYPE=complex,complex,del,del;VARB=0.0,0.0,0.0,-0.0673064;HS;FUNC=[{'origPos':'55242467','origRef':'AATTAAGAGAAGCAACATC','normalizedRef':'AATTAAGAGAAGCAACATC','gene':'EGFR','normalizedPos':'55242467','normalizedAlt':'T','gt':'pos','coding':'c.2237_2255delAATTAAGAGAAGCAACATCinsT','transcript':'NM_005228.3','function':'nonframeshiftBlockSubstitution','protein':'p.Glu746_Ser752delinsVal','location':'exonic','origAlt':'T','exon':'19'}]	GT:GQ:DP:FDP:RO:FRO:AO:FAO:AF:SAR:SAF:SRF:SRR:FSAR:FSAF:FSRF:FSRR	0/4:2127:6091:1963:5942:1689:0,0,0,0:0,0,0,274:0.0,0.0,0.0,0.139582:0,0,0,0:0,0,0,0:3671:2271:0,0,0,101:0,0,0,173:1017:672

Code:
current output

chr5:		FDP=1999	AF=0.248124
chr7:		FDP=1963	AF=0.0,0.0,0.0,0.139582

Code:
desiered

AKT1 c.49G>A p.E17K	chr5:112175324 	NOT DETECTED
AKT1 c.155T>G p.L52R	chr7:55242467	NOT DETECTED
APC c.4033G>T p.E1345*	READS=1999	AF=0.248124
EGFR c.2237_2255delAATTAAGAGAAGCAACATCinsT p.E746_S752delinsV	READS=1963	AF=0.0,0.0,0.0,0.139582

# 2  
Old 01-03-2019
Your first error is that '"'"' is the sequence to have a literal ' within a 'string' in the shell.
You need it when you have an embedded awk code in a shell script or shell command.
Within a pure awk script it is simply a '
This User Gave Thanks to MadeInGermany For This Post:
cmccabe
# 3  
Old 01-04-2019
I think you want a lookup table from file1: $1 is the lookup key and $2 the returned value.
Code:
# in file 1 store the $2 values in s[$1] (s[] indexed by $1)
FNR==NR {
  s[$1]=$2
  next
}

Now you can test existence with (key in s) or get the value with s[key] or loop through the keys with for (key in s).

I think the following captures the wanted values in file2:
Code:
{
  match($8,/'normalizedPos':'([^']+)'/, pos)  # extract value after : into pos[]
  match((";" $8),/;(AF=[^;]+).*;(FDP=[^;]+)/, info) # extract values after AF= and FDP= into info[]
  match($8,/'gene':'([^']+)'/,gene) # extract the value after gene into gene[]
  print "pos[1]="pos[1], "info[1]="info[1], "info[2]="info[2], "gene[1]="gene[1]
}

Note1: the target array as 3rd argument is an extension in GNU-awk. (Maybe you want to consider perl? Then it works on all platforms that have perl.)
Note2: for a most exact string match use a "border character". I have even prepended a ";" to the input string for match() in order to ensure it exists even at the beginning.
This User Gave Thanks to MadeInGermany For This Post:
cmccabe
# 4  
Old 01-04-2019
If Perl is an option, then here's a sample program:


Code:
$
$ cat -n process_files.pl
     1  #!/usr/bin/perl
     2  use strict;
     3
     4  # Set the file names
     5  my $search_file = "file1";
     6  my $data_file = "file2";
     7  my $temp_file = "file1.tmp";
     8
     9  # $sf = The search file; the file from where the search terms are read.
    10  # $df = The data file; the file to be scanned with the search term to fetch
    11  #       values of AF and FDP
    12  # $tf = The temporary file; where the final results are stored temporarily.
    13  #       After all processing is done, you can either move or copy the temp file
    14  #       to the search file. This is much safer than in-place editing of search
    15  #       file.
    16  open(my $sf, "<", $search_file) or die "Can't open $search_file: $!";
    17  open(my $df, "<", $data_file) or die "Can't open $data_file: $!";
    18  open(my $tf, ">", $temp_file) or die "Can't open $temp_file: $!";
    19
    20  # Loop through the search file
    21  while (<$sf>) {
    22      chomp(my $line1 = $_);
    23      # For each line in the search file, extract the search term, which is
    24      # the second "word" from left.
    25      (my $search_term) = $line1 =~ /^\S+\s+(\S+).*$/;
    26
    27      # Initialize AF and FDP to zero-length strings. If the search term is
    28      # not found in data file, we use this fact to print "NOT DETECTED".
    29      my ($af, $fdp) = ("", "");
    30
    31      # Now loop through the data file looking for the search term.
    32      while (<$df>) {
    33          chomp(my $line2 = $_);
    34          # If the search term was found in a particular line of data file,
    35          # set AF and FDP values and stop processing the data file.
    36          if ($line2 =~ /$search_term/) {
    37              ($af, $fdp) = $line2 =~ /AF=(.*?);.*FDP=(.*?);/;
    38              last;
    39          }
    40      }
    41
    42      # If AF and FDP were set, print their values, else "NOT DETECTED".
    43      if ($af eq "" and $fdp eq "") {
    44          print $tf "$line1 NOT DETECTED\n";
    45      } else {
    46          print $tf "$line1 READS=[$fdp] AF=[$af]\n";
    47      }
    48
    49      # Now rewind; set the pointer back to the beginning of data file.
    50      # For the next search term, we will start searching from the top of the
    51      # data file.
    52      seek($df, 0, 0);
    53  }
    54
    55  close($sf) or die "Can't close $search_file: $!";
    56  close($df) or die "Can't close $data_file: $!";
    57  close($tf) or die "Can't close $temp_file: $!";
    58
    59  # At this point, we have the original file "file1" and a temp file "file1.tmp"
    60  # with the desired output. The following statements retain a backup of the
    61  # original file.
    62  my $sf_orig = "file1.orig";
    63  rename($search_file, $sf_orig) or die "Can't rename $search_file to $sf_orig: $!";
    64  rename($temp_file, $search_file) or die "Can't rename $temp_file to $search_file: $!";
    65

$
$ perl process_files.pl
$
$ cat file1
AKT1 c.49G>A p.E17K NOT DETECTED
AKT1 c.155T>G p.L52R NOT DETECTED
APC c.4033G>T p.E1345* READS=[1999] AF=[0.248124]
EGFR c.2237_2255delAATTAAGAGAAGCAACATCinsT p.E746_S752delinsV READS=[1963] AF=[0.0,0.0,0.0,0.139582]

$
$

These 2 Users Gave Thanks to durden_tyler For This Post:
cmccabe MadeInGermany
# 5  
Old 01-05-2019
Thank you both for the help Smilie.
# 6  
Old 01-08-2019
Hi cmccabe ,
In post #1, you said that the desired output from your two sample input files is:
Code:
AKT1 c.49G>A p.E17K	chr5:112175324 	NOT DETECTED
AKT1 c.155T>G p.L52R	chr7:55242467	NOT DETECTED
APC c.4033G>T p.E1345*	READS=1999	AF=0.248124
EGFR c.2237_2255delAATTAAGAGAAGCAACATCinsT p.E746_S752delinsV	READS=1963	AF=0.0,0.0,0.0,0.139582

Given that there is no record in file2 that contains the file1 field 2 values as a coding value in the single-quoted sub-subfields of field 8 in file2 why were the values show in red in the above output selected as the desired output? (I note that the output produced by the perl script durden_tyler suggested omits that field from the output and you didn't comment on that discrepancy.)

Other than having no idea how the 3rd output field in the first two records of your desired output is supposed to be selected, I think I can come up with an awk script that does what you want just using standard awk features (without the gawk extensions your current code is trying to use).
This User Gave Thanks to Don Cragun For This Post:
cmccabe
# 7  
Old 01-12-2019
@Don Cragun the fields in red are a typo on my part (they are not needed). I apologize and thank you Smilie. A standard awk would be great as I was trying but could not figure it out.
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 extract tag and add to each line

In the awk below which executes as is, I am trying to add a condition that will extract the text or value after the FR= for the lines in each line of file1 compared to file2. As is the lines between the two files are either a match, Missing in file 1, or Missing in file2, but I can not add the... (1 Reply)
Discussion started by: cmccabe
1 Replies

2. Shell Programming and Scripting

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

3. Shell Programming and Scripting

awk to update specific value in file with match and add +1 to specific digit

I am trying to use awk to match the NM_ in file with $1 of id which is tab-delimited. The NM_ will always be in the line of file that starts with > and be after the second _. When there is a match between each NM_ and id, then the value of $2 in id is substituted or used to update the NM_. Each NM_... (3 Replies)
Discussion started by: cmccabe
3 Replies

4. Shell Programming and Scripting

Return path of specific tag using awk

The below awk is used with the attached index.html and matches the specific user id in the sub portion with path of /rundb/api/v1/plugin/49/. The command does run but the output is blank. Something changed in the file structure as it used to work. So using the first line in the output: ... (2 Replies)
Discussion started by: cmccabe
2 Replies

5. Shell Programming and Scripting

awk to print string if tag is specific value

In the below awk I am trying to print expName only if another tag planExecuted is true. In addition to the expName I am also printing planShortID. For some reason the word experiment gets printed so I remove it with sed. I have attached the complete index.html as well as included a sample of it... (1 Reply)
Discussion started by: cmccabe
1 Replies

6. Shell Programming and Scripting

Extract values from a specific column to the end

Hello friends, I have a text file with many columns (no. columns vary from row to row) separated by space. I need to collect all the values from 18th column to the end from each line and group them as pairs and then numbering like below.. 1. 18th-col-value 19th-col-value 2. 20th-col-value ... (5 Replies)
Discussion started by: prvnrk
5 Replies

7. Shell Programming and Scripting

Get values from different columns from file2 when match values of file1

Hi everyone, I have file1 and file2 comma separated both. file1 is: Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10 Code7,,,,,,,,, Code5,,,,,,,,, Code3,,,,,,,,, Code9,,,,,,,,, Code2,,,,,,,,,file2... (17 Replies)
Discussion started by: cgkmal
17 Replies

8. Shell Programming and Scripting

Extract all content that match exactly only specific word

Input: 21 templeta parent 35718 36554 . - . ID=parent_cluster_50.21.11; Name=Partial%20parent%20for%20training%20set; 21 templeta kids 35718 36554 . - . ID=_52; Parent=parent_cluster_5085.21.11; 21 templeta ... (7 Replies)
Discussion started by: patrick87
7 Replies

9. Shell Programming and Scripting

AWK: read values from file1; search for values in file2

I have read another post about this issue and am wondering how to adapt it to my own, much simpler, issue. I have a file of user IDs like so: 333333 321321 546465 ...etc I need to take each number and use it to print records wherein the 5th field matches the user ID pulled from the... (2 Replies)
Discussion started by: Bubnoff
2 Replies

10. Shell Programming and Scripting

to extract specific values twice in a file

Hi Friends, I have a file with the following values.. xyz.txt,12345.xml abc.txt,04567.xml cde.txt,12134.xml I would like to extract all the 2nd column values twice as shown in the example like 12345,12345.xml 04567,04567.xml 12134,12134.xml Please advice!! In the formus one of... (7 Replies)
Discussion started by: techmoris
7 Replies
Login or Register to Ask a Question