0
$\begingroup$

Lets say I have multiple table representing 10 countries, and tables contains user_id, and user_score.

Example:

Table CountryA                    Table CountryB  
user_id | user_score             user_id | user_score
---------------------            ---------------------
  001   |    1245                  001   |    1023
  002   |    1563                  002   |    950
   :           :                     :           :
  1000  |     850                  1000  |     1600

What I am trying to do is build a percentile table where each row is for the country and each column is 10th, 20th..

         10th, 20th   .... 100th
countryA score, score ....  score 

I have started to do this on python, what my question is more related to calculating percentile. At the end I want to know what's the score you need to be getting to be on given percentile. Based on whats given in wikipedia (http://en.wikipedia.org/wiki/Percentile). Using n = P/100 * N + 1/2 I would get the rank but not the score. Coming back to my problem, IF I were to use this I have to sort the table by score and then use 1000 as N and then look which row is tally with the answer that I get from the formula. Is this the correct way to do it?

If the approach is correct, would be better that I write user_id, user_score to csv file and load it in to R, and may be R has a nice function to handle this?

$\endgroup$
1
  • $\begingroup$ Sorting method does matter. Let's say 100 children take a test. All 100 children get the exact same score on that test. Using actual percentages, they would all have the same percentage of the test correct. Using percentile ranking, they will be sorted, each with a unique percentile score, based on however you chose to order the data (alphabetically, sociao-economically, by gender, &c.) $\endgroup$
    – Tavrock
    Nov 23 '16 at 19:29
2
$\begingroup$

R has a function quantile() for this purpose.

x <- rnorm(1000)
outputQuantile <- quantile(x, seq(0,1,by=0.1))
cbind(outputQantile)

Output:

> cbind(outputQuantile)
     outputQuantile
0%       -4.0580073
10%      -1.2243461
20%      -0.7642603
30%      -0.4525056
40%      -0.2034955
50%       0.0766790
60%       0.3028018
70%       0.5643582
80%       0.8159190
90%       1.2239155
100%      3.3617534
$\endgroup$
3
  • $\begingroup$ Is the cbind necessary? $\endgroup$
    – JRideout
    Sep 24 '13 at 15:43
  • 1
    $\begingroup$ @JRideout No, it's not. I just like to show it in a column rather than a chain of numbers on a line. When it's applied to many countries, it would help to arrange the number nicely. Another option is data.frame(). $\endgroup$ Sep 24 '13 at 18:13
  • $\begingroup$ @Penguin_Knight should we consider zero when we calculate the percentile. I mean for example lets say user_id=001 had 0 has user_score, should we consider that element or not? $\endgroup$ Oct 4 '13 at 20:26
1
$\begingroup$

A Python implementation using Numpy and Pandas could go as follows:

import numpy as np 
import pandas as pd 
df = pd.DataFrame(np.random.normal(0, 1, (100,5)),
                  columns = ['A','B','C','D','E'])
deciles = pd.DataFrame([df.quantile(q=i) for i in np.arange(0,1.1,.1)])

           A         B         C         D         E
0  -2.141679 -3.075729 -2.550879 -2.521932 -3.213082
1  -1.270757 -1.190909 -0.946950 -1.340856 -1.273375
2  -0.874009 -0.885549 -0.686993 -0.656391 -0.870263
3  -0.601350 -0.481036 -0.474305 -0.467155 -0.516271
4  -0.349637 -0.312597 -0.077435 -0.301975 -0.245720
5  -0.173052 -0.171267  0.131540  0.029373  0.045576
6   0.071071  0.127750  0.289498  0.289505  0.158174
7   0.336159  0.372524  0.552204  0.487191  0.380061
8   0.749916  0.732315  1.111294  0.832070  0.615242
9   1.165074  1.090632  1.397256  1.154968  1.035759
10  2.612770  2.994829  2.399600  2.728854  2.192404
$\endgroup$
1
  • $\begingroup$ Thanks for the great introduce of pandas, I didn't know about these packages and with your post I started to read about it. I see you have used rows for percentiles how can I switch that? $\endgroup$ Sep 26 '13 at 12:19

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.