如何在dplyr中使用distinct()处理多变量及并列情况？

问题背景 #

首次使用dplyr的distinct()函数，需要基于多变量使用该函数并处理并列情况：

• 针对label_18（无并列）运行原代码可得到预期结果；
• 针对label_7（eleCnt和grpID存在并列）运行原代码时，会丢失并列的行，不符合期望；
• 新增label_15数据框，同样存在Element的并列情况，需要保留并列行并给予相同排名。

原代码运行label_7得到的结果：

Element Group   eleCnt   grpID grpRnk
  <chr>   <dbl>    <int>   <int>  <int>
1 R           1        1       3      1
2 R           2        3       7      2

期望label_7的输出：

Element Group   eleCnt   grpID grpRnk   Explain grpRnk column...
  <chr>   <dbl>    <int>   <int>  <int>
1 R           1        1       3      1   Ranked 1st since it has lowest eleCnt & lowest grpID
2 X           3        1       3      1   Also ranked 1st since it ties with above
3 R           2        3       7      2   Ranked 2nd since its eleCnt is 2nd and its grpRnk is 2nd

label_15期望输出：

Element Group eleCnt grpID grpRank
  <chr>   <dbl>  <dbl> <dbl>   <int>
1 R           1      1     3       1
2 X           2      1     3       1
3 X           3      3     7       2

原代码 #

library(dplyr)

label_7 <- data.frame(Element=c("B","R","R","R","R","B","X","X","X","X","X"),
                      Group = c(0,1,1,2,2,0,3,3,0,0,0),
                      eleCnt = c(1,1,2,3,4,2,1,2,3,4,5),
                      grpID = c(0,3,3,7,7,0,3,3,0,0,0))

label_18 <- data.frame(Element = c("R","R","R","X","X","X","X","B","B","R","R","R","R"),
                       Group = c(3,3,3,4,4,4,4,2,2,1,1,2,2),
                       eleCnt = c(1,2,3,1,2,3,4,1,2,4,5,6,7),
                       grpID = c(6,6,6,10,10,10,10,3,3,9,9,13,13))

label_15 <- data.frame(Element = c("B","R","R","R","X","X","X","X"),
                       Group = c(0,1,1,0,2,2,3,3),
                       eleCnt = c(1,1,2,3,1,2,3,4),
                       grpID = c(0,3,3,0,3,3,7,7))

# 原处理代码
label_7 %>% select(Element,Group,eleCnt,grpID) %>% 
  filter(Group > 0) %>% 
  group_by(Element,Group) %>% 
  slice(which.min(Group)) %>% 
  ungroup() %>%
  distinct(eleCnt,grpID, .keep_all = TRUE) %>%
  arrange(eleCnt,grpID) %>%
  mutate(grpRnk = 1:n())

解决方案 #

distinct()的作用是去除重复行，这会直接丢失并列的记录，不符合需求。正确的做法是：

1. 保留所有符合条件的行（不去重）；
2. 使用dense_rank()函数计算排名，该函数会给并列的组分配相同的排名，且后续排名不会跳跃。

修改后的通用处理代码：

process_data <- function(df) {
  df %>% 
    select(Element, Group, eleCnt, grpID) %>% 
    filter(Group > 0) %>% 
    group_by(Element, Group) %>% 
    slice(which.min(Group)) %>% 
    ungroup() %>%
    arrange(eleCnt, grpID) %>%
    mutate(grpRnk = dense_rank(paste(eleCnt, grpID)))
}

测试结果 #

处理label_7

process_data(label_7)

输出：

Element Group eleCnt grpID grpRnk
  <chr>   <dbl>  <int> <int>  <int>
1 R           1      1     3      1
2 X           3      1     3      1
3 R           2      3     7      2

处理label_18

process_data(label_18)

输出：

Element Group eleCnt grpID grpRnk
  <chr>   <dbl>  <int> <int>  <int>
1 B           2      1     3      1
2 R           3      1     6      2
3 X           4      1    10      3
4 R           1      4     9      4
5 R           2      6    13      5

与原预期一致。

处理label_15

process_data(label_15)

输出：

Element Group eleCnt grpID grpRnk
  <chr>   <dbl>  <dbl> <dbl>  <int>
1 R           1      1     3      1
2 X           2      1     3      1
3 X           3      3     7      2

符合期望。

关键说明 #

• 移除distinct(eleCnt, grpID, .keep_all = TRUE)：因为distinct会删除重复的eleCnt+grpID组合，而我们需要保留每个组合下的所有不同Element+Group行；
• 使用dense_rank(paste(eleCnt, grpID))：通过将eleCnt和grpID拼接成字符串作为排名依据，确保相同组合得到相同排名，且排名连续不跳跃；
• 保留原有数据过滤和分组逻辑：filter(Group>0)和group_by(Element,Group) %>% slice(which.min(Group))确保只保留每个Element+Group组的最小Group记录。

内容的提问来源于stack exchange，提问作者Village.Idyot