Rank subgroup by group (dplyr)

user_16109541 0
Viewed 229

This question addresses how to assign the rank of a row within a group. I would like to assign the rank of a subgroup to a row within that subgroup. What I'm really getting at is that I need an abbreviation of the second group_by variable that is guaranteed to be unique, and this is the best way I can think of to go about doing that. Hopefully the desired output below makes this clear enough.

Input dataframe:

my_df <- tibble(
  var1 = c(rep("A", 8), rep("B", 12)),
  var2 = c(rep("long_string_x", 4), 
           rep("long_string_y", 4),
           rep("long_string_x", 4), 
           rep("long_string_y", 4), 
           rep("long_string_z", 4))
)

Desired output:

# A tibble: 20 x 3
   var1  var2          group_rank
   <chr> <chr>              <dbl>
 1 A     long_string_x          1
 2 A     long_string_x          1
 3 A     long_string_x          1
 4 A     long_string_x          1
 5 A     long_string_y          2
 6 A     long_string_y          2
 7 A     long_string_y          2
 8 A     long_string_y          2
 9 B     long_string_x          1
10 B     long_string_x          1
11 B     long_string_x          1
12 B     long_string_x          1
13 B     long_string_y          2
14 B     long_string_y          2
15 B     long_string_y          2
16 B     long_string_y          2
17 B     long_string_z          3
18 B     long_string_z          3
19 B     long_string_z          3
20 B     long_string_z          3

How may I assign group_rank as above, ideally (but not necessarily) using a tidyverse approach?

dplyr r
3 Answers

We could use match after grouping

library(dplyr)
my_df %>% 
   group_by(var1) %>%
   mutate(group_rank = match(var2, unique(var2))) %>%
   ungroup

-output

# A tibble: 20 x 3
   var1  var2          group_rank
   <chr> <chr>              <int>
 1 A     long_string_x          1
 2 A     long_string_x          1
 3 A     long_string_x          1
 4 A     long_string_x          1
 5 A     long_string_y          2
 6 A     long_string_y          2
 7 A     long_string_y          2
 8 A     long_string_y          2
 9 B     long_string_x          1
10 B     long_string_x          1
11 B     long_string_x          1
12 B     long_string_x          1
13 B     long_string_y          2
14 B     long_string_y          2
15 B     long_string_y          2
16 B     long_string_y          2
17 B     long_string_z          3
18 B     long_string_z          3
19 B     long_string_z          3
20 B     long_string_z          3
user_37322710

using the approach to solving the problem of a respected @akrun

library(tidyverse)
my_df <- tibble(
  var1 = c(rep("A", 8), rep("B", 12)),
  var2 = c(rep("long_string_x", 4), 
           rep("long_string_y", 4),
           rep("long_string_x", 4), 
           rep("long_string_y", 4), 
           rep("long_string_z", 4))
)

my_df %>% 
  group_by(var1) %>% 
  mutate(res = data.table::rleid(var2))
#> # A tibble: 20 x 3
#> # Groups:   var1 [2]
#>    var1  var2            res
#>    <chr> <chr>         <int>
#>  1 A     long_string_x     1
#>  2 A     long_string_x     1
#>  3 A     long_string_x     1
#>  4 A     long_string_x     1
#>  5 A     long_string_y     2
#>  6 A     long_string_y     2
#>  7 A     long_string_y     2
#>  8 A     long_string_y     2
#>  9 B     long_string_x     1
#> 10 B     long_string_x     1
#> 11 B     long_string_x     1
#> 12 B     long_string_x     1
#> 13 B     long_string_y     2
#> 14 B     long_string_y     2
#> 15 B     long_string_y     2
#> 16 B     long_string_y     2
#> 17 B     long_string_z     3
#> 18 B     long_string_z     3
#> 19 B     long_string_z     3
#> 20 B     long_string_z     3

Created on 2021-07-12 by the reprex package (v2.0.0)

user_120254830

Update: As Greg pointed out (see comments) that group_by() default is .add = FALSE the intention was to use group_by twice -> then .add = TRUE should be added. like:

library(dplyr)
my_df %>% 
    group_by(var1) %>% 
    mutate(group_rank = cur_group_id()) %>% 
    group_by(var2, .add=TRUE) %>% 
    mutate(group_rank = cur_group_id())

But in this case as Greg pointed out -> this is enough:

my_df %>% group_by(var2) %>% mutate(group_rank = cur_group_id())

First answer: We could use cur_group_id() twice:

library(dplyr)
my_df %>% 
    group_by(var1) %>% 
    mutate(group_rank = cur_group_id()) %>% 
    group_by(var2) %>% 
    mutate(group_rank = cur_group_id())

Output:

   var1  var2          group_rank
   <chr> <chr>              <int>
 1 A     long_string_x          1
 2 A     long_string_x          1
 3 A     long_string_x          1
 4 A     long_string_x          1
 5 A     long_string_y          2
 6 A     long_string_y          2
 7 A     long_string_y          2
 8 A     long_string_y          2
 9 B     long_string_x          1
10 B     long_string_x          1
11 B     long_string_x          1
12 B     long_string_x          1
13 B     long_string_y          2
14 B     long_string_y          2
15 B     long_string_y          2
16 B     long_string_y          2
17 B     long_string_z          3
18 B     long_string_z          3
19 B     long_string_z          3
20 B     long_string_z          3
user_133216470
Related