Ranking of values in one quarter

Question

I am trying to implement a calculation that will rank the Price values in a separate partition. Below you can see my data

df<-data.frame( year=c(2010,2010,2010,2010,2010,2010),
                quarter=c("q1","q1","q1","q2","q2","q2"),
                Price=c(10,20,30,10,20,30)
                )

df

Now I want to count over each quarter and I expect to have 1 for the smallest Price and 3 for the highest Price

  df %>% group_by(quarter) %>% mutate(id = row_number(Price))

Instead of the expected results, I received different results. Below you can see the result from the code. Instead of ranking in separate quarter, ranging is in both quarters.

So can anybody help me how to solve this problem and to receive results as in table below

jay.sf · Accepted Answer · 2023-02-12T16:23:08.463

1

You probably want rank.

transform(df, id=ave(Price, year, quarter, FUN=rank))
#   year quarter Price id
# 1 2010      q1    10  1
# 2 2010      q1    20  2
# 3 2010      q1    30  3
# 4 2010      q2    10  1
# 5 2010      q2    20  2
# 6 2010      q2    30  3

edited Feb 12 '23 at 16:23

answered Feb 12 '23 at 16:18

jay.sf

60,139
8
53
110

This is not exactly what I need. In the second table, you can see what I expect to get – silent_hunter Feb 12 '23 at 16:21

score 1 · Answer 2 · answered Feb 12 '23 at 17:18

With dplyr, use dense_rank

library(dplyr)
df %>% 
  group_by(quarter) %>% 
  mutate(id = dense_rank(Price)) %>% 
  ungroup
# A tibble: 6 × 4
   year quarter Price    id
  <dbl> <chr>   <dbl> <int>
1  2010 q1         10     1
2  2010 q1         20     2
3  2010 q1         30     3
4  2010 q2         10     1
5  2010 q2         20     2
6  2010 q2         30     3

In the newer version of dplyr, can also use .by in mutate

df %>%
  mutate(id = dense_rank(Price), .by = 'quarter')
   year quarter Price id
1 2010      q1    10  1
2 2010      q1    20  2
3 2010      q1    30  3
4 2010      q2    10  1
5 2010      q2    20  2
6 2010      q2    30  3

score 1 · Answer 3 · answered Feb 12 '23 at 17:39

Alternatively with row_number()

library(tidyverse)

df %>% group_by(year, quarter) %>% mutate(id=row_number())

^{Created on 2023-02-12 with reprex v2.0.2}

# A tibble: 6 × 4
# Groups:   year, quarter [2]
   year quarter Price    id
  <dbl> <chr>   <dbl> <int>
1  2010 q1         10     1
2  2010 q1         20     2
3  2010 q1         30     3
4  2010 q2         10     1
5  2010 q2         20     2
6  2010 q2         30     3

Ranking of values in one quarter

3 Answers3