unix: replace one entire column in one file with a single value from another file

Question

I want to replace a column in one file with a single value from another file using unix.

File 1 is a pdb file, structured like this:

HETATM   14  H4B FAD B 600      95.544  50.240  71.308  1.00 -1.00 H  
HETATM   14  H4B FAD B 600      95.544  50.240  71.308  1.00 -1.00 H  

I want to replace column number 11 with a single value which is stored in another file (File 2) which looks like this:

[1, 27, -81.883, 4.0]
[3, 38, -66.122, 12.0]
[3, 57, -62.134, 12.0]

I want the value from File 2 (column 3 of line 1) to be the in column 11 of File 1, so that File 1 looks like this:

HETATM   14  H4B FAD B 600      95.544  50.240  71.308  1.00 -81.88 H  
HETATM   14  H4B FAD B 600      95.544  50.240  71.308  1.00 -81.88 H

I can replace column 11 of File 1 with a single value (2 in this case) using:

awk '{$11=2}1' File1

and I have found code like this from https://stackoverflow.com/questions/7846476/replace-column-in-one-file-with-column-from-another-using-awk

awk 'FNR==NR{a[NR]=$3;next}{$2=a[FNR]}1' f2 f1

However I believe I should be using a combination of awk and sed to get the value I want out of File 2 into File 1.

Code below gives me first line of column 11:

awk 'FNR==1{print $11}'

I simply cannot figure out how to combine the two things.

I cannot search by value because values change with every dataset I have (hundreds of pdb files to modify).

Can someone help?

Both solutions below mess up the formatting of my pdb file, meaning I get:

HETATM 1 PA FAD B 600 95.887 47.194 74.387 1.00 -73.248 

instead of

HETATM    1  PA  FAD B 600      95.987  47.188  74.293  1.00 -73.248

am I doing something wrong or do you have any ideas why that is?

Answer 1

First extract the field you want from File 2:

value="$(awk -F, 'NR==1{print $3;exit}' file2)"

Then plug it into the replacement code for File 1:

awk '{$11 = v} 1' v="$value" file1

Answer 2

Since the value you want is the third column of the first line of file2, you can get that with:

$ awk 'NR==1{print $3}' file2
-81.883,

However, that also includes the comma which, presumably, you don't want. To avoid that, you can tell awk to use either space or comma as the field delimiters using the -F flag:

$ awk -F", " 'NR==1{print $3}' file2
-81.883

Awk allows you to set a variable at the commandline with the -v option:

   -v var=val
   --assign var=val
          Assign the value val to the variable var,  before  execution  of
          the  program  begins.  Such variable values are available to the
          BEGIN rule of an AWK program.

So, you could run awk -vfoo="-81.833" {...} and that would make the value -81.33 available as the variable foo within the awk script. If you combine that with command substitution, you can pass the output of the first awk command (the value you want) as a variable (called, for example, i) to a second script which replaces the 11th field with the value of the variable i:

$ awk -vi="$(awk -F", " 'NR==1{print $3}' file2)" '{$11=i}1;' file1
HETATM 14 H4B FAD B 600 95.544 50.240 71.308 1.00 -81.883 H
HETATM 14 H4B FAD B 600 95.544 50.240 71.308 1.00 -81.883 H

Answer 3

I don't really understand this problem very well, but I'll hazard a solution anyway, I guess.

sed -nse'1!{  :out
              1x
              s/  */&\n/10
              s/^/ /p;t
           }
           x; s/..*//;t out
           g; s/[^ ]* *[^ ]* *//
              s/ .*//;p
           x;    :eat
           $d;n;b eat
'  file1 file2 file3 file4 |
sed '      /^ /!{h;d;}
           s///;N;G
           s/\n[^ ]*\(.*\)\n\(.*\)/\2\1/
'

That might work. If you have a sed which can handle -separate input file streams, then that should alternate between selecting only the field you want from file one and writing only that one field for the entire file, or else mark and prepare each output line for the next input file so the second sed can replace the fields in question.

Basically it works with file pairs - from the first of every two read files it will print only your source column, and then edit that source column into the second of each pair.