CODEWITHSUNDEEP
r
CyberbotZ3006
CyberbotZ3006

Reputation: 85

How to give duplicates rows the same ranking?

The data frame below shows different companies, the products they received, and the date that they received them. What I want to do is have the "Time.Rank" column reflect the chronological rank in which the Received.Date occurred (from earliest - latest date) within the same Account.Name. 

df <- data.frame(
      Company = c("Walmart", "Walmart", "Walmart", "Walmart", "Walmart", "Staples", "Staples"),
      Product.Name = c("tape", "flower", "tape", "chocolate", "pencil", "pencil", "tape"),
      Received.Date = c("2013-09-30", "2013-09-30", "2015-05-08", "2015-05-08", "2015-05-08", "2014-12-12", "2014-12-17"),
      Time.Rank = c("1", "2", "3", "4", "5", "1", "2"))

The question I have is in regards to the Time.Rank column. How I got the Time.Rank column was this: 

df <- data.frame %>%
  mutate(Time.Rank = row_number(Account.Name))

The problem right now is, even though row 1-2, and 3-5 all have the same Received.Date, they still have different rankings. I want rows with the same Received.Date to have the ranking. I.e. both row 1 and 2 should have Time.Rank = 1, and row 3-5 should have Time.Rank = 2. So this: 

 df <- data.frame(
          Company = c("Walmart", "Walmart", "Walmart", "Walmart", "Walmart", "Staples", "Staples"),
          Product.Name = c("tape", "flower", "tape", "chocolate", "pencil", "pencil", "tape"),
          Received.Date = c("2013-09-30", "2013-09-30", "2015-05-08", "2015-05-08", "2015-05-08", "2014-12-12", "2014-12-17"),
          Time.Rank = c("1", "1", "2", "2", "2", "1", "2"))

Upvotes: 3

Views: 1170

Answers (1)

jalapic
jalapic

Reputation: 14192

I think what you're looking for is dense_rank

df %>% group_by(Company) %>% mutate(Time.Rank = dense_rank(Received.Date))


  Company Product.Name Received.Date Time.Rank
1 Walmart         tape    2013-09-30         1
2 Walmart       flower    2013-09-30         1
3 Walmart         tape    2015-05-08         2
4 Walmart    chocolate    2015-05-08         2
5 Walmart       pencil    2015-05-08         2
6 Staples       pencil    2014-12-12         1
7 Staples         tape    2014-12-17         2

Upvotes: 4

Related Questions