How can I add new variable into data frame which will be percentile rank of one of the variables? I can do this in Excel easily, but I really want to do that in R.
Thanks
$\begingroup$
$\endgroup$
Add a comment
|
3 Answers
$\begingroup$
$\endgroup$
5
Given a vector of raw data values, a simple function might look like
perc.rank <- function(x, xo) length(x[x <= xo])/length(x)*100
where x0 is the value for which we want the percentile rank, given the vector x, as suggested on R-bloggers.
However, it might easily be vectorized as
perc.rank <- function(x) trunc(rank(x))/length(x)
which has the advantage of not having to pass each value. So, here is an example of use:
my.df <- data.frame(x=rnorm(200))
my.df <- within(my.df, xr <- perc.rank(x))
-
3$\begingroup$ 1. Your function does not mimic Excel's
percentrank-function, which is good (+1) since the latter gives "strange" results (see my comparison). 2. I wouldn't name the data framedf, becausedfis an R function (the density of the F distribution, see?df). $\endgroup$ Commented Jun 15, 2011 at 11:04 -
1$\begingroup$ @Bernd Thanks. (1) There are some built-in functions for computing PR in various psychometrics packages. I think I grabbed this one from the
CTTpackage a while ago. I didn't check against Excel because I don't have/use it. About (2) I seem to always forget about this! Let's go withmy.*(Perl way) :-) $\endgroup$– chlCommented Jun 15, 2011 at 11:21 -
$\begingroup$ @chl why is the
truncrequired? It seems rank will always return an integer anyway. $\endgroup$ Commented May 10, 2018 at 18:38 -
1$\begingroup$ @Tyler Nope. In case there are ties,
rank()defaults to taking the average of the tied values (cf.ties.method = c("average",...)). $\endgroup$– chlCommented May 11, 2018 at 13:15 -
1$\begingroup$ Beware that NA values should be removed! This can be done by adding
x = x[!is.na(x)]$\endgroup$– AntoineCommented Mar 21, 2021 at 16:55
$\begingroup$
$\endgroup$
4
If your original data.frame is called dfr and the variable of interest is called myvar, you can use dfr$myrank<-rank(dfr$myvar) for normal ranks, or dfr$myrank<-rank(dfr$myvar)/length(myvar) for percentile ranks.
Oh well. If you really want it the Excel way (may not be the simplest solution, but I had some fun using new (to me) functions and avoiding loops):
percentilerank<-function(x){
rx<-rle(sort(x))
smaller<-cumsum(c(0, rx$lengths))[seq(length(rx$lengths))]
larger<-rev(cumsum(c(0, rev(rx$lengths))))[-1]
rxpr<-smaller/(smaller+larger)
rxpr[match(x, rx$values)]
}
so now you can use dfr$myrank<-percentilerank(dfr$myvar)
HTH.
answered Jun 15, 2011 at 10:06
-
$\begingroup$ 1 - (rank/size) gives you same as excel percentilerank $\endgroup$– user333Commented Jun 15, 2011 at 11:24
-
-
$\begingroup$ An anonymous (attempted) editor tried to add the following comment: "Nice function but sometimes, unfortunately, the RLE may return vector of
length < length(dfr$myvar)". $\endgroup$ Commented Aug 26, 2013 at 16:58 -
$\begingroup$ Can you explain or link to the theory of this method? $\endgroup$– mavaviljCommented May 18, 2021 at 18:47
$\begingroup$
$\endgroup$
A problem with the presented answer is that it will not work properly, when you have NAs.
In this case, another possibility (inspired by the function from chl♦) is:
perc.rank <- function(x) trunc(rank(x,na.last = NA))/sum(!is.na(x))
quant <- function (x, p.ile) {
x[which.min(x = abs(perc.rank(x-(p.ile/100))))]
}
Here, x is the vector of values, and p.ile is the percentile by rank. 2.5 percentile by rank of (arbitrary) coef.mat may be calculated by:
quant(coef.mat[,3], 2.5)
[1] 0.00025
or as a single function:
quant <- function (x, p.ile) {
perc.rank <- trunc(rank(x,na.last = NA))/sum(!is.na(x))
x = na.omit(x)
x[which.min(x = abs(perc.rank(x-(p.ile/100))))]
}
