Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Selective multiplication of two columns in two files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Selective multiplication of two columns in two files
# 1  
Old 12-02-2011
Selective multiplication of two columns in two files
Hi again,
I have two files
e.g. file2
e.g. file1
Code:
BB152	6.279650E+02
AA124	6.279650E+02

Code:
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

Code:
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
Code:
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

Code:
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  
Old 12-02-2011
Hi f_o_555,

I don't understand your v>6e-31 condition in the 'awk' command. Try if next one works for you:
Code:
$ 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
This User Gave Thanks to birei For This Post:
f_o_555
# 3  
Old 12-02-2011
Quote:
Originally Posted by birei
Hi f_o_555,

I don't understand your v>6e-31 condition in the 'awk' command. Try if next one works for you:
Code:
$ 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
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  
Old 12-02-2011
Code:
 
awk '{print $2}' file1 > file3
fgrep -f file3 file2

This User Gave Thanks to itkamaraj For This Post:
f_o_555
# 5  
Old 12-02-2011
Quote:
Originally Posted by itkamaraj
Code:
 
awk '{print $2}' file1 > file3
fgrep -f file3 file2

I get nothing...
# 6  
Old 12-02-2011
Sorry,

Use $1 instead of $2
# 7  
Old 12-02-2011
Quote:
Originally Posted by itkamaraj
Sorry,

Use $1 instead of $2
Ok; but I get something different.
Code:
BB152   6.279650E+02
AA124   6.279650E+02

instead of
Code:
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

Hi, I am trying to selectively merge two files based on keys reported in the 1st column. File1: #file1-header1 file1-header2 111 qwe rtz uio 198 asd fgh jkl 165 yxc 789 poi uzt rew 89 lkj File2: #file2-header2 file2-header2 165 ghz nko2 ... (2 Replies)
Discussion started by: dovah
2 Replies

2. Shell Programming and Scripting

Multiplication of two files and and sorting

Hi, I have 2 ASCII files, say file1 AAAAA 3.465830E-12 BBBBB 4.263280E-08 CCCCC 1.113320E-17 DDDDD 0.000000E+00 ... file2 with as many lines as file1 3.932350E-12 1.194380E-07 4.901480E-17 0.000000E+00 3.921180E-40 (3 Replies)
Discussion started by: f_o_555
3 Replies

3. Shell Programming and Scripting

Multiplication of a column from 2 files using awk

Hi, I have two files (file1.txt, file2.txt) in which, I would like to multiply all the values in file1 with the first row value of file2, file1 * second row value of file2, file1 * third row value of file2 and so on. Below are my sample data. file1.txt file2.txt ... (6 Replies)
Discussion started by: ida1215
6 Replies

4. Shell Programming and Scripting

How to copy selective list of files to a directory?

Hi I have 3 directories indexes_with_ts indexes_without_ts process_indexes in each directories it contains *.sql how do I accomplish this: for all the files found in indexes_without_ts, copy the corresponding file in indexes_with_ts to process_indexes. i.e. for... (2 Replies)
Discussion started by: jediwannabe
2 Replies

5. Shell Programming and Scripting

multiplication of two files based on the content of the first column

Hi, This is something that probably it is more difficult to explain than to do. I have two files e.g. FILE1 A15 8.3102E+00 3.2000E-04 A15 8.5688E+00 4.3000E-05 B13 5.1100E-01 1.9960E+00 B16 5.1100E-01 2.3000E-03 B16 8.6770E-01 1.0000E-07 B16 9.8693E-01 3.4000E-05... (4 Replies)
Discussion started by: f_o_555
4 Replies

6. UNIX for Dummies Questions & Answers

Matrix multiplication with different files

Hi, i have file1 which looks like: x1 y1 z1 x2 y2 z2 ...(and so on) and file2 which looks like: a11 a12 a13 a21 a22 a23 a31 a32 a33 and i want to replace file1 with the following values: x1' y1' z1' x2' y2' z2' ...(and so on) (2 Replies)
Discussion started by: ezitoc
2 Replies

7. Shell Programming and Scripting

Selective extraction of data from a files

Hi, I would like to seek for methods to do selective extraction of line froma file. The scenario as follows: I have a file with content: message a received on 11:10:00 file size: 10 bytes send by abc message b received on 11:20:00 file size: 10 bytes send by abc (3 Replies)
Discussion started by: dwgi32
3 Replies

8. Shell Programming and Scripting

how to merge selective content between files using awk?

Hello, I'm new to awk. I've 2 files where H stands for header and T for trailer. The number following T gives the record count in a file. file 1 looks like this: H|A|B|C 1|2|3 1|2|4 2|3|5 T|3 file 2 looks like this: H|A|B|C 4|5|6 7|8|9 T|2 Need to merge the above 2 files such... (2 Replies)
Discussion started by: skumre
2 Replies

9. UNIX for Dummies Questions & Answers

selective concatenation of rows & columns

Dear unix gurus, I have a data file that looks something like this ... x y x y x y x y x y 0 3836 30 3915 60 5984 90 7388 120 8385 150 9038 180 9453 210 9745 240 9906 270 9962 300 9953 330 9915 350 9887 ... (22 Replies)
Discussion started by: tintin72
22 Replies

10. UNIX for Dummies Questions & Answers

Is there any non graphical tool that make selective merge between text files?

whitout using awk / sad and so on? (3 Replies)
Discussion started by: umen
3 Replies
Login or Register to Ask a Question