1

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.

|improve this question
  • 1
    Can't you post an example that's 3 or 4 columns of 3 or 4 rows instead of one that's about 6x20? Also edit your question to show the expected output for your given sample input. You're supposed to provide a minimal, complete, verifiable example, see How to Ask. – Ed Morton Jul 30 '19 at 22:17
  • @EdMorton This is a fraction of my smallest example already. I have over 20 columns in the full file for this example and 40 more rows. Some scripts I've noticed can get rather lengthy, and I want to emphasize how much data the script needs to me able to handle. – Pyrodancer123 Jul 30 '19 at 22:35
  • 1
    You can simply state "I have X rows by Y columns of data" or similar (as I see you have now added), you don't need to clutter your example with it. Right now we can't see the wood for the trees and it'd be the same solution for 3x4 input (for example) as it would for 6x20 input so having all of that data present is adding absolutely no value. – Ed Morton Jul 30 '19 at 22:38
2

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
|improve this answer
1

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
|improve this answer
  • I don't claim to be an expert, but I would think there would have to be a less verbose way to do this, or one that at least didn't involve creating a separate .awk file. Can that script be modified to work within a bash script so that I can take the data on for further processing within the bash script? – Pyrodancer123 Jul 31 '19 at 0:08
  • I could have written a less verbose, more cryptic script but brevity is never a goal of software development so that would have been a bad idea. Any awk script can be called as awk 'script' instead of storing it in a file and executing as awk -f scriptfile - you said you're familiar with bash and awk so I'd have expected you'd know that. – Ed Morton Jul 31 '19 at 2:38
  • 1
    @Pyrodancer123 -- chuckling. Once you have been around for a while, you will find that there are two people that when they give you an awk answer, you can take that to the bank. One is Ed, the other is Charles Duffy. You would do well to learn from them. – David C. Rankin Jul 31 '19 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.