So I have two files, file 1 looks like this:
RR1.out RR2.out RR3.out RR4.out RR5.out RR6.out
45.7597 45.7646 45.4453 45.4448 45.2081 45.1785
55.5468 55.5269 55.3789 55.3598 55.4377 55.4591
51.4768 51.4792 51.6955 51.6972 51.5128 51.5245
54.9851 54.9957 54.9617 54.9688 54.9465 54.9579
45.4459 45.4623 46.1614 46.1679 46.0906 46.0488
File 2 looks like this:
file Gibbs kcal rel pop weighted
RR2.out -1752.142111 -1099486.696073 0.000000 1.0000 0.4591
RR1.out -1752.141887 -1099486.555511 0.140562 0.7890 0.3622
RR4.out -1752.140564 -1099485.725315 0.970758 0.1947 0.0894
RR3.out -1752.140319 -1099485.571575 1.124498 0.1502 0.0689
RR5.out -1752.138532 -1099484.450215 2.245858 0.0227 0.0104
RR6.out -1752.138493 -1099484.425742 2.270331 0.0218 0.0100
I would like to be able to have a script take the first value in column 1 of file 1, find that value in column1 of file2, find the value in column 6 in file 2 that is in the same row as that value, multiply the rest of the numbers in column 1 of file 1 by that value, print to a new file, and then repeat until it runs out of columns. The new data should then all be combined under the same headings as are in file 1 (RR1.out and so forth).
So for example: the first value to lookup would RR1.out, this is in row 3 of file 2's column 1, the value in column 6 in row 3 is 0.3622. So the rest of the values in column1 in file1 should be multiplied by 0.3622 and printed to a new file.
The supercomputer I am running this on can use a variety of languages, but I am most familiar with bash and awk. Python is also operable on the supercomputer.
Any suggestions on where to even begin with this would be greatly appreciated. I believe some sort of array functions with awk would work for looking up values, but I am unsure as to how to transfer variables between documents.
As requested the output should look like
RR1.out RR2.out RR3.out RR4.out RR5.out RR6.out
16.57416 21.01052 3.13118 4.06276 0.47016 0.45178
20.11905 25.49239 3.81560 4.94916 0.57655 0.55459
18.64489 23.63410 3.56181 4.62172 0.53573 0.51524
19.91560 25.24852 3.78686 4.91421 0.57144 0.54957
16.46050 20.87174 3.18052 4.12741 0.47934 0.46048
The end script needs to be able to handle large quantities of data with over 30 columns and 100 rows in file1. The number of columns and rows in file1 is variable with a maximum of 100 columns and 100 rows.