0

I have two files. How to replace the second column in file 2 with column 12 in file 1 in such a way that column 9 in file 1 is the same as column 1 in file 2?

file1

    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

Desired result:

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
......

I tried something like

awk 'NR==FNR{ar[$9]=$0;next}($1 in ar){print ar[$9],$0}' file1 file2

2 Answers 2

2

Using gawk:

$ awk 'NR==FNR{ar[$9]=$12;next}
($1 in ar) {$2= ar[$1]}1'  file1 file2

In this command, NR==FNR{ar[NR]=$12;next} means this rule will be for first file only because FNR would be set to zero after file1. In this rule an array ar is created with contents of field 12 indexed on field 9($9) of first file file1. next prevents further action on file1.

The expression if ($1 in ar) $2=ar[$1] means if first field($1) in found in indices of ar then it changes second field of file2 to ar. This is because ar in indexed on $9 of first file file1. Because $2 of file2 is changed now, 1 will print new $0.

And 1 is awk's idiom for print.

2
  • 1
    Thank you very much and how to do the loop over files when the second input file is that over loop is running? for f in rv*.dat; do awk 'NR==FNR{ar[NR]=$12;next} {for (i=1;i<=length(ar);i++) if (FNR==i) $2=ar[i]}1' cat43_BVS "$f" > temp done How to use $f in this case?
    – Elena Greg
    Jun 8, 2021 at 7:00
  • 1
    @ElenaGreg for f in rv*.dat; do awk 'NR==FNR{ar[NR]=$12;next} {for (i=1;i<=length(ar);i++) if (FNR==i) $2=ar[i]}1' cat43_BVS "$f" > ${f}.new done. If you don't want new files, then you could do inplace editing fo file with -i inplace flag. Jun 8, 2021 at 8:09
1

Since your files are sorted, this can be done with the join command:

join file1 file2 -1 9 -2 1 -o 2.1,1.12,2.3,2.4,2.5

Explanation:

  • The two files are joined on the column #9 of file1 (-1 option) and the column #1 of file2 (-2 option)
  • The output (-o) option is made from fields 2.1 (1st column of file2), 1.12 (12th column of file1), etc.
2
  • +1. Good explanation. Jun 8, 2021 at 16:16
  • Thank you @PrabhjotSingh!
    – xhienne
    Jun 8, 2021 at 19:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .