Adding rank column based on other numeric column

Question

I'm experiencing trouble with creating a new rank column. I have the following data:

Title             Cat. Rank     
The Da Vinci Code Book 19
The Da Vinci Code Book 19
The Da Vinci Code Book 19
Sisterhood....    Book 21
Book X            Book 34
Book X            Book 34

I need to create a new rank column based on the current rank column where Da Vinci code gets a 1 in all rows with da vinci code. When a new title appears the rank should change to a 2 etc.

I've been trying a lot of variations of rank, e.g.:

  dt.reviews.books.new[,sales_rank_cat:=rank(title, ties.method="first"),by=title]

But this assigns a 1 to every title.

Try something like `dt.reviews.books.new[, sales_rank_cat := .GRP, by = title]` — David Arenburg, Mar 05 '17 at 15:06

score 0 · Answer 1 · answered Mar 05 '17 at 14:56

0

It sounds to me like you are saying DaVinci Code will always receive 1 and the others are already in rank order such that the following titles will receive 2, 3, ....n.

library(data.table)
newDF <- data.table(dt.reviews.books.new) %>%
         newDF[, newRank := 1:.N, by = "Title"]

Again and with warning, this ranking assumes your Titles are currently in rank-order. If your new ranking is based on the current rank I would use sort() or arrange() to order by Rank.

answered Mar 05 '17 at 14:56

Jessica Burnett

395
1
13

newDF <- data.table(dt.reviews.books.new) %>% newDF[, newRank := 1:.N, by = "title"] I get the following error: Error in `[.data.table`(., newDF, , `:=`(newRank, 1:.N), by = "title") : Provide either 'by' or 'keyby' but not both – Max Mar 05 '17 at 15:02
And the titles are indeed already in rank order! – Max Mar 05 '17 at 15:04
1

The pipe here is completely irrelevant/confusing/unnecessary – David Arenburg Mar 05 '17 at 15:04

score 0 · Answer 2 · answered Mar 05 '17 at 15:08

0

library(dplyr)
df %>% mutate(rank = dense_rank((rank)))

if there's a meaning to the rank number you can use desc:

df %>% mutate(rank = dense_rank(desc(rank)))

answered Mar 05 '17 at 15:08

staove7

560
5
18

Adding rank column based on other numeric column

2 Answers2