2

I have a tab delimited table (Table1)

 a b c
A 0.5 0.2 0
B 0 0.5 0.4
C 1 0.3 0.4
D 0.2 0 0.2

And a tab delimited file (File1)

A 0.3
B 1
C 0.5
D 0.5

I want to multiple every number in each row in Table1 (for example, Row A (0.5 0.2 and 0)),by the number in the row with the same name in File1 (Row A (0.3)). So for Row A it would be 0.5 * 0.3, 0.2 * 0.3 and 0 * 0.3.

Desired output:

 a b c
A 0.15 0.06 0
B 0 0.5 0.4
C 0.5 0.15 0.2
D 0.1 0 0.1 

To clarify, this is an example table. The letters are variables representing the column/row names - there may be hundreds of columns and rows. Note that the real column/row names may contain numbers, which should not be altered by the script. For Example, Row "A" might be named "ABC123"

Help is greatly appreciated.

3
  • Can you please add your attempts to the question? Users are encouraged to show their efforts before posting. You may still get an answer, but learn by doing is often the best method. – Quasímodo Jun 11 '20 at 16:04
  • is line order important or only first word (search kriteria) used for pairing? how should duplicates in Table1 or File1 handled? – alecxs Jun 11 '20 at 16:05
  • Are the row names always in the same order between the 2 files or could file 1 row names be A B C while file2 is B C A for example? Are all row names from file1 always present in file2 and vice-versa? – Ed Morton Jun 11 '20 at 16:37
4

Here's one way:

$ awk -F"\t" '
    { 
        if(NR==FNR){
            a[$1]=$2;
        }
        else{
            if(FNR==1){print; next} 
            printf "%s",$1; 
            for(i=2;i<=NF;i++){
                printf "\t%0.2f",$i*a[$1]
            }
            printf "\n"
        }
    }' table2 table1 
    a    b    c
A    0.15    0.06    0.00
B    0.00    0.50    0.40
C    0.50    0.15    0.20
D    0.10    0.00    0.10
3
  • This works perfectly. Thanks! – P.tin Jun 11 '20 at 16:41
  • print "" would be better than printf "\n" since it's briefer and, more importantly, uses whatever value ORS has instead of hardcoding the value that you hope/assume ORS has. – Ed Morton Jun 11 '20 at 23:55
  • @EdMorton seeing as I know what the ORS is, I don't see much benefit in that, and chose printf to make it explicit and easier for the OP to understand. – terdon Jun 12 '20 at 8:38

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.