Getting PostgreSQL percent_rank and scipy.stats.percentileofscore results to match
<p>I'm trying to QAQC the results of calculations that are done in a PostgreSQL database, using a python script to read in the inputs to the calculation and echo the calculation steps and compare the final results of the python script against the results from the PostgreSQL calculation. </p>

<p>The calculations in the PostgreSQL database use the <a href="https://www.postgresql.org/docs/current/static/functions-aggregate.html" rel="nofollow">percent_rank function</a>, returning the percentile rank (from 0 to 1) of a single value in a list of values. In the python script I am using the <a href="http://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.percentileofscore.html#scipy-stats-percentileofscore" rel="nofollow">Scipy percentileofscore function.</a>     </p>

<p>So, here's the question: I can't get the results to match, and I am wondering if anyone knows what settings I should use in the Scipy percentileofscore function to match the PostgreSQL percent_rank function.</p>

<p>You can use <a href="http://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.rankdata.html" rel="nofollow"><code>scipy.stats.rankdata</code></a>.  The following example reproduces the result shown at <a href="http://docs.aws.amazon.com/redshift/latest/dg/r_WF_PERCENT_RANK.html" rel="nofollow">http://docs.aws.amazon.com/redshift/latest/dg/r_WF_PERCENT_RANK.html</a>:</p>

<pre><code>In [12]: import numpy as np

In [13]: from scipy.stats import rankdata

In [14]: values = np.array([15, 20, 20, 20, 30, 30, 40])
</code></pre>

<p><code>rankdata(values, method='min')</code> gives the desired rank:</p>

<pre><code>In [15]: rank = rankdata(values, method='min')

In [16]: rank
Out[16]: array([1, 2, 2, 2, 5, 5, 7])
</code></pre>

<p>Then a basic calculation gives the equivalent of <code>percent_rank</code>:</p>

<pre><code>In [17]: (rank - 1) / (len(values) - 1)
Out[17]: 
array([ 0.        ,  0.16666667,  0.16666667,  0.16666667,  0.66666667,
        0.66666667,  1.        ])
</code></pre>

<p>(I'm using Python 3.5.  In Python 2, use something like <code>(rank - 1) / float(len(values) - 1)</code>.)</p>

<hr>

<p>You can use <code>percentileofscore</code>, but:</p>

<ul>
<li>You have to use the argument <code>kind='strict'</code>.</li>
<li>You have to scale the result by <code>n/(n-1)</code>, where <code>n</code> is the number of values.</li>
<li>You have to divide by 100 to convert from a true percentage to a fraction between 0 and 1.</li>
<li><code>percentileofscore</code> expects its second argument to be a scalar, so you have to use a loop to compute the result separately for each value.</li>
</ul>

<p>Here's an example using the same values as above:</p>

<pre><code>In [87]: import numpy as np

In [88]: from scipy.stats import percentileofscore

In [89]: values = np.array([15, 20, 20, 20, 30, 30, 40])

In [90]: n = len(values)
</code></pre>

<p>Here I use a list comprehension to generate the result:</p>

<pre><code>In [91]: [n*percentileofscore(values, val, kind='strict')/100/(n-1) for val in values]
Out[91]: 
[0.0,
 0.16666666666666666,
 0.16666666666666666,
 0.16666666666666666,
 0.66666666666666663,
 0.66666666666666663,
 1.0]
</code></pre>

python postgresql scipy rank percentile