In R, how to group data by multiple columns in the descending order, then give every group an index starting from 1?

user_4827407 0
Viewed 208

My data is like:

a <- data.frame(a1=c(2,2,1,1,2,2,3,3),
                a2=c(5,4,2,2,5,5,6,6),
               a3=c(3,1,5,5,7,7,8,8))

Then, i sort the data like:

aa <- a %>% 
  arrange(desc(a3),desc(a2),desc(a1))

The data looks like:

> aa
  a1 a2 a3
1  3  6  8
2  3  6  8
3  2  5  7
4  2  5  7
5  1  2  5
6  1  2  5
7  2  5  3
8  2  4  1

Now i need to group the data by a3, a2 and a1. So, in aa, the rows 1 and 2 will be in one group, and row 3 and 4 will be in one group as well. Now I need to give every group an index, which starts from 1. So, the data should look like below:

> aa
  a1 a2 a3   Index
1  3  6  8    1
2  3  6  8    1
3  2  5  7    2
4  2  5  7    2
5  1  2  5    3
6  1  2  5    3
7  2  5  3    4
8  2  4  1    5

So in summarizing, I need to arrange the data in the descending order first, then group it, then give every group an index starting from 1. Could anyone help me out here?

r indexing group-by sorting
2 Answers

We could potentially use group_indices, but that would also have a reordering issue. Instead, an option is to paste (or str_c - from stringr) on the columns of interest and then match with unique values of pasted string

library(dplyr)
library(stringr)
aa %>%
   mutate(Index = str_c(a1, a2, a3), 
          Index = match(Index, unique(Index)))

Or instead of arrangeing separately, use it with across

library(tidyr)
a %>% 
   arrange(across(a1:a3, desc)) %>% 
   unite(Index, everything(), remove = FALSE) %>% 
   mutate(Index = match(Index, unique(Index)))

Or with .GRP in data.table

library(dplyr)
setDT(aa)[, Index := .GRP, .(a1, a2, a3)]
aa
#   a1 a2 a3 Index
#1:  3  6  8     1
#2:  3  6  8     1
#3:  2  5  7     2
#4:  2  5  7     2
#5:  1  2  5     3
#6:  1  2  5     3
#7:  2  5  3     4
#8:  2  4  1     5
user_37322710

Base R: 

a_ordered <- with(a, a[rev(order(a1, a2, a3)), ])
a_ordered$idx <- with(a_ordered,
                      cumsum(abs(c(
                        0,
                        diff(as.integer(factor(paste0(
                          a1, a2, a3
                        ))))
                      ))) + 1)

Data: 

a <- data.frame(
  a1 = c(2, 2, 1, 1, 2, 2, 3, 3),
  a2 = c(5, 4, 2, 2, 5, 5, 6, 6),
  a3 = c(3, 1, 5, 5, 7, 7, 8, 8)
)
user_92031580
Related