Selective multiplication of two columns in two files

Shell Programming and Scripting
1

Hi again,
I have two files
e.g. file2
e.g. file1

BB152	6.279650E+02
AA124	6.279650E+02

AA124  6.0273E-01 9.7800E-01
AA124  6.3239E-01 9.7800E-04
AA124  6.4585E-01 7.3839E-02
BB152  6.6250E-01 2.4450E-04
BB152  7.0932E-01 1.3496E-02
CC124  7.1378E-01 2.2690E-02
CC124  7.2279E-01 1.0758E-01
CC124  7.3574E-01 1.3692E-03

desired output

AA124   0.60273000        6.14149770e+02
AA124   0.63239000        6.14149770e-01
AA124   0.64585000        4.63683076e+01
BB152   0.66250000        1.53537442e-01
BB152   0.70932000        8.47501564e+00

so the first two columns of file2 and as third column the third column of file2 multiplied by the second column of file1
if an entry is not listed in file1 it shouldn't appear in the ouput.

I'm trying to use the following awk command

awk 'NR==FNR{a[$1]=$2;next} $1 in a{v=$3*a[$1]}v>6e-31{printf("%s %.5f %s\n",$1,$2,v)}' file2 file1 > file3

but it gives

AA124   0.60273000        6.14149770e+02
AA124   0.63239000        6.14149770e-01
AA124   0.64585000        4.63683076e+01
BB152   0.66250000        1.53537442e-01
BB152   0.70932000        8.47501564e+00
CC124   0.71378000        8.47501564e+00
CC124   0.72279000        8.47501564e+00
CC124   0.73574000        8.47501564e+00

So I get also CC124... with nonsense values.

Could anyone help?
Thanks,
Sarah

2

Hi f_o_555,

I don't understand your v>6e-31 condition in the 'awk' command. Try if next one works for you:

$ cat file1
BB152   6.279650E+02
AA124   6.279650E+02
$ cat file2
AA124  6.0273E-01 9.7800E-01
AA124  6.3239E-01 9.7800E-04
AA124  6.4585E-01 7.3839E-02
BB152  6.6250E-01 2.4450E-04
BB152  7.0932E-01 1.3496E-02
CC124  7.1378E-01 2.2690E-02
CC124  7.2279E-01 1.0758E-01
CC124  7.3574E-01 1.3692E-03
$ awk 'NR==FNR{a[$1]=$2;next} $1 in a{v=$3*a[$1]; printf("%s %.8f %e\n",$1,$2,v)}' file1 file2
AA124 0.60273000 6.141498e+02
AA124 0.63239000 6.141498e-01
AA124 0.64585000 4.636831e+01
BB152 0.66250000 1.535374e-01
BB152 0.70932000 8.475016e+00

Regards,
Birei

1 Like
3

It does! file2 should be file1 though in your suggestion.
I wanted to remove the lines below a certain value of v
Thank you

4 
 
awk '{print $2}' file1 > file3
fgrep -f file3 file2
1 Like
5

I get nothing...

6

Sorry,

Use $1 instead of $2

7

Ok; but I get something different.

BB152   6.279650E+02
AA124   6.279650E+02

instead of

AA124 0.60273000 6.141498e+02
AA124 0.63239000 6.141498e-01
AA124 0.64585000 4.636831e+01
BB152 0.66250000 1.535374e-01
BB152 0.70932000 8.475016e+00
8 
$ cat file1
BB152   6.279650E+02
AA124   6.279650E+02

$ cat file2
AA124  6.0273E-01 9.7800E-01
AA124  6.3239E-01 9.7800E-04
AA124  6.4585E-01 7.3839E-02
BB152  6.6250E-01 2.4450E-04
BB152  7.0932E-01 1.3496E-02
CC124  7.1378E-01 2.2690E-02
CC124  7.2279E-01 1.0758E-01
CC124  7.3574E-01 1.3692E-03

$ awk '{print $1}' file1 > file3 

$ fgrep -f file3 file2
AA124  6.0273E-01 9.7800E-01
AA124  6.3239E-01 9.7800E-04
AA124  6.4585E-01 7.3839E-02
BB152  6.6250E-01 2.4450E-04
BB152  7.0932E-01 1.3496E-02
9

Ok, almost there. The numbers in the third column are not what is needed.
It should be.

AA124 0.60273000 6.141498e+02
AA124 0.63239000 6.141498e-01
AA124 0.64585000 4.636831e+01
BB152 0.66250000 1.535374e-01
BB152 0.70932000 8.475016e+00