1

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
Improve this question
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 Commented Jun 8, 2021 at 11:08
  • I need this question to solve some problems with more specific code. Commented Jun 8, 2021 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. Commented Jun 8, 2021 at 12:09

1 Answer 1

Reset to default
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
Improve this answer
0

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.