Find value from array in one file, look up value in another file, and then use that value to find another and set as variable

Question

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.

Answer 1

Just for variety, here's a solution using mostly bash and bc for floating arithmetic.

#!/usr/bin/env bash

# r is an associative array of weights, indexed by column name

declare -A r

source <(awk '{ print "r[\"" $1 "\"]=" $6}' <( tail +2 file2))

hdr=

while read line
do
        if ! [ $hdr ]
        then
                hdr=($line)
                set -- $line
                for h do
                        printf '%-12s ' "$h"
                done
                printf '\n'
        else
                set -- $line
                for h in ${hdr[@]}
                do
                        coef=${r[$h]}
                        printf '%-11.5f  ' \
                            $(bc <<< "scale=6; $1 * $coef")
                        shift
                done
                printf '\n'
        fi

done < file1

6x5 sample output:

RR1.out      RR2.out      RR3.out      RR4.out      RR5.out      RR6.out      
16.57416     21.01053     3.13118      4.06277      0.47016      0.45179      
20.11905     25.49240     3.81561      4.94917      0.57655      0.55459      
18.64490     23.63410     3.56182      4.62173      0.53573      0.51524      
19.91560     25.24852     3.78686      4.91421      0.57144      0.54958      
16.46050     20.87174     3.18052      4.12741      0.47934      0.46049      

Answer 2

A file that's 100x100 isn't large so it doesn't need any kind of special treatment. I had been picturing generating different output files per column and then using paste to combine them but with a file that small it's not necessary, just storing the values in arrays is fine:

$ cat tst.awk
BEGIN { OFS = "\t" }
NR==FNR {
    key2mult[$1] = $NF
    next
}
FNR==1 {
    for (colNr=1; colNr<=NF; colNr++) {
        colNr2mult[colNr] = key2mult[$colNr]
        printf "%s%s", $colNr, (colNr<NF ? OFS : ORS)
    }
    next
}
{
    for (colNr=1; colNr<=NF; colNr++) {
        vals[FNR,colNr] = $colNr
    }
}
END {
    for (rowNr=2; rowNr<=FNR; rowNr++) {
        for (colNr=1; colNr<=NF; colNr++) {
            printf "%.05f%s", vals[rowNr,colNr] * colNr2mult[colNr], (colNr<NF ? OFS : ORS)
        }
    }
}

.

$ awk -f tst.awk file2 file1
RR1.out RR2.out
16.57416        21.01053
20.11905        25.49240
18.64490        23.63410

The above was run on this input:

$ tail -n +1 file1 file2
==> file1 <==
RR1.out       RR2.out
45.7597       45.7646
55.5468       55.5269
51.4768       51.4792

==> file2 <==
file Gibbs weighted
 RR2.out    -1752.142111    0.4591
 RR1.out    -1752.141887    0.3622
 RR4.out    -1752.140564    0.0894

Using your new sample input:

$ awk -f tst.awk file2 file1
RR1.out RR2.out RR3.out RR4.out RR5.out RR6.out
16.57416        21.01053        3.13118 4.06277 0.47016 0.45178
20.11905        25.49240        3.81561 4.94917 0.57655 0.55459
18.64490        23.63410        3.56182 4.62173 0.53573 0.51525
19.91560        25.24853        3.78686 4.91421 0.57144 0.54958
16.46050        20.87174        3.18052 4.12741 0.47934 0.46049

$ awk -f tst.awk file2 file1 | column -s$'\t' -t
RR1.out   RR2.out   RR3.out  RR4.out  RR5.out  RR6.out
16.57416  21.01053  3.13118  4.06277  0.47016  0.45178
20.11905  25.49240  3.81561  4.94917  0.57655  0.55459
18.64490  23.63410  3.56182  4.62173  0.53573  0.51525
19.91560  25.24853  3.78686  4.91421  0.57144  0.54958
16.46050  20.87174  3.18052  4.12741  0.47934  0.46049