Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk to update field file based 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 update field file based on match
# 1  
Old 05-31-2016
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 Smilie.

awk
Code:
awk 'NR==FNR{A[$1] ; next}  $1 in A { $2 = a[1] }1' file1 file2


file1
Code:
name    version
NM_000593    5
NM_001257406    1
NM_000418    3
NM_021806    3

file2
Code:
616    NM_000593    chr6_apd_hap1    -    4099989    4108752    4100359    4108597    11    4099989,4101848,4102293,4102699,4103432,4103770,4105098,4105722,4106889,4107168,4107819,    4100566,4101985,4102456,4102873,4103621,4103899,4105296,4105928,4107020,4107283,4108752,    0    TAP1    cmpl    cmpl    0,1,0,0,0,0,0,1,2,1,0,
617    NM_000593    chr6_cox_hap2    -    4257513    4266276    4257883    4266121    11    4257513,4259372,4259817,4260223,4260956,4261294,4262624,4263248,4264413,4264692,4265343,    4258090,4259509,4259980,4260397,4261145,4261423,4262822,4263454,4264544,4264807,4266276,    0    TAP1    cmpl    cmpl    0,1,0,0,0,0,0,1,2,1,0,
616    NM_000593    chr6_dbb_hap3    -    4094363    4103126    4094733    4102971    11    4094363,4096222,4096667,4097073,4097806,4098144,4099472,4100096,4101263,4101542,4102193,    4094940,4096359,4096830,4097247,4097995,4098273,4099670,4100302,4101394,4101657,4103126,    0    TAP1    cmpl    cmpl    0,1,0,0,0,0,0,1,2,1,0,
1757    NM_021806    chrX    -    153734489    153744566    153735141    153744096    9
153734489,153735533,153735736,153736141,153736619,153736804,153740180,153741146,153744083,    153735237,153735660,153735821,153736192,153736678,153736928,153740204,153741260,153744566,    0    FAM3A    cmpl    cmpl    0,2,1,1,2,1,1,1,0,
793    NM_001257406    chr16    +    27325229    27376099    27351524    27375151    10    27325229,27351506,27353441,27356189,27357787,27363860,27367128,27370236,27372086,27373572,    27325341,27351594,27353580,27356341,27357939,27364017,27367228,27370315,27372136,27376099,    0    IL4R    cmpl    cmpl    -1,0,1,2,1,0,1,2,0,2,
793    NM_000418    chr16    +    27325229    27376099    27351524    27375151    11    27325229,27341386,27351506,27353441,27356189,27357787,27363860,27367128,27370236,27372086,27373572,    27325341,27341519,27351594,27353580,27356341,27357939,27364017,27367228,27370315,27372136,27376099,    0    IL4R    cmpl    cmpl    -1,-1,0,1,2,1,0,1,2,0,2,

desired output
Code:
616    NM_000593.5    chr6_apd_hap1    -    4099989    4108752    4100359    4108597    11    4099989,4101848,4102293,4102699,4103432,4103770,4105098,4105722,4106889,4107168,4107819,    4100566,4101985,4102456,4102873,4103621,4103899,4105296,4105928,4107020,4107283,4108752,    0    TAP1    cmpl    cmpl    0,1,0,0,0,0,0,1,2,1,0,
617    NM_000593.5    chr6_cox_hap2    -    4257513    4266276    4257883    4266121    11    4257513,4259372,4259817,4260223,4260956,4261294,4262624,4263248,4264413,4264692,4265343,    4258090,4259509,4259980,4260397,4261145,4261423,4262822,4263454,4264544,4264807,4266276,    0    TAP1    cmpl    cmpl    0,1,0,0,0,0,0,1,2,1,0,
616    NM_000593.5    chr6_dbb_hap3    -    4094363    4103126    4094733    4102971    11    4094363,4096222,4096667,4097073,4097806,4098144,4099472,4100096,4101263,4101542,4102193,    4094940,4096359,4096830,4097247,4097995,4098273,4099670,4100302,4101394,4101657,4103126,    0    TAP1    cmpl    cmpl    0,1,0,0,0,0,0,1,2,1,0,
1757    NM_021806.3    chrX    -    153734489    153744566    153735141    153744096    9
153734489,153735533,153735736,153736141,153736619,153736804,153740180,153741146,153744083,    153735237,153735660,153735821,153736192,153736678,153736928,153740204,153741260,153744566,    0    FAM3A    cmpl    cmpl    0,2,1,1,2,1,1,1,0,
793    NM_001257406.1    chr16    +    27325229    27376099    27351524    27375151    10    27325229,27351506,27353441,27356189,27357787,27363860,27367128,27370236,27372086,27373572,    27325341,27351594,27353580,27356341,27357939,27364017,27367228,27370315,27372136,27376099,    0    IL4R    cmpl    cmpl    -1,0,1,2,1,0,1,2,0,2,
793    NM_000418.3    chr16    +    27325229    27376099    27351524    27375151    11    27325229,27341386,27351506,27353441,27356189,27357787,27363860,27367128,27370236,27372086,27373572,    27325341,27341519,27351594,27353580,27356341,27357939,27364017,27367228,27370315,27372136,27376099,    0    IL4R    cmpl    cmpl    -1,-1,0,1,2,1,0,1,2,0,2,

# 2  
Old 05-31-2016
Quote:
Originally Posted by cmccabe
[...]
Code:
awk 'NR==FNR{A[$1] ; next}  $1 in A { $2 = a[1] }1' file1 file2

[...]
Code:
 gawk 'FNR==NR {A[$1]=$2; next}  $2 in A { $2=$2 "." A[$2] }1' file1 file2

This User Gave Thanks to Aia For This Post:
cmccabe
# 3  
Old 06-01-2016
To sort of keep the formatting, try a small adaption to Aia's fine proposal:

Code:
awk 'FNR==NR {A[$1]=$2; next}  $2 in A {sub ($2, $2 "." A[$2]) }1' file1 file2

To update your files, try redirection && move.
This User Gave Thanks to RudiC For This Post:
cmccabe
# 4  
Old 06-02-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. Shell Programming and Scripting

awk to update file based on match in 3 fields

Trying to use awk to store the value of $5 in file1 in array x. That array x is then used to search $4 of file1 to find aa match (I use x to skip the header in file1). Since $4 can have multiple strings in it seperated by a , (comma), I split them and iterate througn each split looking for a match.... (2 Replies)
Discussion started by: cmccabe
2 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

awk to update value based on pattern match in another file

In the awk, thanks you @RavinderSingh13, for the help in below, hopefully it is close as I am trying to update the value in $12 of the tab-delimeted file2 with the matching value in $1 of the space delimeted file1. I have added comments for each line as well. Thank you :). awk awk '$12 ==... (10 Replies)
Discussion started by: cmccabe
10 Replies

5. Shell Programming and Scripting

Perl to update field in file based of match to another file

In the perl below I am trying to set/update the value of $14 (last field) in file2, 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 ;... (4 Replies)
Discussion started by: cmccabe
4 Replies

6. Shell Programming and Scripting

awk to update file based on partial match in field1 and exact match in field2

I am trying to create a cronjob that will run on startup that will look at a list.txt file to see if there is a later version of a database using database.txt as the source. The matching lines are written to output. $1 in database.txt will be in list.txt as a partial match. $2 of database.txt... (2 Replies)
Discussion started by: cmccabe
2 Replies

7. Shell Programming and Scripting

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

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

9. Shell Programming and Scripting

awk match to update contents of file

I am trying to match $1 in file1 with $2 in file2. If a match is found then $3 and $4 of file2 are copied to file1. Both files are tab-delimeted and I am getting a syntax error and would also like to update file1 in-place without creating a new file, but am not sure how. Thank you :). file1 ... (19 Replies)
Discussion started by: cmccabe
19 Replies

10. 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
Login or Register to Ask a Question