Find and replace different columns using awk

Question

I try to make a change in a file:

Find a certain value and substitute it with another value in a different column using awk.

Input (UiO-66Zr-EH.mof):

unit ntype qqatom
  1  'Zr1'  0.0d0
vibration
0
improper
0
unit ntype qqatom
  2  'H1'  0.0d0
vibration
0
improper
0
unit ntype qqatom
  3  'C25'  0.0d0
vibration
0
improper
0
unit ntype qqatom
  4  'O1'  0.0d0

Output (output):

unit ntype qqatom
  1  'Zr1'  2.222d0
vibration
0
improper
0
unit ntype qqatom
  2  'H1'  3.333d0
vibration
0
improper
0
unit ntype qqatom
  3  'C25'  7.456d0
vibration
0
improper
0
unit ntype qqatom
  4  'O1'  9.99d0

I've tried this command:

awk < UiO-66Zr-EH.mof '$2 ~ /Zr1/ {$3 ="2.222.d0"}1''$2 ~ /O1/ {$3 ="9.99d0"}1''$2 ~ /C25/ {$3 ="7.45d0"}1''$2 ~ /H1/ {$3 ="3.333d0"}1' > output

But it didn't function very well.

Can I do that with awk, while also keeping the same formation?

Answer 1

Instead of doing a regex match on the second column, you probably just want to a string comparison. To do this with the example you have given, you have to include the single quotes in the comparison which turns the whole thing into a bit of a shell quoting nightmare. Doing that gives the following:

awk "\$2==\"'Zr1'\" { \$3=\"2.222.d0\" }
     \$2==\"'O1'\" { \$3=\"9.99d0\" }
     \$2==\"'C25'\" { \$3 =\"7.45d0\" }
     \$2==\"'H1'\" { \$3 =\"3.333d0\" }
     { print }" <UiO-66Zr-EH.mof

Answer 2

The first issue here is that your values are quoted. I suggest you simply remove the quotes with a sed command and then, if you really need them, put them back after you've processed the file with awk. Something like

sed "s/'//g" UiO-66Zr-EH.mof | awk '{$2=q $2 q}' q="'" 

The trick above sets the variable q to ' so q $2 q is equivalent to '$2'. Just an easy way to get awk to play nice with single quotes.

Based on your script, you seem to want to perform he following replacements:

$2 is   $3 should be
---------------------
Zr1     2.008.d0
O25     -1.179d0
O1      -0.582d0
C25     -0.121d0
C13     -0.002d0
C1      0.625d0
O29     -0.741d0
H1      0.127d0
H25     0.495d0

If so, I would do something like

$ sed "s/'//g" UiO-66Zr-EH.mof |
   awk 'BEGIN{
        c["Zr1"] = "2.008.d0";
        c["O25"] = "-1.179d0";
        c["O1"] = "-0.582d0";
        c["C25"] = "-0.121d0";
        c["C13"] = "-0.002d0";
        c["C1"] = "0.625d0";
        c["O29"] = "-0.741d0";
        c["H1"] = "0.127d0";
        c["H25"] = "0.495d0";
        }
    {if( $2 in c){$3=c[$2]; $2=q$2q}}1;' q="'" 

Explanation

• The initial sed just removes all single quotes from the input file.
• The result of the sed is passed as input to awk
• The BEGIN{} block is executed before the file is read and an associative array containing your mappings is set up.
• In the main program, I simply check whether $2 is defined in the c array and if it is, I replace $3 with the corresponding value from the array.
• The $2=q$2q just puts the single quotes back around $2.
• Finally, the 1; prints the line.

The output when run on your example is:

unit ntype qqatom
1 'Zr1' 2.008.d0
vibration
0
improper
0
unit ntype qqatom
2 'H1' 0.127d0
vibration
0
improper
0
unit ntype qqatom
3 'C25' -0.121d0
vibration
0
improper
0
unit ntype qqatom
4 'O1' -0.582d0