Revisions to Replacing column using awk

Previous version was "insert" rather than "replace"

Source Link

edited Jun 8, 2021 at 10:04

23.6k
25
55
77

~$ join -1 1 -2 9 -o 1.1,2.12,1.2,1.3,1.4,1.5 file2 source.dat 
49667.8048 6.10352e-05 78.450 3.000 1 1
49667.8782 0.00546265 79.900 1.000 1 1
49668.7284 -0.00497437 40.890 1.000 1 1
49668.8227 -0.00357056 45.790 1.000 1 1
49668.8854 -0.00476074 49.770 5.000 1 1
49670.7279 -0.00326538 66.060 1.000 1 1
49670.8197 -0.00598145 47.380 1.000 1 1
49670.8945 0.00701904 27.270 6.000 1 1
49671.7283 -0.00100708 66.190 1.000 1 1
49671.7994 -0.00503540 65.320 6.000 1 1
49671.8811 0.000000 62.290 1.000 1 1

~$ join -1 1 -2 9 -o 1.1,2.12,1.2,1.3,1.4,1.5 file3 source.dat 
50093.5444 0.00570679 13.480 1.000 1 1
50093.5506 0.00424194 14.830 1.000 1 1
50093.5592 0.00100708 12.150 1.000 1 1
50093.5662 0.00372314 12.150 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.

If it hasEdit

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

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

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

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

~$ join -1 1 -2 9 -o 1.1,2.12,1.2,1.3,1.4,1.5 file2 source.dat 
49667.8048 6.10352e-05 78.450 3.000 1 1
49667.8782 0.00546265 79.900 1.000 1 1
49668.7284 -0.00497437 40.890 1.000 1 1
49668.8227 -0.00357056 45.790 1.000 1 1
49668.8854 -0.00476074 49.770 5.000 1 1
49670.7279 -0.00326538 66.060 1.000 1 1
49670.8197 -0.00598145 47.380 1.000 1 1
49670.8945 0.00701904 27.270 6.000 1 1
49671.7283 -0.00100708 66.190 1.000 1 1
49671.7994 -0.00503540 65.320 6.000 1 1
49671.8811 0.000000 62.290 1.000 1 1

~$ join -1 1 -2 9 -o 1.1,2.12,1.2,1.3,1.4,1.5 file3 source.dat 
50093.5444 0.00570679 13.480 1.000 1 1
50093.5506 0.00424194 14.830 1.000 1 1
50093.5592 0.00100708 12.150 1.000 1 1
50093.5662 0.00372314 12.150 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 from the first file.

If it has to be awk for some reason, try

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

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

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

~$ 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

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

Add awk solution

Source Link

edited Jun 8, 2021 at 9:33

AdminBee

23.6k
25
55
77

If it has to be awk for some reason, try

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

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

Or, in a loop

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

If it has to be awk for some reason, try

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

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

Or, in a loop

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

Source Link

answered Jun 8, 2021 at 8:54

AdminBee

23.6k
25
55
77

You should be able to do that using join:

~$ join -1 1 -2 9 -o 1.1,2.12,1.2,1.3,1.4,1.5 file2 source.dat 
49667.8048 6.10352e-05 78.450 3.000 1 1
49667.8782 0.00546265 79.900 1.000 1 1
49668.7284 -0.00497437 40.890 1.000 1 1
49668.8227 -0.00357056 45.790 1.000 1 1
49668.8854 -0.00476074 49.770 5.000 1 1
49670.7279 -0.00326538 66.060 1.000 1 1
49670.8197 -0.00598145 47.380 1.000 1 1
49670.8945 0.00701904 27.270 6.000 1 1
49671.7283 -0.00100708 66.190 1.000 1 1
49671.7994 -0.00503540 65.320 6.000 1 1
49671.8811 0.000000 62.290 1.000 1 1

~$ join -1 1 -2 9 -o 1.1,2.12,1.2,1.3,1.4,1.5 file3 source.dat 
50093.5444 0.00570679 13.480 1.000 1 1
50093.5506 0.00424194 14.830 1.000 1 1
50093.5592 0.00100708 12.150 1.000 1 1
50093.5662 0.00372314 12.150 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 from the first file.

Stack Exchange Network

Return to Answer