Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk to update value in field based on another field

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to update value in field based on another field
# 1  
Old 12-01-2016
awk to update value in field based on another field
In the tab-delimeted input file below I am trying to use awk to update the value in $2 if TYPE=ins in bold, by adding the value of
HRUN= in italics. In the below since in line 1 TYPE=ins the 117282541 value in $2 has 6 added because that is the value of HRUN=.
Hopefully the awk is a start but I am not how to add the digit in HRUN=, as this will always be different. If TYPE= is anything else besides ins
then the line is printed as is. Thank you Smilie.


input
Code:
chr7	117282541	.	C	CT	19.2911	PASS	AF=0.219512;AO=30;DP=130;FAO=27;FDP=123;FR=.;FRO=96;FSAF=17;FSAR=10;FSRF=49;FSRR=47;FWDB=-0.0766145;FXX=0.00806387;HRUN=6;LEN=1;MLLD=7.86443;OALT=T;OID=.;OMAPALT=CT;OPOS=117282542;OREF=-;QD=0.627353;RBI=0.20132;REFB=-0.0755381;REVB=0.186172;RO=74;SAF=22;SAR=8;SRF=38;SRR=36;SSEN=0;SSEP=0;SSSB=0.282205;STB=0.594772;STBP=0.284;TYPE=ins;VARB=-0.0654345;FUNC=[{'origPos':'117282541','origRef':'C','normalizedRef':'C','gene':'CFTR','normalizedPos':'117282541','normalizedAlt':'CT','gt':'pos','codon':'TTT','coding':'c.3767_3768insT','transcript':'NM_000492.3','function':'frameshiftInsertion','protein':'p.Leu1258fs','location':'exonic','origAlt':'CT','exon':'23'}]	GT:GQ:DP:FDP:RO:FRO:AO:FAO:AF:SAR:SAF:SRF:SRR:FSAR:FSAF:FSRF:FSRR	0/1:19:130:123:74:96:30:27:0.219512:8:22:38:36:10:17:49:47
chr7	117417559	.	A	G	173.255	PASS	AF=0.415254;AO=49;DP=118;FAO=49;FDP=118;FR=.,REALIGNEDx0.4322;FRO=69;FSAF=24;FSAR=25;FSRF=35;FSRR=34;FWDB=0.00735029;FXX=0;HRUN=1;LEN=1;MLLD=114.133;OALT=G;OID=.;OMAPALT=G;OPOS=117417559;OREF=A;QD=5.87305;RBI=0.0298232;REFB=0.00339785;REVB=0.0289033;RO=69;SAF=24;SAR=25;SRF=35;SRR=34;SSEN=0;SSEP=0;SSSB=-0.0181233;STB=0.5102;STBP=0.892;TYPE=snp;VARB=-0.00586008;FUNC=[{'transcript':'NM_033427.2','gene':'CTTNBP2','location':'splicesite_3','exon':'8'}]	GT:GQ:DP:FDP:RO:FRO:AO:FAO:AF:SAR:SAF:SRF:SRR:FSAR:FSAF:FSRF:FSRR	0/1:173:118:118:69:69:49:49:0.415254:25:24:35:34:25:24:35:34

desired output tab-delimeted
Code:
chr7	117282547	.	C	CT	19.2911	PASS	AF=0.219512;AO=30;DP=130;FAO=27;FDP=123;FR=.;FRO=96;FSAF=17;FSAR=10;FSRF=49;FSRR=47;FWDB=-0.0766145;FXX=0.00806387;HRUN=6;LEN=1;MLLD=7.86443;OALT=T;OID=.;OMAPALT=CT;OPOS=117282542;OREF=-;QD=0.627353;RBI=0.20132;REFB=-0.0755381;REVB=0.186172;RO=74;SAF=22;SAR=8;SRF=38;SRR=36;SSEN=0;SSEP=0;SSSB=0.282205;STB=0.594772;STBP=0.284;TYPE=ins;VARB=-0.0654345;FUNC=[{'origPos':'117282541','origRef':'C','normalizedRef':'C','gene':'CFTR','normalizedPos':'117282541','normalizedAlt':'CT','gt':'pos','codon':'TTT','coding':'c.3767_3768insT','transcript':'NM_000492.3','function':'frameshiftInsertion','protein':'p.Leu1258fs','location':'exonic','origAlt':'CT','exon':'23'}]	GT:GQ:DP:FDP:RO:FRO:AO:FAO:AF:SAR:SAF:SRF:SRR:FSAR:FSAF:FSRF:FSRR	0/1:19:130:123:74:96:30:27:0.219512:8:22:38:36:10:17:49:47
chr7	117417559	.	A	G	173.255	PASS	AF=0.415254;AO=49;DP=118;FAO=49;FDP=118;FR=.,REALIGNEDx0.4322;FRO=69;FSAF=24;FSAR=25;FSRF=35;FSRR=34;FWDB=0.00735029;FXX=0;HRUN=1;LEN=1;MLLD=114.133;OALT=G;OID=.;OMAPALT=G;OPOS=117417559;OREF=A;QD=5.87305;RBI=0.0298232;REFB=0.00339785;REVB=0.0289033;RO=69;SAF=24;SAR=25;SRF=35;SRR=34;SSEN=0;SSEP=0;SSSB=-0.0181233;STB=0.5102;STBP=0.892;TYPE=snp;VARB=-0.00586008;FUNC=[{'transcript':'NM_033427.2','gene':'CTTNBP2','location':'splicesite_3','exon':'8'}]	GT:GQ:DP:FDP:RO:FRO:AO:FAO:AF:SAR:SAF:SRF:SRR:FSAR:FSAF:FSRF:FSRR	0/1:173:118:118:69:69:49:49:0.415254:25:24:35:34:25:24:35:34

Code:
awk -F'\t' -v OFS='\t' '{ if (TYPE == "ins") $2=$2+HRUN={x}; print $0 }' file

Last edited by cmccabe; 12-01-2016 at 03:10 PM.. Reason: added details
# 2  
Old 12-01-2016
Hello cmccabe,

Could you please try following and let us know how it goes then.
Code:
awk  -F"\t" 'NF{;split($(NF-2), A,";");split(A[36], B,"=");if(B[2]=="ins"){split(A[14], C,"=");$2+=C[2];}} 1'   OFS="\t"  Input_file

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
cmccabe
# 3  
Old 12-03-2016
Thank you very much Smilie.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Problem with getting awk to multiply a field by a value set based on condition of another field

Hi, So awk is driving me crazy on this one. I have searched everywhere and read man, docs and every related post Google can find and still no luck. The actual files I need to run this on are sensitive in nature, but it is the same thing as if I needed to calculate weighted grades for multiple... (15 Replies)
Discussion started by: cotilloe
15 Replies

2. Shell Programming and Scripting

Update a specific field in file with Variable value based on other Key Word

I have an input file with A=xyz B=pqr I would want the value in Second Field (xyz or pqr) updated with a value present in Shell Variable based on the value passed in the first field. (A or B ) while read line do NEW_VALUE = `some functionality done on $line` If $line=First Field-... (1 Reply)
Discussion started by: infernalhell
1 Replies

3. Shell Programming and Scripting

awk to assign points to variables based on conditions and update specific field

I have been reading old posts and trying to come up with a solution for the below: Use a tab-delimited input file to assign point to variables that are used to update a specific field, Rank. I really couldn't find too much in the way of assigning points to variable, but made an attempt at an awk... (4 Replies)
Discussion started by: cmccabe
4 Replies

4. Shell Programming and Scripting

Perl to update field based on a specific set of rules

In the perl below, which does execute, I am having trouble with the else in Rule 3. The digit in f{8} is extracted and used to update f accordinly along with the value in f. There can be either - * or + before the number that is extracted but the same logic applies, that is if the value is greater... (5 Replies)
Discussion started by: cmccabe
5 Replies

5. Shell Programming and Scripting

awk to update field using matching value in file1 and substring in field in file2

In the awk below I am trying to set/update the value of $14 in file2 in bold, using the matching NM_ in $12 or $9 in file2 with the NM_ in $2 of file1. The lengths of $9 and $12 can be variable but what is consistent is the start pattern will always be NM_ and the end pattern is always ;... (2 Replies)
Discussion started by: cmccabe
2 Replies

6. Shell Programming and Scripting

awk to adjust coordinates in field based on sequential numbers in another field

I am trying to output a tab-delimited result that uses the data from a tab-delimited file to combine and subtract specific lines. If $4 matches in each line then the first matching sequential $6 value is added to $2, unless the value is 1, then the original $2 is used (like in the case of line... (3 Replies)
Discussion started by: cmccabe
3 Replies

7. Shell Programming and Scripting

awk to update field in file based of match in another

I am trying to use awk to match two files that are tab-delimited. When a match is found between file1 $1 and file2 $4, $4 in file2 is updated using the $2 value in file1. If no match is found then the next line is processed. Thank you :). file1 uc001bwr.3 ADC uc001bws.3 ADC... (4 Replies)
Discussion started by: cmccabe
4 Replies

8. Shell Programming and Scripting

awk to update field file based on match

If $1 in file1 matches $2 in file2. Then the value in $2 of file2 is updated to $1"."$2 of file2. The awk seems to only match the two files but not update. Thank you :). awk awk 'NR==FNR{A ; next} $1 in A { $2 = a }1' file1 file2 file1 name version NM_000593 5 NM_001257406... (3 Replies)
Discussion started by: cmccabe
3 Replies

9. Shell Programming and Scripting

Update a field in a file based on condition

Hi i am new to scripting. i have a file file.dat with content as : CONTENT_STORAGE PERCENTAGE FLAG: /storage_01 64% 0 /storage_02 17% 1 I need to update the value of FLAG for a particular CONTENT_STORAGE value I have written the following code #!/bin/sh threshold=20... (1 Reply)
Discussion started by: kichu
1 Replies

10. UNIX for Dummies Questions & Answers

awk - Summing a field based on another field

So, I need to do some summing. I have an Apache log file with the following as a typical line: 127.0.0.1 - frank "GET /apache_pb.gif HTTP/1.0" 200 2326 Now, what I'd like to do is a per-minute sum. So, I can have awk tell me the individual minutes, preserving the dates(since this is a... (7 Replies)
Discussion started by: treesloth
7 Replies
Login or Register to Ask a Question