awk to update field file based on match


 
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:
# 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:
# 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