Add a "rank" column to a data frame

Question

I have a dataframe with counts of different items, in different years:

df <- data.frame(item = rep(c('a','b','c'), 3),                  year = rep(c('2010','2011','2012'), each=3),                  count = c(1,4,6,3,8,3,5,7,9)) 

And I would like to add a "year.rank" column, which gives an item's rank within a given year, where a higher count leads to a higher "rank". With the above, it would look like:

  item year count year.rank 1    a 2010     1         3 2    b 2010     4         2 3    c 2010     6         1 4    a 2011     3         2 5    b 2011     8         1 6    c 2011     3         3 7    a 2012     5         3 8    b 2012     7         2 9    c 2012     9         1 

I know I could do this for the whole data frame using order(df$count), but I'm not sure how I would do it by year.

Answer 1

There is a rank function to help you with that:

transform(df,            year.rank = ave(count, year,                            FUN = function(x) rank(-x, ties.method = "first")))   item year count year.rank 1    a 2010     1         3 2    b 2010     4         2 3    c 2010     6         1 4    a 2011     3         2 5    b 2011     8         1 6    c 2011     3         3 7    a 2012     5         3 8    b 2012     7         2 9    c 2012     9         1 

Answer 2

data.table version for practice:

library(data.table) DT <- as.data.table(df) DT[,yrrank:=rank(-count,ties.method="first"),by=year]     item year count yrrank 1:    a 2010     1      3 2:    b 2010     4      2 3:    c 2010     6      1 4:    a 2011     3      2 5:    b 2011     8      1 6:    c 2011     3      3 7:    a 2012     5      3 8:    b 2012     7      2 9:    c 2012     9      1