CODEWITHSUNDEEP
rdataframerank
Sapling
Sapling

Reputation: 533

create a rank column in a dataframe mixing two other columns in R

I want to add a rank column on a data frame, my data frame looks like this:

df <- data.frame(category = rep(c('c1','c2','c3'), each =3),
             id = seq(1:9),
             count = c(10,10,10,9,8,8,7,6,4))

What i want is:

category id count rank
c1       1   10    9
c2       4   9     8
c3       7   7     7
c1       2   10    6
c2       5   8     5
c3       8   6     4
c1       3   10    3
c2       6   8     2
c3       9   4     1

I would like the rank based on different category, and then their count,from high to low.

Upvotes: 2

Views: 441

Answers (3)

akrun
akrun

Reputation: 887008

We can try

library(data.table)
setDT(df)[order(-count), N:=1:.N, by = category]
df[order(N)][, rank:=.N:1][, N:= NULL][]

library(dplyr)
df %>%
   group_by(category) %>%
   arrange(desc(count)) %>%
   mutate(n = row_number()) %>%
   arrange(n) %>%
   ungroup() %>%
   mutate(rank = rev(row_number()))
#     category    id count     n  rank
#       (fctr) (int) (dbl) (int) (int)
#  1       c1     1    10     1     9
#  2       c1     2    10     2     8
#  3       c1     3    10     3     7
#  4       c2     4     9     1     6
#  5       c2     5     8     2     5
#  6       c2     6     8     3     4
#  7       c3     7     7     1     3
#  8       c3     8     6     2     2
#  9       c3     9     4     3     1

Upvotes: 2

David Arenburg
David Arenburg

Reputation: 92282

Here's a possible implementation of a rank/order compination

library(data.table)
indx <- setDT(df)[, frank(-count, ties.method = "first"), by = category]$V1
df[order(indx)][, Rank := .N:1][]
#    category id count Rank
# 1:       c1  1    10    9
# 2:       c2  4     9    8
# 3:       c3  7     7    7
# 4:       c1  2    10    6
# 5:       c2  5     8    5
# 6:       c3  8     6    4
# 7:       c1  3    10    3
# 8:       c2  6     8    2
# 9:       c3  9     4    1

Upvotes: 3

Pekka
Pekka

Reputation: 2448

Here is one way to do it.

Requires two orderings though...

library(data.table)

df <- data.table(category = rep(c('c1','c2','c3'), each =3),
             id = seq(1:9),
             count = c(10,10,10,9,8,8,7,6,4))

setorder(df,category,-count)
df[,r1 := seq_len(.N),by=category]

setorder(df,r1)
df[,rank := rev(seq_len(.N))]

Upvotes: 2

Related Questions