replace key in a file

[agilo]

Hi,

I do have a small problem to replace a key in a file with a a value set in a second file, for example:

file1:

x1 x2 x3 x4 x5
4521 20 0 7 912
4521 30 1 8 483

file2:

y1 y2 y3 y4 y5
tt1 4521 0 30 ttx
tt1 4521 1 20 tty

I want to replace the key y3 in file2 with the value set in column x3 of file1 where x1 = y2 and x2 = y4.
Both files are sorted.

The result should be:

y1 y2 y3 y4 y5
tt1 4521 0 20 ttx
tt1 4521 1 30 tty



Can any body help,

Thanks in advance

 

[PHV]

A starting point:
awk 'NR==FNR{a[$1,$2]=$3;next}a[$2,$4]!=""{$3=a[$2,$4]}1' file1 file2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[agilo]

Many thanks PHV,
I would like to understand the mapping of the columns for the two files:

awk 'NR==FNR{a[$1,$2]=$3;next}a[$2,$4]!=""{$3=a[$2,$4]}1' file1 file2

a[$1,$2]: means array keep the values of the first
and second columns of the first file in index $3


a[$2,$4]: the same but for columns 2 and 4 for the second file!!

Could you please, explain a little bit.


Thanks,

 

[PHV]

a[$1,$2]=$3
Store x3 in array a indexed by x1,x2
a[$2,$4]!=""{$3=a[$2,$4]}
Replace y3 by x3 if y2=x1 and y4=x2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[agilo]

many thanks for the explaination, the only problem is that this script does not keep the tab between the columns,
I tried adding {FS = "\t"} but it does not work, could you please advice where to put it.

 

[PHV]

awk 'BEGIN{OFS="\t"}NR==FNR{a[$1,$2]=$3;next}a[$2,$4]!=""{$3=a[$2,$4]}1' file1 file2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886