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?

  • What's wrong with the solutions you have? Both of them should work. Well, the second will also include a , is that the issue? Why don't you just run awk '{$11="-81.88"}1' File1 ?
    – terdon
    Commented Oct 27, 2015 at 13:51
  • @terdon it looks like the OP wants to extract the -81.883 from the first line's third column of file 2 but doesn't know how to glue that extract into the replacement code. Commented Oct 27, 2015 at 13:58
  • @gugy, you seem to have created two separate accounts, so you can't edit your own question. Please see here for how to merge them.
    – terdon
    Commented Oct 27, 2015 at 14:00

3 Answers 3


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

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

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

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

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

sed -nse'1!{  :out
              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[^ ]*\(.*\)\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.

You must log in to answer this question.

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