2

I ran the following code to replace the second columns of two files (file2 and file3) with column 12 from file source.dat, based on the 9th column of source.dat that should match to the first columns of file2 and file3.

So far, I tried

for f in fil*; do 
    awk 'NR==FNR{ar[NR]=$12;next} {for (i=1;i<=length(ar);i++) if (FNR==i) $2=ar[i]}1' source.dat "$f" > temp
    awk '{printf("%.4f %12.8f     1.000     1    1\n", $1, $2)}' temp > "${f}_bvs"
done

Output from file2 is correct except for containing an extra line in the end; however, the output for file3 contains other values in the second column. What is wrong, please?

  • source.dat
        1 2000 11 11  7  9 45  840                         49667.8048  18.33  HeI  6.10352e-05
        2 2000 11 11  8 56 57  660                         49667.8782  18.15  HeI   0.00546265
        3 1994 11 12  5 18 10 1020                         49668.7284  18.34  HeI  -0.00497437
        4 1994 11 12  7 35 30  840                         49668.8227  18.14  HeI  -0.00357056
        5 1994 11 12  9  6 42  720                         49668.8854  17.99  HeI  -0.00476074
        6 1994 11 14  5 20 43  600                         49670.7279  18.04  HeI  -0.00326538
        7 1994 11 14  7 32 46  630                         49670.8197  17.84  HeI  -0.00598145
        8 3000 11 14  9 21 14  540                         49670.8945  17.66  HeI   0.00701904
        9 1994 11 15  5 21 14  610                         49671.7283  17.88  HeI  -0.00100708
       10 4445 11 15  7  4  5  540                         49671.7994  17.73  HeI  -0.00503540
       11 1994 11 15  9  1 14  600                         49671.8811  17.53  HeI     0.000000
       12 1996  1 11  0 56  4  301                         50093.5444   2.26  HeI   0.00570679
       13 1996  1 11  1  2 30  601                         50093.5506   2.25  HeI   0.00424194
       14 1996  1 11  1 15 23  541                         50093.5592   2.23  HeI   0.00100708
       15 1996  1 11  1 26 29  420                         50093.5662   2.22  HeI   0.00372314
    
  • file2
    49667.8048   78.450     3.000     1    1
    49667.8782   79.900     1.000     1    1
    49668.7284   40.890     1.000     1    1
    49668.8227   45.790     1.000     1    1
    49668.8854   49.770     5.000     1    1
    49670.7279   66.060     1.000     1    1
    49670.8197   47.380     1.000     1    1
    49670.8945   27.270     6.000     1    1
    49671.7283   66.190     1.000     1    1
    49671.7994   65.320     6.000     1    1
    49671.8811   62.290     1.000     1    1
    
  • file3
    50093.5444   13.480     1.000     1    1
    50093.5506   14.830     1.000     1    1
    50093.5592   12.150     1.000     1    1
    50093.5662   12.150     1.000     1    1
    

Current output

  • for file2
    49667.8048   0.00006104     1.000     1    1
    49667.8782   0.00546265     1.000     1    1
    49668.7284  -0.00497437     1.000     1    1
    49668.8227  -0.00357056     1.000     1    1
    49668.8854  -0.00476074     1.000     1    1
    49670.7279  -0.00326538     1.000     1    1
    49670.8197  -0.00598145     1.000     1    1
    49670.8945   0.00701904     1.000     1    1
    49671.7283  -0.00100708     1.000     1    1
    49671.7994  -0.00503540     1.000     1    1
    49671.8811   0.00000000     1.000     1    1
    0.0057   0.00000000     1.000     1    1
    
  • for file3
    50093.5444   0.00006104     1.000     1    1
    50093.5506   0.00546265     1.000     1    1
    50093.5592  -0.00497437     1.000     1    1
    50093.5662  -0.00357056     1.000     1    1
    
3
  • I'm voting to close as a duplicate since I don't see any difference with your previous question: How to replace a column in a file matching to another column
    – xhienne
    Jun 8 at 11:08
  • I need this question to solve some problems with more specific code.
    – Elena Greg
    Jun 8 at 11:11
  • The question is the same and the accepted answer is just a variation of the accepted answer there, which would equally work here. The only difference is that file1 and file2 have been swapped. This is the very definition of a duplicate question.
    – xhienne
    Jun 8 at 12:09
1

You should be able to do that using join:

~$ join -1 1 -2 9 -o 1.1,2.12,1.3,1.4,1.5 file2 source.dat 
49667.8048 6.10352e-05 3.000 1 1
49667.8782 0.00546265 1.000 1 1
49668.7284 -0.00497437 1.000 1 1
49668.8227 -0.00357056 1.000 1 1
49668.8854 -0.00476074 5.000 1 1
49670.7279 -0.00326538 1.000 1 1
49670.8197 -0.00598145 1.000 1 1
49670.8945 0.00701904 6.000 1 1
49671.7283 -0.00100708 1.000 1 1
49671.7994 -0.00503540 6.000 1 1
49671.8811 0.000000 1.000 1 1

~$ join -1 1 -2 9 -o 1.1,2.12,1.3,1.4,1.5 file3 source.dat
50093.5444 0.00570679 1.000 1 1
50093.5506 0.00424194 1.000 1 1
50093.5592 0.00100708 1.000 1 1
50093.5662 0.00372314 1.000 1 1

This will instruct join to match file2 (or file3) as first file and source.dat as second file on columns 1 and 9, respectively (-1 1 -2 9) and output the first column of the first file, the 12th from the second, and then the remaining ones (except the original column 2) from the first file.

Edit

It seems that this is a follow-up question to a previous question, and that you want to implement this using awk. In that case, try

~$ awk 'FNR==NR{v[$9]=$12;next} {print $1,v[$1],$3,$4,$5}' source.dat file2 
49667.8048 6.10352e-05 3.000 1 1
49667.8782 0.00546265 1.000 1 1
49668.7284 -0.00497437 1.000 1 1
49668.8227 -0.00357056 1.000 1 1
49668.8854 -0.00476074 5.000 1 1
49670.7279 -0.00326538 1.000 1 1
49670.8197 -0.00598145 1.000 1 1
49670.8945 0.00701904 6.000 1 1
49671.7283 -0.00100708 1.000 1 1
49671.7994 -0.00503540 6.000 1 1
49671.8811 0.000000 1.000 1 1

~$  awk 'FNR==NR{v[$9]=$12;next} {print $1,v[$1],$3,$4,$5}' source.dat file3
50093.5444 0.00570679 1.000 1 1
50093.5506 0.00424194 1.000 1 1
50093.5592 0.00100708 1.000 1 1
50093.5662 0.00372314 1.000 1 1

Or, in a loop

for f in file*; do awk 'FNR==NR{v[$9]=$12;next} {print $1,v[$1],$3,$4,$5}' source.dat "$f" > "${f}_bvs"; done
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.