This is a follow up to unix: replace one entire column in one file with a single value from another file

I am trying to replace one column of a file (file1) with one specific value from another file (file2).

file1 is structured like this:

HETATM    8  P   FAD B 600      98.424  46.244  76.016  1.00 18.65
HETATM    9  O1P FAD B 600      98.634  44.801  75.700  1.00 17.69 O  
HETATM   10  O2P FAD B 600      98.010  46.640  77.387  1.00 15.59 O  
HETATM   11 H5B1 FAD B 600      96.970  48.950  72.795  1.00 -1.00 H  

and I absolutely need to conserve that structure.

file2 is structured like this:

1 27, -81.883, 4.0
5 48, -67.737, 20.0
1 55, -72.923, 4.0
4 27, -62.64, 16.0

I noticed that awk is "misbehaving" and looses the format of my pdb file, meaning that instead of:

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

I get

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

I have tried:

value="$(awk -F, 'NR==1{print $2;exit}' $file2)"
#option 1: replaces the column I want but messes up the format
awk -F ' ' '{$11 = v} 1' v="$value" $file1 >TEST1
#option 2: keeps the format but adds the value at the end only
awk -F ' ', '{$2 = v} 1' v="$value" $file1 >TEST2
awk -F, '{$11 = v} 1' v="$value" $file1 >TEST3

I guess it is because a pdb file does not have the same delimiters for all columns and awk is not dealing with that in the manner I want it to.

Any ideas how to "tame" awk for this problem or what other command to use?

  • Dirty hack: Add a tr ' ' '\t' TEST1 pass. Tabs align things! – Mingye Wang Oct 28 '15 at 15:17
  • If the separator is a tabulation you may set awk to use a tabulation as the output field separator: awk -F ' ' 'BEGIN {OFS="\t"}; {$11 = v} 1' v="$value" $file1 >TEST1. Otherwise use one of the methods in the answers below, they are all valid. – kos Oct 28 '15 at 15:54

Use a regex ([^[:blank:]] i.e. non-blank) and replace the 11th match:

awk '{print gensub (/[^[:blank:]]+/, v, 11)}' v="$value" infile

Same with sed:

sed "s/[^[:blank:]]\{1,\}/${value}/11" infile

Another way, if your file has fixed length fields and you know the "position" of each field (e.g. assuming only spaces in your sample file, the 11th field takes up 4 chars, from 57th to 60th on each line)

awk '{print substr($0,1,56) v substr($0,61)}' v=$value file


sed -E "s/^(.{56}).{4}(.*)$/\1${value}\2/" infile
| improve this answer | |
  • gensub() is a GAWK feature and is not specified by POSIX. It may not be available in other versions of AWK. – Paused until further notice. Oct 28 '15 at 21:05

I'd offer to use sed for your task:

IFS=',' read -r a value b <"$file2"
#for second field:
sed "s/.[0-9]\b/$value/" "$file1" > TEST1
#for 11th field:
sed "s/\S.\.[0-9]\{2\}\b/$value/" "$file1" > TEST1
| improve this answer | |

With GAWK 4, you can preserve the field separators by explicitly splitting a string (or the whole line) and iterating over the result of the split (fields and separators) for output.

This example uses FPAT (a regex specifying the field structure) and patsplit() but could use FS (a regex specifying the field separator or containing a single space to represent [ \t\n]+) and split() instead.

gawk "v=$value" '{n = patsplit($0, arr, FPAT, seps); arr[11] = v; for (i = 0; i <= n; i++) {printf "%s%s", a[i], seps[i]}; print ""}'

Note that a[0] will always be null, seps[0] will contain any leading separator and seps[n] will be any separator characters (whitespace) at the end of the input line.'

Here is the oneliner in a more readable form:

gawk "v=$value" '
        n = patsplit($0, arr, FPAT, seps); 
        arr[11] = v; 
        for (i = 0; i <= n; i++) {
            printf "%s%s", a[i], seps[i]
        print ""
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.