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[$1] ; next}  $1 in A { $2 = a[1] }1' file1 file2

file1

name    version
NM_000593    5
NM_001257406    1
NM_000418    3
NM_021806    3

file2

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

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,
 gawk 'FNR==NR {A[$1]=$2; next}  $2 in A { $2=$2 "." A[$2] }1' file1 file2
1 Like

To sort of keep the formatting, try a small adaption to Aia's fine proposal:

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.

1 Like

Thank you very much :).