I'm trying to use a lookup table to do a search and replace for two specific columns and keep getting a blank column as output. I've followed the syntax for several examples of lookup tables that I've found on stack, but no joy. Here is a snippet from each of the files.
Sample lookup table -- want to search for instances of column 1 in my data file and replace them with the corresponding value in column 2 (first row is a header):
#xyz type
N 400
C13 401
13A 402
13B 402
13C 402
C14 405
The source file to be substituted has the following format:
1 N 0.293000 2.545000 16.605000 0 2 6 10 14
2 C13 0.197000 2.816000 15.141000 0 1
3 13A 1.173000 2.887000 14.676000 0
4 13B -0.319000 3.756000 14.937000 0
5 13C -0.351000 1.998000 14.678000 0
6 C14 0.749000 3.776000 17.277000 0 1
The corresponding values in column 2 of the lookup table will replace the values in column 6 of my source file (currently all zeroes). Here's the awk one-liner that I thought should work:
awk -v OFS='\t' 'NR==1 { next } FNR==NR { a[$1]=$2; next } $2 in a { $6=a[$1] }1' lookup.txt source.txt
But my output essentially deletes the entire entry for column 6:
1 N 0.293000 2.545000 16.605000 2 6 10 14
2 C13 0.197000 2.816000 15.141000 1
3 13A 1.173000 2.887000 14.676000
4 13B -0.319000 3.756000 14.937000
5 13C -0.351000 1.998000 14.678000
6 C14 0.749000 3.776000 17.277000 1
(The sixth column should be 400 to 405. I considered using sed, but I have duplicate values in the source and output columns of my lookup table, so that won't work in this case. What's frustrating is that I had this one-liner working on almost the exact same source file the other week, but now can only get this behavior. I'd love to be able to modify my awk call to do lookups of two different columns simultaneously, but wanted to start simple for now. Thanks!