Hi, I need help to update values 2

[amiliq]

Hi,

I need help to update values in my file1.txt. I need a script to check and match the ID in file1.txt and match against ID in file2.txt. If matched, the value for START should be updated by the values in $3 (file2.txt) and remains those unmatched. I tried a code something like this:

awk  'NR==FNR{if ($0~/exNum 1/) {rp[++cnt]=$3};next};
      /START/    {$2 = rp[++xtr]}
      1
    ' file2 file1

but, my output screwed as there are unmatched IDs that i need them to be remained unchanged in the file1.txt

My files are-

file1.txt

ID    P_200 
START    12412 
END    12444 
// 
ID    P_6  
START   235411   
END    18763  
//  
ID    P_10  
START    631012  
END    32814  
//  
ID    P_60 
START    3112 
END    3281 
// 
ID    P_9 
START    5812 
END    6112 
// 
ID    P_133 
START    389417  
END    314124  
//

file2.txt

ex    37193    37735    P_10    
S     37193    37735    P_10     exNum 5
ex    37862    38019    P_10    
S     37862    38019    P_10     exNum 4
ex    38076    38835    P_10    
S     38076    38835    P_10     exNum 3
ex    38880    39050    P_10    
S     38880    39050    P_10     exNum 2
ex    39093    39644    P_10    
S     39093    39644    P_10     exNum 1
ex    21204    22151    P_6    
S     21204    22151    P_6     exNum 2
ex    22217    22765    P_6    
S     22217    22765    P_6     exNum 1
ex    42657    42674    P_133    
S     42657    42674    P_133     exNum 1

The output should remain those unmatched ID and supposed to be like this:

ID    P_200
START    12412
END    12444
@@
ID    P_6 
START   22765 
 END    18763 
@@ 
ID    P_10 
START    39644 
END    32814 
@@
 ID    P_60
START    3112
END    3281
@@
ID    P_9
START    5812
END    6112
@@
ID    P_133
START    42674 
END    314124 
@@

The START values for P_6, P_10 and P_133 should be updated with new values from file2.txt, while the values of START for ID P_200, P_60 and P_9 should remain unchanged as there are no match in file2 for all of them. i have thousands of data to work on and your help on this is highly appreciated. Thanks

 

[PHV]

A starting point:

awk '
NR==FNR{if($0~/exNum 1/)rp[$4]=$3;next}
/^ID/{p=$2}
/^START/{if(p in rp)$2=rp[p]}
1
' file2.txt file1.txt

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[amiliq]

Hi PHV,

It worked great!!
But, can you pls explain to me about the script?
Thanks so much for your kind help.

 

[Annihilannic]

awk '
[COLOR=#006600]# process the first file[/color]
[COLOR=#00B0B0]NR[/color]==[COLOR=#00B0B0]FNR[/color]{
  [COLOR=#006600]# for lines containing "exNum 1"[/color]
  [COLOR=#0000FF]if[/color]($[COLOR=#FF0000]0[/color]~/exNum [COLOR=#FF0000]1[/color]/) {
    [COLOR=#006600]# save the replacement value, indexed by P_number [/color]
    rp[$[COLOR=#FF0000]4[/color]]=$[COLOR=#FF0000]3[/color]
  }
  [COLOR=#006600]# skip to next line and start over[/color]
  [COLOR=#FF0000]next[/color]
}

[COLOR=#006600]# process the second file[/color]
[COLOR=#006600]# save the P_number[/color]
/^ID/{p=$[COLOR=#FF0000]2[/color]}
/^START/{
  [COLOR=#006600]# if there is a replacement value for this P_number[/color]
  [COLOR=#0000FF]if[/color](p [COLOR=#0000FF]in[/color] rp) {
    [COLOR=#006600]# replace the second field with the replacement value[/color]
    $[COLOR=#FF0000]2[/color]=rp[p]
  }
}
[COLOR=#006600]# print all lines from second file[/color]
[COLOR=#FF0000]1[/color]
' file2.txt file1.txt

Annihilannic
[small]tgmlify - code syntax highlighting for your tek-tips posts[/small]

 

[amiliq]

Hi Annihilannic,

Thanks so much! Now i understand how it works...

 

[Annihilannic]

(Correction of broken TGML in my previous post.)

awk '
# process the first file
NR==FNR{
 # for lines containing "exNum 1"
 if($0~/exNum 1/) {
   # save the replacement value, indexed by P_number 
   rp[$4]=$3
 }
 # skip to next line and start over
 next
}

# process the second file
# save the P_number
/^ID/{p=$2}
/^START/{
 # if there is a replacement value for this P_number
 if(p in rp) {
   # replace the second field with the replacement value
   $2=rp[p]
 }
}
# print all lines from second file
1
' file2.txt file1.txt

Annihilannic
[small]tgmlify - code syntax highlighting for your tek-tips posts[/small]