Hi monks,

I've been struggling with this one for some time now, but I can't seem to get it to work. I wan't to convert a list of rankings into a list of rankratio's. I have stored into a MySQL database the following information: gene names, and rankings for three different techniques. 

+-----------------+-------------+---------------+-------------+
| gene_name       | g2d_ranking | pocus_ranking | dgp_ranking |
+-----------------+-------------+---------------+-------------+
| ENSG00000179050 |             |               |           4 |
| ENSG00000124183 |         237 |               |           2 |
| ENSG00000179033 |             |               |           7 |
| ENSG00000124181 |             |           13  |           1 |
| ENSG00000174306 |             |               |           5 |
| ENSG00000132793 |             |               |           8 |
| ENSG00000183798 |             |               |           6 |
| ENSG00000124177 |             |               |           3 |
| ENSG00000087530 |         153 |               |             |
| ENSG00000185513 |         258 |               |             |
| ENSG00000101052 |         231 |               |             |
| ENSG00000124249 |         154 |             7 |             |
| ENSG00000101098 |         159 |               |             |
| ENSG00000166913 |         141 |               |             |
+-----------------+-------------+---------------+-------------+

[download]
Step 1: for all the collumns, the lowest value should get assigned rank 1, the second lowest value 2, etc.
Step 2: count the number of rankings in each column (is eaqual to 'find the ranking with the highest value').
+-----------------+-------------+---------------+-------------+
| gene_name       | g2d_ranking | pocus_ranking | dgp_ranking |
+-----------------+-------------+---------------+-------------+
| ENSG00000179050 |             |               |           4 |
| ENSG00000124183 |           6 |               |           2 |
| ENSG00000179033 |             |               |           7 |
| ENSG00000124181 |             |            2  |           1 |
| ENSG00000174306 |             |               |           5 |
| ENSG00000132793 |             |               |           8 |
| ENSG00000183798 |             |               |           6 |
| ENSG00000124177 |             |               |           3 |
| ENSG00000087530 |           2 |               |             |
| ENSG00000185513 |           7 |               |             |
| ENSG00000101052 |           5 |               |             |
| ENSG00000124249 |           3 |             1 |             |
| ENSG00000101098 |           4 |               |             |
| ENSG00000166913 |           1 |               |             |
+-----------------+-------------+---------------+-------------+
  count:                      7               2             8

[download]
Step 3: for each cell, divide the ranking by the 'count of rankings for that column', as to get rankratio's. 
+-----------------+-------------+---------------+-------------+
| gene_name       | g2d_ranking | pocus_ranking | dgp_ranking |
+-----------------+-------------+---------------+-------------+
| ENSG00000179050 |             |               |       0.500 |
| ENSG00000124183 |       0.857 |               |       0.250 |
| ENSG00000179033 |             |               |       0.875 |
| ENSG00000124181 |             |         1.000 |       0.125 |
| ENSG00000174306 |             |               |       0.625 |
| ENSG00000132793 |             |               |       1.000 |
| ENSG00000183798 |             |               |       0.750 |
| ENSG00000124177 |             |               |       0.375 |
| ENSG00000087530 |       0.285 |               |             |
| ENSG00000185513 |       1.000 |               |             |
| ENSG00000101052 |       0.714 |               |             |
| ENSG00000124249 |       0.428 |         0.500 |             |
| ENSG00000101098 |       0.714 |               |             |
| ENSG00000166913 |       0.142 |               |             |
+-----------------+-------------+---------------+-------------+

[download]


I've various code snippets lying around that do some part of the problem, but can't manage to get them into one working construct. Either it's a "illegal division by zero problem" (a column can have no elements), or then I get a "Use of uninitialized value" warning because I try to divide an undefined value.

So, I wanted to get some fresh insigths, and see how the other monasterians would tackle this problem. Hope you can put me in the rigth direction.

Update: Fixed typo (s/1.857/1.000/).

In reply to converting list of rankings into list of rankratio's. by BioGeek

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.