Ronan M Ronan M - 1 month ago 14
R Question

adding a column to a data frame in R based on the rank of another column

Here is a reproducible example of my data. For the following data frame:

df <- data.frame(Subject = c('John', 'John', 'John', 'John','Mary', 'Mary', 'Mary', 'Mary'),
SNR = c(-4,-4,0,4,0,4,4,8))


I would like to add a column 'rank' that provides a ranking for SNR by Subject, so that it would look like this:

Subject SNR Rank
John -4 1
John -4 1
John 0 2
John 4 3
Mary 0 1
Mary 4 2
Mary 4 2
Mary 8 3


I have tried using:

dfNew <- transform(df, Rank = ave(SNR, Subject, FUN = function(x) rank(x, ties.method = "first")))


But I get the following:

Subject SNR Rank
John -4 1
John -4 2
John 0 3
John 4 4
Mary 0 1
Mary 4 2
Mary 4 3
Mary 8 4


I have also tried using the different ties.method options, but none give me what I am looking for (i.e., ranking only from 1-3).

Any help would be much appreciated!

Answer

Using aggregate and factor in base R:

ag <- aggregate(SNR~Subject, df, function(x) as.numeric(factor(x)))
df$rank <- c(t(ag[,-1]))

  Subject SNR rank
1    John  -4    1
2    John  -4    1
3    John   0    2
4    John   4    3
5    Mary   0    1
6    Mary   4    2
7    Mary   4    2
8    Mary   8    3